6 Replies Latest reply on Jun 24, 2015 2:25 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.

      By the way, I know that there is a report already for business hours at 95th Percentile Circuit Report based on local site business hours



      However, that report is for based on Monthly time frame instead of the "last 30 days", which what my team needs.  Given that we're not SQL savvy, we could not figure out the logic that needed tweaking.  Also, the report required additional changes to our setup (GMT time, Custom Properties, etc.).  So, we thought a simpler report might be best.  We then found another report on Thwack and tried to tweak the code. Unfortunately, when we run the script, 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


      I would very much appreciate your insights on this matter.

        • Re: 95TH PERCENTILE IN AND OUT FOR LAST 30 DAYS
          zackm

          At a quick glance, this looks like it is a last 30 days report. Line 5 is the 30 day mark; used in line 8 to setup the @endTimeT1 variable to getdate()-30

           

          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
          

           

           

          If this was a last 1 month report, it would look more like this:

           

          declare @startofcurrentmonth datetime
          set @startofcurrentmonth = dateadd(month,datediff(month,0,getdate()),0)
          
          ...
            WHERE
            (datetime >= dateadd(month, -1, @startofcurrentmonth)) and (datetime < @startofcurrentmonth) --last month
          ...
          
            • Re: 95TH PERCENTILE IN AND OUT FOR LAST 30 DAYS
              mr.e

              zackm,

               

              Thanks for  your reply.  The original report we found in Thwack was set to pull data for the last month, but the network team did not want that.  The issue we have is tweaking the code so it would pull data for the last 30 days.  Any ideas?

                • Re: 95TH PERCENTILE IN AND OUT FOR LAST 30 DAYS
                  zackm

                  It already is pulling data from the last 30 days. The original code is not looking at the first and last day of any month. If you run that today, it would be from today to today - 30; not from May 1 - 31st.

                    • Re: 95TH PERCENTILE IN AND OUT FOR LAST 30 DAYS
                      mr.e

                      As I stated earlier, when we run the report, we get NULL values in the 95th_Percentile_IN, 95th_Percentile_Out and Max_95th columns.  So, the calculations are getting messed up somehow.  That's what we're trying to fix but are not getting anywhere. 

                        • Re: 95TH PERCENTILE IN AND OUT FOR LAST 30 DAYS
                          zackm

                          Gotcha. Basically, your best option would be to take an existin report and modify it. SolarWinds comes with a built-in report called "95th Percentile Traffic Rate - Last 7 Days'. If you take the raw SQL out of that report, you can modify it to show you the last 30 days as seen below:

                           

                          SET NOCOUNT OFF
                          SET ROWCOUNT 0
                          DECLARE @StartDate DateTime
                          DECLARE @EndDate DateTime
                          SET @StartDate = CAST((ROUND(CAST(GetDate() - 30 AS FLOAT), 0, 1)) as datetime)
                          SET @EndDate = GetDate()
                          
                          
                          SELECT
                            Nodes.Caption AS NodeName,
                            Interfaces.Caption AS Interface_Caption,
                            Maxbps_In95,
                            Maxbps_Out95,
                            Maxbps_95
                          FROM Nodes
                          INNER JOIN Interfaces ON Nodes.NodeID = Interfaces.NodeID
                          INNER JOIN
                            (SELECT
                            InterfaceID,
                            dbo.GetInBps95th(InterfaceID, @StartDate, @EndDate) AS Maxbps_In95,
                            dbo.GetOutBps95th(InterfaceID, @StartDate, @EndDate) AS Maxbps_Out95,
                            dbo.GetMaxBps95th(InterfaceID, @StartDate, @EndDate) AS Maxbps_95
                            FROM InterfaceTraffic
                            WHERE InterfaceTraffic.DateTime >= @StartDate AND InterfaceTraffic.DateTime <= @EndDate
                            GROUP BY InterfaceID
                            ) TrafficStat
                          ON Interfaces.InterfaceID = TrafficStat.InterfaceID
                          ORDER BY NodeName, Interface_Caption
                          
                          

                           

                           

                          This query works on an environment with almost 7,000 interfaces and returns no NULL values. There are some zeroes, but that is expected on interfaces with no traffic.

                          1 of 1 people found this helpful