Reconciling Cisco SmartNet Report and Orion Serial Numbers, Part 3
Combining Data, Reports, Updating Custom Properties
At this point, we have made tables and refined the data from two sources, Orion and the Cisco Total Care Spreadsheet. If you got here with looking at Parts 1 and 2, please read through those first.
[=== COMBINING THE DATA ===]
use DevWishSmartNet
Re-acquaint yourself with the data.
Select * from [dbo].[CiscoInventory]
Select * from [dbo].[OrionNodesSN]
Try this SELECT statement with each of the three WHERE clauses below it. We are basically looking to see where the data matches between Cisco and Orion. Everything may match. If so, you have nothing to do. If there are discrepancies, then run the rest of this page to combine the data and create the reports.
SELECT [O].[IP_Address]
,[CI].[IPAddress]
,[O].[SysName]
,[CI].[SNMPName]
,[O].[OrionSurveyedSerialNumber]
,[CI].[SerNum]
,[O].[SNMatchedOnSysName]
FROM [dbo].[OrionNodesSN][O]
JOIN [dbo].[CiscoInventory][CI] ON [O].[OrionSurveyedSerialNumber]=[CI].[SerNum]
WHERE [O].[SysName]=[CI].[SNMPName]
--WHERE NOT [O].[SysName]=[CI].[SNMPName]
--WHERE NOT [O].[IP_Address]=[CI].[IPAddress]
Check for a specific serial number, as needed.
SELECT * FROM [dbo].[OrionNodesSN][O] WHERE [O].[OrionSurveyedSerialNumber]='###########'
SELECT * FROM [dbo].[CiscoInventory][CI] WHERE [CI].[SerNum]='###########'
This set of Updates will set boolean results (yes) for if devices match between Cisco and Orion. We want to see two matches: Serial number and IP address, Serial number and SysName/SNMPname, or IP Address and SysName/SNMPname. If just one matches, we don't necessarily know we have a match. If there are 2 matches, then we are confident we have a match between devices. These are data sets we will use in the Reports.
BEGIN TRAN
Update [O]
SET [O].[SNMatchedOnSysName]='yes'
,[O].[SmartnetCovered]=[CI].[CoverageStatus]
FROM [dbo].[OrionNodesSN][O]
JOIN [dbo].[CiscoInventory][CI] ON [O].[OrionSurveyedSerialNumber]=[CI].[SerNum] AND [O].[SysName]=[CI].[SNMPName]
Update [O]
SET [O].[SNMatchedOnIP]='yes'
,[O].[SmartnetCovered]=[CI].[CoverageStatus]
FROM [dbo].[OrionNodesSN][O]
JOIN [dbo].[CiscoInventory][CI] ON [O].[OrionSurveyedSerialNumber]=[CI].[SerNum] AND [O].[IP_Address]=[CI].[IPAddress]
Update [O]
SET [O].[MatchIPSysName]='yes'
,[O].[SmartnetCovered]=[CI].[CoverageStatus]
FROM [dbo].[OrionNodesSN][O]
JOIN [dbo].[CiscoInventory][CI] ON [O].[IP_Address]=[CI].[IPAddress] AND [O].[SysName]=[CI].[SNMPName]
Check your work.
SELECT * FROM [dbo].[OrionNodesSN]
Then Update or Rollback.
-- COMMIT TRAN
-- ROLLBACK TRAN
[=== THE REPORTS ===]
Here is the fruit of all this. These reports clearly outline differences in the data. Using the results from this, you should be able to see places where the Cisco inventory scanning tool is missing devices, or devices that need to be added into Orion, or devices that you don’t know whether or not they are covered by SmartNet. Additionally, if you keep custom properties in Orion, you can populate information regarding SmartNet coverage into those fields by using these results.
The key concept here is thus. If we have a singular match on IP address, or node name, or serial number, does that mean that we have matched two devices? Not necessarily. But what about the serial number? If the serial number matches, then isn’t it a match, even if the node name and IP address do not match? If you find that case, you should definitely look at it to determine why the serial number matches but Orion thinks it has a different IP address and name than Cisco. Usually, you should have at least 2 items matching between Orion and Cisco for the same device.
Often you will set Orion to poll a device on its management address. The Cisco scanning tool however, uses CDP to determine neighbor relationships. Some devices have a command which you can set which interface to use for sending CDP results, but not all Cisco OS have that feature. So, you could match on host name and serial number for the same device, but not on IP Address. If you have SmartNet on your devices, those devices may stay in service a long time. During that time, who know what happens, mergers, acquisitions, re-naming schemes. The Cisco database seems to keep the old name (in my experience) even when the node name changes. This makes for difficulty matching devices if both the host name and the ip address don’t match. However, the Cisco (and Orion) results for the system name obtained via SNMP should have the same results. I want to thank Stephen from Loop1 for that insight. So, we match on System name. Nearly all devices then, should have a match on sysname-SNMPName, and Serial number.
-No-Pairs Orion Devices Report-
This report shows devices in OrionNodesSN table which do not have a 2-out-of-3 Serial Number/Caption/IP Address pairing from Orion does not match Cisco's spreadsheet. This could mean: Cisco does not know of the device; or, Cisco has a different IP address and caption for the same serial number; or, it may be in the All Equipment sheet not in the Covered or Not Covered sheets. It also could mean that Orion did not find a serial number, and the IP address/SNMPName pairs do not match. This report should yield zero results under normal conditions.
SELECT [O].[IP_Address]
,[O].[SysName]
,[O].[OrionSurveyedSerialNumber]
FROM [dbo].[OrionNodesSN][O]
WHERE NOT ([O].[MatchIPSysName]='yes' OR [O].[SNMatchedOnSysName]='yes' OR [O].[SNMatchedOnIP]='yes')
GROUP BY [O].[IP_Address] ,[O].[SysName] ,[O].[OrionSurveyedSerialNumber]
ORDER BY [O].[SysName]
-No-Pairs Cisco Devices Report-
These are devices in the Cisco Spreadsheet that did not match the Orion Tables on any of three iterations of pair-matchings. Basically, it is similar as above, except for it is viewed from the Cisco side. Results from this report could indicate devices removed from service both from Orion and Cisco (Coverage=No). Note, coverage=No could be in error. Or, it could be devices that need to be removed from Smartnet because that are not in use anymore. These could be devices that are in service but did not match IP address or Caption due to different data. These could also be in-service devices which Orion does not know about (need to add devices to Orion). The report looks quite different from the above because the Boolean checks we mead earlier were saved to an Orion table, not to the Cisco table. We could use left-join and then refer to the Boolean checks, or we could left-join and just do the Boolean checks again. I chose the later.
;WITH
NoMat_SerN_IP AS (
SELECT [SNMPName], [IPAddress], [SerNum]
FROM [dbo].[CiscoInventory][CI]
LEFT JOIN [dbo].[OrionNodesSN][O]
ON [O].[OrionSurveyedSerialNumber]=[CI].[SerNum]
AND [O].[IP_Address]=[CI].[IPAddress]
WHERE [O].[IP_Address] IS NULL ),
NoMat_SerN_Nm AS (
SELECT [SNMPName], [IPAddress], [SerNum]
FROM [dbo].[CiscoInventory][CI]
LEFT JOIN [dbo].[OrionNodesSN][O]
ON [O].[OrionSurveyedSerialNumber]=[CI].[SerNum]
AND [O].[SysName]=[CI].[SNMPName]
WHERE [O].[SysName] IS NULL ),
NoMat_IP_Nm AS (
SELECT [SNMPName], [IPAddress], [SerNum]
FROM [dbo].[CiscoInventory][CI]
LEFT JOIN [dbo].[OrionNodesSN][O]
ON [O].[IP_Address]=[CI].[IPAddress]
AND [O].[SysName]=[CI].[SNMPName]
WHERE [O].[SysName] IS NULL )
SELECT * FROM NoMat_SerN_Nm EXCEPT ( SELECT * FROM NoMat_SerN_IP )
UNION
SELECT * FROM NoMat_SerN_IP EXCEPT ( SELECT * FROM NoMat_IP_Nm )
UNION
SELECT * FROM NoMat_IP_Nm EXCEPT (SELECT * FROM NoMat_SerN_Nm )
-Devices in NPM w/o Serial Number Report-
This report shows devices in OrionNodesSN table which did not match CiscoInventory due to Orion did not have a serial# for the device. For these, we should rediscover in NPM, and reinventory in NCM. Then any that are still left, we should manually log-in, obtain the SN, and manual verify. May be helpful to store Serial Numbers in a Custom Property. Report to Solarwinds the make and version of the device so they can improve serial number collection for the future.
SELECT
[O].[IP_Address]
,[O].[SysName]
,[CI].[SNMPName]
,[CI].[IPAddress]
FROM [dbo].[OrionNodesSN][O]
JOIN [dbo].[CiscoInventory][CI] ON [O].[IP_Address]=[CI].[IPAddress] AND [O].[SysName]=[CI].[SNMPName]
WHERE [O].[OrionSurveyedSerialNumber] IS NULL
--GROUP BY [O].[IP_Address] ,[O].[SysName] ,[O].[OrionSurveyedSerialNumber]
ORDER BY [O].[SysName]
-Same SN, Different IP and Name Report-
This report shows devices in OrionNodesSN table which do not match CiscoInventory. While the Serial Numbers do match, both the IP Address and Hostname do not match. This query should yeild no results. If it does, investigate Hostname or IP Address matching: Why does hostname not match? Why does IP address not match?
SELECT
[OrionNodeID]=[O].[NodeID]
,[O].[IP_Address]
,[O].[SysName]
,[O].[OrionSurveyedSerialNumber]
,[CI].[IPAddress]
,[CI].[SNMPName]
,[CI].[SerNum]
FROM [dbo].[OrionNodesSN][O]
JOIN [dbo].[CiscoInventory][CI] ON [O].[OrionSurveyedSerialNumber]=[CI].[SerNum]
WHERE [O].[OrionSurveyedSerialNumber] IS NOT NULL
AND NOT [O].[IP_Address]=[CI].[IPAddress]
AND NOT [O].[SysName]=[CI].[SNMPName]
ORDER BY [ON].[SysName]
-Unknown Coverage Report-
This report shows serial numbers are known in Orion, and in Cisco All Equipment, but are found in neither Covered nor Not Covered Spreadsheets. The results are devices in Orion which we cannot tell if the device has SmartNet coverage or not. Since the Serial numbers are in Cisco All Equipment (AE) but not in either SNC or SNNC, then we cannot tell if it is covered or not.
SELECT
[Orion Name]=[O].[SysName]
,[Orion IP]=[O].[IP_Address]
,[Orion Ser#]=[O].[OrionSurveyedSerialNumber]
FROM [dbo].[OrionNodesSN][O]
JOIN (SELECT *
FROM (SELECT [ae].[SerNum] FROM [dbo].[SmartNetAllEquipment][ae]
EXCEPT SELECT [snc].[SerNum] FROM [dbo].[SmartNetCovered][snc])[x]
EXCEPT SELECT [snnc].[SerNum] FROM [dbo].[SmartNetNotCovered][snnc])[y]
ON [O].[OrionSurveyedSerialNumber]=[y].[SerNum]
Need proof? Pop a serial number from the results in the 3 lines below, and run the query.
SELECT * FROM [dbo].[SmartNetAllEquipment][ae] WHERE [SerNum]='XXXXXXXXXXX'
SELECT * FROM [dbo].[SmartNetCovered][snc] WHERE [SerNum]='XXXXXXXXXX'
SELECT * FROM [dbo].[SmartNetNotCovered][snnc] WHERE [SerNum]='XXXXXXXXXX'
-Serial Numbers Not in Cisco Report-
These serial numbers are known in Orion, but are not in any of the Cisco spreadsheets: All Equipment, Covered or Not Covered. The results show devices in Orion which do not match any serial# in Cisco spreadsheet, and do not match Name and IP address. Are these devices obtained outside Cisco official channels? Do these devices need to be added to Cisco SmartNet?
SELECT [O].[SysName]
,[O].[IP_Address]
,[z].[SerNum]
,[O].[MatchIPSysName]
,[O].[SNMatchedOnSysName]
,[O].[SNMatchedOnIP]
FROM (
SELECT
-- [x].[SysName]
--,[x].[IP_Address]
[SerNum]=[x].[OrionSurveyedSerialNumber]
--,[x].[MatchIPSysName]
--,[x].[SNMatchedOnSysName]
--,[x].[SNMatchedOnIP]
FROM [dbo].[OrionNodesSN][x]
WHERE [x].[OrionSurveyedSerialNumber] IS NOT NULL
EXCEPT SELECT Distinct *
FROM (SELECT [SerNum]
FROM [dbo].[SmartNetAllEquipment]
UNION SELECT [SerNum]
FROM [dbo].[SmartNetCovered]
UNION SELECT [SerNum]
FROM [dbo].[SmartNetNotCovered])[y]
) [z] JOIN [dbo].[OrionNodesSN][O] ON [z].[SerNum]=[O].[OrionSurveyedSerialNumber]
ORDER BY [O].[SysName]
-Same IP/Name-Different Serial# Report-
This report shows devices in Orion which have a serial number, and match IP and name, but do not match the serial number in Cisco Spreadsheet. Are they unsupported switches in a stack? Has the device been replaced but re-inventory and rediscovery not done yet? It is important when replacing a device with a new device or a warrant replacement, that you do a re-inventory/rediscovery.
Select
[O].[SysName]
,[O].[IP_Address]
,[O].[OrionSurveyedSerialNumber]
,[O].[SNMatchedOnSysName]
,[O].[SNMatchedOnIP]
,[O].[MatchIPSysName]
FROM [dbo].[OrionNodesSN][O]
WHERE [O].[OrionSurveyedSerialNumber] IS NOT NULL
AND [O].[MatchIPSysName]='yes'
AND [O].[SNMatchedOnSysName] IS NULL
AND [O].[SNMatchedOnIP] IS NULL
ORDER BY [O].[IP_Address]
[=== Update Custom Properties for SmartNet Covered Nodes ===]
You could populate the remaining fields in [OrionNodesSN] with data you know, current date, data from [SmartNetCovered] and [CiscoInventory]. It's always wise to verify your data before making changes, so this query will show what will be updated. If you updated directly from [SmartNetCovered] table, you could get duplicates (remember in the Cisco Import document, we looked for active rows and signed rows if active didn't exist? We will do the same here as part of this update.
So, we're going to want to get support contact information from SNC, and product information from CI.
SELECT [O].[NodeID]
,[O].[SysName]
,[O].[IP_Address]
,[Manufacturer] ='Cisco'
,[Model] =[CI].[ProductID]
,[CiscoProductID] =[CI].[ProductID]
,[CiscoProductFamily]=[CI].[ProductFamily]
,[SuptContrEMail] ='cs-support@cisco.com'
,[SuptContrPhone] ='1-800-553-2447'
,[SuptContrWebsite] ='http://support.cisco.com';
,[SuptContrProvider] =[x].[VAR]
,[SuptContrNumber] =[x].[Contract#]
,[SuptContrEndDate] =[x].[Expiration]
,[SuptContrSLA] =[x].[SLA]
,[ModifiedDt] =CONVERT (date, GETDATE())
,[ModifiedBy] ='username'
,[SmartnetCovered] =[CI].[CoverageStatus]
FROM [dbo].[OrionNodesSN][O]
JOIN [CiscoInventory][CI] ON [O].[OrionSurveyedSerialNumber]=[CI].[SerNum]
JOIN (SELECT
[SNC].[VAR]
,[SNC].[Contract#]
,[SNC].[Expiration]
,[SNC].[SLA]
,[SNC].[SerNum]
FROM [dbo].[SmartNetCovered][SNC]
WHERE NOT [SNC].[CoverageStatus] ='SIGNED'
UNION(SELECT
[T].[VAR]
,[T].[Contract#]
,[T].[Expiration]
,[T].[SLA]
,[T].[SerNum]
FROM [dbo].[SmartNetCovered][T]
JOIN ( SELECT [S].[SerNum]
FROM [dbo].[SmartNetCovered][S]
WHERE [S].[CoverageStatus] ='SIGNED'
EXCEPT
SELECT [U].[SerNum]
FROM [dbo].[SmartNetCovered][U]
WHERE [U].[CoverageStatus] ='ACTIVE'
) [H] ON [T].[SerNum] = [H].[SerNum]
)
)[x] ON [CI].[SerNum] = [x].[SerNum]
WHERE [O].[MatchIPSysName] ='yes'
OR [O].[SNMatchedOnSysName] ='yes'
OR [O].[SNMatchedOnIP] ='yes'
Once you are satisfied with the above, use the query as the basis to copy the data to the [OrionNodesSN] table. Then check your work and commit or roll back.
BEGIN TRAN
UPDATE [O]
SET [O].[Manufacturer] ='Cisco'
,[O].[Model] =[CI].[ProductID]
,[O].[CiscoProductID] =[CI].[ProductID]
,[O].[CiscoProductFamily]=[CI].[ProductFamily]
,[O].[SuptContrProvider]=[x].[VAR]
,[O].[SuptContrNumber] =[x].[Contract#]
,[O].[SuptContrEMail] ='cs-support@cisco.com'
,[O].[SuptContrPhone] ='1-800-553-2447'
,[O].[SuptContrWebsite] ='http://support.cisco.com';
,[O].[SuptContrEndDate] =[x].[Expiration]
,[O].[SuptContrSLA] =[x].[SLA]
,[O].[ModifiedDt] =CONVERT (date, GETDATE())
,[O].[ModifiedBy] ='username'
FROM [dbo].[OrionNodesSN][O]
JOIN [CiscoInventory][CI] ON [O].[OrionSurveyedSerialNumber]=[CI].[SerNum]
JOIN (SELECT
[SNC].[VAR]
,[SNC].[Contract#]
,[SNC].[Expiration]
,[SNC].[SLA]
,[SNC].[SerNum]
FROM [dbo].[SmartNetCovered][SNC]
WHERE NOT [SNC].[CoverageStatus] ='SIGNED'
UNION(SELECT
[T].[VAR]
,[T].[Contract#]
,[T].[Expiration]
,[T].[SLA]
,[T].[SerNum]
FROM [dbo].[SmartNetCovered][T]
JOIN ( SELECT [S].[SerNum]
FROM [dbo].[SmartNetCovered][S]
WHERE [S].[CoverageStatus] ='SIGNED'
EXCEPT
SELECT [U].[SerNum]
FROM [dbo].[SmartNetCovered][U]
WHERE [U].[CoverageStatus] ='ACTIVE'
) [H] ON [T].[SerNum] = [H].[SerNum]
)
)[x] ON [CI].[SerNum] = [x].[SerNum]
WHERE [O].[MatchIPSysName] ='yes'
OR [O].[SNMatchedOnSysName] ='yes'
OR [O].[SNMatchedOnIP] ='yes'
Check your work.
SELECT *
FROM [OrionNodesSN][O]
WHERE [O].[SmartnetCovered] ='Active'
ORDER BY [O].[NodeID]
-- COMMIT TRAN
-- ROLLBACK TRAN
If you have custom properties for support information, you could use data from this report to update it. Note, it is usually okay to update custom properties using SQL query. I suggest you know your fields. If the field is limited to specific data, make sure the data you are writing matched the field requirements. The queries below assumes that the Orion database is named NetPerfMon. Change this to your Orion database name.
First build the query to verify all is good. Since we already copied one line data per serial number (active vs signed) into OrionNodesSN, and we are pulling data from there, now we just need to identify unique instances of NodeIDs (not serial numbers) to copy into custom properties. This is bacause NPM lists nodes on a logical basis. Five switches in a single stack get one entry in NPM, and hence has one set of Nodes Custom Properties.
SELECT DISTINCT [O].[NodeID]
,[SmartNet] = [O].[SmartNetCovered]
,[SuptContrProvider]= [O].[SuptContrProvider]
,[SuptContrNumber] = [O].[SuptContrNumber]
,[SuptContrEMail] = [O].[SuptContrEMail]
,[SuptContrPhone] = [O].[SuptContrPhone]
,[SuptContrWebsite] = [O].[SuptContrWebsite]
,[SuptContrEndDate] = [O].[SuptContrEndDate]
,[SuptContrSLA] = [O].[SuptContrSLA]
FROM [NetPerfMon].[dbo].[NodesCustomProperties][cp]
JOIN [dbo].[OrionNodesSN][O] ON [cp].[NodeID]=[O].[NodeID]
WHERE ([O].[SmartnetCovered] ='Active' OR [O].[SmartnetCovered] ='Signed')
AND ([O].[SNMatchedOnSysName] ='yes' OR [O].[SNMatchedOnIP] ='yes')
ORDER BY [O].[NodeID]
Once you are satisfied with the query, create to update, review and commit/rollback.
BEGIN TRAN
Update [cp]
SET [cp].[SmartNet] = [O].[SmartNetCovered]
,[cp].[SuptContrProvider] = [O].[SuptContrProvider]
,[cp].[SuptContrNumber] = [O].[SuptContrNumber]
,[cp].[SuptContrEMail] = [O].[SuptContrEMail]
,[cp].[SuptContrPhone] = [O].[SuptContrPhone]
,[cp].[SuptContrWebsite] = [O].[SuptContrWebsite]
,[cp].[SuptContrEndDate] = [O].[SuptContrEndDate]
,[cp].[SuptContrSLA] = [O].[SuptContrSLA]
FROM [NetPerfMon].[dbo].[NodesCustomProperties][cp]
JOIN [dbo].[OrionNodesSN][O] ON [cp].[NodeID]=[O].[NodeID]
WHERE ([O].[SmartnetCovered] ='Active' OR [O].[SmartnetCovered] ='Signed')
AND ([O].[SNMatchedOnSysName] ='yes' OR [O].[SNMatchedOnIP] ='yes')
Check your work.
SELECT * FROM [NetPerfMon].[dbo].[NodesCustomProperties][cp]
JOIN [NetPerfMon].[dbo].[NodesData][n] ON [n].[NodeID]=[cp].[NodeID]
WHERE [n].[Vendor]='Cisco'
-- COMMIT TRAN
-- ROLLBACK TRAN
[=== Where to go Next ===]
After writing all this up, it seems like there could have been easier ways to do this. For example, the process might be changed to move the Active/Signed query to point when copying data from the three Cisco tables (Covered, Not Covered, All) to the CiscoInventory table. Perhaps some additional fields could be placed in the Cisco Inventory Table, to make comparison between Cisco and Orion easier. There were also unused fields copied from Orion. For certain clean-up tasks, or if there are devices that need to be found (for example, name and IP address don’t match, but serial number does), then perhaps those fields might com in handy.
You probably have some good ideas about more updated or report you can get from this data. Or, perhaps, better formatting of reports. If so, please share your thoughts below.
Regards, Eric Bradford
