Hi, has anyone been successful in inserting an interface filter in out-of-the-box 95th percentile traffic reports in Orion? It uses a SWQL query, and if I insert a filter for a single interface, it would work. However, the tricky part is filtering for multiple interfaces (or devices), because when I do, it just provides me the same, repeated traffic rate for each interface.
The original out-of-the-box query is below:
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_In95, Maxbps_Out95, Maxbps_95 FROM Orion.Nodes INNER JOIN Orion.NPM.Interfaces ON Nodes.NodeID = Interfaces.NodeID INNER JOIN ( SELECT OuterInterfaceTraffic.InterfaceID, (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(Maxbps) as maxMaxbps FROM ( SELECT TOP 95 PERCENT Maxbps FROM (SELECT (CASE WHEN OutAveragebps > InAveragebps THEN OutAveragebps ELSE InAveragebps END) AS Maxbps FROM Orion.NPM.InterfaceTraffic WHERE InterfaceID = OuterInterfaceTraffic.InterfaceID AND DateTime >= AddDay(-7, GETUTCDATE())) AS MaxbpsSet ORDER BY Maxbps ASC )) AS Maxbps_95 FROM Orion.NPM.InterfaceTraffic AS OuterInterfaceTraffic WHERE OuterInterfaceTraffic.DateTime >= AddDay(-7, GETUTCDATE()) GROUP BY OuterInterfaceTraffic.InterfaceID ) TrafficStat ON Interfaces.InterfaceID = TrafficStat.InterfaceID
The query I modified to filter on a specific interface:
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_In95, Maxbps_Out95, Maxbps_95
FROM Orion.Nodes INNER JOIN Orion.NPM.Interfaces ON Nodes.NodeID = Interfaces.NodeID
INNER JOIN ( SELECT OuterInterfaceTraffic.InterfaceID,
(SELECT MAX(InAveragebps) as maxInMaxbps FROM
( SELECT TOP 95 PERCENT InAveragebps FROM Orion.NPM.InterfaceTraffic WHERE InterfaceID = '107949' 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 = '107949' AND DateTime >= AddDay(-7, GETUTCDATE()) ORDER BY OutMaxbps ASC )) AS Maxbps_Out95,
(SELECT MAX(Maxbps) as maxMaxbps FROM
( SELECT TOP 95 PERCENT Maxbps FROM
(SELECT
(CASE WHEN OutAveragebps > InAveragebps THEN OutAveragebps ELSE InAveragebps END) AS Maxbps FROM Orion.NPM.InterfaceTraffic WHERE InterfaceID = '107949' AND DateTime >= AddDay(-7, GETUTCDATE())
) AS MaxbpsSet ORDER BY Maxbps ASC
)
) AS Maxbps_95 FROM Orion.NPM.InterfaceTraffic AS OuterInterfaceTraffic WHERE OuterInterfaceTraffic.DateTime >= AddDay(-7, GETUTCDATE()) AND OuterInterfaceTraffic.InterfaceID = '107949' GROUP BY OuterInterfaceTraffic.InterfaceID ) TrafficStat ON Interfaces.InterfaceID = TrafficStat.InterfaceID
When I modify the above query to display multiple interfaces (by enumerating multiple interface IDs), I get the same data for each interface.
Hope someone has encountered this and has found a solution. Thanks in advance.
Regards,
Paulo
