2 Replies Latest reply on Apr 13, 2009 4:08 PM by tdking

    Need help with SQL code

    klc2009

      I have a mental block when it comes to deciphering SQL code so I'm hoping someone can help.

      I am trying to modify a copy of the canned historical traffic report that shows the 95th percentile of bandwidth used over the last month for each interface. I need a similar report, but for just one interface or a select group of interfaces. Please show me how to modify the following SQL code so that I can change which interface ID to run the report against.

      I would also like to add the custom bandwidth field (InBandwidth, OutBandwidth) to the report, so if you could also show me where and what to add to achieve that as well, I would greatly appreciate it.

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

       

      Thanks!

      klc2009