2 Replies Latest reply on Sep 21, 2016 10:54 AM by wickedoz

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

    wickedoz

      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.

        • Re: Needed - UPS Universal Device Poller Status values for all UPS listed in a single report
          jkrenzien

          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'

            • Re: Needed - UPS Universal Device Poller Status values for all UPS listed in a single report
              wickedoz

              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