cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 9

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

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.

2 Replies
Level 12

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'

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