0 Replies Latest reply on Sep 5, 2018 10:36 AM by lbsoulliere

    Bandwidth analysis report

    lbsoulliere

      I have been looking for a way to automatically create a report to easily determine the network link which need an upgrade.

       

      The average or 95th are good but show their limits when it come to more isolated burst (which may generate impact on customer)

       

      I added 2 variables : PCOT and ISLAND

       

      PCOT = Polling cycle over threshold --> Quantity of polling cycle for which the utilization was >= 80%

       

      In this example, the polling interval was every minute and the query keep only records between 8 and 16. So this is 14400 minutes.

       

      The ISLAND is the quantity of time that there was a contiguous utilization >= 80%

       

      This query run in 7min on 1812 interfaces, polling every minutes, details stats for 1 month.

       

      I'm sure this query can be optimized because I barely understand what I do in SQL

       

      I don't want any lesson of SQL here. If you think you can do better, just post it!

       

      If you can optimize it, you will have my eternal gratitude

       

      It serve his purpose in my company, I'm just posting it so that other can use it / contribute.

       

      * I started to build the query based on another script on Thawk for the 95th percentile, so some variable have the same name.

       

       

      *** QUERY UPDATED 2018-09-04 ***

      Added SET DATEFIRST 7

       

       

      DECLARE @StartBusinessHours FLOAT

      DECLARE @EndBusinessHours FLOAT

      DECLARE @Threshold FLOAT

      DECLARE @PollingInterval FLOAT

      DECLARE @Days FLOAT

       

      SET @StartBusinessHours = 8

      SET @EndBusinessHours = 16

      SET @Threshold = 80

      SET @PollingInterval = 1

      SET @Days = 30

      SET DATEFIRST 7

       

      SELECT DISTINCT SecondSet.interfaceid                 AS INTERFACEID,

                      FourthSet.caption                     AS NOM_SITE,

                      SecondSet.caption                     AS NOM_INTERFACE,

                      ThirdSet.nb_island                    AS NB_ISLAND_GT_30,

                      MOY_MAX = CASE

                                  WHEN Secondset.avg_in_percent >

                                       Secondset.avg_out_percent THEN

                                  Secondset.avg_in_percent

                                  ELSE Secondset.avg_out_percent

                                END,

                      Firstset.pcot_percent                 AS PCOT80_POURCENT,

                      FirstSet.polling_cycle_over_threshold AS PCOT80,

                      Secondset.avg_in                      AS MOY_IN,

                      Secondset.avg_out                     AS MOY_OUT,

                      Secondset.avg_in_percent              AS MOY_IN_POURCENT,

                      Secondset.avg_out_percent             AS MOY_OUT_POURCENT,

                      Secondset.inbandwidth                 AS CAPACITE_IN,

                      Secondset.outbandwidth                AS CAPACITE_OUT,

                      FifthSet.PRECISION

      FROM   (SELECT i.interfaceid,

                     Count(i.interfaceid)

                            AS Polling_Cycle_Over_Threshold,

                     Round(

             ( NULLIF(Count(i.interfaceid), 0) / ( ( 24 ) * 60 * @Days ) ) * 100, 2)

                     AS

                     PCOT_PERCENT

              FROM   [SURV_PRO_NPM].[dbo].[interfacetraffic_detail] AS I WITH (nolock)

                     INNER JOIN [SURV_PRO_NPM].[dbo].[interfaces] AS J WITH (nolock)

                             ON ( [I].interfaceid = [j].interfaceid )

              WHERE  ( i.in_maxbps >= ( NULLIF(J.inbandwidth, 0) * @Threshold / 100 )

                        OR i.out_maxbps >= (

                           NULLIF(J.outbandwidth, 0) * @Threshold / 100 ) )

                     AND ( ( Datepart(dw, datetime) ) <> 1

                           AND ( Datepart(dw, datetime) <> 7 ) )

                     AND ( Datepart(hh, datetime) >= @StartBusinessHours

                           AND ( Datepart(hh, datetime) <= @EndBusinessHours ) )

              GROUP  BY i.interfaceid,

                        j.interfacename) AS FirstSet

             FULL OUTER JOIN (SELECT

             Avg(NULLIF(i.in_maxbps, 0))

             AS AVG_IN,

             Avg(NULLIF(i.in_maxbps, 0)) / NULLIF(J.inbandwidth, 0) * 100   AS

             AVG_IN_PERCENT

             ,

             Avg(NULLIF(i.out_maxbps, 0))

             AS AVG_OUT,

             Avg(NULLIF(i.out_maxbps, 0)) / NULLIF(J.outbandwidth, 0) * 100 AS

             AVG_OUT_PERCENT,

             i.interfaceid,

             i.nodeid,

             j.caption,

             J.inbandwidth,

             J.outbandwidth

             FROM   [dbo].[interfacetraffic_detail] AS I WITH (nolock)

             INNER JOIN [SURV_PRO_NPM].[dbo].[interfaces] AS J WITH (nolock)

             ON ( [i].interfaceid = [j].interfaceid )

             WHERE  ( ( Datepart(dw, datetime) ) <> 1

             AND ( Datepart(dw, datetime) <> 7 ) )

             AND ( Datepart(hh, datetime) >= @StartBusinessHours

             AND ( Datepart(hh, datetime) <= @EndBusinessHours ) )

             GROUP  BY i.interfaceid,

             i.nodeid,

             J.caption,

             J.outbandwidth,

             J.inbandwidth) AS SecondSet

                          ON FirstSet.interfaceid = SecondSet.interfaceid

             FULL OUTER JOIN (SELECT Count(*) AS NB_ISLAND,

                                     interface_id

                              FROM   (SELECT Count(grp)  AS NB_ISLAND,

                                             interfaceid AS INTERFACE_ID

                                      FROM   (SELECT Datediff(minute, '19000101',

                                                     i.datetime)

                                                     - Row_number()

                                             OVER (

                                               ORDER BY i.interfaceid, datetime)

                                                     AS GRP,

                                                     i.interfaceid,

                                                     i.datetime,

                                                     j.interfacename

                                              FROM

                                     [SURV_PRO_NPM].[dbo].[interfacetraffic_detail] AS

                                     I WITH

                                     (nolock

                                     )

                                     INNER JOIN [SURV_PRO_NPM].[dbo].[interfaces] AS J

                                                WITH (

                                                nolock)

                                             ON ( [i].interfaceid = [j].interfaceid )

                                              WHERE  ( i.in_maxbps >=

             (

             NULLIF(J.inbandwidth, 0) * @Threshold / 100 )

              OR i.out_maxbps >=

                 (

                 NULLIF(J.outbandwidth, 0) * @Threshold / 100 ) )

             AND ( ( Datepart(dw, datetime) ) <> 1

                 AND ( Datepart(dw, datetime) <> 7

                     ) )

             AND ( Datepart(hh, datetime) >=

                 @StartBusinessHours

                 AND ( Datepart(hh, datetime) <=

                       @EndBusinessHours

                     ) )

             GROUP  BY i.interfaceid,

              j.interfacename,

              i.datetime) AS SUB1

             GROUP  BY grp,

             interfaceid

             HAVING Count(grp) >= 30) AS SUB2

             GROUP  BY interface_id) AS ThirdSet

             ON FirstSet.interfaceid = ThirdSet.interface_id

             INNER JOIN (SELECT n.nodeid,

                                n.caption

                         FROM   nodes AS N WITH (nolock)) AS FourthSet

                     ON SecondSet.nodeid = FourthSet.nodeid

             INNER JOIN (SELECT Round(( Cast(Count(*) AS FLOAT) / 44640 ) * 100, -1)

                                AS

                                PRECISION,

                                I.interfaceid

                         FROM   interfacetraffic_detail AS I WITH (nolock)

                         GROUP  BY interfaceid) AS FifthSet

                     ON SecondSet.interfaceid = FifthSet.interfaceid

      ORDER  BY polling_cycle_over_threshold DESC