Hourly Custom Poller Status From Previous Day - SWQL Query Help

[cameljake]

Hello,

I'm trying to create an SWQL query that returns the value of a custom poller for each hour in the previous day. I understand how to gather the data from the pollers but I am a total noob when it comes to the date time stuff. Specifically, I'm trying to get it to return one hour per row, for each hour in the previous day. So there would be a row for 12:00AM, 1:00AM etc all the way to 11PM with the corresponding custom poller status at that time.

The query that I have right now minus the DateTime stuff looks like this:

SELECT cps.Status
FROM Orion.NPM.CustomPollerStatistics cps
left outer join Orion.NPM.CustomPollerAssignment cpa On (cpa.CustomPollerAssignmentID = cps.CustomPollerAssignmentID)
left outer join Orion.NPM.CustomPollers cp On (cp.CustomPollerID = cpa.CustomPollerID)
Where CustomPollerName='F5_MyAccess'

If I'm correct, this query returns the Status of the custom poller named "F5_MyAccess" at a given time. If anyone can help me out with narrowing it down to an hour by hour for the previous day it would be super helpful!

[cameljake · Accepted solution]

I actually just figured out how to do this. Here is the query if anyone else needs help with this

SELECT top 100 N.Caption as Name, cps.Status, tolocal(DATETRUNC('hour',CPS.DateTime)) as date
FROM Orion.Nodes N
INNER JOIN Orion.NPM.CustomPollerAssignment CPA ON N.NodeID=CPA.NodeID
FULL JOIN Orion.NPM.CustomPollerStatistics CPS ON CPS.CustomPollerAssignmentID=CPA.CustomPollerAssignmentID
INNER JOIN Orion.NodesCustomProperties CP ON CP.NodeID= N.NodeID
Where CustomPollerName='F5_MyAccess' AND daydiff(tolocal(CPS.datetime),tolocal(GetDate())) = 1 AND Minute(cps.DateTime) = '0'
GROUP BY tolocal(DateTrunc('hour', CPS.DateTime)), cps.Status, N.Caption
Order By tolocal(DateTrunc('hour', CPS.DateTime))

[bobmarley]

Thanks for posting this!