Comprehensive Server Report (CPU, Memory, Disk and Availability)

What I ended up doing was first creating the CPU / Memory portion of the report using the Gauges and Charts they provide in the custom report builder.

Then added my own Custom Table [SQL Query] at the bottom to handle the Disk/Volume information.

Its working nicely.

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

What I ended up doing was first creating the CPU / Memory portion of the report using the Gauges and Charts they provide in the custom report builder.

Then added my own Custom Table [SQL Query] at the bottom to handle the Disk/Volume information.

Its working nicely.

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

Can you elaborate on how you incorporated memory and CPU?

The report manager gives you built in charts and gauges to build a report. I just used that.

Reports > All Reports > Report Manager > Create New Report

Add Content > Charts > Average CPU Load

Add Content > Gauges > Average CPU Load & Memory Utilization – Pie Gauges

Then I added my own table content and pasted in the SQL query above.

Add Content > Reports > Custom Table > Advanced Database and SQL Query

Thanks. Ok. If I have a static list of servers to create a report against how can we mortals wit multiple tenants tie all of this together for a group report for each client?

The report builder allows you to choose which servers you want to include. 

As for the SQL query i couldn't tell you without seeing your setup, but it would likely just be another line to the WHERE statement.