SWQL Calculate Percentages and Filter to only above 75%

Question

Solarwinds Gurus.. I been trying to learn SWQL but struggling to get percentages of 90, 95, 99 percentile from interface speed. The formula should be (% Utilization (90th) = Average Output bps (95) / Interface Speed) then filter that column for anything above 75%. Below is the current working report that is a canned report by Solarwinds. 
 
 SELECT 
 I.InterfaceID,
 I.InterfaceSpeed,
 N.NodeID,
 N.Caption AS NodeName,
 N.DetailsUrl AS NDetailsUrl,
 N.VendorIcon AS Vendor,
 I.Caption AS Interface_Caption,
 I.InterfaceIcon AS Interface_Icon,
 I.DetailsUrl AS IDetailsUrl,
 AverageInboundBps90th,
 AverageOutboundBps90th,
 AverageInboundBps95th,
 AverageOutboundBps95th,
 AverageInboundBps99th,
 AverageOutboundBps99th
FROM
(
 SELECT 
 InterfaceID,
 AverageInboundBps90th,
 AverageOutboundBps90th,
 AverageInboundBps95th,
 AverageOutboundBps95th,
 AverageInboundBps99th,
 AverageOutboundBps99th
 FROM Orion.NPM.InterfacePercentiles
 WHERE [DateTime] > (GETUTCDATE()-30)
) AS IP
JOIN Orion.NPM.Interfaces I ON I.InterfaceID = IP.InterfaceID
JOIN Orion.Nodes N ON I.NodeID = N.NodeIDSELECT 
 I.InterfaceID,
 I.InterfaceSpeed,
 N.NodeID,
 N.Caption AS NodeName,
 N.DetailsUrl AS NDetailsUrl,
 N.VendorIcon AS Vendor,
 I.Caption AS Interface_Caption,
 I.InterfaceIcon AS Interface_Icon,
 I.DetailsUrl AS IDetailsUrl,
 AverageInboundBps90th,
 AverageOutboundBps90th,
 AverageInboundBps95th,
 AverageOutboundBps95th,
 AverageInboundBps99th,
 AverageOutboundBps99th
FROM
(
 SELECT 
 InterfaceID,
 AverageInboundBps90th,
 AverageOutboundBps90th,
 AverageInboundBps95th,
 AverageOutboundBps95th,
 AverageInboundBps99th,
 AverageOutboundBps99th
 FROM Orion.NPM.InterfacePercentiles
 WHERE [DateTime] > (GETUTCDATE()-30)
) AS IP
JOIN Orion.NPM.Interfaces I ON I.InterfaceID = IP.InterfaceID
JOIN Orion.Nodes N ON I.NodeID = N.NodeID

KMSigma.SWI · Accepted Answer

Are you looking for the percent utilization of that percentile bps? 
 Reported 90th Inbound / Max Inbound speed (In Bandwidth) = 90th Inbound % 
 Or something else and I'm completely off the mark. 
 I wrote this for some reason. 
 SELECT [Interfaces].Node.NodeID
 , [Interfaces].Node.Caption AS [NodeCaption]
 , [Interfaces].Node.VendorInfo.DisplayName AS [Vendor]
 , [Interfaces].InterfaceID
 , [Interfaces].Caption AS [Interface]
 , [Interfaces].DetailsUrl AS [_LinkFor_Interface]
 , [Interfaces].InBandwidth
 , [Interfaces].OutBandwidth
 , [Percents].AverageInboundBps90th
 -- DIVIDE BY ZERO LOGIC
 -- If the inbound speed is detected as 0, we can't divide by zero, so we have to ignore it
 , CASE 
 WHEN [Interfaces].InBandwidth > 0
 -- This will give us the raw percentage. We'll multiply by 100 for a prettier number
 THEN (([Percents].AverageInboundBps90th / [Interfaces].InBandwidth) * 100.0)
 ELSE 0
 END AS [In90thPercent]
 , [Percents].AverageOutboundBps90th
 , CASE 
 WHEN [Interfaces].OutBandwidth > 0
 -- This will give us the raw percentage. We'll multiply by 100 for a prettier number
 THEN (([Percents].AverageOutboundBps90th / [Interfaces].InBandwidth) * 100.0)
 ELSE 0
 END AS [Out90thPercent]
 , [Percents].AverageInboundBps95th
 , CASE 
 WHEN [Interfaces].InBandwidth > 0
 -- This will give us the raw percentage. We'll multiply by 100 for a prettier number
 THEN (([Percents].AverageInboundBps95th / [Interfaces].InBandwidth) * 100.0)
 ELSE 0
 END AS [In95thPercent]
 , [Percents].AverageOutboundBps95th
 , CASE 
 WHEN [Interfaces].OutBandwidth > 0
 -- This will give us the raw percentage. We'll multiply by 100 for a prettier number
 THEN (([Percents].AverageOutboundBps95th / [Interfaces].InBandwidth) * 100.0)
 ELSE 0
 END AS [Out95thPercent]
 , [Percents].AverageInboundBps99th
 , CASE 
 WHEN [Interfaces].InBandwidth > 0
 -- This will give us the raw percentage. We'll multiply by 100 for a prettier number
 THEN (([Percents].AverageInboundBps99th / [Interfaces].InBandwidth) * 100.0)
 ELSE 0
 END AS [In99thPercent]
 , [Percents].AverageOutboundBps99th
 , CASE 
 WHEN [Interfaces].OutBandwidth > 0
 -- This will give us the raw percentage. We'll multiply by 100 for a prettier number
 THEN (([Percents].AverageOutboundBps99th / [Interfaces].InBandwidth) * 100.0)
 ELSE 0
 END AS [Out99thPercent]
FROM (
 SELECT InterfaceID
 , AverageInboundBps90th
 , AverageOutboundBps90th
 , AverageInboundBps95th
 , AverageOutboundBps95th
 , AverageInboundBps99th
 , AverageOutboundBps99th
 FROM Orion.NPM.InterfacePercentiles
 -- a Date Span MUST be provided for this sub query to work
 WHERE [DateTime] > ADDDAY(- 1, GETUTCDATE())
 ) AS [Percents]
JOIN Orion.NPM.Interfaces AS [Interfaces]
 ON [Interfaces].InterfaceID = [Percents].InterfaceID
 
 The output looks something like this:

SWQL Calculate Percentages and Filter to only above 75%

Top Replies