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

Someone recently posted something very similar that I use but now I can't find it here...

It was named Server+Report.xml I believe.

Hi,

Thanks for the input but this report does not contain the details for Fixed Disk Volumes ie Drive C: or D:  etc. The Node report can contain CPU and Memory but we're having trouble including the disk volumes

Yep, I feel your pain. My PMs want the same thing.

select

nodez.NodeID as id,

Nodez.Caption as Servername,

HDD.CapacityB as HDDsize,

hdd.Model,

hdd2.VolumeName,

hdd2.CapacityB,

Nodez.DeviceRole as DeviceRole,

IPAddr.IPAddress as 'IP Addresses'

FROM dbo.Nodes AS Nodez

join dbo.NodeIPAddresses as IPAddr on Nodez.NodeID = IPAddr.NodeID

join dbo.AssetInventory_HardDrive as HDD on Nodez.NodeID = HDD.NodeID

join dbo.AssetInventory_LogicalDrive as HDD2 on Nodez.NodeID = HDD2.NodeID

where DeviceRole = 'server'

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.