5 Replies Latest reply on Jul 14, 2016 5:15 PM by mesverrum

    Report for 95th percentile calculated daily and shown as a percentage of max bandwidth for the last 30 days during business hours

    dkendall

      This report will give you a list of interfaces that have the custom yes/no attribute of isCircuit and their DAILY 95th percentile percentage of bandwidth for the last 30 days.  This is not a 30 day average report, but rather a historical daily report.  The number of days in the report can be modified as can the business hours to suit your needs.  My sql may need to be cleaned up a little, but it works.  Enjoy!

       

      DECLARE @IncDate varchar(50)
      DECLARE @intFlag INT
      DECLARE @cmd varchar (MAX)
      DECLARE @ManyCol varchar(MAX)
      DECLARE @InnerCmd1 varchar(MAX)
      DECLARE @InnerCmd2 varchar(MAX)
      DECLARE @INTFLAG1 varchar(2)
      DECLARE @StartTime varchar(8)
      DECLARE @EndTime varchar(8)
      SET @ManyCol = ''
      SET @intFlag = 1

      SET @StartTime = '06:00:00'
      SET @EndTime = '18:00:00'

      While (@intFlag <= 30)
      BEGIN
      SET @IncDate = CAST((convert(varchar(10), getdate() - @intFlag,10)) as varchar(50))
      SET @ManyCol = @ManyCol + ',CAST(("Maxbps_In95_' + @IncDate + '"/Interfaces.InBandwidth)*100 AS numeric(10,2)) AS ''Percent_In_' + @IncDate + '''
      , ' + 'CAST(("Maxbps_Out95_' + @IncDate + '"/Interfaces.OutBandwidth)*100 AS numeric(10,2)) AS ''Percent_Out_' + @IncDate + '''
       '
      SET @intFlag = @intFlag + 1
      end

      SET @intFlag = 1

      SET @InnerCmd1 = ''
      WHILE @intFlag <= 30
      BEGIN
      SET @IncDate = CAST((convert(varchar(10), getdate() - @intFlag,10)) as varchar(50))
      SET @INTFLAG1 = CAST(@intFlag AS VARCHAR)
      SET @InnerCmd1 = @InnerCmd1 + ' INNER JOIN (
      SELECT InterfaceID, dbo.GetInBps95th(AA.InterfaceID, cast(''' + @IncDate + ' ' + @StartTime + ''' AS datetime), cast(''' + @IncDate + ' ' + @EndTime + ''' AS datetime)) AS ''Maxbps_In95_' + @IncDate +
       ''' FROM (
        SELECT DISTINCT InterfaceID
        FROM dbo.InterfaceTraffic WHERE DateTime >= cast(''' + @IncDate + ' ' + @StartTime + ''' AS datetime) AND DateTime <= cast(''' +  @IncDate + ' ' + @EndTime + ''' AS datetime)
       ) AS AA
      ) AS Result_In_' + @INTFLAG1 + ' ON (Interfaces.InterfaceID = Result_In_' + @INTFLAG1 + '.InterfaceID)
      '
      SET @intFlag = @intFlag + 1
      END

      SET @intFlag = 1
      SET @InnerCmd2 = ''
      WHILE @intFlag <=30
      BEGIN
      SET @IncDate = CAST((convert(varchar(10), getdate() - @intFlag,10)) as varchar(50))
      SET @INTFLAG1 = CAST(@intFlag AS VARCHAR)
      SET @InnerCmd2 = @InnerCmd2 + '
      INNER JOIN 
      (
       SELECT InterfaceID, dbo.GetOutBps95th(AA.InterfaceID, cast(''' + @IncDate + ' ' + @StartTime + ''' AS datetime), cast(''' + @IncDate + ' ' + @EndTime + ''' AS datetime)) AS ''Maxbps_Out95_' + @IncDate +
       ''' FROM (
        SELECT DISTINCT InterfaceID
        FROM dbo.InterfaceTraffic WHERE DateTime >= cast(''' + @IncDate + ' ' + @StartTime + ''' AS datetime) AND DateTime <= cast(''' +  @IncDate + ' ' + @EndTime + ''' AS datetime)
       ) AS AA
      ) AS RESULT_OUT_' + @intFlag1 + ' ON (Interfaces.InterfaceID = RESULT_OUT_' + @intFlag1 + '.InterfaceID)
      '
      SET @intFlag = @intFlag + 1
      END

      SET @cmd = 'SELECT Interfaces.InterfaceId,
      Nodes.NodeID,
      Nodes.Caption AS NodeName,
      Interfaces.Caption AS Interface_Caption' + @ManyCol + '
      FROM Nodes
      INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID)' + @InnerCmd1 + @InnerCmd2 + '
      WHERE (Interfaces.isCircuit = 1)
      ORDER BY InterfaceID, NodeName, Interface_Caption'

      exec (@cmd)

        • Re: Report for 95th percentile calculated daily and shown as a percentage of max bandwidth for the last 30 days during business hours
          dkendall

          Here is a more readable format.

           

          DECLARE
           @IncDate VARCHAR(50)DECLARE
            @intFlag INT DECLARE
             @cmd VARCHAR(MAX)DECLARE
              @ManyCol VARCHAR(MAX)DECLARE
               @InnerCmd1 VARCHAR(MAX)DECLARE
                @InnerCmd2 VARCHAR(MAX)DECLARE
                 @INTFLAG1 VARCHAR(2)DECLARE
                  @StartTime VARCHAR(8)DECLARE
                   @EndTime VARCHAR(8)
                  SET @ManyCol = ''
                  SET @intFlag = 1
                  SET @StartTime = '06:00:00'
                  SET @EndTime = '18:00:00'
                  WHILE(@intFlag <= 30)
                  BEGIN

                  SET @IncDate = CAST(
                   (
                    CONVERT(
                     VARCHAR(10),
                     getdate()- @intFlag,
                     10
                    )
                   )AS VARCHAR(50)
                  )
                  SET @ManyCol = @ManyCol + ',CAST(("' + @IncDate + '"/Interfaces.InBandwidth)*100 AS numeric(10,2)) AS ''' + @IncDate + ''' '
                  SET @intFlag = @intFlag + 1
                  END

                  SET @intFlag = 1
                  SET @InnerCmd1 = ''
                  WHILE @intFlag <= 30
                  BEGIN

                  SET @IncDate = CAST(
                   (
                    CONVERT(
                     VARCHAR(10),
                     getdate()- @intFlag,
                     10
                    )
                   )AS VARCHAR(50)
                  )
                  SET @INTFLAG1 = CAST(@intFlag AS VARCHAR)
                  SET @InnerCmd1 = @InnerCmd1 + ' INNER JOIN (
          SELECT InterfaceID, dbo.GetInBps95th(AA.InterfaceID, cast(''' + @IncDate + ' ' + @StartTime + ''' AS datetime), cast(''' + @IncDate + ' ' + @EndTime + ''' AS datetime)) AS ''' + @IncDate + ''' FROM (
            SELECT DISTINCT InterfaceID
            FROM dbo.InterfaceTraffic WHERE DateTime >= cast(''' + @IncDate + ' ' + @StartTime + ''' AS datetime) AND DateTime <= cast(''' + @IncDate + ' ' + @EndTime + ''' AS datetime)
           ) AS AA
          ) AS Result_In_' + @INTFLAG1 + ' ON (Interfaces.InterfaceID = Result_In_' + @INTFLAG1 + '.InterfaceID)
          '
                  SET @intFlag = @intFlag + 1
                  END

                  SET @intFlag = 1
                  SET @InnerCmd2 = ''
                  WHILE @intFlag <= 30
                  BEGIN

                  SET @IncDate = CAST(
                   (
                    CONVERT(
                     VARCHAR(10),
                     getdate()- @intFlag,
                     10
                    )
                   )AS VARCHAR(50)
                  )
                  SET @INTFLAG1 = CAST(@intFlag AS VARCHAR)
                  SET @InnerCmd2 = @InnerCmd2 + '
          INNER JOIN 
          (
           SELECT InterfaceID, dbo.GetOutBps95th(AA.InterfaceID, cast(''' + @IncDate + ' ' + @StartTime + ''' AS datetime), cast(''' + @IncDate + ' ' + @EndTime + ''' AS datetime)) AS ''' + @IncDate + ''' FROM (
            SELECT DISTINCT InterfaceID
            FROM dbo.InterfaceTraffic WHERE DateTime >= cast(''' + @IncDate + ' ' + @StartTime + ''' AS datetime) AND DateTime <= cast(''' + @IncDate + ' ' + @EndTime + ''' AS datetime)
           ) AS AA
          ) AS RESULT_OUT_' + @intFlag1 + ' ON (Interfaces.InterfaceID = RESULT_OUT_' + @intFlag1 + '.InterfaceID)
          '
                  SET @intFlag = @intFlag + 1
                  END

          SET @cmd = '
          SELECT Interfaces.InterfaceId,
          Nodes.NodeID,
          Nodes.Caption + ''-IN'' AS NodeName,
          Interfaces.Caption AS Interface_Caption ' + @ManyCol + '
          FROM Nodes
          INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID)' + @InnerCmd1 + '
          WHERE (Interfaces.isCircuit = 1)
          UNION ALL
          SELECT Interfaces.InterfaceId,
          Nodes.NodeID,
          Nodes.Caption + ''-OUT'' AS NodeName,
          Interfaces.Caption AS Interface_Caption ' + @ManyCol + '
          FROM Nodes
          INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID)' + @InnerCmd2 + '
          WHERE (Interfaces.isCircuit = 1)
          ORDER BY InterfaceId, NodeName
          '
          print @cmd

          EXEC(@cmd)