This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

95TH PERCENTILE IN AND OUT FOR LAST 30 DAYS

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.

  • 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

    ...

  • 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?

  • 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.

  • 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.  emoticons_sad.png

  • 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.

  • Yes, that did it.   Many thanks, zackm