( 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