0 Replies Latest reply on Nov 27, 2015 5:56 PM by csowerby

    existing WAN saturation report critique and questions

    csowerby

      Hi there -- please indulge me on a multipart question:

       

      1. An individual in my company who has now left created this query to report on saturation events above 90% average inbound utilization in the previous month as a method to draw attention to sites that may benefit from WAN upgrades. It appears to work but I am unsure what the Count(*) actual measures -- would it be minutes or some other metric? Any other comments?

       

      -- saturation report

      DECLARE @StartDate DateTime

      DECLARE @EndDate DateTime

      SET @StartDate = DATEADD(d, DATEDIFF(d, 0, DATEADD(m, -1, DATEADD(d, 1 - day(getdate()), getdate()))), 0)

      SET @EndDate = DATEADD(ms, -2,DATEADD(d, DATEDIFF(d, 0, DATEADD(d, 1 - day(getdate()), getdate())), 0));

      SELECT    

        Nodes.HA,

        Nodes.Site,

              Nodes.Address,

              Nodes.City,

              Nodes.SiteType,

        Interfaces.WANFeed,

              (Interfaces.InBandwidth / 1000000) As Subscribed_Mbps,

                Count(*) as SaturationEvents

       

      FROM

        Interfaces

        INNER JOIN InterfaceTraffic_Detail ON Interfaces.InterfaceID = InterfaceTraffic_Detail.InterfaceID

        INNER JOIN Nodes ON Interfaces.NodeID = Nodes.NodeID

      WHERE     

        (NOT (Interfaces.WANFeed IS NULL))

         AND (InterfaceTraffic_Detail.In_Averagebps / Interfaces.InBandwidth > .9) AND

             (Nodes.SiteType = 'Acute' OR Nodes.SiteType = 'DC' OR Nodes.SiteType='Acute-DC') AND

             ((DateTime between @StartDate and @EndDate))

       

      GROUP BY

              Nodes.HA,

              Nodes.Site,

              Nodes.Address,

              Nodes.City,

              Nodes.SiteType,

              Interfaces.WANFeed,

              Interfaces.InBandwidth

       

      Order By SaturationEvents Desc

       

      2. Assuming the above report is reasonable is there a way to add the saturationevents metric to our version of the 95th percentile report? I realize one is the last 30 days and one is last months. Also the SiteType criteria needs to be aligned. 

       

      DECLARE @StartDate DateTime

      DECLARE @EndDate DateTime

       

      SET @StartDate =CAST((ROUND(CAST(GetDate() - 30 AS FLOAT), 0, 1)) as datetime)

      SET @EndDate = GetDate()

       

      SELECT

      Nodes.HA,

      Nodes.Site,

      Nodes.Address,

      Nodes.City,

      Interfaces.WANFeed,

      Nodes.SiteType,

      Interfaces.InBandwidth AS Recv_Bandwidth,

      Interfaces.InterfaceId,

      Nodes.NodeID,

      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

       

      WHERE

      (NOT (Interfaces.WANFeed IS NULL)) AND

      (Nodes.SiteType = 'Acute-DC') AND

      (Interfaces.WANFeed <> 'Secondary')

       

      ORDER BY HA,NodeName, Interface_Caption

       

      3. Is there a database reference that describes the differences between TrafficStat and InterfaceTraffic_Detail tables?

       

      Thanks everyone -- if my questions are not clear please let me know.