SWQL Query to Group Results in increments of 5 minutes

Question

Hello,

I'm trying to create an SWQL query that groups the stats of a custom poller in 5 minute intervals. Currently the poller is configured to poll every minute, but I don't need it to be that granular. Just the statistic that was captured at the 5 minute mark each time it was polled. I have the rest of the query ready but I'm unsure on how to do the DateTime part of the query. I'm struggling to figure out how some of this DateTime stuff works so any insight would be appreciated. Here is the query minus the date time stuff:

SELECT N.Caption as Name, cps.Status, {datetime stuff goes here} 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
GROUP BY {datetime stuff goes here}, n.caption, cps.status

mesverrum · Accepted Answer

This looks like it'll do the job

SELECT N.Caption as Name, cps.Status, ADDMINUTE(MINUTEDIFF('2000', cps.DateTime) / 5 * 5, '2000') 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 daydiff(tolocal(CPS.datetime),tolocal(GetDate())) = 1
and CustomPollerName='F5_MyAccess'
GROUP BY n.caption, cps.status, ADDMINUTE(MINUTEDIFF('2000', cps.DateTime) / 5 * 5, '2000')

Modified for SWQL from this thread, https://stackoverflow.com/questions/5002661/how-to-group-time-by-hour-or-by-10-minutes

This logic will get weird results if a particular node happens to be polled more than once in that 5 min window and got different status codes, like if someone manually forced a poll after making a change, but I'm figuring that may not be super important to your use case.

If you want to change the want the timestamps are grouped just swap out the 5's for 10's or 30's or whatever

SWQL Query to Group Results in increments of 5 minutes

Top Replies