3 Replies Latest reply on Feb 8, 2011 3:42 PM by cmgurley

    Custom Reporting--- 95th percentile for WAN interfaces.

       Hi All

       I want to modify an exisiting provided Custom SQL query from Report Writer called "95th Percentile Traffic Rate- Last 7 Days".. Unforntuately I am not very familiar with SQL.  I would like to provide a report that displays the 95th percentile for WAN interfaces for the last 7 days.  I have created a Custom interface field called "interface_type2" Below is the SQL query in ReportWriter that generates the 95th percentile for all interfaces:

       DECLARE @StartDate DateTime
      DECLARE @EndDate DateTime

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

      SELECT Interfaces.InterfaceId,
      Nodes.Caption AS NodeName,
      Nodes.VendorIcon AS Vendor_Icon,       
      Interfaces.Caption AS Interface_Caption,
      Interfaces.InterfaceIcon AS Interface_Icon,
      Maxbps_In95,
      Maxbps_Out95,
      Maxbps_95
      FROM Nodes
      INNER JOIN Interfaces
          ON (Nodes.NodeID = Interfaces.NodeID)
      ---------------------
      INNER JOIN    
      (
          SELECT InterfaceID, dbo.GetInBps95th(AA.InterfaceID, @StartDate, @EndDate) AS Maxbps_In95
          FROM (
              SELECT DISTINCT A.InterfaceID
              FROM dbo.InterfaceTraffic A
              WHERE A.DateTime >= @StartDate AND A.DateTime <=  @EndDate
          ) AS AA
      ) as RESULT_IN
          ON (Interfaces.InterfaceID = RESULT_IN.InterfaceID)
      ---------------------
      INNER JOIN    
      (
          SELECT InterfaceID, dbo.GetOutBps95th(AA.InterfaceID, @StartDate, @EndDate) AS Maxbps_Out95
          FROM (
              SELECT DISTINCT A.InterfaceID
              FROM dbo.InterfaceTraffic A
              WHERE A.DateTime >= @StartDate AND A.DateTime <=  @EndDate
          ) AS AA
      ) as RESULT_OUT
          ON (Interfaces.InterfaceID = RESULT_OUT.InterfaceID)
      ---------------------
      INNER JOIN    
      (
          SELECT InterfaceID, dbo.GetMaxBps95th(AA.InterfaceID, @StartDate, @EndDate) AS Maxbps_95
          FROM (
              SELECT DISTINCT A.InterfaceID
              FROM dbo.InterfaceTraffic A
              WHERE A.DateTime >= @StartDate AND A.DateTime <=  @EndDate
          ) AS AA
      ) as RESULT_MAX
          ON (Interfaces.InterfaceID = RESULT_MAX.InterfaceID)
      ---------------------
       ORDER BY NodeName, Interface_Caption

       

      I am wondering if someone help the SQL statements needed to "filter" and display only the interfaces that have the "WAN" in the Custom interface field called "interface_type2"

       

      thanks for your help

       

      chuck harpham

      Symantec

      Network Engineering/Monitoring