0 Replies Latest reply on Aug 8, 2018 12:40 PM by lbsoulliere

    Bandwidth analysis

    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.

       

       

      DECLARE @StartBusinessHours Float

      DECLARE @EndBusinessHours Float

      DECLARE @Threshold Float

      DECLARE @PollingInterval Float

      SET @StartBusinessHours = 8

      SET @EndBusinessHours = 16

      SET @Threshold = 80

      SET @PollingInterval = 1

       

       

      SELECT SecondSet.InterfaceID as INTERFACEID, SecondSet.CaptionNode as NOM_SITE, FourthSet.TYPE, 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 FROM

       

      (SELECT i.interfaceid, COUNT(i.interfaceid) as Polling_Cycle_Over_Threshold, ROUND((nullif(COUNT(i.interfaceid),0)/((@EndBusinessHours-@StartBusinessHours)*60*30))*100,2) as PCOT_PERCENT

       

      FROM

         [SURV_PRO_NPM].[dbo].[InterfaceTraffic_Detail] as I With (NOLOCK)

         INNER JOIN [SURV_PRO_NPM].[dbo].[Nodes] as N With (NOLOCK)

          ON ([n].NodeID = [i].NodeID )

         INNER JOIN [SURV_PRO_NPM].[dbo].[Interfaces] as J With (NOLOCK)

          ON ([n].NodeID = [j].NodeID)

       

      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, N.Caption as CaptionNode, J.InBandwidth,J.OutBandwidth

      FROM [dbo].[InterfaceTraffic_Detail] as I With (NOLOCK)

         INNER JOIN [SURV_PRO_NPM].[dbo].[Nodes] as N With (NOLOCK)

          ON ([n].NodeID = [i].NodeID )

         INNER JOIN [SURV_PRO_NPM].[dbo].[Interfaces] as J With (NOLOCK)

          ON ([n].NodeID = [j].NodeID)

      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, N.Caption, J.OutBandwidth, J.InBandwidth) as SecondSet

       

      ON FirstSet.interfaceid = SecondSet.interfaceid

       

      FULL OUTER JOIN

       

      (SELECT COUNT(*) as NB_ISLAND, INTERFACE_ID,NOM_SITE FROM

      (

      SELECT caption as NOM_SITE, 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, n.Caption

      FROM

         [SURV_PRO_NPM].[dbo].[InterfaceTraffic_Detail] as I With (NOLOCK)

         INNER JOIN [SURV_PRO_NPM].[dbo].[Nodes] as N With (NOLOCK)

          ON ([n].NodeID = [i].NodeID )

         INNER JOIN [SURV_PRO_NPM].[dbo].[Interfaces] as J With (NOLOCK)

          ON ([n].NodeID = [j].NodeID)

      WHERE (i.In_Maxbps >= (nullif(J.InBandwidth,0)*@Threshold/100) OR i.Out_Maxbps >= (nullif(J.OutBandwidth,0)*@Threshold/100)) -- AND (j.interfaceid = 2148)

      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, n.Caption

      ) as SUB1

      GROUP BY GRP, interfaceid, caption

      HAVING COUNT(GRP) >=30

      ) as SUB2

      GROUP BY INTERFACE_ID,NOM_SITE) as ThirdSet

       

      ON FirstSet.interfaceid = ThirdSet.INTERFACE_ID

      ORDER BY Polling_Cycle_Over_Threshold DESC