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'