Vidéos d'aide de Screencast

SQL Hardware resources report.

Created: 06 Décembre 2012 • Updated: 08 Décembre 2012 | 4 comments
Ce problème a été résolu. Voir la 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.

Commentaires CommentairesAccéder au dernier commentaire

l'image des Andrew Bosch

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

l'image des network101

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.

l'image des Andrew Bosch

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