Reconciling Cisco SmartNet Report and Orion Serial Numbers - Part 2
Importing Data From Orion
If this is your first time going through this process, go through the “Cisco-Orion Reconciliation Step 1 Import Cisco Data.docx” file first. You must create the database first. After that is created, it doesn’t matter which of these two are run first.
EXTREMELY IMPORTANT: Do not change the anything in the NodesData, NCM_NodeProperties, StackedSwitchMembers (etc.) tables of your Solarwinds Database. Make sure you've backed up your SW database just in case you make a mistake.
Import three tables from Solarwinds database. The basic information we need are just Node ID, Node Name, IP Address, and Serial Numbers. You may import more if you want, such as custom properties which may help in determining unresolved nodes. The data you import will be per node ID, not per Serial Number. Since Cisco organized by serial number and NPM organizes by NodeID, some manipulation will be needed.
[== Populate NPM Data into OrionImport ==]
USE DevWishSmartNet
Creates a table and imports data from NodesData and NodesCustomProperties tables. If you have already created the table, skip this step.
SELECT
[nd].[NodeID]
,[nd].[IP_Address]
,[nd].[Caption] -- Device name as was manually entered into Orion
,[nd].[SysName] -- Device name according to SNMP query
,[nd].[MachineType]
,[cp].[SerialNumber]
,[nd].[Vendor]
,[cp].[Airport_Code]
,[cp].[SuptContrProvider]
,[cp].[SuptContrNumber]
,[cp].[SuptContrEMail]
,[cp].[SuptContrPhone]
,[cp].[SuptContrWebsite]
,[cp].[SuptContrEndDate]
,[cp].[SuptContrSLA]
INTO [DevWishSmartNet].[dbo].[OrionImport]
FROM [NetPerfMon].[dbo].[NodesData][nd]
LEFT JOIN [NetPerfMon].[dbo].[NodesCustomProperties][cp] ON [nd].[NodeID]=[cp].[NodeID]
WHERE [Vendor] LIKE 'Cisco'
Check your work.
SELECT * FROM [DevWishSmartNet].[dbo].[OrionImport]
If you just did the previous part (this is the first time you are doing this), then skip this step. Otherwise, if the table already exists, then truncate the old information, and copy new information into it. This would be for subsequent iterations (maybe you do this monthly).
TRUNCATE TABLE [DevWishSmartNet].[dbo].[OrionImport]
INSERT INTO [DevWishSmartNet].[dbo].[OrionImport] (
[NodeID]
,[IP_Address]
,[Caption]
,[SysName]
,[MachineType]
,[SerialNumber]
,[Vendor]
,[Airport_Code]
,[SuptContrProvider]
,[SuptContrNumber]
,[SuptContrEMail]
,[SuptContrPhone]
,[SuptContrWebsite]
,[SuptContrEndDate]
,[SuptContrSLA] )
SELECT [nd].[NodeID]
,[IP_Address]
,[Caption] -- Device name as was manually entered into Orion
,[SysName] -- Device name according to SNMP query
,[MachineType]
,[SerialNumber]
,[Vendor]
,[Airport_Code]
,[SuptContrProvider]
,[SuptContrNumber]
,[SuptContrEMail]
,[SuptContrPhone]
,[SuptContrWebsite]
,[SuptContrEndDate]
,[SuptContrSLA]
FROM [NetPerfMon].[dbo].[NodesData][nd]
LEFT JOIN [NetPerfMon].[dbo].[NodesCustomProperties][ncp] ON [nd].[NodeID]=[ncp].[NodeID]
WHERE [nd].[Vendor]='Cisco'
AND [nd].[IP_Address] IS NOT NULL
Verify your work.
SELECT * FROM [dbo].[OrionImport]
[== Create a Temp Table of All (desired) Devices with Serial Numbers. ==]
We get to this list by taking all serial numbers and NodeIDs from SwitchStackMember (SSM), then add to that all Physical Entities which are not part of SSM, and then add to that all NodeIDs that are in NPM, but not already added to this list. [Also note, that we are referring to all desired nodes (Cisco devices which have an IP address), not ALL devices in OrionNPM.] Ergo:
Total list of devices per each SN = All SSM + all PhysEnt not is SSM + All NPM not in either SSM or PhysEnt.
In the method below, this equals [Line2a]+ [Line4]+ [Line5a]
Returns Node ID, Node Name, IP Address, Serial Number only – extra fields are not necessary yet. Later, we will join this list with the data we downloaded in order to have a complete database for data per Serial Number.
If you are going through this a second or third… time since the SQL file was run, and hasn’t been closed yet, then you’ll need to drop the temp table so it can be recreated. Otherwise skip the drop line, and just continue. This query is almost 2 pages long, when needs to be run all at the same time. This is a fairly long query, run everything for the next page and a half.
-- DROP TABLE #TargetedNodes
;with
CTE_Line2a AS (
SELECT
[OI].[NodeID]
,[OI].[Caption]
,[OI].[IP_Address]
,[SSM].[SerialNumber]
FROM [DevWishSmartNet].[dbo].[OrionImport][OI]
JOIN [NetPerfMon].[dbo].[NPM_SwitchStackMember][SSM]with(nolock) ON [OI].[NodeID]=[ssm].[NodeID]
GROUP BY [OI].[NodeID] ,[OI].[Caption] ,[OI].[IP_Address] ,[SSM].[SerialNumber] ),
CTE_Line2b AS (
SELECT
[OI].[NodeID]
,[OI].[Caption]
,[OI].[IP_Address]
FROM [DevWishSmartNet].[dbo].[OrionImport][OI]
JOIN [NetPerfMon].[dbo].[NPM_SwitchStackMember][SSM]with(nolock) ON [OI].[NodeID]=[ssm].[NodeID]
GROUP BY [OI].[NodeID],[OI].[Caption],[OI].[IP_Address] ),
CTE_Line3 AS (
SELECT
[OI].[NodeID]
,[OI].[Caption]
,[OI].[IP_Address]
,[Serial]=NULL
FROM [DevWishSmartNet].[dbo].[OrionImport][OI]
LEFT JOIN CTE_Line2b [L2b] ON [OI].[NodeID]=[L2b].[NodeID]
WHERE ([L2b].[NodeID] IS NULL AND [OI].[Vendor] = 'Cisco') ),
CTE_Line4 AS (
SELECT
[NN].[CoreNodeID]
,[NN].[NodeCaption]
,[NN].[AgentIP]
FROM [NetPerfMon].[dbo].[NCM_Nodes][NN]with(nolock)
LEFT JOIN CTE_Line2b[L2b] ON [NN].[CoreNodeID]=[L2b].[NodeID]
WHERE [L2b].[NodeID] IS NULL
AND [NN].[Vendor] = 'Cisco' ),
CTE_Line5a AS (
SELECT [NN].[CoreNodeID]
,[NN].[NodeCaption]
,[NN].[AgentIP]
,[NEP].[Serial]
FROM [NetPerfMon].[dbo].[NCM_Nodes][NN]with(nolock)
JOIN [NetPerfMon].[dbo].[NCM_Entity_Physical][NEP]with(nolock) ON [NN].[NodeID]=[NEP].[NodeID]
where ( class=3 AND NOT Model like 'PA%' AND NOT Model ='' )
GROUP BY [NN].[CoreNodeID] ,[NN].[NodeCaption] ,[NN].[AgentIP] ,[NEP].[Serial] ),
CTE_Line5b AS (
SELECT
[NN].[CoreNodeID]
,[NN].[NodeCaption]
,[NN].[AgentIP]
FROM [NetPerfMon].[dbo].[NCM_Nodes][NN]with(nolock)
JOIN [NetPerfMon].[dbo].[NCM_Entity_Physical][NEP]with(nolock) ON [NN].[NodeID]=[NEP].[NodeID]
WHERE (class=3 AND NOT Model like 'PA%' AND NOT Model ='')
GROUP BY [NN].[CoreNodeID] ,[NN].[NodeCaption] ,[NN].[AgentIP] ),
CTE_Line6 AS (
SELECT
[L4].[CoreNodeID]
,[L4].[NodeCaption]
,[L4].[AgentIP]
FROM CTE_Line4 [L4]
JOIN CTE_Line5b [L5b] ON [L4].[CoreNodeID] = [L5b].[CoreNodeID]
GROUP BY [L4].[CoreNodeID],[L4].[NodeCaption],[L4].[AgentIP] )
SELECT
[L8].[NodeID]
,[L8].[Caption]
,[L8].[IP_Address]
,[L8].[Serial]
INTO #TargetedNodes
FROM (
(SELECT
[L3].[NodeID]
,[L3].[Caption]
,[L3].[IP_Address]
,[L3].[Serial]
FROM CTE_Line3 [L3]
FULL Outer JOIN CTE_Line6 [L6] ON [L3].[NodeID]=[L6].[CoreNodeID]
WHERE [L6].[CoreNodeID] IS NULL )
UNION (SELECT
[L5a].[CoreNodeID]
,[L5a].[NodeCaption]
,[L5a].[AgentIP]
,[L5a].[Serial]
FROM CTE_Line4 [L4]
JOIN CTE_Line5a [L5a] ON [L4].[CoreNodeID] = [L5a].[CoreNodeID])
UNION ALL (SELECT
*
FROM CTE_Line2a)
) [L8]
ORDER BY [L8].[Serial], L8.NodeID
Check your work.
SELECT * FROM #TargetedNodes
[== Create OrionNodesSN table ==]
This table should have all identified nodes, and all serial number, one line per each SN, along with other information in other columns. This will be the table that we use to compare again the Cisco Table. Fewer columns than OrionImport.
Delete the [OrionNodesSN] table if it already exists. If this is your first time running this, skip this line.
DELETE FROM [DevWishSmartNet].[dbo].[OrionNodesSN]
Create table if not created yet. Some of these fields are fields that will receive data from the Cisco tables, or are custom properties that I have created in my Orion for storing vendor support contract information. Feel free to eliminate any fields that don’t apply to your company. There are several points below where you would have to delete (or add) fields.
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [DevWishSmartNet].[dbo].[OrionNodesSN] (
[NodeID] [Integer] NOT NULL,
[Caption] [nvarchar](255) NULL,
[SysName] [nvarchar](255) NULL,
[IP_Address] [nvarchar](50) NULL,
[OrionSurveyedSerialNumber] [nvarchar](255) NULL,
[Vendor] [nvarchar](255) NULL,
[MachineType] [nvarchar](255) NULL,
[Manufacturer] [nvarchar](255) NULL,
[Model] [nvarchar](255) NULL,
[CiscoProductID] [nvarchar](120) NULL,
[CiscoProductFamily] [nvarchar](200) NULL,
[SuptContrProvider] [nvarchar](400) NULL,
[SuptContrNumber] [nvarchar](400) NULL,
[SuptContrEMail] [nvarchar](400) NULL,
[SuptContrPhone] [nvarchar](40) NULL,
[SuptContrWebsite] [nvarchar](400) NULL,
[SuptContrEndDate] [nvarchar](400) NULL,
[SuptContrSLA] [nvarchar](400) NULL,
[CreatedDt] [datetime] NULL,
[CreatedBy] [nvarchar](40) NULL,
[ModifiedDt] [datetime] NULL,
[ModifiedBy] [nvarchar](40) NULL,
[SNMatchedOnIP] [nvarchar](3) NULL,
[SNMatchedOnSysName] [nvarchar](3) NULL,
[MatchIPSysName] [nvarchar](3) NULL,
[SmartnetCovered] [nvarchar](40) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [DevWishSmartNet].[dbo].[OrionNodesSN] ADD CONSTRAINT [DF_OrionNodesSN_SmartnetCovered] DEFAULT (N'unknown') FOR [SmartnetCovered]
GO
Check your work.
Select * from [DevWishSmartNet].[dbo].[OrionNodesSN] ORDER BY Caption
[==Copy Data from #TargetedNodes into OrionNodesSN table ==]
Inserts all #TargetedNodes rows into [OrionNodesSN]. If [OrionNodesSN] is empty, it will copy all rows. If [OrionNodesSN] has data in it, it will copy only new nodes. Truncate the table data if you want to start with fresh data.
-- TRUNCATE TABLE [OrionNodesSN]
INSERT INTO [OrionNodesSN]
([NodeID],
[Caption],
[IP_Address],
[OrionSurveyedSerialNumber])
SELECT
[t].[NodeID]
,[t].[Caption]
,[t].[IP_Address]
,[t].[Serial]
FROM ( SELECT [src].[NodeID]
FROM [#TargetedNodes][SRC]
EXCEPT
SELECT [dst].[NodeID]
FROM [OrionNodesSN][dst]
) [x]
JOIN [#TargetedNodes][t] ON [x].NodeID=[t].[NodeID]
Check your work.
SELECT * FROM [DevWishSmartNet].[dbo].[OrionNodesSN]
[==Copy Data from #TargetedNodes and OrionImport into OrionNodesSN table ==]
Copy the remaining fields data from [OrionImport] table into [OrionNodesSN] table ([Vendor], [MachineType], [SuptContrProvider], [SuptContrNumber], [SuptContrEMail], [SuptContrPhone], [SuptContrWebsite], [SuptContrEndDate], [SuptContrSLA], [DtImportFmOrion]).
You may have noticed several types of populating data into tables. Here is a summary of the types:
"INSERT INTO … SELECT" is used for adding data rows to a defined table,
"UPDATE … SET" is used for updating columns for rows that exist in a table already,
"SELECT… INTO" is used to create a new table from selected data (without indexes).
UPDATE [O]
SET [SysName]=[OI].[SysName]
,[Vendor] = [OI].[Vendor]
,[MachineType]=[OI].[MachineType]
,[SuptContrProvider]=[OI].[SuptContrProvider]
,[SuptContrNumber]=[OI].[SuptContrNumber]
,[SuptContrEMail]=[OI].[SuptContrEMail]
,[SuptContrPhone]=[OI].[SuptContrPhone]
,[SuptContrWebsite]=[OI].[SuptContrWebsite]
,[SuptContrEndDate]=[OI].[SuptContrEndDate]
,[SuptContrSLA]=[OI].[SuptContrSLA]
,[CreatedDt]=GETDATE()
,[CreatedBy]=SUSER_NAME()
FROM [DevWishSmartNet].[dbo].[OrionNodesSN][O] JOIN [DevWishSmartNet].[dbo].[OrionImport][OI] ON [O].[NodeID]=[OI].[NodeID]
WHERE [O].[NodeID]=[OI].[NodeID]
AND [O].[Vendor] IS NULL
Check your work (compare).
SELECT * FROM [DevWishSmartNet].[dbo].[OrionNodesSN] ORCER BY NodeID
SELECT * FROM [DevWishSmartNet].[dbo]. [OrionImport] ORDER BY NodeID
[== Massage Data in OrionTgtNodesSN Table ==]
Convert all node names to uppercase to avoid possible case matching issues between Cisco and Orion. Then, remove domain extensions to suit your company.
Change to all uppercase Letters for Node Caption and SysName.
BEGIN TRAN
UPDATE [a]
SET [a].[Caption] = UPPER ([b].[Caption])
FROM [dbo].[OrionNodesSN] [a]
JOIN(SELECT [Caption]
FROM [dbo].[OrionNodesSN]
GROUP BY [Caption]
) [b] ON [a].[Caption] = [b].[Caption]
UPDATE [a]
SET [a].[SysName] = UPPER ([b].[SysName])
FROM [dbo].[OrionNodesSN] [a]
JOIN(SELECT [SysName]
FROM [dbo].[OrionNodesSN]
GROUP BY [SysName]
) [b] ON [a].[SysName] = [b].[SysName]
(The Commit/Rollback lines are reserved for after removing domain names.)
Remove Domain Names.
DECLARE @fs VARCHAR(100) = '.COMPANY.COM';
DECLARE @ft VARCHAR(100) = '.OLDCO1.COM';
DECLARE @fu VARCHAR(100) = '.OLDCO2.COM';
DECLARE @fv VARCHAR(100) = '.CORP';
DECLARE @fw VARCHAR(100) = '.INTERNAL';
UPDATE [a]
SET [a].[Caption] = REPLACE ([b].[Caption],@fs,'')
FROM [dbo].[OrionNodesSN] [a]
JOIN(SELECT [Caption]
FROM [dbo].[OrionNodesSN]
WHERE [Caption] like '%'+ @fs
GROUP BY [Caption]
) [b] ON [a].[Caption] = [b].[Caption]
UPDATE [a]
SET [a].[Caption] = REPLACE ([b].[Caption],@ft,'')
FROM [dbo].[OrionNodesSN] [a]
JOIN(SELECT [Caption]
FROM [dbo].[OrionNodesSN]
WHERE [Caption] like '%'+ @ft
GROUP BY [Caption]
) [b] ON [a].[Caption] = [b].[Caption]
UPDATE [a]
SET [a].[Caption] = REPLACE ([b].[Caption],@fu,'')
FROM [dbo].[OrionNodesSN] [a]
JOIN(SELECT [Caption]
FROM [dbo].[OrionNodesSN]
WHERE [Caption] like '%'+ @fu
GROUP BY [Caption]
) [b] ON [a].[Caption] = [b].[Caption]
UPDATE [a]
SET [a].[Caption] = REPLACE ([b].[Caption],@fv,'')
FROM [dbo].[OrionNodesSN] [a]
JOIN(SELECT [Caption]
FROM [dbo].[OrionNodesSN]
WHERE [Caption] like '%'+ @fv
GROUP BY [Caption]
) [b] ON [a].[Caption] = [b].[Caption]
UPDATE [a]
SET [a].[Caption] = REPLACE ([b].[Caption],@fw,'')
FROM [dbo].[OrionNodesSN] [a]
JOIN(SELECT [Caption]
FROM [dbo].[OrionNodesSN]
WHERE [Caption] like '%'+ @fw
GROUP BY [Caption]
) [b] ON [a].[Caption] = [b].[Caption]
UPDATE [a]
SET [a].[SysName] = REPLACE ([b].[SysName],@fs,'')
FROM [dbo].[OrionNodesSN] [a]
JOIN(SELECT [SysName]
FROM [dbo].[OrionNodesSN]
WHERE [SysName] like '%'+ @fs
GROUP BY [SysName]
) [b] ON [a].[SysName] = [b].[SysName]
UPDATE [a]
SET [a].[SysName] = REPLACE ([b].[SysName],@ft,'')
FROM [dbo].[OrionNodesSN] [a]
JOIN(SELECT [SysName]
FROM [dbo].[OrionNodesSN]
WHERE [SysName] like '%'+ @ft
GROUP BY [SysName]
) [b] ON [a].[SysName] = [b].[SysName]
UPDATE [a]
SET [a].[SysName] = REPLACE ([b].[SysName],@fu,'')
FROM [dbo].[OrionNodesSN] [a]
JOIN(SELECT [SysName]
FROM [dbo].[OrionNodesSN]
WHERE [SysName] like '%'+ @fu
GROUP BY [SysName]
) [b] ON [a].[SysName] = [b].[SysName]
UPDATE [a]
SET [a].[SysName] = REPLACE ([b].[SysName],@fv,'')
FROM [dbo].[OrionNodesSN] [a]
JOIN(SELECT [SysName]
FROM [dbo].[OrionNodesSN]
WHERE [SysName] like '%'+ @fv
GROUP BY [SysName]
) [b] ON [a].[SysName] = [b].[SysName]
UPDATE [a]
SET [a].[SysName] = REPLACE ([b].[SysName],@fw,'')
FROM [dbo].[OrionNodesSN] [a]
JOIN(SELECT [SysName]
FROM [dbo].[OrionNodesSN]
WHERE [SysName] like '%'+ @fw
GROUP BY [SysName]
) [b] ON [a].[SysName] = [b].[SysName]
Review your data, see if you missed anything. Roll back (below) if there are errors, or Commit the changes, once satisfied.
-- COMMIT TRAN
-- ROLLBACK TRAN
Check your work.
SELECT * FROM [dbo].[OrionNodesSN]
You have completed Step 2. You have imported data from various tables in Orion, and then extracting multiple chassis serial numbers per node ID, as necessary, making sure that none were duplicate. The data was then massaged to ensure there were no case or domain extension matching issues, and then the data wil associated data was copied into a new table.
The next step (3) is “Cisco-Orion Reconciliation Step 3 Combining Data.docx”. In the last step, we will combine data, and create reports. The reports can be used to update Custom Properties in NPM, or determine if changes need to be made to SmartNet coverage, or determine if Nodes need to be re-inventoried or re-discovered .
