cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 8

SWQL Query to Group Results in increments of 5 minutes

Jump to solution

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

Tags (2)
0 Kudos
1 Solution

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

- Marc Netterfield, Github

View solution in original post

3 Replies

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

- Marc Netterfield, Github

View solution in original post

Thank you so much! What does the '2000' represent in the query? 

0 Kudos
in this case it starts counting from the year 2000, but you can switch it to any time in the past thats older than the start of your data, it wakes however many minutes there have been since then, divides that number by 5 into whatever whole number, then adds that number back in to be the start of the 5 minute bucket.
- Marc Netterfield, Github