Client Management Suite

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.

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

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.

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

Thanks again Andrew this is great.