Pulling list of serial numbers for cisco devices?

I am trying to match items in solarwinds against a list of devices we are paying for coverage on at out company. I have spent a few days trying to create a report that would do this for me, but I haven't been able to get serial numbers/service tags for all devices. I am relatively new to solarwinds so I may not be doing things correctly.

How I have found info for SOME of our devices:

1. Click on the device itself

2. In node details page, scroll down until I find a box labeled "hardware details". This gives me info on "Model" and "Service Tag"

This is the most useful to me so far, as the service tags match what we have on our coverage report. I can easily compare these two in Excel to see what matches. (Unfortunately we have hundreds of devices on our coverage report, so its usefulness has all but evaporated, hence wanting to create a report.)

______________________________________________________________________________________________________________________________________________________________________________________________

I am unsure if some devices are running protocol more friendly to SW and as such are able to pull this information while the majority of other are not.

My current report:

1. Caption - Displaying the name of the machine within SW (Displaying given name of machine in SW)

2. Machine Type (Showing manufactures name for the machine IE: Windows 2008 R2 Server, Cisco 3845, Hyper-V Server ect.) This is very useful to me if it can be combined with a serial/service number or model number.

3. Description. Detailed description of device (IE Windows 2016 Server =  Hardware: Intel64 Family 6 Model 63 Stepping 2 AT/AT COMPATIBLE - Software: Windows Version 10.0 (Build 14393 Multiprocessor Free). This I plan on removing so things look a little nicer.

4. Service Tag. This is what i actually need. Currently it is only displaying the ST of Cisco 3845, Catalyst 4948, Cisco WSC 6513 and some others that are actually of use (though very few).

5. Description. Showing the manufacture of the device IE windows, cisco, riverbed technology ect.

TL:DR

My company has tasked me with finding out what devices we are actually using that we are paying for, versus what we are paying for and not using.

I need a way of pulling as may serial number/ service tags as possible, export this list (I know how to do this), compare this list to another list of devices my company is paying for coverage on.

I am having trouble pulling a large amount of serial numbers/ service tags

I am more than happy to answer any questions and will do so to the best of my ability. I greatly appreciate any feedback you may have.

Regards,

Gus E.

  • Gus,

    The answer to your question depends largely on what software packages you're running, ie NPM, NCM or both.  It also depends on what versions you're running too and the hardware your scanning.  Case in point a stacked Cisco 37xx will only report the master switch, but if you're running a newer release of Solarwinds there's a separate report that supplies the serial numbers of the stack members.  The same goes for Cisco Nexus Flex cards.

    Assuming you have both NPM and NCM, here's a SQL report that I built that pretty much supplies everything you're looking for.

    SELECT [dbo].[NodesData].Location

    ,[dbo].[NodesData].SysName

    ,[dbo].[NodesData].Vendor

    ,[dbo].[NodesData].IP_Address

    ,[dbo].[NCM_Entity_Physical].Name as Entity_Name

    ,[dbo].[NCM_Entity_Physical].Description as Entity_Description

    ,[dbo].[NCM_Entity_Physical].Serial as Entity_Serial

    ,[dbo].[APM_HardwareAlertData].Model

    ,[dbo].[NPM_SwitchStackMember].SwitchNumber as Stack#

    ,[dbo].[NPM_SwitchStackMember].Model AS Stack_Model#

    ,[dbo].[NPM_SwitchStackMember].SerialNumber AS Stack_Serial

    FROM [dbo].[NodesData]

    FULL OUTER JOIN [dbo].[APM_HardwareAlertData]

    ON [dbo].[NodesData].NodeID=[dbo].[APM_HardwareAlertData].NodeID

    FULL OUTER JOIN [dbo].[NPM_SwitchStackMember]

    ON [dbo].[NodesData].NodeID=[dbo].[NPM_SwitchStackMember].NodeID

    FULL OUTER JOIN [dbo].[NCM_NodeProperties]

    ON [dbo]. [NCM_NodeProperties].CoreNodeID=[dbo].[NodesData].NodeID

    FULL OUTER JOIN [dbo].[NCM_Entity_Physical]

    ON [dbo].[NCM_NodeProperties].NodeID=[dbo].[NCM_Entity_Physical].NodeID

    WHERE ([dbo].[NCM_Entity_Physical].Serial is not null and [dbo].[NCM_Entity_Physical].Serial <> '')

    Capture.JPG

    Capture.JPG

  • Nice work bourlis​. That is a good way to show the serial numbers in Solarwinds. I don't know if guseulitt​ found it useful since he didn't reply. I am working on a similar solution, but it goes step (a few steps) beyond a report of SN in SW DB.

    We have some hundreds of Cisco devices, when it comes time to audit for SmartNet coverage, it take a long time to do a stare and compare between the Cisco-provided list, and the Solarwinds-assembled list. Now, I know there are features in NCM that are supposed to help with this, but they rely on complete and accurate data. What if the data is incomplete or not a direct comparison. Since not all identifiers in the SmartNet spreadsheet match the identifiers in Solarwinds:

    • device names,
    • IP addresses,
    • Serial Number,

    -- thus we have a lot of cross referencing to do. Sometimes are left with orphans on both lists which we need to verify which devices need to be added or removed. It is my goal to automate this comparison. I have not worked on this in over 6 weeks, but plan on picking it back up. Here's what I have:

    1. Create a new database [do not modify your SW DB]
    2. Parse information from SmartNet spreadsheet and Import data into a table in the new database (remove domain extensions from host names). Import: Host names, IP address, Serial number, Model, address, city, giving each serial number an index number
    3. Parse information from Solarwinds database and Import data into a separate table: Host names, IP addresses, Serial number, Model, Branch/location, giving each node/serial number an index number.
    4. Create an table cross referencing the 2 sets of index numbers, by matching on serial numbers first, then on IP Addresses, then on Serial numbers.
    5. Assemble a list of IP addresses from remaining SmartNet devices.
    6. Pull the most recent config for each of the remaining unmatched Solarwinds nodes into a new table in the new DB. Search those configs for the IP addresses from step 5 to find remaining devices (example: a device which does not have a SN in SW is polled on IP address from VLAN255, but SmartNet has a different host name and the IP address from VLAN2). This search on step 6 should reveal these.
    7. If there are devices left unmatched, login and manually explore them. It is possible the device in SW should be added to Smartnet, or the device in Smarnet is not longer on network and can be removed from SmartNet, or the serial number recorded somewhere has a 0 instead of an O or a I instead of a 1 -- depending on the entry method.
    8. Use the cross-reference to update Cisco's data (or Solarwinds') so next time things are easier.

    Anyway, I'll post my solution when completed. (May not be soon.)

  • You could also add to your WHERE clause a line specifying that the Entity_Class = 3.  Unless my initial testing is not accurate, this will filter out everything but the switches themselves.  So no more power supplies or line cards! (examples of the extra items) It seems to have alleviated all the duplicate entries you return when pertaining to switch stacks.

    Bear in mind that I converted your SQL into SWQL, so you would want to test to verify that you see what I see.  I put this into a Custom Query widget on one of my custom views because one of the network engineers wanted a quick way to pull/view the information from SolarWinds.

  • troy.allen I like it!. Do you have the rest of the mappings for Entity_Class?

  • I did not dig into it to be able to answer that question.  I just noticed that my list - which was @3300 rows - with the above SQL dropped to @300 rows with that added to the where clause.  From looking at the results (when I included adding the Entity_Class) that the value 3 seemed to correspond to the network device itself.

    We have multiple offices and many of them have multiple switch stacks - as large as 8 in a stack.  With the original query, the output included all 8 switches - with their hardware - 8 times.  So, instead of one of those switch stacks being 8 rows of output, I was getting ~200 rows for the switch stack.

    ADDED:

    Class 1 and 10 are port related.

    Class 11 is the connection between stacked switches

    Class 12 is processors

    Class 2 - do not see entries...

    Class 3 is the chassis

    Class 4 is backplane

    Class 5 is Containers/slots

    Class 6 is power supply

    Class 7 is fans

    Class 8 is sensors

    Class 9 is modules

  • Continuing on with my deciphering of serial numbers in the Orion NCM database tables, I have found out that the data in [NCM_Entity_Physical] may be unreliable if NCM Inventory is not current. I ran the following two queries and compared the two.

    Query 1 joining [NCM_Nodes] table with [NPM_SwitchStackMember] table:

    SELECT

        [NN].[CoreNodeID]

       ,[NN].[NodeID]

       ,[NN].[NodeCaption]

       ,[NN].[AgentIP]

       ,[SSM].[SerialNumber]

    FROM [NetPerfMon].[dbo].[NCM_Nodes][NN]

    JOIN [NetPerfMon].[dbo].[NPM_SwitchStackMember][SSM]with(nolock) ON [NN].[CoreNodeID]=[ssm].[NodeID]

    Order by [ssm].[NodeID],[SSM].[SerialNumber]

    Query 2 joining [NCM_Nodes] table with [NCM_Entity_Physical] table:

    SELECT

        [NN].[CoreNodeID]

       ,[NN].[NodeID]

       ,[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 Like 'Air%'

      AND NOT Model Like 'MID%'

      AND NOT Model like 'Fabric%'

      and not Model =''

    order by [nn].[CoreNodeID],[NEP].[Serial]

    On some nodes, I get the same data. However, I some some that do not match. Different numbers of switches in the stack, different serial numbers. I did some investigation. The switch stacks where there were differences were switch stacks which had been upgraded (replaced with newer and more switches). The device object in NCM and NPM had not been deleted between stacks of devices.  I went to Manage Nodes, found the node and selected List Resources; the resources were updated. I clicked Rediscover and ran the queries again. The data did not change. I went into Config Management, re-ran inventory on the device, then re-ran the queries; data did update. So, it seems that the Physical Entities table is based upon information obtained through inventory. So, if there are changes to the devices, it is important to run inventory after the change.

  • Another couple fine points if working with [NCM_Entity_Physical] table:

    • There may be some entries with blank serial numbers -- these are provisioned switch members which don't exist yet, or they are switch stack members which have been removed from the stack.
    • There may be some entries with NULL serial numbers -- I found a few of these on a switch that had the correct number of lines for switch stack members as were in the switch stack, but I only 1 serial number of 5 were recorded, the other 4 were NULL. Showing Inventory on the switch stack (IOS command line), did show the correct serial numbers. Not sure why it was NULL. Perhaps inventory failed to update correctly?
  • I wanted to give you an update. In spare time, I have been working on a manner of reconciling Chassis Serial Numbers in NPM and NCM with reports you can download from Cisco. I pretty much completed it. It is important to update Cisco device firmware in order to apply security fixes, operational fixes, etc. It is important to know whether devices are covered by Smartnet, because updating devices not covered is a violation of licensing (not legal advice, but check it out). If your environment is large, with thousands of devices, it will takes a long time to cross reference your inventory in Orion with the Inventory from Cisco.

    If you use tables like, "[NCM_VulnerabilitiesAnnouncements]", you can create a report of all serial numbers which are covered under Smartnet, and have a vulnerability with CVSS score of 9 or higher, according the in actual inventory NPM and NCM know about. Cisco provides a report of devices it knows about in spreadsheet workbook format, including a page for Covered, one for Not Covered, and one for all devices, among other sheets in the workbook. By creating your own database, importing from the Cisco Workbook, and from Orion, and then using set theory, you can find out things like:

    • Which serial numbers are in Orion but not on (any) page of the Cisco report.
    • Which serial numbers are on the All Equipment page, but not on the Covered or Not Covered pages, and hence you haven't a clue whether they are covered or not.
    • Which serial numbers are in the Cisco workbook, but not in Orion.
    • Which serial numbers match on IP address between Orion and Cisco, but not on hostname/caption.
    • Which serial numbers match on Hostname/Caption between Orion and Cisco but not on IP Address.
    • Which hostname/IP address combinations match between Cisco and Orion, but not on Serial numbers.

    Using reports like those, you can determine which devices to remove from Smartnet Renewal. Which devices need to be added to Smartnet. Which devices might have escaped being entered into Orion. If there are errors in Smartnet coverage which could cause delays later if/when Smartnet is needed to obtain service on a device.

    So, I'd like to share this with the community here. serena or yumdarling​ or other -- where should I post this? It is big. I mean really big. I did a write-up to document how to run it, for others at my company (in case something catastrophic befalls me). This would be the basis of my post. But it will be quite long, probably better to post in 4 parts [Importing from Orion, Importing from Cisco, Combining Data, Running Reports].

  • Impressive work and true dedication to the cause - I’m already excited to see the full blown breakdown.

    I would suggest it needs to go into Monitoring Central if possible! If not then Reports Lab?

  • Hi Eric!

    This would be great in the Network Configuration Manager​ forum!!

    Thanks for posting emoticons_happy.png