SQL函数计算流量的95值

发布时间：2020-07-30 03:31:31 作者：骑士救兵
来源：网络阅读：618

我用的是Solarwinds系统，部分内容就结合Solarwinds系统一起写了。最后落地也是通过系统的Report定时自动出报告并且可以发邮件。
不过计算方法是通过定义SQL函数，然后使用SQL查询来获取到的，这部分内容是通用的。

95th 计算方法

从 Solarwinds 官方网站搜索“95th”关键字能获取到说明的文档。
95th Percentile Calculations in the Orion Platform：
https://documentation.solarwinds.com/en/Success_Center/orionplatform/Content/Core-95th-Percentile-Calculations-sw80.htm

Over the 10 hours, the following 120 values were collected for inbound traffic (Mb/s):

0.149 0.623 0.281 0.136 0.024 0.042 0.097 0.185 0.198 0.243 0.274 0.390 0.971 0.633 0.238 0.142 
0.119 0.176 0.131 0.127 0.169 0.223 0.291 0.236 0.124 0.072 0.197 0.105 0.138 0.233 0.374 0.290 
0.871 0.433 0.248 0.242 0.169 0.116 0.121 0.427 0.249 0.223 0.231 0.336 0.014 0.442 0.197 0.125 
0.108 0.244 0.264 0.190 0.471 0.033 0.228 0.942 0.219 0.076 0.331 0.227 0.849 0.323 0.221 0.196 
0.223 0.642 0.197 0.385 0.098 0.263 0.174 0.690 0.571 0.233 0.208 0.242 0.139 0.186 0.331 0.124 
0.249 0.643 0.481 0.936 0.124 0.742 0.497 0.085 0.398 0.643 0.074 0.590 0.771 0.833 0.438 0.242 
0.092 0.376 0.231 0.627 0.249 0.663 0.181 0.636 0.224 0.342 0.697 0.285 0.108 0.211 0.074 0.490 
0.271 0.133 0.338 0.242 0.519 0.376 0.331 0.227

The values are reordered from high to low.

0.971 0.942 0.936 0.871 0.849 0.833 0.771 0.742 0.697 0.690 0.663 0.643 0.643 0.642 0.636 0.633 
0.627 0.623 0.590 0.571 0.519 0.497 0.490 0.481 0.471 0.442 0.438 0.433 0.427 0.398 0.390 0.385 
0.376 0.376 0.374 0.342 0.338 0.336 0.331 0.331 0.331 0.323 0.291 0.290 0.285 0.281 0.274 0.271 
0.264 0.263 0.249 0.249 0.249 0.248 0.244 0.243 0.242 0.242 0.242 0.242 0.238 0.236 0.233 0.233 
0.231 0.231 0.228 0.227 0.227 0.224 0.223 0.223 0.223 0.221 0.219 0.211 0.208 0.198 0.197 0.197 
0.197 0.196 0.190 0.186 0.185 0.181 0.176 0.174 0.169 0.169 0.149 0.142 0.139 0.138 0.136 0.133 
0.131 0.127 0.125 0.124 0.124 0.124 0.121 0.119 0.116 0.108 0.108 0.105 0.098 0.097 0.092 0.085 
0.076 0.074 0.074 0.072 0.042 0.033 0.024 0.014

The first 6 values are dropped, as these equal the top 5% of the values.

0.771 0.742 0.697 0.690 0.663 0.643 0.643 0.642 0.636 0.633 0.627 0.623 0.590 0.571 0.519 0.497 
0.490 0.481 0.471 0.442 0.438 0.433 0.427 0.398 0.390 0.385 0.376 0.376 0.374 0.342 0.338 0.336 
0.331 0.331 0.331 0.323 0.291 0.290 0.285 0.281 0.274 0.271 0.264 0.263 0.249 0.249 0.249 0.248 
0.244 0.243 0.242 0.242 0.242 0.242 0.238 0.236 0.233 0.233 0.231 0.231 0.228 0.227 0.227 0.224 
0.223 0.223 0.223 0.221 0.219 0.211 0.208 0.198 0.197 0.197 0.197 0.196 0.190 0.186 0.185 0.181 
0.176 0.174 0.169 0.169 0.149 0.142 0.139 0.138 0.136 0.133 0.131 0.127 0.125 0.124 0.124 0.124 
0.121 0.119 0.116 0.108 0.108 0.105 0.098 0.097 0.092 0.085 0.076 0.074 0.074 0.072 0.042 0.033 
0.024 0.014

The 95th percentile is 0.771.

SQL 函数

系统上安装的工具是：SQL Server Management Studio

新建函数的命令模板

新建内联表值函数：

-- ================================================
-- Template generated from Template Explorer using:
-- Create Inline Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE FUNCTION <Inline_Function_Name, sysname, FunctionName> 
(   
    -- Add the parameters for the function here
    <@param1, sysname, @p1> <Data_Type_For_Param1, , int>, 
    <@param2, sysname, @p2> <Data_Type_For_Param2, , char>
)
RETURNS TABLE 
AS
RETURN 
(
    -- Add the SELECT statement with parameter references here
    SELECT 0
)
GO

新建多语句表值函数：

-- ================================================
-- Template generated from Template Explorer using:
-- Create Multi-Statement Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE FUNCTION <Table_Function_Name, sysname, FunctionName> 
(
    -- Add the parameters for the function here
    <@param1, sysname, @p1> <data_type_for_param1, , int>, 
    <@param2, sysname, @p2> <data_type_for_param2, , char>
)
RETURNS 
<@Table_Variable_Name, sysname, @Table_Var> TABLE 
(
    -- Add the column definitions for the TABLE variable here
    <Column_1, sysname, c1> <Data_Type_For_Column1, , int>, 
    <Column_2, sysname, c2> <Data_Type_For_Column2, , int>
)
AS
BEGIN
    -- Fill the table variable with the rows for your result set

    RETURN 
END
GO

新建标量值函数：

-- ================================================
-- Template generated from Template Explorer using:
-- Create Scalar Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION <Scalar_Function_Name, sysname, FunctionName> 
(
    -- Add the parameters for the function here
    <@Param1, sysname, @p1> <Data_Type_For_Param1, , int>
)
RETURNS <Function_Data_Type, ,int>
AS
BEGIN
    -- Declare the return variable here
    DECLARE <@ResultVar, sysname, @Result> <Function_Data_Type, ,int>

    -- Add the T-SQL statements to compute the return value here
    SELECT <@ResultVar, sysname, @Result> = <@Param1, sysname, @p1>

    -- Return the result of the function
    RETURN <@ResultVar, sysname, @Result>

END
GO

Solarwinds系统使用的计算95th的函数

通过标量值函数来实现的。
一共有3个，一个In方向GetInBps95th，一个Out方向GetOutBps95th。
还有一个单个时间点的In和Out方向取大值计算结果。
函数已经在系统中了，这里是修改函数的命令模板。

计算In方向的95th

USE [SolarwindsOrion]
GO
/****** Object:  UserDefinedFunction [dbo].[GetInBps95th]    Script Date: 2019/11/14 11:07:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[GetInBps95th] 
(
    @InterfaceId int,
    @StartDate DateTime,
    @EndDate DateTime
)
RETURNS real
AS
BEGIN
    DECLARE @ResultVar real

    SELECT @ResultVar = MAX(In_Maxbps)
    FROM (
        SELECT TOP 95 PERCENT In_Maxbps
        FROM dbo.InterfaceTraffic WITH (NOLOCK)
        WHERE InterfaceID = @InterfaceId AND DateTime >= @StartDate AND DateTime <= @EndDate
        ORDER BY In_Maxbps ASC
    ) AS AA

    RETURN @ResultVar

END

Out方向的只是换一个字段，其他都一样。

计算双向的95th

USE [SolarwindsOrion]
GO
/****** Object:  UserDefinedFunction [dbo].[GetMaxBps95th]    Script Date: 2019/11/14 11:08:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[GetMaxBps95th] 
(
    @InterfaceId int,
    @StartDate DateTime,
    @EndDate DateTime
)
RETURNS real
AS
BEGIN
    DECLARE @ResultVar real

    SELECT @ResultVar = MAX(Maxbps)
    FROM (
        SELECT TOP 95 PERCENT Maxbps
        FROM (SELECT (CASE WHEN Out_Maxbps > In_Maxbps THEN Out_Maxbps ELSE In_Maxbps END) AS Maxbps
            FROM dbo.InterfaceTraffic WITH (NOLOCK)
            WHERE InterfaceID = @InterfaceId AND DateTime >= @StartDate AND DateTime <= @EndDate) AS MaxbpsSet
        ORDER BY Maxbps ASC
    ) AS AA

    RETURN @ResultVar

END

多了一层子查询，使用WHEN子句用来判断取大的值。

自定义函数

因为原有的函数不能满足需求，需要改一下，增加一个时间段筛选的变量。需求就是一周或者一个月，每天只取工作时间的数据。
这里按时间的小时数 'DATEPART(hh,DateTime)' 只要是9到16这些数值就表示每天的9点到17点了。
另外还有一个时间段的需求是每天有2段时间，考虑到通用性，这里用了数组的方式来设置，把需要的小时的数值定义在数组里。实际没有用数组，用字符串来模拟了。

创建自定义函数

创建一个自定义函数，在原有的函数的基础上，增加时间段的筛选：

USE [SolarwindsOrion]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Steed Xu
-- Create date: 2019/11/14
-- Description: Base on GetInBps95th, add BusyHours.
-- =============================================
CREATE FUNCTION [dbo].[GetInBps95thBusyHours] 
(
    @InterfaceId int,
    @StartDate DateTime,
    @EndDate DateTime,
    @BusyHours VarChar(64)
)
RETURNS real
AS
BEGIN
    DECLARE @ResultVar real

    SELECT @ResultVar = MAX(In_Maxbps)
    FROM (
        SELECT TOP 95 PERCENT In_Maxbps
        FROM dbo.InterfaceTraffic WITH (NOLOCK)
        WHERE InterfaceID = @InterfaceId 
            AND DateTime >= @StartDate AND DateTime <= @EndDate
            AND ','+RTRIM(@BusyHours)+',' LIKE '%,'+CAST(DATEPART(hh,DateTime) AS varchar)+',%'
        ORDER BY In_Maxbps ASC
    ) AS AA

    RETURN @ResultVar

END
GO

执行上面的语句后，就会添加到数据库中。
注意：这里定义字符串变量的时候要指定大小，否则虽然可以成功执行，但是结果会和预期的不一样。

解决数组的问题

筛选方式使用取时间的小时数，一天里哪几个时段是需要的，就定义在数组里。
使用的时候这样定义：

DECLARE @BusyHours VarChar(64)
SET @BusyHours = '9,10,11,12,13,14,15,16'

这样就是要取9点到17点的数据。

对应的WHERE可以这样写：

WHERE ','+RTrim(@BusyHours)+',' LIKE '%,'+CAST(datepart(hh,DateTime) AS VarChar)+',%'

基本用字符串模拟出了数组的效果，在这里是够用了。

修改自定义函数

如果创建的时候写错了，则可以用下面的模板进行修改，更新到数据库中。

USE [SolarwindsOrion]
GO
/****** Object:  UserDefinedFunction [dbo].[GetInBps95thBusyHours]    Script Date: 2019/11/14 13:43:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Steed Xu
-- Create date: 2019/11/14
-- Description: Base on GetInBps95th, add BusyHours.
-- =============================================
ALTER FUNCTION [dbo].[GetInBps95thBusyHours]
(
    @InterfaceId int,
    @StartDate DateTime,
    @EndDate DateTime,
    @BusyHours VarChar(64)
)
RETURNS real
AS
BEGIN
    DECLARE @ResultVar real

    SELECT @ResultVar = MAX(In_Maxbps)
    FROM (
        SELECT TOP 95 PERCENT In_Maxbps
        FROM dbo.InterfaceTraffic WITH (NOLOCK)
        WHERE InterfaceID = @InterfaceId 
            AND DateTime >= @StartDate AND DateTime <= @EndDate 
            AND ','+RTRIM(@BusyHours)+',' LIKE '%,'+CAST(DATEPART(hh,DateTime) AS varchar)+',%'
        ORDER BY In_Maxbps ASC
    ) AS AA

    RETURN @ResultVar

END

在SQL中调用函数

供应商给的使用模板

完整的SQL查询语句：

完整的查询语句如下：
SET NOCOUNT OFF
SET ROWCOUNT 0

DECLARE @StartDate DateTime
DECLARE @EndDate DateTime

SET @StartDate = CAST((ROUND(CAST(GetDate() - 7 AS FLOAT), 0, 1)) as datetime)
SET @EndDate = GetDate()

SELECT Interfaces.InterfaceId,
    Nodes.NodeID,
    Nodes.Caption AS NodeName,
    Nodes.VendorIcon AS Vendor_Icon,
    Interfaces.Caption AS Interface_Caption,
    Interfaces.InterfaceIcon AS Interface_Icon,
    Maxbps_In95,
    Maxbps_Out95,
    Maxbps_95
FROM Nodes
INNER JOIN Interfaces ON Nodes.NodeID = Interfaces.NodeID
INNER JOIN (
    SELECT InterfaceID,
        dbo.GetInBps95th(InterfaceID, @StartDate, @EndDate) AS Maxbps_In95,
        dbo.GetOutBps95th(InterfaceID, @StartDate, @EndDate) AS Maxbps_Out95,
        dbo.GetMaxBps95th(InterfaceID, @StartDate, @EndDate) AS Maxbps_95
    FROM InterfaceTraffic
    WHERE InterfaceTraffic.DateTime >= @StartDate AND InterfaceTraffic.DateTime <= @EndDate
    GROUP BY InterfaceID
) TrafficStat
ON Interfaces.InterfaceID = TrafficStat.InterfaceID
WHERE (1=1)
AND  
(
    (Nodes.Vendor = 'Cisco') AND
    (Interfaces.Comments = 'MT')
)
ORDER BY 
Maxbps_In95 desc,
Maxbps_Out95 desc

按需求修改的查询语句

需求是要计算一周的数据，这里是获取上一周的数据，从周一开始。
完整的查询语句：

SET NOCOUNT OFF
SET ROWCOUNT 0

DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
DECLARE @BusyHours VarChar(64)

SET @StartDate = DATEADD(week,-1,DATEADD(week,DATEDIFF(week,0,getdate()),0))
SET @EndDate = DATEADD(week,DATEDIFF(week,0,getdate()),0)
SET @BusyHours = '9,10,11,12,13,14,15,16'

SELECT Interfaces.InterfaceId,
    Nodes.NodeID,
    Nodes.Caption AS NodeName,
    Nodes.VendorIcon AS Vendor_Icon,
    Nodes.IP_Address,
    Interfaces.InterfaceName AS Interface_Name,
    Interfaces.InterfaceAlias AS Description,
    Interfaces.InterfaceIcon AS Interface_Icon,
    Maxbps_In95,
    Maxbps_Out95,
    @StartDate AS StartDate,
    @EndDate AS EndDate,
    @BusyHours AS BusyHours,
    Interfaces.Status AS Interfaces_Status
FROM Nodes
INNER JOIN Interfaces ON Nodes.NodeID = Interfaces.NodeID
INNER JOIN (
    SELECT InterfaceID,
        dbo.GetInBps95thBusyHours(InterfaceID, @StartDate, @EndDate, @BusyHours) AS Maxbps_In95,
        dbo.GetOutBps95thBusyHours(InterfaceID, @StartDate, @EndDate, @BusyHours) AS Maxbps_Out95
    FROM InterfaceTraffic
    WHERE InterfaceTraffic.DateTime >= @StartDate
        AND InterfaceTraffic.DateTime <= @EndDate
        AND ','+RTRIM(@BusyHours)+',' LIKE '%,'+CAST(DATEPART(hh,DateTime) AS varchar)+',%'
    GROUP BY InterfaceID
) TrafficStat
ON Interfaces.InterfaceId = TrafficStat.InterfaceId
WHERE (1=1)
    AND Nodes.IP_Address IN ('172.16.6.1','172.16.6.2','172.16.5.3','172.16.10.1','172.16.10.2','172.16.5.4')
    AND Interfaces.InterfaceAlias <> ''
    AND Interfaces.Status IN (1,2)
ORDER BY Interfaces.InterfaceId

注意：这里定义字符串变量的时候要指定大小，否则虽然可以成功执行，但是结果会和预期的不一样。

验证计算结果

在写一个查询语句，把一个端口下的所有的流量数据都导出。验证函数的结果是否符合预期。
完整的查询语句：

SET NOCOUNT OFF
SET ROWCOUNT 0

DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
DECLARE @BusyHours VarChar(64)
DECLARE @InterfaceId int

SET @StartDate = DATEADD(week,-1,DATEADD(week,DATEDIFF(week,0,getdate()),0))
SET @EndDate = DATEADD(week,DATEDIFF(week,0,getdate()),0)
SET @BusyHours = '9,10,11,12,13,14,15,16'
SET @InterfaceId = 28993

SELECT TOP 95 PERCENT 
    DateTime, InterfaceID, In_Maxbps, Out_Maxbps, 
    @StartDate AS StartDate,
    @EndDate AS EndDate,
    @BusyHours AS BusyHours
FROM InterfaceTraffic
WHERE InterfaceTraffic.DateTime >= @StartDate
    AND InterfaceTraffic.DateTime <= @EndDate
    AND ','+RTrim(@BusyHours)+',' LIKE '%,'+CAST(datepart(hh,DateTime) AS VarChar)+',%'
    AND InterfaceID = @InterfaceId
ORDER BY In_Maxbps ASC