SWQL Calculate Percentages and Filter to only above 75%

I was kicking around the API recently and happened upon this little fun bit.  You don't need to do your own calculations. Those are done by the Orion.NPM.InterfacePercentiles entity.

SELECT [Interfaces].Node.NodeID
      , [Interfaces].Node.Caption AS [NodeCaption]
      , [Interfaces].Node.VendorInfo.DisplayName AS [Vendor]
      , [Interfaces].Node.VendorInfo.Icon AS [_IconFor_Vendor]
      , [Interfaces].InterfaceID
      , [Interfaces].Caption AS [Interface]
      , [Interfaces].DetailsUrl AS [_LinkFor_Interface]
      , [Interfaces].InBandwidth
      , [Interfaces].OutBandwidth
      , [Percents].AverageInboundBps90th
      , [Percents].AverageOutboundBps90th
      , [Percents].AverageInboundBps95th
      , [Percents].AverageOutboundBps95th
      , [Percents].AverageInboundBps99th
      , [Percents].AverageOutboundBps99th
FROM (
      SELECT InterfaceID
            , AverageInboundBps90th
            , AverageOutboundBps90th
            , AverageInboundBps95th
            , AverageOutboundBps95th
            , AverageInboundBps99th
            , AverageOutboundBps99th
      FROM Orion.NPM.InterfacePercentiles
      -- you MUST put a time filter here - the shorter the better (for performance)
      WHERE [DateTime] > ADDDAY(- 30, GETUTCDATE())
      ) AS [Percents]
JOIN Orion.NPM.Interfaces AS [Interfaces]
      ON [Interfaces].InterfaceID = [Percents].InterfaceID

This should have everything you were looking for.

In addition to what KMSigma said, you can pretty much just use formula as you mentioned as long as if you're SELECTing it you give it a column name (AS x). If you were filtering with it you can probably just whack something like that in the WHERE. Sometimes you may need a subquery but doesnt seem like you'd need anything extra so far.

I am probably doing something wrong but it still returns the data as bps and not percents. Example of the output below.

Not sure what your ideal data would look like, could you mock up an example or elaborate?

(If the 90th percentile was selected as a percentage, it'd show 90 i guess?)

It should show the percentage utilization on the percentile for the interface vs the speed (Bps). Currently, I take the data as an CSV export and apply this formula (AverageOutboundBps90th / InterfaceSpeed = PercentUtilizationOutbound90th) to get the utilization and filter only stuff above 75% for 90th, 95th, 99th.

This formula does not work. I think I.InterfaceSpeed is a string and not an integer. I simplified the SWQL to only one of the percentiles. The query errors out with a generic error. If this formula can work then it gets what I need.

    AverageOutboundBps90th / I.InterfaceSpeed AS PercentUtilizationOutbound90th

SELECT 
    I.InterfaceID,
    I.InterfaceSpeed,
    N.NodeID,
    N.Caption AS NodeName,
    I.Caption AS Interface_Caption,
    AverageOutboundBps90th / I.InterfaceSpeed AS PercentUtilizationOutbound90th
FROM
(
    SELECT InterfaceID,
        AverageOutboundBps90th
    FROM Orion.NPM.InterfacePercentiles
    WHERE [DateTime] > ADDDAY(- 7, GETUTCDATE())
) AS IP
JOIN Orion.NPM.Interfaces I ON I.InterfaceID = IP.InterfaceID
JOIN Orion.Nodes N ON I.NodeID = N.NodeID;

I dont get how that doesnt output 90, but if it's a string/interger type problem, multiply the string by 1

It reports the Bps not the percent. The 90th percentile is the point at which 90 percent of a given population falls below it. In other words, it's the cutoff for being in the top 10 percent of any given group.

The multiply the string by 1 does not work. Same generic error.

Here is the output of it working and reporting AverageOutboundBps90th in Bps.

Here is the preview results with the multiply by 1.

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:

Ok that makes more sense, was looking at "it still returns the data as bps and not percents"

To not get a generic error, pop it in swql studio

SELECT
I.InterfaceID,
I.InterfaceSpeed,
N.NodeID,
N.Caption AS NodeName,
I.Caption AS Interface_Caption,
AverageOutboundBps90th / I.InterfaceSpeed AS PercentUtilizationOutbound90th
FROM
(
SELECT InterfaceID,
AverageOutboundBps90th
FROM Orion.NPM.InterfacePercentiles
WHERE [DateTime] > ADDDAY(- 7, GETUTCDATE())
) AS IP
JOIN Orion.NPM.Interfaces I ON I.InterfaceID = IP.InterfaceID
JOIN Orion.Nodes N ON I.NodeID = N.NodeID;

Running your block above in my environment works fine, but is real slow, wouldnt be surprised if you were timing out, perhaps there's a filter applied on the page this lives on that cuts it down a bit, but otherwise it's pulling a week of history for every interface in your environment

This is awesome! This is exactly I was looking for! Thanks!