SQL函數(shù)計(jì)算流量的95值
我用的是Solarwinds系統(tǒng)，部分內(nèi)容就結(jié)合Solarwinds系統(tǒng)一起寫了。最后落地也是通過系統(tǒng)的Report定時(shí)自動(dòng)出報(bào)告并且可以發(fā)郵件。
不過計(jì)算方法是通過定義SQL函數(shù)，然后使用SQL查詢來獲取到的，這部分內(nèi)容是通用的。
成都創(chuàng)新互聯(lián)服務(wù)項(xiàng)目包括信宜網(wǎng)站建設(shè)、信宜網(wǎng)站制作、信宜網(wǎng)頁制作以及信宜網(wǎng)絡(luò)營銷策劃等。多年來，我們專注于互聯(lián)網(wǎng)行業(yè)，利用自身積累的技術(shù)優(yōu)勢(shì)、行業(yè)經(jīng)驗(yàn)、深度合作伙伴關(guān)系等，向廣大中小型企業(yè)、政府機(jī)構(gòu)等提供互聯(lián)網(wǎng)行業(yè)的解決方案，信宜網(wǎng)站推廣取得了明顯的社會(huì)效益與經(jīng)濟(jì)效益。目前，我們服務(wù)的客戶以成都為中心已經(jīng)輻射到信宜省份的部分城市，未來相信會(huì)繼續(xù)擴(kuò)大服務(wù)區(qū)域并繼續(xù)獲得客戶的支持與信任！
95th 計(jì)算方法
從 Solarwinds 官方網(wǎng)站搜索“95th”關(guān)鍵字能獲取到說明的文檔。
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.227The 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.014The 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 函數(shù)
系統(tǒng)上安裝的工具是：SQL Server Management Studio
新建函數(shù)的命令模板
新建內(nèi)聯(lián)表值函數(shù)：
-- ================================================
-- 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:
-- Create date:
-- Description:
-- =============================================
CREATE FUNCTION
(
-- Add the parameters for the function here
<@param1, sysname, @p1> ,
<@param2, sysname, @p2>
)
RETURNS TABLE
AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT 0
)
GO 新建多語句表值函數(shù)：
-- ================================================
-- 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:
-- Create date:
-- Description:
-- =============================================
CREATE FUNCTION
(
-- Add the parameters for the function here
<@param1, sysname, @p1> ,
<@param2, sysname, @p2>
)
RETURNS
<@Table_Variable_Name, sysname, @Table_Var> TABLE
(
-- Add the column definitions for the TABLE variable here
,
)
AS
BEGIN
-- Fill the table variable with the rows for your result set
RETURN
END
GO 新建標(biāo)量值函數(shù)：
-- ================================================
-- 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:
-- Create date:
-- Description:
-- =============================================
CREATE FUNCTION
(
-- Add the parameters for the function here
<@Param1, sysname, @p1>
)
RETURNS
AS
BEGIN
-- Declare the return variable here
DECLARE <@ResultVar, sysname, @Result>
-- 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系統(tǒng)使用的計(jì)算95th的函數(shù)
通過標(biāo)量值函數(shù)來實(shí)現(xiàn)的。
一共有3個(gè)，一個(gè)In方向GetInBps95th，一個(gè)Out方向GetOutBps95th。
還有一個(gè)單個(gè)時(shí)間點(diǎn)的In和Out方向取大值計(jì)算結(jié)果。
函數(shù)已經(jīng)在系統(tǒng)中了，這里是修改函數(shù)的命令模板。
計(jì)算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
ENDOut方向的只是換一個(gè)字段，其他都一樣。
計(jì)算雙向的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子句用來判斷取大的值。
自定義函數(shù)
因?yàn)樵械暮瘮?shù)不能滿足需求，需要改一下，增加一個(gè)時(shí)間段篩選的變量。需求就是一周或者一個(gè)月，每天只取工作時(shí)間的數(shù)據(jù)。
這里按時(shí)間的小時(shí)數(shù) 'DATEPART(hh,DateTime)' 只要是9到16這些數(shù)值就表示每天的9點(diǎn)到17點(diǎn)了。
另外還有一個(gè)時(shí)間段的需求是每天有2段時(shí)間，考慮到通用性，這里用了數(shù)組的方式來設(shè)置，把需要的小時(shí)的數(shù)值定義在數(shù)組里。實(shí)際沒有用數(shù)組，用字符串來模擬了。
創(chuàng)建自定義函數(shù)
創(chuàng)建一個(gè)自定義函數(shù)，在原有的函數(shù)的基礎(chǔ)上，增加時(shí)間段的篩選：
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執(zhí)行上面的語句后，就會(huì)添加到數(shù)據(jù)庫中。
注意：這里定義字符串變量的時(shí)候要指定大小，否則雖然可以成功執(zhí)行，但是結(jié)果會(huì)和預(yù)期的不一樣。
解決數(shù)組的問題
篩選方式使用取時(shí)間的小時(shí)數(shù)，一天里哪幾個(gè)時(shí)段是需要的，就定義在數(shù)組里。
使用的時(shí)候這樣定義：
DECLARE @BusyHours VarChar(64)
SET @BusyHours = '9,10,11,12,13,14,15,16'這樣就是要取9點(diǎn)到17點(diǎn)的數(shù)據(jù)。
對(duì)應(yīng)的WHERE可以這樣寫：
WHERE ','+RTrim(@BusyHours)+',' LIKE '%,'+CAST(datepart(hh,DateTime) AS VarChar)+',%'基本用字符串模擬出了數(shù)組的效果，在這里是夠用了。
修改自定義函數(shù)
如果創(chuàng)建的時(shí)候?qū)戝e(cuò)了，則可以用下面的模板進(jìn)行修改，更新到數(shù)據(jù)庫中。
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中調(diào)用函數(shù)
供應(yīng)商給的使用模板
完整的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按需求修改的查詢語句
需求是要計(jì)算一周的數(shù)據(jù)，這里是獲取上一周的數(shù)據(jù)，從周一開始。
完整的查詢語句：
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注意：這里定義字符串變量的時(shí)候要指定大小，否則雖然可以成功執(zhí)行，但是結(jié)果會(huì)和預(yù)期的不一樣。
驗(yàn)證計(jì)算結(jié)果
在寫一個(gè)查詢語句，把一個(gè)端口下的所有的流量數(shù)據(jù)都導(dǎo)出。驗(yàn)證函數(shù)的結(jié)果是否符合預(yù)期。
完整的查詢語句：
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標(biāo)題名稱：SQL函數(shù)計(jì)算流量的95值
URL分享：http://m.jiaotiyi.com/article/gipspc.html