I had a similar issue to the one described in this post: . We needed to calculate the 95th percentile across an arbitrary number of interfaces in order to bill customers.
I built a report that will generate a 95th percentile report for an arbitrary number of interfaces, one or more, associated with a single billed entity.
Process:
1. Create an interface custom property of type text called Billed_Entity
2. Create the following scalar functions on your SQL Server
Function 1:
USE [NetPerfMon]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Ben Wiechman
-- Create date: 2010-11-02
-- Description: Calculate 95th percentile throughput for the sum of all traffic sent across one or more interfaces based on a five minute poll interval.
-- =============================================
CREATE FUNCTION [dbo].[GetInCombinedBps95th]
(
@Billing_Account nvarchar(50),
@StartDate DATETIME,
@EndDate DATETIME
)
RETURNS real
AS
BEGIN
DECLARE @ResultVar real
DECLARE @TrafficDetails TABLE (
Combined_bps real
)
INSERT INTO @TrafficDetails ( Combined_bps )
SELECT
TOP 95 PERCENT SUM(InterfaceTraffic_Detail.In_Maxbps) AS Combined_In_Maxbps
FROM Interfaces
INNER JOIN InterfaceTraffic_Detail
ON ( InterfaceTraffic_Detail.InterfaceID = Interfaces.InterfaceID )
WHERE
Interfaces.Billed_Entity IS NOT NULL
AND
Interfaces.Billed_Entity = @Billing_Account
AND
InterfaceTraffic_Detail.DateTime BETWEEN @StartDate AND @EndDate
GROUP BY dateadd(mi, datediff(mi,0,InterfaceTraffic_Detail.DateTime)/5*5, 0),Interfaces.Billed_Entity
ORDER BY Combined_In_Maxbps ASC
SELECT @ResultVar = MAX( Combined_bps ) FROM @TrafficDetails
RETURN @ResultVar
END
Function 2:
USE [NetPerfMon]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Ben Wiechman
-- Create date: 2010-11-02
-- Description: Calculate 95th percentile throughput for the sum of all traffic sent across one or more interfaces based on a five minute poll interval.
-- =============================================
CREATE FUNCTION [dbo].[GetOutCombinedBps95th]
(
@Billing_Account nvarchar(50),
@StartDate DATETIME,
@EndDate DATETIME
)
RETURNS real
AS
BEGIN
DECLARE @ResultVar real
DECLARE @TrafficDetails TABLE (
Combined_bps real
)
INSERT INTO @TrafficDetails ( Combined_bps )
SELECT
TOP 95 PERCENT SUM(InterfaceTraffic_Detail.Out_Maxbps) AS Combined_Out_Maxbps
FROM Interfaces
INNER JOIN InterfaceTraffic_Detail
ON ( InterfaceTraffic_Detail.InterfaceID = Interfaces.InterfaceID )
WHERE
Interfaces.Billed_Entity IS NOT NULL
AND
Interfaces.Billed_Entity = @Billing_Account
AND
InterfaceTraffic_Detail.DateTime BETWEEN @StartDate AND @EndDate
GROUP BY dateadd(mi, datediff(mi,0,InterfaceTraffic_Detail.DateTime)/5*5, 0),Interfaces.Billed_Entity
ORDER BY Combined_Out_Maxbps ASC
SELECT @ResultVar = MAX( Combined_bps ) FROM @TrafficDetails
RETURN @ResultVar
END
3. Add the following report. (Probably works best to create a new copy of one of the existing 95th percentile reports)
DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate = DATEADD(d, DATEDIFF(d, 0, DATEADD(m, -1, DATEADD(d, 1 - day(getdate()), getdate()))), 0)
SET @EndDate = DATEADD(ms, -2,DATEADD(d, DATEDIFF(d, 0, DATEADD(d, 1 - day(getdate()), getdate())), 0))
SELECT
Billed_Entity AS Account,
dbo.GetInCombinedBps95th(Billed_Entity, @StartDate, @EndDate) / 1048576 AS Maxbps_In95,
dbo.GetOutCombinedBps95th(Billed_Entity, @StartDate, @EndDate) / 1048576 AS Maxbps_Out95
FROM Interfaces
WHERE Billed_Entity IS NOT NULL
GROUP BY Billed_Entity
ORDER BY Billed_Entity
4. Configure all interfaces associated with a customer so that the Billed_Entity custom property contains the same value. Each unique instance of the Billed_Entity will be printed on it's own line in the report.
The stored function rounds the poll time recorded in the InterfaceTraffic_Detail table to the previous five minute mark, then adds up all interfaces with the same Billed_Entity property for that time slot. The top 5% of the records are discarded, and the top value is returned as the 95th percentile value. The report simply generates one entry for each separate instance of Billed_Entity that is found as an interface custom property.
There are several assumptions:
- Interfaces are being polled on a five minute interval. (This could be adjusted by modifying the stored function)
- NPM has been reconfigured to maintain the detailed information long enough to allow you to generate the reports for the previous month, in other words, longer than 31 days if you are generating the reports for the previous month on the first day of the following month. If you don't want the reports to change over the course of the month as the detailed statistics are summarized you need to maintain detailed interface statistics for more than two months.
I'm not really a SQL guru so if anyone has any modifications or recommendations I'd love to hear them.
