Shulou(Shulou.com)06/02 Report--

I use the Solarwinds system, part of the content will be written together with the Solarwinds system. The final landing is also through the Report of the system to report automatically and can send e-mail.

However, the calculation method is obtained by defining the SQL function and then using the SQL query, which is generic.

95th calculation method

Search the official Solarwinds website for the "95th" keyword to get the documentation for instructions.

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.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.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.119 0.116 0.108 0.105 98 0.097 0.092 0.076 0.074 0.072 0.033 0.024 0.014 The 95th percentile is 0.771.SQL function

The tool installed on the system is: SQL Server Management Studio

Create a command template for a new function

Create a new inline table-valued function:

-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 ONGOSET QUOTED_IDENTIFIER ONGO-- =-- Author:-- Create date:-- Description:-- = CREATE FUNCTION (--Add the parameters for the function here) ) RETURNS TABLE ASRETURN (--Add the SELECT statement with parameter references here SELECT 0) GO

Create a new multi-statement table-valued function:

-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 ONGOSET QUOTED_IDENTIFIER ONGO-- =-- Author:-- Create date:-- Description:-- = CREATE FUNCTION (--Add the parameters for the function here) RETURNS TABLE (--Add the column definitions for the TABLE variable here,) ASBEGIN-- Fill the table variable with the rows for your result set RETURN ENDGO

Create a new scalar-valued function:

-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 ONGOSET QUOTED_IDENTIFIER ONGO-- =-- Author:-- Create date:-- Description:-- = CREATE FUNCTION (--Add the parameters) For the function here) RETURNS ASBEGIN-- Declare the return variable here DECLARE-- Add the T-SQL statements to compute the return value here SELECT =-- function used by the Return the result of the function RETURN ENDGOSolarwinds system to calculate 95th

It is realized by scalar-valued functions.

There are three of them, one in the In direction GetInBps95th and one in the Out direction GetOutBps95th.

There is also a single time point in which the In and Out directions are calculated with large values.

The function is already in the system, and here is the command template for modifying the function.

Calculate the 95th in the In direction

USE [SolarwindsOrion] GO/* Object: UserDefinedFunction [dbo]. [GetInBps95th] Script Date: 11:07:12 on 2019-11-14 * / SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER FUNCTION [dbo]. [GetInBps95th] (@ InterfaceId int, @ StartDate DateTime EndDate DateTime) RETURNS realASBEGIN 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 In_Maxbps THEN Out_Maxbps ELSE In_Maxbps END) AS Maxbps FROM dbo.InterfaceTraffic WITH (NOLOCK) WHERE InterfaceID = @ InterfaceId AND DateTime > = @ StartDate AND DateTime = @ StartDate AND DateTime = @ StartDate AND DateTime = @ StartDate AND InterfaceTraffic.DateTime

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.