0 Replies Latest reply on Jan 11, 2012 1:45 PM by smbernacki

    Report Summarization - Hourly summarization

    smbernacki

      I have a requirement to provide my customer with a monthly report to show average and peak utilization of an inteface from 8am - 5pm M-F.  This part is no problem.

      The questions I have is how is the data summarized?  In the summarization tab, I have selected to summarize the data hourly.

      So is the data reported on day 1 at 8am the average and peak from the 7am - 8am hour, or the 8am - 9am hour?

       

      Below is the SQL for the report I am running.

       

      SELECT  TOP 10000 Convert(DateTime,Floor(Cast((DateTime) as Float)*24)/24,0) AS SummaryDateTime,
      Nodes.NodeID AS NodeID,
      Interfaces.InterfaceID AS InterfaceID,
      Nodes.Caption AS NodeName,
      Nodes.VendorIcon AS Vendor_Icon,
      Interfaces.Caption AS Interface_Caption,
      Interfaces.InterfaceIcon AS Interface_Icon,
      AVG(InterfaceTraffic.In_Averagebps) AS AVERAGE_of_Average_Receive_bps,
      MAX(InterfaceTraffic.In_Maxbps) AS MAX_of_Peak_Receive_bps,
      AVG(InterfaceTraffic.Out_Averagebps) AS AVERAGE_of_Average_Transmit_bps,
      MAX(InterfaceTraffic.Out_Maxbps) AS MAX_of_Peak_Transmit_bps,
      SUM(InterfaceTraffic.In_TotalBytes) AS SUM_of_Total_Bytes_Received,
      SUM(InterfaceTraffic.Out_TotalBytes) AS SUM_of_Total_Bytes_Transmitted

      FROM
      (Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID))  INNER JOIN InterfaceTraffic ON (Interfaces.InterfaceID = InterfaceTraffic.InterfaceID AND InterfaceTraffic.NodeID = Nodes.NodeID)


      WHERE
      ( DateTime BETWEEN 40887 AND 40918 )
       AND 
      (
        (Nodes.Caption = 'FW-PGH-INTERSITE') AND
        (Convert(Char,DateTime,108) >= '08:00') AND
        (Convert(Char,DateTime,108) <= '17:01') AND
        (DATEPART(weekday, DateTime) <> 1) AND
        (DATEPART(weekday, DateTime) <> 7) AND
        (Interfaces.InterfaceName LIKE '%inside%')
      )


      GROUP BY Convert(DateTime,Floor(Cast((DateTime) as Float)*24)/24,0),
      Nodes.NodeID, Interfaces.InterfaceID, Nodes.Caption, Nodes.VendorIcon, Interfaces.Caption, Interfaces.InterfaceIcon


      ORDER BY SummaryDateTime ASC, 6 ASC