Hi,
I'm trying to create a single report that will show me the following columns (data collection period is Last Month):
- Node Name
- Site Code (Node custom property)
- Interface Name (WAN interface of a particular site, identified by Interface Custom Property, although could use Node Custom Property if required)
- Average Response Time of Node
- Inbound Peak Hour Load (collect hourly average of inbound utilisation % and then collect the peak/max of these hourly figures, so one figure per interface for the month)
- Outbound Peak Hour Load (collect hourly average of outbound utilisation % and then collect the peak/max of these hourly figures, so one figure per interface for the month)
I've started off with a query that shows me the hourly interface utilisation (receive), but I don't know where to go from here to get the peak hourly load. Do I have to create some sort of nested SQL query (select from the results of the below query) or will I need a separate table to populate the data into first? If the latter, is it still possible to run this type of report on a monthly schedule?
I also still have to determine how to then include the monthly average response time to the node but I thought I'd try to get the interface information in first.
SELECT Nodes.Caption AS 'Node_Name',
Nodes.SiteCode AS 'Site_Code',
Interfaces.InterfaceName AS 'Interface_Name',
Case InBandwidth
When 0 Then 0
Else (In_Averagebps/InBandwidth) * 100
End AS Recv_Percent_Utilization
FROM
(Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID))
INNER JOIN InterfaceTraffic ON (Interfaces.InterfaceID = InterfaceTraffic.InterfaceID AND InterfaceTraffic.NodeID = Nodes.NodeID)
WHERE
(
MONTH(DateTime) = (MONTH(GETDATE()-DAY(GETDATE()))) -- filter last month
AND YEAR(DateTime) = YEAR(GETDATE()-DAY(GETDATE()))) -- filter last month
AND Interfaces.WAN_Interface = 1 -- filter WAN Interface = True
)
ORDER BY 1 ASC
Thanks