Video Screencast Help

SQL Hardware resources report.

Created: 06 Dec 2012 • Updated: 08 Dec 2012 | 4 comments
This issue has been solved. See solution.

Hello. I have been asked by management to identify computers that need to be replaced.

Please can someone make me a sql report that has the following tables.

 

computer name, user name, total memory, processor type, total disk size,

 

 

Thanks in advance.

Comments 4 CommentsJump to latest comment

Andrew Bosch's picture

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's picture

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's picture

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