Hourly Average bps- Need SWQL Help

Question

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

mesverrum · Accepted Answer

I vpn'ed into the lab to check, whatever you were doing with your datetime where condition, that seems weird. Are you trying to get entries just for yesterday, or the last 24 hours? I guess what I'm seeing in your original query is a data point for every hour between the start of the day yesterday and now?

This is what i'd do for for last 24 hours:

SELECT I.FullName

, DateTrunc('Hour', NIT.DateTime) AS Date

, ROUND(AVG(nit.InAveragebps)/1000000, 1) AS AVG_IN_MBPS

, ROUND(MIN(nit.InMinbps)/1000000, 2) AS MIN_MBPS

, ROUND(MAX(nit.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 = i.NodeID

WHERE I.FullName LIKE '%outside%'

AND CP.City NOT LIKE '%MAIN%'

AND nit.datetime >= addday(-1,getutcDate())

GROUP BY I.FullName, DateTrunc('Hour', NIT.DateTime)

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

and for previous day

SELECT I.FullName

, DateTrunc('Hour', NIT.DateTime) AS Date

, ROUND(AVG(nit.InAveragebps)/1000000, 1) AS AVG_IN_MBPS

, ROUND(MIN(nit.InMinbps)/1000000, 2) AS MIN_MBPS

, ROUND(MAX(nit.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 = i.NodeID

WHERE I.FullName LIKE '%outside%'

AND CP.City NOT LIKE '%MAIN%'

AND daydiff(nit.datetime,getutcDate()) = 1

GROUP BY I.FullName, DateTrunc('Hour', NIT.DateTime)

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

Hourly Average bps- Need SWQL Help

Top Replies