2 Replies Latest reply on Feb 2, 2018 5:09 PM by cahunt

    NPM Report on Custom Pollers

    johnlad

      Hopefully someone can guide me in the right direction.  I am creating a report to indicate battery replacement dates and serial numbers for our monitored UPS's.   I can pull the information using the Poller Name from Custom Pollers, but when the report generates because these are show as status they end up in the same column in the report.  Ideally I was hoping to have something like

       

      Node     IP Address     Battery Replacement Date     Serial Number     Machine Type     Building     Vendor

       

      But I can't seem to figure out how to break Status into two columns one for serial and one for battery replacement date.  Is there a way to separate these? I know if I query the database they all show under status, but I am not sure how I can tell reporter write that they are different.

       

        • Re: NPM Report on Custom Pollers
          cahunt

          To do what you want you could use a pivot in a SQL report. the Issue with that is unless you have congruence in details from the MIB Tables the data you want to display together may or may not work well. Worst thing here is once you have your report setup you might have to separate different vendors or portions of your UnDP's into different reports with adjusted queries.

           

          Give this a go and see how well it shows for your APC's - note the Vendor Variable at the top, and the OID details in the where clause. Then reference your applied OIDs to different nodes to get what you need for another vendor. To pull the same table use the full OID minus the last column identifer (last #). Depending on the Vendors MIB setup you may or may not be able to shave more off of the OID to use a bigger wild card. Some vendors have MIB tables that work, and some have additional details that might 'mess up' your report. You could add a clause on the unique name <> X to exclude certain UnDP's but be careful of building too large a query that might adversly affect the report load time.

           

          Cheers!

           

           

          /* !Matrices for each vendor type and OID Table*/

          DECLARE @cols AS NVARCHAR(MAX),

              @query  AS NVARCHAR(MAX),

              @ven AS NVARCHAR(MAX)

           

           

          Set @ven = 'American Power Conversion Corp.'  -- Adjust the Vendor to create reports for other devices

           

           

          select @cols = STUFF((SELECT ',' + QUOTENAME(p.UniqueName)

                              FROM Nodes n

          JOIN CustomPollerAssignment ca ON n.NodeID = ca.NodeID

          JOIN CustomPollers p on ca.CustomPollerID = p.PollerID

          WHERE n.Vendor = @ven and (P.Enabled = 1) AND (

          (P.OID like '1.3.6.1.4.1.318.1.1.1.2.2.%')   -- If you change the OID here it also needs to change in the where clause to match in the set @query section.

          )

                              group by p.UniqueName

                              order by p.UniqueName

                      FOR XML PATH(''), TYPE

                      ).value('.', 'NVARCHAR(MAX)')

                  ,1,1,'')

           

           

          set @query = 'SELECT Vendor, NodeID, Caption, IP_Address, MachineType, RowID, ' + @cols + ' from

                       (

          SELECT

          n.Vendor

          ,n.NodeID

          ,n.Caption

          ,n.IP_Address

          ,n.MachineType

          ,p.GroupName

          ,p.UniqueName

          ,cps.RowID

          ,cps.Status as Status

           

           

          FROM Nodes n

           

           

          RIGHT JOIN CustomPollerAssignment cpa ON n.NodeID = cpa.NodeID

          LEFT JOIN CustomPollers p on cpa.CustomPollerID = p.PollerID

          RIGHT JOIN CustomPollerStatus cps on cps.CustomPollerAssignmentID = cpa.CustomPollerAssignmentID

           

           

           

          WHERE ((n.Vendor = ''' + @ven + ''') AND (P.OID like ''1.3.6.1.4.1.318.1.1.1.2.2.%''))   --- This needs to match the OID in the where clause above

           

           

                      ) x

                      pivot

                      (

                          max(status)

                          for UniqueName in (' + @cols + ')

                      ) p '

           

           

          execute(@query)

           

           

           

           

           

          -CharlesH

          Loop1 Systems: SolarWinds Training and Professional Services