Hello Folks,

 

Got a Requirement today to generate a report on CPU, Disk and Memory Usage, Usually this report is used by the Infrastructure teams to access current usage and forecast capacity

 

Hence I spend some time on Research,

 

Found a Helpful Article, Custom Report: Hourly average CPU and memory usage - SolarWinds Worldwide, LLC. Help and Support

 

  1. Click Settings > Manage reports.
  2. Click Create new Report:
    • Select Custom Table and click Select and Continue.
    • Set the Data Selection as follows, and click Add to layout:
      • For Selection method use - Specific Objects
      • Select the nodes for the report.
  3. To setup the Custom table, click Add Column. Add the following:
    • Node Name
    • Average CPU Load
    • Average Memory Used
  4. Set Timestamp to hourly.
  5. Set Sort and Group Results By to - Node.
  6. Set From option to Last 24 hours.
  7. Finish the report wizard.

 

The Steps mentioned above give you a perfect report on CPU and Memory Usage, I choose to aggregate it for last 24 hours and the sample is shown  below:

 

This was good now, i wanted to get the Disk report,

 

Hence found a helpful thread, which lists down the SQL query to get the disk report ( Logical Disk)

 

SELECT

   N.NodeID AS 'Node ID'

  ,N.Caption AS 'Server Name'

  ,I.IPAddress as 'IP Addresses'

  ,V.Caption AS 'Volume Name'

  ,V.VolumeDescription AS 'Volume Description'

  ,V.VolumeType AS 'Volume Type'

  ,V.VolumeTypeID AS 'Volume Type ID'

  ,ROUND(V.VolumeSize/1073741824,0) AS 'SIZE /GB'

  ,ROUND(V.VolumePercentUsed,0) AS 'Percent Used'

  ,ROUND(V.VolumeSpaceUsed/1073741824,0) AS 'USED /GB' --VolumeSpaceUsed is calculated in Bytes (/ by 1073741824 to get GB)

  ,ROUND(V.VolumeSpaceAvailable/1073741824,0) AS 'FREE /GB' --VolumeSpaceAvailable is calculated in Bytes (/ by 1073741824 to get GB)

  ,CASE

  WHEN V.VolumePercentUsed >= 95 THEN 'WARNING Less than 5% Free'

  ELSE ' '

  END AS 'Warning'

 

FROM dbo.Nodes AS N

  INNER JOIN dbo.NodeIPAddresses AS I

  ON N.NodeID = I.NodeID

  INNER JOIN dbo.Volumes AS V

  ON N.NodeID = V.NodeID

 

 

WHERE

  V.VolumeTypeID = (4)

 

 

Order BY

  N.Caption ASC

 

Output Sample :

This two reports were really helpful, but then the requirement was to show the data in one single report, i tried hands on another Advanced Reporting option

 

So using the Advance Report Editor,

Selection Method was Specific Object (Static Selection ) Choose Windows as a group

Now while Specifying columns, Average CPU Load, Disk Usage, Memory Usage got the error below

 

 

I believe, this may be achievable using the Advanced SQL Query Editor, however not sure of the Implications this may cause and the data might be fetched from different buckets and might cause performance issues.