Ok Everyone - Hope all is well.
First off - I am NOT a SQL/SWQL guy. I am in need of some help. I want to create a table on one of my dashboards that list the UPSs that need a battery replacement, as well as the battery needed. This is just to speed things up when ordering replacements due to the variety we use.
Here is my current query -
SELECT
A.NodeName AS NodeName,
MAX(CASE WHEN Poller_Name='upsAdvIdentSkuNumber' THEN CAST(Status AS varchar) ELSE '' END) upsAdvIdentSkuNumber,
MAX(CASE WHEN Poller_Name='upsAdvBatteryInternalSKU ' THEN CAST(Status AS varchar) ELSE '' END) upsAdvBatteryInternalSKU ,
MAX(CASE WHEN Poller_Name='upsAdvBatteryExternalSKU' THEN CAST(Status AS varchar) ELSE '' END) upsAdvBatteryExternalSKU ,
MAX(CASE WHEN Poller_Name='upsAdvBatteryReplaceIndicator' THEN CAST(Status AS varchar) ELSE '' END) upsAdvBatteryReplaceIndicator
FROM
(
SELECT
Nodes.Caption AS NodeName,CustomNodePollers_CustomPollers.UniqueName AS Poller_Name, CustomNodePollerStatus_CustomPollerStatus.Status AS Status
FROM
((Nodes INNER JOIN CustomPollerAssignment CustomNodePollerAssignment_CustomPollerAssignment ON (Nodes.NodeID = CustomNodePollerAssignment_CustomPollerAssignment.NodeID)) INNER JOIN CustomPollers CustomNodePollers_CustomPollers ON (CustomNodePollerAssignment_CustomPollerAssignment.CustomPollerID = CustomNodePollers_CustomPollers.CustomPollerID)) INNER JOIN CustomPollerStatus CustomNodePollerStatus_CustomPollerStatus ON (CustomNodePollerAssignment_CustomPollerAssignment.CustomPollerAssignmentID = CustomNodePollerStatus_CustomPollerStatus.CustomPollerAssignmentID)
WHERE
(
(CustomNodePollers_CustomPollers.UniqueName = 'upsAdvIdentSkuNumber') OR
(CustomNodePollers_CustomPollers.UniqueName = 'upsAdvBatteryInternalSKU') OR
(CustomNodePollers_CustomPollers.UniqueName = 'upsAdvBatteryExternalSKU') OR
(CustomNodePollers_CustomPollers.UniqueName = 'upsAdvBatteryReplaceIndicator')
)
AND
(
(CustomNodePollerAssignment_CustomPollerAssignment.InterfaceID = 0)
)
)A
GROUP BY NodeName
Output looks like this:
Now what id like to do from here, is filter or only show the devices that need replacement. As a bonus - only show externalSKU if it returns a value.
Thanks All for any advice or assistance you can give.
