0 Replies Latest reply on Nov 2, 2010 4:04 PM by bwiechman

    95th Percentile Across Arbitrary Interfaces

    bwiechman

      I had a similar issue to the one described in this post: 95th Percentile Aggregation. 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.