This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

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.

Parents
  • 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.)

  • 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?
Reply
  • 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?
Children
No Data