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

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

Reply
  • 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.

Children
  • 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