I ran across a interesting item in NCM today, but first a bit of background. I am working on a way to reconcile the spreadsheet we get from Cisco regarding Smartnet coverage with our NPM nodes. Unfortunately, not all the node names that Cisco reports are the same as are on the devices today. [This is due to the history of merged companies]. Also, not all IP addresses that were reported to Cisco are the same addresses that NPM manages nodes on. So, we don't have an easy way to reconcile the two.
So my thought is to parse the Cisco spreadsheet, and match the IP addresses that do match, and then search for the IP addresses that do not match -- in the NCM Config Archive. Basically, I would identify the most recent config for each node, and then search for the Regular Expression for an IP address being assigned in an interface. I should be able to map the Cisco Smartnet addresses with the addressing in the configs and then I have the ability to cross-reference. I know, you are wondering what this has to do with APs. Read on...
So, I wanted to check my work and verify that I was getting only one config per node, and that I was getting a config for each node. And that was when I noticed that there was about 200 count difference between nodes in NCM and nodes with configs in NCM. How could I have 200 nodes in NCM without configs? The answer is that some device never have downloaded a config -- so it looks like I now have something to occupy my time between 12/26 and 12/31. Yippee (sound that like Garfield the cat). But, of those, many of them are APs. Now, I don't know the full history of Solarwinds at this company. I don't knw if they were manually added, or if they were pulled in from the WLCs. We do use WLCs here. I can't imagine any reason why we would have APs in NCM when we have WLCs.
Can anyone think of a good reason to keep APs in NCM? I'm inclined to delete them.
.
.
.
So, some of you SQL script kiddies are probably wanting to see my script. Here it is -- this tells me which nodes are in NCM but have no configs.
SELECT [x].*
FROM [dbo].[NCM_Nodes] [x]
JOIN (
SELECT [NodeIDA] = [a].[NodeID],[NodeIDB]=[b].[NodeID]
FROM [dbo].[NCM_Nodes] [a]
LEFT OUTER JOIN (
SELECT [NodeID]
FROM [dbo].[NCM_ConfigArchive] [c] WITH (NOLOCK)
GROUP BY [NodeID]
) [b] ON ([a].[NodeID]=[b].[NodeID])
) [y] ON ([x].[NodeID]=[y].[NodeIDA])
WHERE [y].[NodeIDB] IS NULL
If you are interested in seeing the most recent config for each node, then this is the query:
SELECT
[x].*
,[y].[Config]
FROM(SELECT TOP 1000
[Date&Time]=MAX([a].[DownloadTime])
,[n].[CoreNodeID]
,[n].[NodeCaption]
,[n].[AgentIP]
,[a].[NodeID]
FROM [dbo].[NCM_ConfigArchive][a] WITH (NOLOCK)
JOIN [dbo].[NCM_Nodes][n] ON [n].[NodeID]=[a].[NodeID]
GROUP BY [a].[NodeID],[n].[CoreNodeID],[n].[NodeCaption],[n].[AgentIP]
Order by [Date&Time])[x]
JOIN [dbo].[NCM_ConfigArchive][y] ON [x].[Date&Time]=[y].[DownloadTime] AND [x].[NodeID]=[y].[NodeID]