Better Living Through Narratology

SCCM Report listing count of computers and associated hardware information

I needed a report today that would show me the amount of computers that we had of a certain make and model (easy enough) but the report also had to show the associated hardware information for those models.  Mildly problematic on a certain scale because someone with a specific model of computer out there is bound to have more or less RAM than someone else with the same model of computer.

After a good deal of fumbling around (SQL is not my strong suit), I pieced this together and it worked.

select
 GSCS.Manufacturer0 as [Manufacturer],
 GSCS.Model0 as [Model],
 MRY.TotalPhysicalMemory0 as [RAM],
 GSP.MaxClockSpeed0 as [CPU Speed (MHz)],
 GSLD.Size0 as [Disk Size (MB)],
 count(*) as [Count]
from v_R_System RS
left join v_GS_PROCESSOR GSP on RS.ResourceID = GSP.ResourceID
left join v_GS_X86_PC_MEMORY MRY on RS.ResourceID = MRY.ResourceID
left join v_GS_COMPUTER_SYSTEM GSCS on RS.ResourceID = GSCS.ResourceID
left join v_GS_LOGICAL_DISK GSLD on RS.ResourceID = GSLD.ResourceID
 Group by GSCS.Model0, MRY.TotalPhysicalMemory0, GSP.MaxClockSpeed0, GSCS.Manufacturer0, GSLD.Size0
 Order by GSCS.Model0

Following that pattern, I think you could throw any other info you want in there and it should fly.  Again… not a SQL guy, so take this at face value!  There may be a better way of lumping the info together.

Posted in IT, SCCM, SQL | Comments Off on SCCM Report listing count of computers and associated hardware information