3 Replies Latest reply on Dec 19, 2017 5:23 PM by hpstech

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

    michaelwoodside

      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%')

        • Re: Cisco Wireless Lan Contoller Serial Number Report (SQL Query, Required Custom Poller)
          hpstech

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

           

          Has anyone been able to do it?

            • Re: Cisco Wireless Lan Contoller Serial Number Report (SQL Query, Required Custom Poller)
              michaelwoodside

              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'

              1 of 1 people found this helpful