SELECT vc.Name
,vc.[User]
,CAST(vm.[Total Physical Memory (Bytes)] / 1073741824.0E AS DECIMAL(10, 2)) AS [Total Memory (GB)]
,vp.Model [CPU Type]
,CAST(hd.Total / 1048576.0E AS DECIMAL(10, 2)) AS [Total Disk Size (GB)]
FROM vComputer vc
JOIN Inv_HW_Computer_System vm
ON vm._ResourceGuid = vc.Guid
JOIN vHWProcessor vp
ON vp._ResourceGuid = vc.Guid
JOIN (SELECT _ResourceGuid, SUM([Max Media Size (Kilobytes)]) AS Total
FROM Inv_HW_Storage
WHERE [Media Type] = 29
GROUP BY _ResourceGuid
) hd
ON hd._ResourceGuid = vc.Guid
ORDER BY Name

------------------------------------
Sr. Principal SQA Engineer
Symantec

network101

SQL Hardware resources report. - Comment:06 Dec 2012 : Link

Thanks for that, excellent work as always!

can you add in two additional tables for me please.

Operating system, free disk space.

Thanks for all your help with creating these reports Andrew.

Andrew Bosch Symantec Employee

SQL Hardware resources report. - Comment:07 Dec 2012 : Link

Here you go!

SELECT vc.Name
,vc.[User]
,vc.[OS Name] AS [Operating System]
,CAST(vm.[Total Physical Memory (Bytes)] / 1073741824.0E AS DECIMAL(10, 2)) AS [Total Memory (GB)]
,vp.Model [CPU Type]
,CAST(hd.Total / 1073741824.0E AS DECIMAL(10, 2)) AS [Total Disk Size (GB)]
,CAST(hd.TotalFree / 1073741824.0E AS DECIMAL(10, 2)) AS [Free Disk Space (GB)]
FROM vComputer vc
JOIN Inv_HW_Computer_System vm
ON vm._ResourceGuid = vc.Guid
JOIN vHWProcessor vp
ON vp._ResourceGuid = vc.Guid
JOIN (SELECT _ResourceGuid, SUM([Size (Bytes)]) AS Total, SUM([Free Space (Bytes)]) AS TotalFree
FROM vHWLogicalDisk
WHERE [Logical Disk Type] = 3
GROUP BY _ResourceGuid
) hd
ON hd._ResourceGuid = vc.Guid
ORDER BY Name

------------------------------------
Sr. Principal SQA Engineer
Symantec

SOLUTION

network101

SQL Hardware resources report. - Comment:08 Dec 2012 : Link

Thanks again Andrew this is great.

SQL Hardware resources report.

Comments 4 Comments • Jump to latest comment

Would you like to reply?

Links