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.

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

• LinkedIN: Loop1 Systems
• Facebook: Loop1 Systems
• Twitter: @Loop1Systems