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.

Need Help to Construct a SWQL query to Generate an APC UPS/PDU Report on Specific Values

zagwill over 2 years ago

Site ID
Hostname
IP Address
SKU
Firmware Rev
Current Charge
Rated power (in watts)
Watts used (%)
Battery Replacement Date
UPS Age (mfr date)
Battery Run Time
Serial Number

I've started to obtain the APC UPS/PDU Serial Numbers and Firmware, but the results are being returned in the Serial Number column. I need to segregate the values, since the recipients won't understand the report. Here's my beginning query:

SELECT TOP 1000 C.SiteID, CPS.NodeID, N.NodeName, CPS.Status AS SerialNumber
FROM Orion.NPM.CustomPollerStatusOnNode CPS
JOIN Orion.Nodes N on CPS.NodeID=N.NodeID
JOIN Orion.NodesCustomProperties C on C.NodeID=N.NodeID
WHERE CustomPollerID = 'e955f524-a816-48f7-9765-ff1be5362c39' OR CustomPollerID = 'acbe9348-ddea-4847-bfa5-ce596ea6f2c7' OR CustomPollerID = '354b7a4d-e405-4214-a531-5cf6d865ba4d'

I'm fairly new to using SWQL and of course SQL in general, and welcome any recommendations on generating this type of detailed inventory report on the APC UPS/PDU devices.

Thanks in Advance!

Will Q.

Parents

0 zagwill over 2 years ago

I have an update, but all of the returned values are in the SerialNumber column, and need a way to add non Orion DB columns and place the specific CustomPollerID results in each column. Here's the query:

SELECT TOP 2000 C.SiteID, CPS.NodeID, N.IPAddress, N.NodeName, CPS.Status AS SerialNumber, CP.UniqueName AS CustomPollerName
FROM Orion.NPM.CustomPollerStatusOnNode CPS
JOIN Orion.Nodes N on CPS.NodeID=N.NodeID
JOIN Orion.NodesCustomProperties C on C.NodeID=N.NodeID
INNER JOIN Orion.NPM.CustomPollers CP on CPS.CustomPollerID=CP.CustomPollerID
WHERE CustomPollerID = 'e955f524-a816-48f7-9765-ff1be5362c39' OR CustomPollerID = 'acbe9348-ddea-4847-bfa5-ce596ea6f2c7' OR CustomPollerID = '354b7a4d-e405-4214-a531-5cf6d865ba4d' OR CustomPollerID = '765ebd1a-c8f3-4c9c-8409-1510e8052424' OR CustomPollerID = 'ef9753c3-d565-4d9b-9e1b-2dd1f63f5f62' OR CustomPollerID = '9c268144-aa18-4049-bf78-45474ae7d8d7' OR CustomPollerID = 'd05467ee-03a3-4173-9be3-d189c493754e'
ORDER BY NodeID ASC, UniqueName ASC

Example Results
Cancel
Vote Up 0 Vote Down

Cancel

Reply

0 zagwill over 2 years ago

I have an update, but all of the returned values are in the SerialNumber column, and need a way to add non Orion DB columns and place the specific CustomPollerID results in each column. Here's the query:

SELECT TOP 2000 C.SiteID, CPS.NodeID, N.IPAddress, N.NodeName, CPS.Status AS SerialNumber, CP.UniqueName AS CustomPollerName
FROM Orion.NPM.CustomPollerStatusOnNode CPS
JOIN Orion.Nodes N on CPS.NodeID=N.NodeID
JOIN Orion.NodesCustomProperties C on C.NodeID=N.NodeID
INNER JOIN Orion.NPM.CustomPollers CP on CPS.CustomPollerID=CP.CustomPollerID
WHERE CustomPollerID = 'e955f524-a816-48f7-9765-ff1be5362c39' OR CustomPollerID = 'acbe9348-ddea-4847-bfa5-ce596ea6f2c7' OR CustomPollerID = '354b7a4d-e405-4214-a531-5cf6d865ba4d' OR CustomPollerID = '765ebd1a-c8f3-4c9c-8409-1510e8052424' OR CustomPollerID = 'ef9753c3-d565-4d9b-9e1b-2dd1f63f5f62' OR CustomPollerID = '9c268144-aa18-4049-bf78-45474ae7d8d7' OR CustomPollerID = 'd05467ee-03a3-4173-9be3-d189c493754e'
ORDER BY NodeID ASC, UniqueName ASC

Example Results
Cancel
Vote Up 0 Vote Down

Cancel

Children

No Data