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