Report to include multiple custom pollers

I'm not a SQL guy, and I was hoping someone out there could help me out. I run all HP boxes, and I've got a series of custom pollers that is polling for information such as NIC redundancy condition, Power supply redundancy, hardware model, and Management version. I downloaded these custom pollers from Thwack. I've been able to generate a custom SQL report that grabs the node name, vendor icon, IP, and the status of one of the custom pollers mentioned above -- but only one of them. I have to generate a new report for each item. I was hoping someone could help me find a way to get each one of those items on the same report. Before I show the code, here are two difficulties we had in getting this report to work. First, it will only poll the data if the custom poller is set to keep history. Second, unless you specify the time element, you will get every single instance that the poller generated for each node assigned. Here is the code I'm using for the current report (that only shows 1 of the stats):

SELECT Nodes.Caption AS NodeName,

--Nodes.Vendor AS Vendor,
Nodes.VendorIcon AS Vendor_Icon,
Nodes.IP_Address AS IP_Address,
Nodes.Location AS Location,

CustomNodePollers_CustomPollers.UniqueName AS Poller_Name,

CustomNodePollerStatistics_CustomPollerStatistics.Status AS Status,

CustomNodePollerStatistics_CustomPollerStatistics.DateTime

FROM

((Nodes INNER JOIN CustomPollerAssignment CustomNodePollerAssignment_CustomPollerAssignment ON (Nodes.NodeID = CustomNodePollerAssignment_CustomPollerAssignment.NodeID)) INNER JOIN CustomPollerStatistics CustomNodePollerStatistics_CustomPollerStatistics ON (CustomNodePollerAssignment_CustomPollerAssignment.CustomPollerAssignmentID = CustomNodePollerStatistics_CustomPollerStatistics.CustomPollerAssignmentID)) INNER JOIN CustomPollers CustomNodePollers_CustomPollers ON (CustomNodePollerAssignment_CustomPollerAssignment.CustomPollerID = CustomNodePollers_CustomPollers.CustomPollerID)

WHERE

CustomNodePollerStatistics_CustomPollerStatistics.DateTime >= DATEADD(mi, -5,GETUTCDATE())

AND

(CustomNodePollers_CustomPollers.UniqueName = 'Server_Model')

--Change the above line so that the UniqueName equals the custom poller you want the report to use

AND

(

(CustomNodePollerAssignment_CustomPollerAssignment.InterfaceID = 0)

)

ORDER BY Location,IP_Address ASC

Thanks in advance,

Brandon

Find more posts tagged with

sql

report

Accepted answers

All comments

branfarm

Can anybody help me out?

AlbanyNYMike

Bran,

I would set up the report entirely different. I would start with creating a new report. Select the current status of Nodes, interfaces, etc template.

Select the following fileds. Vender Icon sort *, Node name sort ascending, IP address sort *, Poller Name sort *, Status sort *

Filter results

you may have a way to filter for only the nodes you want, if you dont you will have to do the foloowing:

You will need a custom property to define the nodes you want included in the results.

After you set that up you can filter accordingly.

Records where "your custom proprty group" is equal to "the value you created in the custom property"

records where poller name is equal to "poller xxx"

record where poller name is equal to "poller yyy" etc.

You can do the same for historical data, just create a new report using the custom pollers historical template.

Hope that helps!

DirtySouth

Bump....I'm trying to do something similar. I would like to create a report that contains data from two different custom pollers. The "Current Node Status" reports only allow me to report on one of the custom pollers. Anyone else have thoughts on how to do this or example code?

qle

Can you be more specific about what you're trying to accomplish or what information you'd like to include on the report?