Generate a monthly average utilization report for peak business hours

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.

1. Create a summary report of average utilization in your environment
   
   • To run the report in the SolarWinds Platform Web Console, create a web-based report.

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

1. In the SolarWinds Platform 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.  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:

 -- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.

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)

6. Enter a Selection Name, and click Add to Layout. Click Preview Results.
   

7. Click Add Column.

8. 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.

   
   

    

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