Submit a ticketCall us

WebinarUpcoming Webinar: Easily Automate Backups and Simplify Log Message Management

Backing up your network configuration and logging data are a few steps to helping keep your network safe. In this in-depth webinar, we’ll show you how these tasks can be automated to save your IT team time while maintaining accurate archives of your data.

Register now.

Home > Success Center > Network Performance Monitor (NPM) > Generate a monthly average utilization report for peak business hours

Generate a monthly average utilization report for peak business hours

Overview

This article describes how to generate a monthly average utilization report for all devices on a network that only shows data for peak business hours, such as 8:00 AM to 7:00 PM.

Environment

All NPM versions

Steps

  1. Create a monthly average utilization report for your business hours.
  2. Create a summary report of average utilization in your environment

Create a monthly average utilization report for designated hours

  1. Click Start > All Programs > SolarWinds Orion > Report Writer.
  2. In the left pane, click Historical Traffic Reports > Total Bytes Transferred by Interface - Last Month.
  3. On the General tab, edit the Report Title and the Description.GenReport1.png
  4. On the Select Fields tab, select the following boxes:
    • Select the following fields
    • Node Name
    • Interface Name
  5. Click Browse (...) and click Add a new field.
  6. Click the Field asterisk (*), and then select Historical Interface Traffic > Transmit+Receive Data Combined > Circuit Utilization (Avg of Recv,Xmit).
    GenReport2_AddField.png
  7. Specify the business hours:
    1. On the Filter Results tab, click Browse (...) > Add a new elementary condition.
    2. Click the first asterisk (*), and then select Date/Time (Traffic Filtering Only) > Time of Day (24 hour format).
    3. Click is equal to, and then select greater or equal.
    4. Click the second asterisk (*), and then enter the start time of your peak business hours in 24-hour hh:mm format (For example, 08:00).
    5. Click Browse (...) > Add a new elementary condition.
    6. Click the first asterisk (*), and select Date/Time (Traffic Filtering Only) > Time of Day (24 hour format).
    7. Click is equal to, and select less or equal.
    8. Click the second asterisk (*), and enter the end time of your peak business hours in 24-hour hh:mm format (For example, 19:00).
    9. Click Preview on the right of the Report Designer pane.
      GenReport3_FilterResults.png
  8. Click Save to save the report. You can find it in Historical Traffic Reports.

The report currently displays the average circuit utilization of each interface for the month. To summarize the results into a single line report of average utilization of the entire network, complete the following steps:  

Create a report of the average utilization of your entire network (Report Writer)

  1. In the Report Writer toolbar, select the new report, and click Design.
  2. Click Report > Show SQL to open the SQL tab and display the report query.
  3. Copy the entire query.
    For example, if your business hours are 8:00 - 17:00, the code looks as follows:

    SELECT  TOP 10000 Nodes.Caption AS NodeName,
    Interfaces.InterfaceName AS Interface_Name,
    AVG(Case
                When InBandwidth+OutBandwidth=0 Then 0
                When InBandwidth=0 Then
                (Out_Averagebps/OutBandwidth) * 100
                When OutBandwidth=0 Then
                (In_Averagebps/InBandwidth) * 100
                Else
                ( (Out_AverageBps/OutBandwidth)+(In_AverageBps/InBandwidth))*50
                End) AS AVERAGE_of_CircuitUtil_AvgRecvXmit,
    CONVERT(DateTime,
    LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),
    101) AS SummaryMonth 

    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 43007 AND 43037.9999884259 )
     AND  
    (
      (Convert(Char,DateTime,108) >= '08:00') AND 
      (Convert(Char,DateTime,108) <= '17:00')
    )


    GROUP BY CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101), 
    Nodes.Caption, Interfaces.InterfaceName


    ORDER BY 1 ASC, 2 ASC ,SummaryMonth ASC

     

  4. Click File > New Report.

  5. Select Advanced SQL and click OK.
  6. On the General tab, edit the Report Title and the Description.
  7. On the SQL tab, paste the SQL query from your first report into the empty field.
  8. Complete the following edits in the code:
    1. In the GROUP BY clause, remove Nodes.Caption and Interfaces.InterfaceName.
    2. Delete the ORDER BY clause.
    3. Edit the SELECT clause to read as "SELECT 'Average % Utilization of all interfaces' AS AVERAGE_of_CircuitUtil_AvgRecvXmit,".

    With business hours 8:00 - 17:00, the code looks as follows:

    SELECT 'Average % Utilization of all interfaces' AS AVERAGE_of_CircuitUtil_AvgRecvXmit,
    AVG(Case
                When InBandwidth+OutBandwidth=0 Then 0
                When InBandwidth=0 Then
                (Out_Averagebps/OutBandwidth) * 100
                When OutBandwidth=0 Then
                (In_Averagebps/InBandwidth) * 100
                Else
                ( (Out_AverageBps/OutBandwidth)+(In_AverageBps/InBandwidth))*50
                End) AS AVERAGE_of_CircuitUtil_AvgRecvXmit,
    CONVERT(DateTime,
    LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),
    101) AS SummaryMonth 

    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 43007 AND 43037.9999884259 )
     AND  
    (
      (Convert(Char,DateTime,108) >= '08:00') AND 
      (Convert(Char,DateTime,108) <= '17:00')
    )


    GROUP BY CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101)

  9. Save the report.

When you click Preview, you can preview the report. The report consists of one line summarizing the average percent utilization of all interfaces during the previous month.

 

Create a report of the average utilization of your entire network in the Orion Web Console

  1. In the Orion Web Console, click Reports > All Reports, and then click Manage Reports in the top right corner.
  2. Click Create New Report.
  3. In Add Content, select Custom Table, and click Select and Continue.
  4. In Select Contents, select Advanced Database Query (SQL, SWQL), and the SQL option.
  5. Back in the Report Writer, click Report > Show SQL and copy the entire query.
  6.  Back in the Orion Web Console, in the Add Content window, paste the query into the field, and complete the following edits in the code:

    1. In the GROUP BY clause, remove Nodes.Caption and Interfaces.InterfaceName.
    2. Delete the ORDER BY clause.
    3. Edit the SELECT clause to read as "SELECT 'Average % Utilization of all interfaces' AS AVERAGE_of_CircuitUtil_AvgRecvXmit,".

      With business hours 8:00 - 17:00, the code looks as follows:

      SELECT 'Average % Utilization of all interfaces' AS AVERAGE_of_CircuitUtil_AvgRecvXmit,
      AVG(Case
                  When InBandwidth+OutBandwidth=0 Then 0
                  When InBandwidth=0 Then
                  (Out_Averagebps/OutBandwidth) * 100
                  When OutBandwidth=0 Then
                  (In_Averagebps/InBandwidth) * 100
                  Else
                  ( (Out_AverageBps/OutBandwidth)+(In_AverageBps/InBandwidth))*50
                  End) AS AVERAGE_of_CircuitUtil_AvgRecvXmit,
      CONVERT(DateTime,
      LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),
      101) AS SummaryMonth 

      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 43007 AND 43037.9999884259 )
       AND  
      (
        (Convert(Char,DateTime,108) >= '08:00') AND 
        (Convert(Char,DateTime,108) <= '17:00')
      )


      GROUP BY CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101)

  7. Enter a Selection Name, and click Add to Layout. Click Preview Results.
    GenReport4_AddContent_webconsole.png

  8. Click Add Column.

  9. In Add Column, select all available columns.

    To change the column names in the report, click Advanced, and provide a Display Name. To change the order or columns, drag the columns to the new position.


    GenReport5_Customize columns.png

     

  10. Complete the Add Report wizard.
    You have created a new web-based report. To run the report in the Orion Web Console, click Reports > All Reports, find and click the report.

 

 

Last modified

Tags

Classifications

Public