Solarwinds Orion/NPM business hours 95th percentile query

[Jesse McGraw <jlmcgraw () gmail com>]

( I'm not sure if this will be generally useful, but I needed it so I 
thought I'd share in case others may too )

I have a system that uses Solarwinds NPM/Orion to collect interface 
utilization data from devices scattered around the globe and I found 
myself needing to calculate 95th percentile values from it that only 
takes into account local business hours (i.e. no weekends or nights).

After much googling and banging around on the keyboard this is the query 
that I came up with.  As it stands you have to manually adjust the query 
for the timezones of the SQL database itself and the various devices 
you're querying, it would be smarter to add a custom field for each 
device representing its UTC offset and use that value in the query but I 
haven't made that happen yet

I am certainly no SQL maestro so I've also put it into a github 
repository in case anyone has ideas on how to improve it or fix any 
silly mistakes I've made

https://github.com/jlmcgraw/sql_queries_for_solarwinds_orion/blob/master/solarwinds_orion_95th_percentile_business_hours_sql_query.sql

--Jesse


-- This is a query to calculate 95th percentile statistics for bits in, 
bits out,
-- and a new column that is the max of bits in vs. bits out for each sample
-- only for business hours (i.e. excluding weekends and hours before / 
after work
-- hours)
--
-- Edit the "WHERE" statement in the 
"InterfaceTraffic_Detail_BusinessHours" CTE
--  to choose which devices you're querying
--
-- Developed/tested with
--  Microsoft SQL server 2014
--  Orion Platform 2017.1, NPM 12.1
--  Uses the detailed last 30 days view of interface statistics
--      [swnpm].[dbo].[InterfaceTraffic_Detail]
--      you may wish to use different input data
--
-- Issues
--  You currently must adjust the timezone setting manually and be sure 
to query
--      only devices that are all in the same timezone
--  Surely performance can be improved


-- To Do
--  Document adding a custom column with a UTC offset for each device 
and modify
--      this query to use that value instead
--  Account for standard vs. daylight savings time

DECLARE @SampleOffset Float
DECLARE @TargetDeviceOffset Float
DECLARE @TargetPercentile Float
DECLARE @StartBusinessHours Float
DECLARE @EndBusinessHours Float

-- The UTC offset of the timezone the samples are stored in
-- (i.e. where the database is)
SET @SampleOffset = -4.0

-- The UTC offset of the timezone where the target devices are
SET @TargetDeviceOffset = -4.0

-- Target percentile as a decimal
SET @TargetPercentile = 0.95

-- When do business hours start ( 0700 = 7am )
SET @StartBusinessHours = 7

-- When do business hours end ( 1800 = 6pm )
SET @EndBusinessHours = 18
;


WITH
    InterfaceTraffic_Detail_BusinessHours AS (
        -- Create a CTE showing only business hours data
        -- Also adding a MaxBps column
        SELECT
             i.DateTime
             ,i.interfaceid
             ,i.[In_Maxbps]
             ,i.[out_Maxbps]
            ,MaxBps =
                    CASE
                        --Use whichever is greater of IN vs. OUT
                        WHEN Out_Maxbps > In_Maxbps THEN Out_Maxbps
                        ELSE In_Maxbps
                    END

        FROM
            [swnpm].[dbo].[InterfaceTraffic_Detail] as I
            INNER JOIN [swnpm].[dbo].[Nodes]  as N
                ON (n.NodeID = [i].NodeID )

        WHERE
            (n.SysName LIKE '%pattern1%'
            -- or n.SysName LIKE '%pattern1%'
            -- or n.SysName LIKE '%pattern2%'
            -- or n.SysName LIKE '%pattern3%'
            -- or n.SysName LIKE '%pattern4%'
            )
            AND
            (
            --    This adjusts for both the timezone of the samples and 
the target device
            -- Not Saturday or Sunday after adjusting for timezones
 (DATEPART(dw,DATEADD(hh,-@SampleOffset+@TargetDeviceOffset,DateTime)) 
<> 1 AND 
(DATEPART(dw,DATEADD(hh,-@SampleOffset+@TargetDeviceOffset,DateTime)) <> 
7) )
                AND
            -- Between @StartBusinessHours and @EndBusinessHours after 
adjusting for timezones
 (DATEPART(Hour,DATEADD(hh,-@SampleOffset+@TargetDeviceOffset,DateTime)) >= @StartBusinessHours AND 
(DATEPART(Hour,DATEADD(hh,-@SampleOffset+@TargetDeviceOffset,DateTime)) <= @EndBusinessHours))
            )

        )
,
    Percentile_IN as (
        -- A CTE that builds on InterfaceTraffic_Detail_BusinessHours 
for calculating
        -- the chosen percentile value for each interfaceId
        SELECT
          t.InterfaceID,
          -- The smallest value in the chosen percentile
          -- 
http://www.dummies.com/education/math/statistics/how-to-calculate-percentiles-in-statistics/
          Min(CASE
            WHEN seqnum >= @TargetPercentile * cnt
              THEN
                [In_Maxbps]
          END) AS percentile
        FROM (
            SELECT
              t.*,
              ROW_NUMBER() OVER (PARTITION BY t.InterfaceID ORDER BY 
[In_Maxbps]) AS seqnum,
              COUNT(*) OVER (PARTITION BY t.InterfaceID) AS cnt
            FROM InterfaceTraffic_Detail_BusinessHours t
            ) t
        GROUP BY t.InterfaceID
        )
,
    Percentile_out as (
        -- A CTE that builds on InterfaceTraffic_Detail_BusinessHours 
for calculating
        -- the chosen percentile value for each interfaceId
        SELECT
          o.InterfaceID,
          -- The smallest value in the chosen percentile
          -- 
http://www.dummies.com/education/math/statistics/how-to-calculate-percentiles-in-statistics/
          Min(CASE
            WHEN seqnum >= @TargetPercentile * cnt
              THEN
                [out_Maxbps]
          END) AS percentile
        FROM (
            SELECT
              o.*,
              ROW_NUMBER() OVER (PARTITION BY o.InterfaceID ORDER BY 
[Out_Maxbps]) AS seqnum,
              COUNT(*) OVER (PARTITION BY o.InterfaceID) AS cnt
            FROM InterfaceTraffic_Detail_BusinessHours o
            ) o
        GROUP BY o.InterfaceID
)
    ,Percentile_max as (
        -- A CTE that builds on InterfaceTraffic_Detail_BusinessHours 
for calculating
        -- the chosen percentile value for each interfaceId
        SELECT
          m.InterfaceID,
          -- The smallest value in the chosen percentile
          -- 
http://www.dummies.com/education/math/statistics/how-to-calculate-percentiles-in-statistics/
          Min(CASE
            WHEN seqnum >= @TargetPercentile * cnt
              THEN
                MaxBps
          END) AS percentile
        FROM (
            SELECT
              m.*,
              ROW_NUMBER() OVER (PARTITION BY m.InterfaceID ORDER BY 
MaxBps) AS seqnum,
              COUNT(*) OVER (PARTITION BY m.InterfaceID) AS cnt
            FROM InterfaceTraffic_Detail_BusinessHours m
            ) m
        GROUP BY m.InterfaceID
)
SELECT
    Nodes.NodeID
    ,Interfaces.InterfaceId
    ,Nodes.SysName
    ,Interfaces.Caption AS Interface_Caption
    ,InterfaceSpeed
    ,Percentile_in.percentile  AS in_percentile
    ,Percentile_out.percentile AS out_percentile
    ,Percentile_max.percentile AS max_percentile
    , UTC_offset = @TargetDeviceOffset
    , SYSDATETIMEOFFSET () as Date

FROM [swnpm].[dbo].[Nodes]
    INNER JOIN [swnpm].[dbo].[Interfaces]
        ON (Nodes.NodeID = Interfaces.NodeID )
    INNER JOIN Percentile_in
        ON (Interfaces.InterfaceId = Percentile_in.InterfaceId)
    INNER JOIN Percentile_out
        ON (Interfaces.InterfaceId = Percentile_out.InterfaceId)
    INNER JOIN Percentile_max
        ON (Interfaces.InterfaceId = Percentile_max.InterfaceId)

ORDER BY
    SysName, Interface_Caption