This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

How to create report showing interface peak hour load?

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