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.
nodez.NodeID as id,
Nodez.Caption as Servername,
HDD.CapacityB as HDDsize,
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.
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)
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
V.VolumeTypeID = (4)
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.