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.

Needed - UPS Universal Device Poller Status values for all UPS listed in a single report

wickedoz over 7 years ago

I am trying to create a report that shows the values from the Universal Device Poller Status for each of our UPS. We want to use the report to plan battery replacements, firmware upgrades, etc. Additionally we would like to see if we are polling for each UPS without having to drill down to the Node Detail Summary for each device/node.

Top Replies

0 jkrenzien over 7 years ago

Create report and use the advanced database query for the selection method. I have put the query I use for an asset report below. It doesn't grab all of the Custom Pollers you would want so you can either create a new report for each poller or do some fancy SQL that I had to have one of our DBAs make to condense the multiple rows into columns (I had to do it for Asset info on our thin APs). I might be able to figure out what my DBA did in the second query, but I would be guessing a lot. Well after you have the query you just add fields you want in the report table. Hopes this helped or that someone else has a better solution.
UPS ASSET SQL Query
     SELECT DISTINCT
     CustomPollerAssignmentView.CustomPollerAssignmentID AS NetObjectID,
     CustomPollerAssignmentView.NodeID,
     CustomPollerAssignmentView.AssignmentName AS Name,
     CustomPollerStatus.Status AS Serial_Number,
     CustomPollerStatus.RowID,
     NodesData.IP_Address,
     NodesData.Caption,
     NodesData.Vendor,
     NodesData.IOSVersion AS Firmware_Version,
     NodesData.Status,
     NodesData.MachineType,
     NodesCustomProperties.County,
     NodesCustomProperties.Site_Type,
     NodesCustomProperties.Service_Owner

     FROM
     (CustomPollerStatus INNER JOIN CustomPollerAssignmentView ON
     (CustomPollerStatus.CustomPollerAssignmentID = CustomPollerAssignmentView.CustomPollerAssignmentID))
     LEFT OUTER JOIN CustomPollers ON
     (CustomPollerAssignmentView.CustomPollerID = CustomPollers.CustomPollerID)
     RIGHT JOIN NodesData ON
      (CustomPollerAssignmentView.NodeID = NodesData.NodeID)
     RIGHT JOIN NodesCustomProperties ON
      (CustomPollerAssignmentView.NodeID = NodesCustomProperties.NodeID)

     WHERE
     (
       (NodesData.Vendor = 'American Power Conversion Corp.') AND
       (CustomPollers.UniqueName = 'upsAdvIdentSerialNumber')
     )
Thin AP SQL Query
     --Run this query on S200SOLWINDSQL: [SolarWindsOrion] database

     IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
     BEGIN
         DROP TABLE #Temp
     END
     --Selecting all the data from the select query into a Temptable #Temp
     SELECT *
     INTO #Temp
     FROM
     (
     SELECT DISTINCT
     CustomPollerAssignmentView.CustomPollerAssignmentID AS NetObjectID,
     CustomPollerAssignmentView.NodeID,
     CustomPollerAssignmentView.AssignmentName,
     CustomPollerStatus.Status,
     CustomPollerStatus.RowID,
     NodesData.IP_Address,
     NodesData.Caption AS Controller_Name,
     NodesData.Vendor,
     NodesData.MachineType AS Controller_Model,
     NodesCustomProperties.County,
     NodesCustomProperties.Site_Type,
     NodesCustomProperties.Service_Owner


     FROM
     (CustomPollerStatus INNER JOIN CustomPollerAssignmentView ON
     (CustomPollerStatus.CustomPollerAssignmentID = CustomPollerAssignmentView.CustomPollerAssignmentID))
     LEFT OUTER JOIN CustomPollers ON
     (CustomPollerAssignmentView.CustomPollerID = CustomPollers.CustomPollerID)
     RIGHT JOIN NodesData ON
      (CustomPollerAssignmentView.NodeID = NodesData.NodeID)
     RIGHT JOIN NodesCustomProperties ON
      (CustomPollerAssignmentView.NodeID = NodesCustomProperties.NodeID)


     WHERE
     (
       (NodesData.MachineType = 'Cisco 5500 Series Wireless Controller')
     )) AS TEMP

     --Creating another Temptable #Temp2 to Insert the required columns in a desired way

     IF OBJECT_ID('tempdb..#Temp2') IS NOT NULL
     BEGIN
         DROP TABLE #Temp2
     END
     create table #temp2
     (
         AssignmentName nvarchar(100),
       [Status] [nvarchar](100) NULL,
       [RowID] [varchar](100) NULL,
       [IP_Address] [nvarchar](50) NULL
      )

     insert into #temp2
     (AssignmentName,status,RowID,IP_Address) select SUBSTRING(LTRIM(RTRIM(AssignmentName)),1,CHARINDEX(' ',LTRIM(RTRIM(AssignmentName)))-1), Status, RowID, IP_Address
     from #Temp

     --Selecting the data required using PIVOT(Convert Rows to columns using 'Pivot')

     SELECT bsnAPSerialNumber,bsnAPName, bsnApIpAddress,bsnAPLocation,bsnAPIOSVersion,bsnAPModel,RowID, IP_Address from
     (
     Select * from #temp2) As BaseData
     PIVOT (
             max(Status)
       For AssignmentName
       IN([bsnAPSerialNumber],[bsnAPName],[bsnApIpAddress],[bsnAPLocation],[bsnAPIOSVersion],[bsnAPModel])
     )
     AS PivotTable

      --For specific RowID results, use where clause

     --WHERE
     --ROWID='0.38.11.201.110.0'
Cancel
Vote Up +1 Vote Down

Cancel
0 wickedoz over 7 years ago in reply to jkrenzien

Thanks! That got me in the right direction.
This is the query that I ended up coming up with:
IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
     BEGIN
         DROP TABLE #TEMP
     END
SELECT *
     INTO #TEMP
     FROM
     (
SELECT
     NodesData.Caption AS 'Device',
NodesData.IP_Address AS 'IP',
CustomPollerAssignmentView.CustomPollerName,
CustomPollerAssignmentView.CurrentValue
FROM CustomPollerStatus
INNER JOIN CustomPollerAssignmentView
ON (CustomPollerStatus.CustomPollerAssignmentID = CustomPollerAssignmentView.CustomPollerAssignmentID)
LEFT OUTER JOIN CustomPollers
ON (CustomPollerAssignmentView.CustomPollerID = CustomPollers.CustomPollerID)
RIGHT JOIN NodesData
ON (CustomPollerAssignmentView.NodeID = NodesData.NodeID)
WHERE NodesData.MachineType = 'SMARTUPS2') AS TEMP
SELECT Device, IP,
[upsBasicOutputStatus] AS Status,
[upsAdvIdentSerialNumber] AS Serial_Num,
[upsAdvIdentFirmwareRevision] AS Firmware,
[upsAdvIdentDateOfManufacture] AS Mfr_Date,
[upsAdvBatteryTempFahrenheit] AS Bat_Temp,
[upsBasicBatteryTimeOnBattery] AS Bat_Time_On,
[upsAdvBatteryRunTimeRemaining] AS Bat_Time_Remain,
[upsAdvBatteryNumOfBattPacks] AS Bat_Packs,
[upsAdvBatteryNumOfBadBattPacks] AS Bat_Packs_Bad,
[upsAdvBatteryReplaceIndicator] AS Bat_Replace,
[upsBasicBatteryLastReplaceDate] AS Bat_Replace_Date,
[upsAdvOutputVoltage] AS Volt_Out,
[upsAdvOutputCurrent] AS Current_Out,
[upsAdvOutputLoad] AS Load,
[upsAdvInputMinLineVoltage] AS Min_Volt,
[upsAdvInputMaxLineVoltage] AS Max_Volt
FROM (SELECT CurrentValue, CustomPollerName, IP, Device
      FROM #TEMP) p
PIVOT
(
MAX (CurrentValue)
FOR CustomPollerName IN
([upsBasicOutputStatus],
[upsAdvIdentSerialNumber],
[upsAdvIdentFirmwareRevision],
[upsAdvIdentDateOfManufacture],
[upsAdvBatteryTempFahrenheit],
[upsBasicBatteryTimeOnBattery],
[upsAdvBatteryRunTimeRemaining],
[upsAdvBatteryNumOfBattPacks],
[upsAdvBatteryNumOfBadBattPacks],
[upsAdvBatteryReplaceIndicator],
[upsBasicBatteryLastReplaceDate],
[upsAdvOutputVoltage],
[upsAdvOutputCurrent],
[upsAdvOutputLoad],
[upsAdvInputMinLineVoltage],
[upsAdvInputMaxLineVoltage])) AS pvt
ORDER BY pvt.Device, IP
DROP TABLE #TEMP
Cancel
Vote Up +1 Vote Down

Cancel