1 Reply Latest reply on Jun 30, 2017 3:38 PM by designerfx

    How to create report showing interface peak hour load?

    shuth

      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