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