Interface Utilization for consecutive XX Minutes

Question

Hi Forum,

Is there a way to report peak interface utilization which exceeds X% for more than XX consecutive minutes during business hours?

Thanks in advance.

sunvad · Answer

Thank you @bobmarley, this one definitely worked. It is a pretty long list based on my filter :-) Is there a way to pull this report only during business hours, say 9am to 5pm in the last 30 days/last month time period? thanks again.

bobmarley · Answer

Here is another way that may be easier to read and follow. Included the interface percentages in and out and did the Where differently to include devices with the word ROUTER in the in the name.

The Where calculated on percentage of in or out traffic.

--Changed this down to 5 minute intervals to get all polls, match your interface polling time
SELECT tolocal (downsample  (h.DateTime ,'00:5:00')) as [Timestamp],

--Added Utilization in and out in percentage
N.Caption as [Device Name], I.Caption as [Interface Name], I.inBandwidth as [Bandwidth], I.InPercentUtil as [Utilization In], I.OutPercentUtil as [Utilization Out],
avg(H.InAveragebps) as [AverageIn],
avg(H.OutAveragebps) as [AverageOut]

FROM Orion.Nodes N
INNER JOIN Orion.NPM.Interfaces AS I 
ON N.NodeID = I.NodeID
INNER JOIN Orion.NPM.InterfaceTraffic AS H 
ON I.InterfaceID = H.InterfaceID

--Change the name ROUTER to match nodes in your network.
WHERE N.Caption LIKE '%ROUTER%' AND h.DateTime > ADDDATE('Hour', -1, GETDATE()) AND I.InPercentUtil >= 90 OR N.Caption LIKE '%rtr-%' AND h.DateTime > ADDDATE('Hour', -1, GETDATE()) AND I.OutPercentUtil >= 90

GROUP BY
N.Caption, I.Caption, I.inBandwidth, I.InPercentUtil, I.OutPercentUtil, downsample(H.DateTime,'00:5:00')

Order By I.Caption DESC

bobmarley · Answer

I put some comments in the query below.

SELECT tolocal (downsample (h.DateTime ,'00:5:00')) as [Timestamp], this is the average for 5 minutes. If you change it don't forget to change it in both places it shows up.The circuit I was using was 10GB or 10,000,000,000. 90 percent of that is 
 9,000,000,000

That is shown in the Where clause as H.InAveragebps >= '9000000000' also H.OutAveragebps >= '9000000000'

This line tells the query to get only the last hour, adjust to your needs ADDDATE('Hour', -1, GETDATE()

The query will only report results for time periods that exceed 90 percent for each 5 minute average.

--Changed this down to 5 minute intervals to get all polls, match your interface polling time
SELECT tolocal (downsample  (h.DateTime ,'00:5:00')) as [Timestamp],

N.Caption as [Device Name], I.Caption as [Interface Name], I.inBandwidth as [Bandwidth],
avg(H.InAveragebps) as [AverageIn],
avg(H.OutAveragebps) as [AverageOut]

FROM Orion.Nodes N
INNER JOIN Orion.NPM.Interfaces AS I 
ON N.NodeID = I.NodeID
INNER JOIN Orion.NPM.InterfaceTraffic AS H 
ON I.InterfaceID = H.InterfaceID

--Put your node and interface name here ADDDATE('Hour', -1 tells the query how far to go back. The circuit I used had a bandwidth of 10000000000, 90 percent of that is 
--9000000000 so this will display only the times when the circuit was over 90 percent.

WHERE N.Caption LIKE '%YOURNODENAME%' AND I.Caption LIKE '%YOURCIRCUITNAME%' AND h.DateTime > ADDDATE('Hour', -1, GETDATE()) AND H.InAveragebps >= '9000000000' OR N.Caption LIKE '%bdc-rtr-isp-1111-1-NEW%' AND I.Caption LIKE '% Circuit ID: L4YS750288%' AND h.DateTime > ADDDATE('Hour', -1, GETDATE()) AND H.OutAveragebps >= '9000000000'

--Changed this down to 5 minute intervals to get all polls, match your interface polling time
GROUP BY
N.Caption, I.Caption, I.inBandwidth,  downsample(H.DateTime,'00:5:00')

Order By Timestamp DESC