Reconciling Cisco SmartNet Report and Orion Serial Numbers - Part 1
Importing Data From Cisco
These three documents cover importing data from Orion and from the Cisco Customer Care report (SmartNet Report), then combining the data and finally running reports. Work on reconciling Cisco and Orion database information is done in a separate database. This done so that we don’t damage the Orion database with our work. So, you need to create a database if not already done, and name it something.
When I was younger, I used to like the Charles Bronson “Death Wish” movie series. When I was thinking of a name for the feature I wished had been developed already, regarding SmartNet, I came up with Development Wish SmartNet. From that came “DevWishSmartNet” as the database name for working outside of the Orion environment to reconcile Orion and SmartNet information. Since it vaguely sounded like a movie I liked at one time, it stuck. Perhaps there is a better name. Feel free to change it, just realize there are many places you will need to change it. You will need to create a database for use with these procedures.
A few notes on the scripts – I have often included “Check you work” scripts. These are optional, but I found helpful in checking my data along the way. Once you are familiar with the processes here, you can skip the checks. I have included scripts for creating tables, and scripts for working with table once created. After you go through this the first time, you won’t need to create the tables again, so skip those steps. Of course, if you make changes to the tables, you would delete the old table a create it new with your changes. If you run this monthly, you will just truncate the old data, but allow the tables to remain, and just copy and manipulate new data.
These files are not intended to run the whole file at a time. However, there is enough here, that once you are confident with the output of the process, you could delete the extraneous scripts that aren’t needed every time you run this, and then run it all at once. Also, the scripts should run sufficiently in Solarwinds Database Manager. However, for projects of this size, I would recommend using Microsoft SQL Server Management Studio.
[==IMPORTING DEVICES FROM SMARTNET WORKBOOK==]
[==CREATE TABLE FOR RAW IMPORT==]
If your non-Orion database already has a table for importing devices from Cisco, clear out the data.
Check for the Data:
SELECT * FROM [DevWishSmartNet].[dbo].[RawCiscoImportCovered]
SELECT * FROM [DevWishSmartNet].[dbo].[RawCiscoImportNotCovered]
Truncate the Data:
TRUNCATE TABLE [DevWishSmartNet].[dbo].[RawCiscoImportNotCovered]
TRUNCATE TABLE [DevWishSmartNet].[dbo].[RawCiscoImportCovered]
Otherwise, you will need to create your table for importing not-covered devices from Cisco. Or just have the import wizard create the table for you. I chose to create this table because I wanted to control the names & sizes of columns. If you have already created the tables, skip this section.
USE [DevWishSmartNet]
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [DevWishSmartNet].[dbo].[RawCiscoImportCovered] (
[Hostname] [nvarchar](120) NULL, -- Hostname
[SNMPName] [nvarchar](120) NULL, -- SNMP Hostname
[IPAddress] [nvarchar](40) NULL, -- IP Address
[SerNum] [nvarchar](40) NULL, -- Serial Number
[ProductID] [nvarchar](120) NULL, -- Product ID
[ProductFamily] [nvarchar](200) NULL, -- Product Family
[EquipmentType] [nvarchar](40) NULL, -- Equipment Type
[Contract#] [nvarchar](40) NULL, -- Contract No.
[Expiration] [datetime] NULL, -- Coverage End
[CoverageStatus] [nvarchar](40) NULL, -- Coverage Status
[SLA] [nvarchar](40) NULL, -- Service Level
[VAR] [nvarchar](120) NULL, -- Customer
[ContractPID] [nvarchar](200) NULL, -- Contract PID
[Address] [nvarchar](200) NULL, -- Address
[City] [nvarchar](40) NULL, -- City
[State] [nvarchar](40) NULL, -- State
[Country] [nvarchar](40) NULL -- Country
) ON [PRIMARY]
Likewise, create your table for importing covered devices from Cisco. Or, just have the import wizard create the table for you. Again, I chose to create a table because I wanted to control the names & sizes of columns. If you have already created the tables, skip this section.
USE [DevWishSmartNet]
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [DevWishSmartNet].[dbo].[RawCiscoImportNotCovered] (
[Hostname] [nvarchar](120) NULL, -- Hostname
[SNMPName] [nvarchar](120) NULL, -- SNMP Hostname
[IPAddress] [nvarchar](40) NULL, -- IP Address
[SerNum] [nvarchar](40) NULL, -- Serial Number
[ProductID] [nvarchar](120) NULL, -- Product ID
[ProductFamily] [nvarchar](200) NULL, -- Product Family
[EquipmentType] [nvarchar](40) NULL, -- Equipment Type
[Address] [nvarchar](200) NULL, -- Address
[City] [nvarchar](40) NULL, -- City
[State] [nvarchar](40) NULL, -- State
[Country] [nvarchar](40) NULL, -- Country
[ReasonNotCovered] [nvarchar](40) NULL, -- Reason Not Covered
[Comments] [nvarchar](80) NULL, -- Comments
[WarrantyType] [nvarchar](40) NULL , -- Warranty Type
[WarrantyStartDate] [datetime] NULL, -- Warranty Start Date
[WarrantyEndDate] [datetime] NULL -- Warranty End Date
) ON [PRIMARY]
If your database already has a RAWCiscoImportAllEquipment table, clear the data out. Otherwise create the table. This is the last import table to make. If you have already created the tables, skip this section (other than truncating previous data).
SELECT * FROM [DevWishSmartNet].[dbo].[RawCiscoImportAllEquipment]
TRUNCATE TABLE [DevWishSmartNet].[dbo].[RawCiscoImportAllEquipment]
USE [DevWishSmartNet]
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [DevWishSmartNet].[dbo].[RawCiscoImportAllEquipment] (
[Hostname] [nvarchar](120) NULL, -- Hostname
[SNMPName] [nvarchar](120) NULL, -- SNMP Hostname
[IPAddress] [nvarchar](40) NULL, -- IP Address
[SerNum] [nvarchar](40) NULL, -- Serial Number
[ProductID] [nvarchar](120) NULL, -- Product ID
[ProductFamily] [nvarchar](200) NULL, -- Product Family
[EquipmentType] [nvarchar](40) NULL, -- Equipment Type
[Contract#] [nvarchar](40) NULL, -- Contract Number
[SLA] [nvarchar](40) NULL, -- Service Level
[CoverageStatus] [nvarchar](40) NULL, -- Coverage Status
[Expiration] [datetime] NULL, -- Coverage End Date
[ReasonNotCovered] [nvarchar](40) NULL, -- Reason Not Covered
[VAR] [nvarchar](120) NULL, -- Customer
[Address] [nvarchar](200) NULL, -- Installed-At Address
[City] [nvarchar](40) NULL, -- Installed-At City
[State] [nvarchar](40) NULL, -- Installed-At State
[Country] [nvarchar](40) NULL, -- Installed-At Country
[WarrantyType] [nvarchar](40) NULL, -- Warranty Type
[WarrantyStartDate] [datetime] NULL, -- Warranty Start Date
[WarrantyEndDate] [datetime] NULL -- Warranty End Date
) ON [PRIMARY]
[== IMPORTING RAW DATA ==]
PRIOR to uploading data, download a new copy of the Cisco Contracts Management Report. Enter the workbook, and delete rows 1-3 and 5 from three sheets: Covered, Not Covered, and All Equipment. We want the column headers to be on the top row. Save the spreadsheet under a new name: CiscoImport.xlsm. If you already have a copy under that name, either del the old one, or just write over it.
There is an app called SQL Server Import and Export Data Wizard listed in the start menu Microsoft SQL Server. Use this tool to import data from the Cisco Import spreadsheet pages.
- Use data source type "Microsoft Excel", browse to and select the CiscoImport spreqadsheet, and verify the Excel version is correct. Click Next.
- Use data destination type "SQL Server Native Client 11.0". Type in or select the server name (pdx0netsql01), set the correct authentication (I use my Windows account credentials). Select the Database name (DevWishSmartNet), or create a new database. Click Next.
- Select Copy data from one or more tables or views. Click next.
- Select All Equipment$, Covered$, and Not Covered$. Then click the destination for each row, and select the table name:
All Devices$ >> [dbo].[RawCiscoImportAllDevices]
Covered$ >> [dbo].[RawCiscoImportCovered]
NotCovered$ >> [dbo].[RawCiscoImportNotCovered]
- For each sheet/row you are going to import, click Edit Mappings button. Be sure to correct the column mappings to include the columns you want. in the destination column click the rows that need to be mapped, which are currently ignored, and then select the database column that it should map to. Example:
Before: SNMP SysName <ignore>
After: SNMP SysName SNMPName nvarchar checked 120
Leave other columns we aren't going to import as <ignore>. Do this for every column
which must be imported. Map columns as follows:
Covered$ Sheet >> RawCiscoImportCovered table:
Sheet Column Database field Data Type
Hostname [Hostname] [nvarchar](120)
SNMP Sysname [SNMPName] [nvarchar](120)
IP Address [IPAddress] [nvarchar](40)
Serial Number [SerNum] [nvarchar](40)
Product ID [ProductID] [nvarchar](120)
Product Family [ProductFamily] [nvarchar](200)
Equipment Type [EquipmentType] [nvarchar](40)
Contract No. [Contract#] [nvarchar](40)
Coverage End [Expiration] [datetime]
Coverage Status [CoverageStatus] [nvarchar](40)
Service Level [SLA] [nvarchar](40)
Customer [VAR] [nvarchar](120)
Contract PID [ContractPID] [nvarchar](200)
Address [Address] [nvarchar](200)
City [City] [nvarchar](40)
State [State] [nvarchar](40)
Country [Country] [nvarchar](40)
NotCovered$ Sheet >> RawCiscoImportNotCovered table:
Sheet Column Database field Data Type
Hostname [Hostname] [nvarchar](120)
SNMP Sysname [SNMPName] [nvarchar](120)
IP Address [IPAddress] [nvarchar](40)
Serial Number [SerNum] [nvarchar](40)
Product ID [ProductID] [nvarchar](120)
Product Family [ProductFamily] [nvarchar](200)
Equipment Type [EquipmentType] [nvarchar](40)
Address [Address] [nvarchar](200)
City [City] [nvarchar](40)
State [State] [nvarchar](40)
Country [Country] [nvarchar](40)
Comments [Comments] [nvarchar](80)
Warranty Type [WarrantyType] [nvarchar](40)
Warranty Start Date [WarrantyStartDate] [datetime]
Warranty End Date [WarrantyEndDate] [datetime]
All Equipment$ Sheet >> RawCiscoAllEquipment table:
Sheet Column Database Field Data Type
Hostname [Hostname] [nvarchar](120)
SNMP Sysname [SNMPName] [nvarchar](120)
IP Address [IPAddress] [nvarchar](40)
Serial Number [SerNum] [nvarchar](40)
Product ID [ProductID] [nvarchar](120)
Product Family [ProductFamily] [nvarchar](200)
Equipment Type [EquipmentType] [nvarchar](40)
Contract No. [Contract#] [nvarchar](40)
Service Level [SLA] [nvarchar](40)
Coverage End [Expiration] [datetime]
Coverage Status [CoverageStatus] [nvarchar](40)
Customer [VAR] [nvarchar](120)
Contract PID [ContractPID] [nvarchar](200)
Installed-At Address [Address] [nvarchar](200)
Installed-At City [City] [nvarchar](40)
Installed-At State [State] [nvarchar](40)
Installed-At Country [Country] [nvarchar](40)
Reason Not Covered [ReasonNotCovered] [nvarchar](40)
Comments [Comments] [nvarchar](80)
Warranty Type [WarrantyType] [nvarchar](40)
Warranty Start Date [WarrantyStartDate] [datetime]
Warranty End Date [WarrantyEndDate] [datetime]
Be sure to select the new sheet you saved, and your database.
!!! *** DO NOT IMPORT INTO YOUR SOLARWINDS DATABASE! *** !!!
Click Next.
- Review the data type mappings. When satisfied, click Next.
- Select Run immediately. It seems prudent to reiterate that you should only import into your database built for this operation, not into your Orion database. Click Next. Click Finish. If anything fails double check your mappings, and that the datatypes for the columns are correct.
Note: in step 7, you can save a copy of the import wizard by clicking Save SSIS Package, and File System. Give the files a reasonable name, like, CiscoImport, that way you can use it again. So, next time, you could download a new spreadsheet, edit the rows out, and save as the same name in the same place, then truncate the old raw data from the two tables and then just run the package. However, I haven't had much luck with SSIS packages, so I don’t use them.
[== Create Working Tables ==]
Copy from the RawImport tables into SmartNetCovered and SmartNetNotCovered tables. When doing this copy, use the where clauses to ignore lines you don't want (like aironet, powersupplies, etc). I was mostly concerned with chassis serial numbers, not phones, or APs, etc., so they are not copied to the next tables. Modify the data (remove domain name extensions, set to all the same case (I chose upper case)), inspect the data.
The very first time you do this, the [SmartNetCovered] and [SmartNetNotCovered] tables will not exist, so use these two queries, otherwise, skip down. In the database files, these lines are commented. If you run these lines, select the lines, uncomment them, run them, recomment them. They are shown here without commented out for ease of reading. If you have already created the tables, please skip the next three SELECT INTO queries.
SELECT [RC].[Hostname]
,[RC].[SNMPName]
,[RC].[IPAddress]
,[RC].[SerNum]
,[RC].[ProductID]
,[RC].[ProductFamily]
,[RC].[WarrantyEndDate]
,[RC].[WarrantyStartDate]
,[CoverageStatus] ='No'
INTO [dbo].[SmartNetNotCovered]
FROM [dbo].[RawCiscoImportNotCovered][RC]
WHERE [RC].[EquipmentType] = 'CHASSIS'
AND NOT [RC].[ProductFamily] LIKE 'Cisco Aironet%'
AND NOT [RC].[ProductFamily] LIKE 'Cisco TelePres%'
AND NOT [RC].[ProductFamily] = 'Cisco DX Series'
SELECT [RC].[Hostname]
,[RC].[SNMPName]
,[RC].[IPAddress]
,[RC].[SerNum]
,[RC].[ProductID]
,[RC].[ProductFamily]
,[RC].[Contract#]
,[RC].[Expiration]
,[RC].[CoverageStatus]
,[RC].[SLA]
,[RC].[VAR]
INTO [dbo].[SmartNetCovered]
FROM [dbo].[RawCiscoImportCovered][RC]
WHERE [RC].[EquipmentType] = 'CHASSIS'
AND NOT [RC].[ProductFamily] LIKE 'Cisco Aironet%'
AND NOT [RC].[ProductFamily] LIKE 'Cisco TelePres%'
AND NOT [RC].[ProductFamily] = 'Cisco DX Series'
SELECT [RC].[Hostname]
,[RC].[SNMPName]
,[RC].[IPAddress]
,[RC].[SerNum]`
,[RC].[ProductID]
,[RC].[ProductFamily]
,[RC].[Contract#]
,[RC].[Expiration]
,[RC].[CoverageStatus]
,[RC].[VAR]
INTO [dbo].[SmartNetAllEquipment]
FROM [dbo].[RawCiscoImportAllEquipment][RC]
WHERE [RC].[EquipmentType] = 'CHASSIS'
AND NOT [RC].[ProductFamily] LIKE 'Cisco Aironet%'
AND NOT [RC].[ProductFamily] LIKE 'Cisco TelePres%'
AND NOT [RC].[ProductFamily] = 'Cisco DX Series'
Check your work.
SELECT * FROM [dbo].[SmartNetCovered]
SELECT * FROM [dbo].[SmartNetNotCovered]
SELECT * FROM [dbo].[SmartNetAllEquipment]
[== Copy from Raw Tables to Working Tables ==]
If the SmartNetNotCovered table already exists, truncate the old information, and copy new.
TRUNCATE TABLE [dbo].[SmartNetNotCovered]
INSERT INTO [SmartNetNotCovered] (
[Hostname]
,[SNMPName]
,[IPAddress]
,[SerNum]
,[ProductID]
,[ProductFamily]
,[WarrantyEndDate]
,[WarrantyStartDate]
,[CoverageStatus])
SELECT
[RC].[Hostname]
,[RC].[SNMPName]
,[RC].[IPAddress]
,[RC].[SerNum]
,[RC].[ProductID]
,[RC].[ProductFamily]
,[RC].[WarrantyEndDate]
,[RC].[WarrantyStartDate]
,[CoverageStatus] ='No'
FROM [RawCiscoImportNotCovered][RC]
WHERE [RC].[EquipmentType] = 'CHASSIS'
AND NOT [RC].[ProductFamily] LIKE 'Cisco Aironet%'
AND NOT [RC].[ProductFamily] LIKE 'Cisco TelePres%'
AND NOT [RC].[ProductFamily] = 'Cisco DX Series'
Verify your work.
SELECT * FROM [dbo].[SmartNetNotCovered]
If the SmartNetCovered table already exists, truncate the old information, and copy new.
TRUNCATE TABLE [dbo].[SmartNetCovered]
INSERT INTO [SmartNetCovered] (
[Hostname]
,[SNMPName]
,[IPAddress]
,[SerNum]
,[ProductID]
,[ProductFamily]
,[Contract#]
,[Expiration]
,[CoverageStatus]
,[SLA]
,[VAR] )
SELECT
[RC].[Hostname]
,[RC].[SNMPName]
,[RC].[IPAddress]
,[RC].[SerNum]
,[RC].[ProductID]
,[RC].[ProductFamily]
,[RC].[Contract#]
,[RC].[Expiration]
,[RC].[CoverageStatus]
,[RC].[SLA]
,[RC].[VAR]
FROM [dbo].[RawCiscoImportCovered][RC]
WHERE [RC].[EquipmentType] = 'CHASSIS'
AND NOT [RC].[ProductFamily] LIKE 'Cisco Aironet%'
AND NOT [RC].[ProductFamily] LIKE 'Cisco TelePres%'
AND NOT [RC].[ProductFamily] = 'Cisco DX Series'
Verify your work
SELECT * FROM [dbo].[SmartNetCovered]
If the SmartNetAllEquipment table already exists, truncate the old information, and copy new.
TRUNCATE TABLE [dbo].[SmartNetAllEquipment]
INSERT INTO [dbo].[SmartNetAllEquipment] (
[Hostname]
,[SNMPName]
,[IPAddress]
,[SerNum]
,[ProductID]
,[ProductFamily]
,[Contract#]
,[Expiration]
,[CoverageStatus]
,[VAR] )
SELECT
[RC].[Hostname]
,[RC].[SNMPName]
,[RC].[IPAddress]
,[RC].[SerNum]
,[RC].[ProductID]
,[RC].[ProductFamily]
,[RC].[Contract#]
,[RC].[Expiration]
,[RC].[CoverageStatus]
,[RC].[VAR]
FROM [dbo].[RawCiscoImportAllEquipment][RC]
WHERE [RC].[EquipmentType] = 'CHASSIS'
AND NOT [RC].[ProductFamily] LIKE 'Cisco Aironet%'
AND NOT [RC].[ProductFamily] LIKE 'Cisco TelePres%'
AND NOT [RC].[ProductFamily] = 'Cisco DX Series'
SELECT * FROM [dbo].[SmartNetAllEquipment]
Verify your work
SELECT * FROM [dbo].[SmartNetAllEquipment]
[== Set Case ==]
This step involves massaging the data so it is easier to use. We don’t need the domain name extensions, so we will remove them, and since there could be case sensitivity issues, we set everything to upper case. When it comes to domains, you will put your domains extensions there.
First, we'll put all Hostnames and SNMPName characters in uppercase, so we don't have to worry about case sensitivity issues when matching Cisco with Orion databases. If you haven’t used TRANs before. They are temp changes, which after you see the results, you either roll them back or commit them. The Rollback and Commit lines are commented out at the bottom, just select the portion of the command you are going to use, without the double-dashes, and then execute that selection.
BEGIN TRAN
UPDATE [a]
SET [a].[Hostname] = UPPER ([b].[Hostname])
FROM [dbo].[SmartNetCovered] [a]
JOIN(SELECT [Hostname]
FROM [dbo].[SmartNetCovered]
GROUP BY [Hostname]
) [b] ON [a].[Hostname] = [b].[Hostname]
UPDATE [a]
SET [a].[SNMPName] = UPPER ([b].[SNMPName])
FROM [dbo].[SmartNetCovered] [a]
JOIN(SELECT [SNMPName]
FROM [dbo].[SmartNetCovered]
GROUP BY [SNMPName]
) [b] ON [a].[SNMPName] = [b].[SNMPName]
UPDATE [a]
SET [a].[Hostname] = UPPER ([b].[Hostname])
FROM [dbo].[SmartNetNotCovered] [a]
JOIN(SELECT [Hostname]
FROM [dbo].[SmartNetNotCovered]
GROUP BY [Hostname]
) [b] ON [a].[Hostname] = [b].[Hostname]
UPDATE [a]
SET [a].[SNMPName] = UPPER ([b].[SNMPName])
FROM [dbo].[SmartNetNotCovered] [a]
JOIN(SELECT [SNMPName]
FROM [dbo].[SmartNetNotCovered]
GROUP BY [SNMPName]
) [b] ON [a].[SNMPName] = [b].[SNMPName]
UPDATE [a]
SET [a].[Hostname] = UPPER ([b].[Hostname])
FROM [dbo].[SmartNetAllEquipment] [a]
JOIN(SELECT [Hostname]
FROM [dbo].[SmartNetAllEquipment]
GROUP BY [Hostname]
) [b] ON [a].[Hostname] = [b].[Hostname]
UPDATE [a]
SET [a].[SNMPName] = UPPER ([b].[SNMPName])
FROM [dbo].[SmartNetAllEquipment] [a]
JOIN(SELECT [SNMPName]
FROM [dbo].[SmartNetAllEquipment]
GROUP BY [SNMPName]
) [b] ON [a].[SNMPName] = [b].[SNMPName]
Select * from [dbo].[SmartNetCovered]
Select * from [dbo].[SmartNetNotCovered]
Select * from [dbo].[SmartNetAllEquipment]
-- COMMIT TRAN
-- ROLLBACK TRAN
[== Remove Domain Extensions ==]
Next remove the domain extensions from the Hostnames, and SNMP names. This runs on all 3 tables we are currently manipulating. Remember to replace the extensions with ones you use. If you have fewer domain names to remove, the also remove the lines below the declare statement that correspond to the variable you did not declare. There will be 6 areas to delete (or add) below, each area has 8 lines, each beginning with the word UPDATE. All of this, from BEGIN TRAN to where the COMMIT and ROLLBACK lines are should be run together.
BEGIN TRAN
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].[Hostname] = REPLACE ([b].[Hostname],@fs,'')
FROM [dbo].[SmartNetCovered] [a]
JOIN(SELECT [Hostname]
FROM [dbo].[SmartNetCovered]
WHERE [Hostname] like '%'+ @fs
GROUP BY [Hostname]
) [b] ON [a].[Hostname] = [b].[Hostname]
UPDATE [a]
SET [a].[Hostname] = REPLACE ([b].[Hostname],@ft,'')
FROM [dbo].[SmartNetCovered] [a]
JOIN(SELECT [Hostname]
FROM [dbo].[SmartNetCovered]
WHERE [Hostname] like '%'+ @ft
GROUP BY [Hostname]
) [b] ON [a].[Hostname] = [b].[Hostname]
UPDATE [a]
SET [a].[Hostname] = REPLACE ([b].[Hostname],@fu,'')
FROM [dbo].[SmartNetCovered] [a]
JOIN(SELECT [Hostname]
FROM [dbo].[SmartNetCovered]
WHERE [Hostname] like '%'+ @fu
GROUP BY [Hostname]
) [b] ON [a].[Hostname] = [b].[Hostname]
UPDATE [a]
SET [a].[Hostname] = REPLACE ([b].[Hostname],@fv,'')
FROM [dbo].[SmartNetCovered] [a]
JOIN(SELECT [Hostname]
FROM [dbo].[SmartNetCovered]
WHERE [Hostname] like '%'+ @fv
GROUP BY [Hostname]
) [b] ON [a].[Hostname] = [b].[Hostname]
UPDATE [a]
SET [a].[Hostname] = REPLACE ([b].[Hostname],@fw,'')
FROM [dbo].[SmartNetCovered] [a]
JOIN(SELECT [Hostname]
FROM [dbo].[SmartNetCovered]
WHERE [Hostname] like '%'+ @fw
GROUP BY [Hostname]
) [b] ON [a].[Hostname] = [b].[Hostname]
UPDATE [a]
SET [a].[SNMPName] = REPLACE ([b].[SNMPName],@fs,'')
FROM [dbo].[SmartNetCovered] [a]
JOIN(SELECT [SNMPName]
FROM [dbo].[SmartNetCovered]
WHERE [SNMPName] like '%'+ @fs
GROUP BY [SNMPName]
) [b] ON [a].[SNMPName] = [b].[SNMPName]
UPDATE [a]
SET [a].[SNMPName] = REPLACE ([b].[SNMPName],@ft,'')
FROM [dbo].[SmartNetCovered] [a]
JOIN(SELECT [SNMPName]
FROM [dbo].[SmartNetCovered]
WHERE [SNMPName] like '%'+ @ft
GROUP BY [SNMPName]
) [b] ON [a].[SNMPName] = [b].[SNMPName]
UPDATE [a]
SET [a].[SNMPName] = REPLACE ([b].[SNMPName],@fu,'')
FROM [dbo].[SmartNetCovered] [a]
JOIN(SELECT [SNMPName]
FROM [dbo].[SmartNetCovered]
WHERE [SNMPName] like '%'+ @fu
GROUP BY [SNMPName]
) [b] ON [a].[SNMPName] = [b].[SNMPName]
UPDATE [a]
SET [a].[SNMPName] = REPLACE ([b].[SNMPName],@fv,'')
FROM [dbo].[SmartNetCovered] [a]
JOIN(SELECT [SNMPName]
FROM [dbo].[SmartNetCovered]
WHERE [SNMPName] like '%'+ @fv
GROUP BY [SNMPName]
) [b] ON [a].[SNMPName] = [b].[SNMPName]
UPDATE [a]
SET [a].[SNMPName] = REPLACE ([b].[SNMPName],@fw,'')
FROM [dbo].[SmartNetCovered] [a]
JOIN(SELECT [SNMPName]
FROM [dbo].[SmartNetCovered]
WHERE [SNMPName] like '%'+ @fw
GROUP BY [SNMPName]
) [b] ON [a].[SNMPName] = [b].[SNMPName]
UPDATE [a]
SET [a].[Hostname] = REPLACE ([b].[Hostname],@fs,'')
FROM [dbo].[SmartNetNotCovered] [a]
JOIN(SELECT [Hostname]
FROM [dbo].[SmartNetNotCovered]
WHERE [Hostname] like '%'+ @fs
GROUP BY [Hostname]
) [b] ON [a].[Hostname] = [b].[Hostname]
UPDATE [a]
SET [a].[Hostname] = REPLACE ([b].[Hostname],@ft,'')
FROM [dbo].[SmartNetNotCovered] [a]
JOIN(SELECT [Hostname]
FROM [dbo].[SmartNetNotCovered]
WHERE [Hostname] like '%'+ @ft
GROUP BY [Hostname]
) [b] ON [a].[Hostname] = [b].[Hostname]
UPDATE [a]
SET [a].[Hostname] = REPLACE ([b].[Hostname],@fu,'')
FROM [dbo].[SmartNetNotCovered] [a]
JOIN(SELECT [Hostname]
FROM [dbo].[SmartNetNotCovered]
WHERE [Hostname] like '%'+ @fu
GROUP BY [Hostname]
) [b] ON [a].[Hostname] = [b].[Hostname]
UPDATE [a]
SET [a].[Hostname] = REPLACE ([b].[Hostname],@fv,'')
FROM [dbo].[SmartNetNotCovered] [a]
JOIN(SELECT [Hostname]
FROM [dbo].[SmartNetNotCovered]
WHERE [Hostname] like '%'+ @fv
GROUP BY [Hostname]
) [b] ON [a].[Hostname] = [b].[Hostname]
UPDATE [a]
SET [a].[Hostname] = REPLACE ([b].[Hostname],@fw,'')
FROM [dbo].[SmartNetNotCovered] [a]
JOIN(SELECT [Hostname]
FROM [dbo].[SmartNetNotCovered]
WHERE [Hostname] like '%'+ @fw
GROUP BY [Hostname]
) [b] ON [a].[Hostname] = [b].[Hostname]
UPDATE [a]
SET [a].[SNMPName] = REPLACE ([b].[SNMPName],@fs,'')
FROM [dbo].[SmartNetNotCovered] [a]
JOIN(SELECT [SNMPName]
FROM [dbo].[SmartNetNotCovered]
WHERE [SNMPName] like '%'+ @fs
GROUP BY [SNMPName]
) [b] ON [a].[SNMPName] = [b].[SNMPName]
UPDATE [a]
SET [a].[SNMPName] = REPLACE ([b].[SNMPName],@ft,'')
FROM [dbo].[SmartNetNotCovered] [a]
JOIN(SELECT [SNMPName]
FROM [dbo].[SmartNetNotCovered]
WHERE [SNMPName] like '%'+ @ft
GROUP BY [SNMPName]
) [b] ON [a].[SNMPName] = [b].[SNMPName]
UPDATE [a]
SET [a].[SNMPName] = REPLACE ([b].[SNMPName],@fu,'')
FROM [dbo].[SmartNetNotCovered] [a]
JOIN(SELECT [SNMPName]
FROM [dbo].[SmartNetNotCovered]
WHERE [SNMPName] like '%'+ @fu
GROUP BY [SNMPName]
) [b] ON [a].[SNMPName] = [b].[SNMPName]
UPDATE [a]
SET [a].[SNMPName] = REPLACE ([b].[SNMPName],@fv,'')
FROM [dbo].[SmartNetNotCovered] [a]
JOIN(SELECT [SNMPName]
FROM [dbo].[SmartNetNotCovered]
WHERE [SNMPName] like '%'+ @fv
GROUP BY [SNMPName]
) [b] ON [a].[SNMPName] = [b].[SNMPName]
UPDATE [a]
SET [a].[SNMPName] = REPLACE ([b].[SNMPName],@fw,'')
FROM [dbo].[SmartNetNotCovered] [a]
JOIN(SELECT [SNMPName]
FROM [dbo].[SmartNetNotCovered]
WHERE [SNMPName] like '%'+ @fw
GROUP BY [SNMPName]
) [b] ON [a].[SNMPName] = [b].[SNMPName]
UPDATE [a]
SET [a].[Hostname] = REPLACE ([b].[Hostname],@fs,'')
FROM [dbo].[SmartNetAllEquipment] [a]
JOIN(SELECT [Hostname]
FROM [dbo].[SmartNetAllEquipment]
WHERE [Hostname] like '%'+ @fs
GROUP BY [Hostname]
) [b] ON [a].[Hostname] = [b].[Hostname]
UPDATE [a]
SET [a].[Hostname] = REPLACE ([b].[Hostname],@ft,'')
FROM [dbo].[SmartNetAllEquipment] [a]
JOIN(SELECT [Hostname]
FROM [dbo].[SmartNetAllEquipment]
WHERE [Hostname] like '%'+ @ft
GROUP BY [Hostname]
) [b] ON [a].[Hostname] = [b].[Hostname]
UPDATE [a]
SET [a].[Hostname] = REPLACE ([b].[Hostname],@fu,'')
FROM [dbo].[SmartNetAllEquipment] [a]
JOIN(SELECT [Hostname]
FROM [dbo].[SmartNetAllEquipment]
WHERE [Hostname] like '%'+ @fu
GROUP BY [Hostname]
) [b] ON [a].[Hostname] = [b].[Hostname]
UPDATE [a]
SET [a].[Hostname] = REPLACE ([b].[Hostname],@fv,'')
FROM [dbo].[SmartNetAllEquipment] [a]
JOIN(SELECT [Hostname]
FROM [dbo].[SmartNetAllEquipment]
WHERE [Hostname] like '%'+ @fv
GROUP BY [Hostname]
) [b] ON [a].[Hostname] = [b].[Hostname]
UPDATE [a]
SET [a].[Hostname] = REPLACE ([b].[Hostname],@fw,'')
FROM [dbo].[SmartNetAllEquipment] [a]
JOIN(SELECT [Hostname]
FROM [dbo].[SmartNetAllEquipment]
WHERE [Hostname] like '%'+ @fw
GROUP BY [Hostname]
) [b] ON [a].[Hostname] = [b].[Hostname]
UPDATE [a]
SET [a].[SNMPName] = REPLACE ([b].[SNMPName],@fs,'')
FROM [dbo].[SmartNetAllEquipment] [a]
JOIN(SELECT [SNMPName]
FROM [dbo].[SmartNetAllEquipment]
WHERE [SNMPName] like '%'+ @fs
GROUP BY [SNMPName]
) [b] ON [a].[SNMPName] = [b].[SNMPName]
UPDATE [a]
SET [a].[SNMPName] = REPLACE ([b].[SNMPName],@ft,'')
FROM [dbo].[SmartNetAllEquipment] [a]
JOIN(SELECT [SNMPName]
FROM [dbo].[SmartNetAllEquipment]
WHERE [SNMPName] like '%'+ @ft
GROUP BY [SNMPName]
) [b] ON [a].[SNMPName] = [b].[SNMPName]
UPDATE [a]
SET [a].[SNMPName] = REPLACE ([b].[SNMPName],@fu,'')
FROM [dbo].[SmartNetAllEquipment] [a]
JOIN(SELECT [SNMPName]
FROM [dbo].[SmartNetAllEquipment]
WHERE [SNMPName] like '%'+ @fu
GROUP BY [SNMPName]
) [b] ON [a].[SNMPName] = [b].[SNMPName]
UPDATE [a]
SET [a].[SNMPName] = REPLACE ([b].[SNMPName],@fv,'')
FROM [dbo].[SmartNetAllEquipment] [a]
JOIN(SELECT [SNMPName]
FROM [dbo].[SmartNetAllEquipment]
WHERE [SNMPName] like '%'+ @fv
GROUP BY [SNMPName]
) [b] ON [a].[SNMPName] = [b].[SNMPName]
UPDATE [a]
SET [a].[SNMPName] = REPLACE ([b].[SNMPName],@fw,'')
FROM [dbo].[SmartNetAllEquipment] [a]
JOIN(SELECT [SNMPName]
FROM [dbo].[SmartNetAllEquipment]
WHERE [SNMPName] like '%'+ @fw
GROUP BY [SNMPName]
) [b] ON [a].[SNMPName] = [b].[SNMPName]
Review your data, see if you missed anything.
Select * from [dbo].[SmartNetCovered]
Select * from [dbo].[SmartNetNotCovered]
Select * from [dbo].[SmartNetAllEquipment]
Roll back (below) if there are errors, or Commit the changes, once satisfied.
-- COMMIT TRAN
-- ROLLBACK TRAN
[== Crete CiscoInventory Table to Combine Not Covered and Covered Data ==]
Combine the Covered and Not Covered sheets, import the data into a new table: CiscoInventory. This imports all rows from both tables. If the table already exists, then truncate the table and copy fresh data.
This creates the [CiscoInventory] table and populates it if not already created. To use the query, select the whole query, uncomment it, run it, then recommend it.
--SELECT
-- [U].[Hostname]
-- ,[U].[SNMPName]
-- ,[U].[IPAddress]
-- ,[U].[SerNum]
-- ,[U].[ProductID]
-- ,[U].[ProductFamily]
-- ,[U].[CoverageStatus]
--INTO [dbo].[CiscoInventory]
--FROM (
-- SELECT
-- [SNNC].[Hostname]
-- ,[SNNC].[SNMPName]
-- ,[SNNC].[IPAddress]
-- ,[SNNC].[SerNum]
-- ,[SNNC].[ProductID]
-- ,[SNNC].[ProductFamily]
-- ,[SNNC].[CoverageStatus]
-- FROM [dbo].[SmartNetNotCovered][SNNC]
-- UNION
-- SELECT
-- [SNC].[Hostname]
-- ,[SNC].[SNMPName]
-- ,[SNC].[IPAddress]
-- ,[SNC].[SerNum]
-- ,[SNC].[ProductID]
-- ,[SNC].[ProductFamily]
-- ,[SNC].[CoverageStatus]
-- FROM [dbo].[SmartNetCovered][SNC]
-- ) [U]
Check your work.
SELECT * FROM [dbo].[CiscoInventory]
[== Combine Covered and Not Covered Tables into New Table ==]
We want to copy all (specified) rows from SmartNetNotCovered as being not covered, plus all 'Active' rows from SmartNetCovered, plus if any Ser# from SmartNetCovered are 'Signed' but only if there is not a corresponding row with 'Active'.
There are times when you might have changed your SmartNet VAR. If that is the case, then many devices will show up on Cisco’s report twice – once for the active contract and once for the signed contract the will become active one the old contract expires. To accommodate this, we will prefer any row that has “Active” as the coverage status, but we will also accept any row that has “Signed” as the coverage status if “Active” doesn’t exist, but we don’t want both, since that would duplicate data. If this doesn’t apply to you, then everything that is covered will be “Active”, and the query works anyway.
TRUNCATE TABLE [dbo].[CiscoInventory]
INSERT INTO [CiscoInventory] (
[Hostname]
,[SNMPName]
,[IPAddress]
,[SerNum]
,[ProductID]
,[ProductFamily]
,[CoverageStatus] )
SELECT [SNNC].[Hostname]
,[SNNC].[SNMPName]
,[SNNC].[IPAddress]
,[SNNC].[SerNum]
,[SNNC].[ProductID]
,[SNNC].[ProductFamily]
,[SNNC].[CoverageStatus]
FROM [dbo].[SmartNetNotCovered][SNNC]
UNION
SELECT [SNC].[Hostname]
,[SNC].[SNMPName]
,[SNC].[IPAddress]
,[SNC].[SerNum]
,[SNC].[ProductID]
,[SNC].[ProductFamily]
,[SNC].[CoverageStatus]
FROM [dbo].[SmartNetCovered][SNC]
WHERE NOT [SNC].[CoverageStatus] ='SIGNED'
UNION (
SELECT [T].[Hostname]
,[T].[SNMPName]
,[T].[IPAddress]
,[T].[SerNum]
,[T].[ProductID]
,[T].[ProductFamily]
,[T].[CoverageStatus]
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] )
Verify your work.
SELECT * FROM [dbo].[CiscoInventory]
You have completed Step 1. You have imported data from Cisco’s SmartNet report, and then identified which data you are interested in, and then massaged the data you won’t have mismatches based upon Case or domain extensions. You have then taken that data and combined it into a single Table, along with a filed indicating if it has SmartNet coverage. The next step will have a similar flow, but has some differences, and is a bit less involved. Please go to the document “Cisco-Orion Reconciliation Step 2 Import Orion Data.xls”.
