Hi All,
No working knowledge of SWQL here - but I managed to cobble together a simple report within Orion Solarwinds where I can leverage a table of specific interfaces for the 90/95/99th percentile for 30days.
I was looking if someone could point me in the right direction how to modify the SWQL query to instead look for a custom property, ideally an interface tag, as opposed to the InterfaceID.
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(- 30, 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(- 30, 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(- 30, 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(- 30, 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(- 30, 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(- 30, GETUTCDATE())
ORDER BY OutAveragebps ASC
)
) AS Maxbps_Out99
FROM Orion.NPM.InterfaceTraffic AS OuterInterfaceTraffic
WHERE OuterInterfaceTraffic.DATETIME >= AddDay(- 30, GETUTCDATE())
GROUP BY OuterInterfaceTraffic.InterfaceID
) TrafficStat ON Interfaces.InterfaceID = TrafficStat.InterfaceID
WHERE Interfaces.InterfaceID = '216432'
OR Interfaces.InterfaceID = '176107'
OR Interfaces.InterfaceID = '181596'
