2 Replies Latest reply on Nov 23, 2016 2:54 PM by johnny ringo

    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