How to average detailed data into hourly averages with SWQL?

johnny_ringo

Hello all,

I am trying to create a report that documents the hourly average and max of VPNs that are up across my remote sites over the last week.  I have the query mostly working but the I can't figure out how to get SWQL to actually calculate the aggregate functions for my data.  Right now it is just truncating the datetime field to the hour, but it is still giving me mulitple records per system, per hour.  Can anyone point out what I am missing?  Thank you for your help!

SELECT

N.Caption,

CP.City,

CPA.AssignmentName,

AVG(CPS.RawStatus) AS AVG_VPNs,

MAX(CPS.RawStatus) AS MAX_VPNs,

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 N.MachineType LIKE '%ASA%' AND AssignmentName LIKE '%Tunnels%' AND CP.City NOT LIKE '%HQ%'

AND DateTime > GetDAte()-7

GROUP BY N.Caption , CP.City, CPS.Datetime, CPA.AssignmentNAme

ORDER BY CP.City ASC

tdanner

You are SELECTing the timestamp truncated to the hour, but you are GROUPing BY the raw timestamp. Change the GROUP BY clause to this:

GROUP BY N.Caption , CP.City, DateTrunc('hour', CPS.DateTime), CPA.AssignmentName