0 Replies Latest reply on Apr 25, 2018 3:21 PM by johnny ringo

    Hourly Average bps- Need SWQL Help

    johnny ringo

      All,

      I have the below report that is intended to give me an hourly average bps over the last 24 hours.  However my data doesnt appear to be getting averaged over the hour, as I have multiple entries per hour for the same interface.  Can anyone clue me in to what is messed up in my query?  thank you!

       

      SELECT I.FullName, DateTrunc('Hour', NIT.DateTime) AS Date, ROUND(AVG(InAveragebps)/1000000, 1) AS AVG_IN_MBPS,  ROUND(MIN(InMinbps)/1000000, 2) AS MIN_MBPS, ROUND(MAX(InMaxbps)/1000000, 2) AS MAX_MBPS

       

      FROM Orion.NPM.InterfaceTraffic NIT

      INNER JOIN Orion.NPM.Interfaces I ON I.InterfaceID = NIT.InterfaceID

      INNER JOIN Orion.NodesCustomProperties CP ON CP.NodeID = NIT.NodeID

      WHERE I.FullName LIKE '%outside%' AND CP.City NOT LIKE '%MAIN%'

      AND DateTrunc('DAY', NIT.DateTime) >= DateTrunc('DAY', getDate()-1)

      GROUP BY I.FullName, NIT.DATETIME

      ORDER BY ROUND(AVG(InAveragebps)/1000000, 1) DESC