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