This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Cisco Wireless Lan Contoller Serial Number Report (SQL Query, Required Custom Poller)

Hey Guys,

Figured I would post this information here as I recently had to create a Custom Report for pulling back the Serial Numbers from Cisco 5500 WLC.  This information isn't pulled natively by Solarwinds for some reason, so we had to create a custom Poller to query the SNMP OID for the Serial number and then select the first entry from that list and store it as a value.  After that was done, we needed to populate this into a report the customer could run quarterly for Asset Audits.  Since the hardest part of the report was the Custom Query, where we had  to connect the NODE ID with the Custom Poller ID and then join the results so that the NODE ID and Poller ID matched, then pull the value from the custom poller databse, I am pasting the SQL query we used for this report so that it may help someone else.

SELECT

CustomNodePollerStatus_CustomPollerStatus.Status AS Serial_Number, Nodes.Vendor AS Vendor,Nodes.Description as Description, Nodes.City AS City, Nodes.IP_Address AS IP_Address, Nodes.MachineType AS Machine_Type, Nodes.Caption AS Node_Name, Nodes.IOSVersion AS IOS_Version, Nodes.SNMPVersion AS SNMPVersion, CustomPollers_CustomPollers.UniqueName as Poller_Name

FROM

(Nodes INNER JOIN CustomPollerAssignment CustomNodePollerAssignment_CustomPollerAssignment ON (Nodes.NodeID = CustomNodePollerAssignment_CustomPollerAssignment.NodeID))  INNER JOIN CustomPollerStatus CustomNodePollerStatus_CustomPollerStatus ON (CustomNodePollerAssignment_CustomPollerAssignment.CustomPollerAssignmentID = CustomNodePollerStatus_CustomPollerStatus.CustomPollerAssignmentID) INNER JOIN CustomPollers CustomPollers_CustomPollers ON (CustomNodePollerAssignment_CustomPollerAssignment.PollerID = CustomPollers_CustomPollers.PollerID)

WHERE

(

( Nodes.Vendor = 'Cisco')  AND (CustomPollers_CustomPollers.UniqueName Like 'WLCSer%') and (Nodes.Caption Like '%55%')

)

ORDER by Node_Name Desc

Details of the Query:

CustomNodePollerStatus_CustomPollerStatus.Status AS Serial_Number == Value retrieved by the Actual Custom Poller.

Nodes.Vendor AS Vendor == Vendor of the Hardware allows us to thin results.

Nodes.Description as Description == Value field for Model 5508, etc...

Nodes.City AS City

Nodes.IP_Address AS IP_Address

Nodes.MachineType AS Machine_Type

Nodes.Caption AS Node_Name

Nodes.IOSVersion AS IOS_Version

Nodes.SNMPVersion AS SNMPVersion == for SNMP Audit (v2 or v3)

CustomPollers_CustomPollers.UniqueName as Poller_Name == Name of my Custom Poller so we can make sure results are correct, and join is done on correct poller

This is where the Real work is being done in the Query:

Join results based on NODE ID, the results from Custom Pollers are in a separate Table, and the node id of the Nodes Table and Custom Assignment Table are the Only Links

(Nodes INNER JOIN CustomPollerAssignment CustomNodePollerAssignment_CustomPollerAssignment ON (Nodes.NodeID = CustomNodePollerAssignment_CustomPollerAssignment.NodeID))

Once we have the Assignment ID, we have to join the Poller Status Table, and the Assignment table to enable us to pair the Results with the NODE ID.

  INNER JOIN CustomPollerStatus CustomNodePollerStatus_CustomPollerStatus ON (CustomNodePollerAssignment_CustomPollerAssignment.CustomPollerAssignmentID = CustomNodePollerStatus_CustomPollerStatus.CustomPollerAssignmentID)

Once the Assignment ID is linked to the NODE ID we can use the Assignment PollerID to get the results from the Poller pollerID fields.

INNER JOIN CustomPollers CustomPollers_CustomPollers ON (CustomNodePollerAssignment_CustomPollerAssignment.PollerID = CustomPollers_CustomPollers.PollerID)

Filter your Matching Data and pull the results you actually need (My Poller was named WLCSerials)

                 ( Nodes.Vendor = 'Cisco')  AND (CustomPollers_CustomPollers.UniqueName Like 'WLCSer%') and (Nodes.Caption Like '%55%')

  • It would be great to have an all inclusive Serial # Report somehow.

    Has anyone been able to do it?

  • What Kind of hardware are we talking about??  All Cisco, Various Vendors??  it would depend if the Serial number is grabbed by Default.  If it is, I have configured a Few Custom Reports that grab the data and then correlate with the Node ID.  The Cisco WLC Serial for example is not captured, so one has to capture that Serial then Correlate and combine with the others.

    Two Seperate Queries I have used are below.

    1. Solarwinds Serial Number Report ( if gathered by NCM)

    ==========================
    SELECT OrionNodes.Caption, OrionNodes.DetailsUrl, OrionNodes.Status, OrionNodes.ChildStatus, NcmEntityPhysical.EntityName, OrionNodes.IP_Address, NcmEntityPhysical.EntityDescription, NcmEntityPhysical.Serial, NcmEntityPhysical.Manufacturer, NcmEntityPhysical.Model, NcmNodeProperties.NodeComments, OrionNodes.SysName, OrionNodes.NodeDescription, OrionNodes.Location, OrionNodes.Contact, OrionNodes.MachineType, OrionNodes.LastBoot, OrionNodes.IOSVersion
    FROM NCM.NodeProperties AS NcmNodeProperties
    INNER JOIN Orion.Nodes AS OrionNodes ON NcmNodeProperties.CoreNodeID=OrionNodes.NodeID
    LEFT JOIN NCM.EntityPhysical AS NcmEntityPhysical ON NcmEntityPhysical.NodeID=NcmNodeProperties.NodeID AND NcmEntityPhysical.EntityClass=3
    WHERE OrionNodes.Vendor='Cisco

    2. Cisco Devices with only Service Tag info

    =====================
    SELECT
    Nodes.Vendor AS Vendor, Nodes.City AS City, Nodes.IP_Address AS IP_Address, Nodes.MachineType AS Machine_Type, Nodes.Caption AS Node_Name, Nodes.IOSVersion AS IOS_Version, Nodes.SNMPVersion AS SNMPVersion, APM_HardwareAlertData.ServiceTag AS Hardware_Service_Tag
    FROM
    Nodes INNER JOIN APM_HardwareAlertData ON (Nodes.NodeID = APM_HardwareAlertData.NodeId)
    WHERE
    (
      (Nodes.Vendor = 'Cisco')
    )

    I tend to use the Following Query to Find Devices that DON'T have a Serial Number by Default as that list is usually Shorter so I can create a custom Poller and then add the results to my other Queries.

    ===================
    No service Tag info
    ===================
    SELECT
    Nodes.Vendor AS Vendor, Nodes.City AS City, Nodes.IP_Address AS IP_Address, Nodes.MachineType AS Machine_Type, Nodes.Caption AS Node_Name, Nodes.IOSVersion AS IOS_Version, Nodes.SNMPVersion AS SNMPVersion, APM_HardwareAlertData.ServiceTag AS Hardware_Service_Tag
    FROM
    Nodes LEFT JOIN APM_HardwareAlertData ON (Nodes.NodeID = APM_HardwareAlertData.NodeId)
    WHERE  APM_HardwareAlertData.ServiceTag is NULL AND Vendor = 'Cisco'
  • Thank you Michael. This is awesome.

  • Michael,

    Your SQL Query is great and I have found it very helpful but…

    I have been looking for a way to JOIN the Table [dbo].[Wireless_AccessPoints]
    and Columns [Name] and {IPAddress] with the Table [dbo].[CustomPollerStatus].[Status]
    with the Access Point’s Serial Numbers.

    It is great that this SQL Query gives us the Wireless LAN
    Controllers (WLC) and their Access Points Serial Numbers.

    I believe that we can get a JOIN to work using the [dbo].[Wireless_AccessPoints].[ID]
    and or [dbo].[Wireless_AccessPoints].[Index] Numbers.

    The [dbo].[Wireless_AccessPoints].[NodeID] and [dbo].[Wireless_AccessPoints].[ParentID]
    being for the WLCs.

    I will look forward to your feedback on this.

    WLC Thin [Light Weight] Access Point Serial Number. Display & Report?

    Cheers,

    Dan