1 Reply Latest reply on Jun 22, 2015 1:02 PM by mr.e

    95th Percentile IN and OUT for last 30 days

    mr.e

      I found a code in Thwack that give 95th Percentile for the last month.  However, our network folks want the last 30 days instead.  We tried to tweak the code but we're getting NULL values in the 95th_Percentile_IN, 95th_Percentile_Out and Max_95th columns.  So, I'd appreciate if you guys could tell me what changes are needed, so the report can work.  See below...

       

      DECLARE @Today DATETIME = GETDATE()

      DECLARE @startTimeT1 DATETIME;

      DECLARE @endTimeT1 DATETIME;

      DECLARE @intFlag INT;

      SET @intFlag = 30

       

      SET @startTimeT1 = CAST(getdate() AS DATETIME)

      SET @endTimeT1   = CAST(getdate()- @intFlag AS DATETIME)

      set nocount on

      create table #InterfaceIds24 (

            InterfaceID int NOT NULL,

            CONSTRAINT [PK_#InterfaceIds24] PRIMARY KEY CLUSTERED

            (

                  InterfaceID ASC

            )

      )     

      insert into #InterfaceIds24

          SELECT DISTINCT A.InterfaceID

          FROM dbo.InterfaceTraffic A

          WHERE A.DateTime >= @startTimeT1 AND A.DateTime <=  @endTimeT1

       

      SELECT TOP 20

      @startTimeT1

      , @endTimeT1 

       

      ,Nodes.NodeID, Interfaces.InterfaceID

      ,Nodes.Caption AS NodeName,

       

      Interfaces.Caption AS Interface_Caption,

      Interfaces.InterfaceSpeed,

      Round(dbo.GetInBps95th(InterfaceID, @startTimeT1, @endTimeT1) / Interfaces.InterfaceSpeed * 100,1) AS '95th_Percent_In',

      Round(dbo.GetOutBps95th(InterfaceID, @startTimeT1, @endTimeT1) / Interfaces.InterfaceSpeed * 100,1) AS '95th_Percent_Out',

      Round(dbo.GetMaxBps95th(InterfaceID, @startTimeT1, @endTimeT1)/ Interfaces.InterfaceSpeed * 100,1) AS 'Max 95th'

      FROM Nodes 

       

       

      INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID)

      WHERE

      (Interfaces.InterfaceSpeed != 0)

        AND   (Nodes.Status != '9')

      ORDER BY '95th_Percent_IN' DESC, Interface_Caption

       

      DROP TABLE #InterfaceIds24