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.