This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Get the 90/95/99th Percentile Traffic Rate Report for the Last 2 hours

Hi, Can anyone help me to tweak a report using SWQL/SQL query? I need to extract all the Top 25 Interface Utilization with 90/95/99th Percentile Traffic every 2 hours? 

SELECT Interfaces.InterfaceId
	,Nodes.NodeID
	,Nodes.Caption AS NodeName
	,Nodes.DetailsUrl AS NDetailsUrl
	,Nodes.VendorIcon AS Vendor
	,Interfaces.Caption AS Interface_Caption
	,Interfaces.InterfaceIcon AS Interface_Icon
	,Interfaces.DetailsUrl AS IDetailsUrl
	,Maxbps_In90
	,Maxbps_Out90
	,Maxbps_In95
	,Maxbps_Out95
	,Maxbps_In99
	,Maxbps_Out99
FROM Orion.Nodes
INNER JOIN Orion.NPM.Interfaces ON Nodes.NodeID = Interfaces.NodeID
INNER JOIN (
	SELECT OuterInterfaceTraffic.InterfaceID
		,(
			SELECT MAX(InAverageBps) AS maxInAverageBps
			FROM (
				SELECT TOP 90 PERCENT InAveragebps
				FROM Orion.NPM.InterfaceTraffic
				WHERE InterfaceID = OuterInterfaceTraffic.InterfaceID
					AND DATETIME >= AddDay(- 7, GETUTCDATE())
				ORDER BY InAveragebps ASC
				)
			) AS Maxbps_In90
		,(
			SELECT MAX(OutAverageBps) AS maxOutAverageBps
			FROM (
				SELECT TOP 90 PERCENT OutAveragebps
				FROM Orion.NPM.InterfaceTraffic
				WHERE InterfaceID = OuterInterfaceTraffic.InterfaceID
					AND DATETIME >= AddDay(- 7, GETUTCDATE())
				ORDER BY OutAveragebps ASC
				)
			) AS Maxbps_Out90
		,(
			SELECT MAX(InAveragebps) AS maxInMaxbps
			FROM (
				SELECT TOP 95 PERCENT InAveragebps
				FROM Orion.NPM.InterfaceTraffic
				WHERE InterfaceID = OuterInterfaceTraffic.InterfaceID
					AND DATETIME >= AddDay(- 7, GETUTCDATE())
				ORDER BY InMaxbps ASC
				)
			) AS Maxbps_In95
		,(
			SELECT MAX(OutAveragebps) AS maxOutMaxbps
			FROM (
				SELECT TOP 95 PERCENT OutAveragebps
				FROM Orion.NPM.InterfaceTraffic
				WHERE InterfaceID = OuterInterfaceTraffic.InterfaceID
					AND DATETIME >= AddDay(- 7, GETUTCDATE())
				ORDER BY OutMaxbps ASC
				)
			) AS Maxbps_Out95
		,(
			SELECT MAX(InAverageBps) AS maxInAverageBps
			FROM (
				SELECT TOP 99 PERCENT InAveragebps
				FROM Orion.NPM.InterfaceTraffic
				WHERE InterfaceID = OuterInterfaceTraffic.InterfaceID
					AND DATETIME >= AddDay(- 7, GETUTCDATE())
				ORDER BY InAveragebps ASC
				)
			) AS Maxbps_In99
		,(
			SELECT MAX(OutAverageBps) AS maxOutAverageBps
			FROM (
				SELECT TOP 99 PERCENT OutAveragebps
				FROM Orion.NPM.InterfaceTraffic
				WHERE InterfaceID = OuterInterfaceTraffic.InterfaceID
					AND DATETIME >= AddDay(- 7, GETUTCDATE())
				ORDER BY OutAveragebps ASC
				)
			) AS Maxbps_Out99
	FROM Orion.NPM.InterfaceTraffic AS OuterInterfaceTraffic
	WHERE OuterInterfaceTraffic.DATETIME >= AddDay(- 7, GETUTCDATE())
	GROUP BY OuterInterfaceTraffic.InterfaceID
	) TrafficStat ON Interfaces.InterfaceID = TrafficStat.InterfaceID
WHERE Interfaces.InterfaceID = '378387'




Parents
  • You want the above level of detail but summarized for specific each 2 hour blocks?

    Like this?

    Node Name  Interface Name  Timeframe  Max In BPS (90th Percentile)  Max In BPS (95th Percentile)  Max In BPS (99th Percentile)  Max Out BPS (90th Percentile)  Max Out BPS (95th Percentile)  Max Out BPS (99th Percentile)
    SampleNode1  eth0/11  2022-07-27 12:00 AM 12.66 12.99 15.02 12.66 12.99 15.02
    SampleNode1  eth0/11  2022-07-27 02:00 AM 14.66 16.99 19.02 22.66 22.99 25.02

    Or did you want it broken down in a different way?

  • Hi, I need the query to get all the interface with high bandwidth utilization including 90th/95th/99th for the past 2 hours. Would it be possible? Or just the Top 50 Interface.

  • Oh!  Just for the most recent 2 hours and not a 2-hour by 2-hour breakdown?

    You could just change the WHERE clause filters on the InterfaceTraffic time fields (be sure to do each one).  Just alter it from:

    -- Pulls the info for the last week
    WHERE InterfaceID = OuterInterfaceTraffic.InterfaceID
    AND DATETIME >= AddDay(- 7, GETUTCDATE())

    to:

    -- Pulls the info for the last 2 hours
    WHERE InterfaceID = OuterInterfaceTraffic.InterfaceID
    AND DateTime >= AddHour(- 2, GETUTCDATE())

Reply
  • Oh!  Just for the most recent 2 hours and not a 2-hour by 2-hour breakdown?

    You could just change the WHERE clause filters on the InterfaceTraffic time fields (be sure to do each one).  Just alter it from:

    -- Pulls the info for the last week
    WHERE InterfaceID = OuterInterfaceTraffic.InterfaceID
    AND DATETIME >= AddDay(- 7, GETUTCDATE())

    to:

    -- Pulls the info for the last 2 hours
    WHERE InterfaceID = OuterInterfaceTraffic.InterfaceID
    AND DateTime >= AddHour(- 2, GETUTCDATE())

Children