How to create a MS Configuration manager report that will Count All Computers for each Model
This guide will show you how to create a report in Microsoft System Center Configuration Manager 2012(R2) / Current branch 1702. In this example we will be creating a report which lists all system models and displays a count of each model.
SCCM 2012 (last checked on build 1702) does not have a prebuilt report that will simply return a list of all the distinct manufacturer/model numbers for all the computers in the environment and a count of each type.
This SCCM report will retrieve all the computer models in one column and count of each model in the the adjacent column:
SELECT Model0 AS Model, COUNT(*) AS Count, Domain0 FROM dbo.v_GS_COMPUTER_SYSTEM GROUP BY Model0, Domain0
Output of the report:
If you happen to have Lenovo models in your organization, you will find it useful to translate the Lenovo’s models to real model names. The following query will do that:
SELECT COUNT(*) No_Of_Items, CASE WHEN MODEL0 IN('10AXS2PX00') THEN 'Lenovo M73' WHEN MODEL0 IN('10ahs00d00') THEN 'Lenovo M83' WHEN MODEL0 IN('10FCS06W00','10FCS0W500','10FHS00D00','10FHS07Q00','10FHS0AK00') THEN 'Lenovo M900' WHEN MODEL0 IN('10MKS03H00','10MKS04G00','10MKS04H00') THEN 'Lenovo M910s ' WHEN MODEL0 IN('10A7A00P00','10A7S00P00','10A7A00L00','10A7CTO','10A7S00D00', '10A7S00S00','10A7S02700','10A7S02800','10A7S02D00','10A8A02H0C', '10A8S2E100','10A9003PIV','10A9S02X00') THEN 'Lenovo M93p' WHEN MODEL0 IN('SLIC-BPC') THEN 'HP Compaq Elite 8300 BPC' WHEN MODEL0 = 'To be filled by O.E.M.' THEN 'WeyTech' ELSE Model0 END [Model] FROM v_GS_COMPUTER_SYSTEM WHERE model0 like '%hp%' or model0 like '%think%' or model0 like '%10%' or model0 like '%O.E.M%' or model0 like '%SLIC-BPC%' GROUP BY CASE WHEN MODEL0 IN('10AXS2PX00') THEN 'Lenovo M73' WHEN MODEL0 IN('10ahs00d00') THEN 'Lenovo M83' WHEN MODEL0 IN('10FCS06W00','10FCS0W500','10FHS00D00','10FHS07Q00','10FHS0AK00') THEN 'Lenovo M900' WHEN MODEL0 IN('10MKS03H00','10MKS04G00','10MKS04H00') THEN 'Lenovo M910s ' WHEN MODEL0 IN('10A7A00P00','10A7S00P00','10A7A00L00', '10A7CTO','10A7S00D00', '10A7S00S00','10A7S02700','10A7S02800', '10A7S02D00','10A8A02H0C', '10A8S2E100','10A9003PIV','10A9S02X00') THEN 'Lenovo M93p' WHEN MODEL0 IN('SLIC-BPC') THEN 'HP Compaq Elite 8300 BPC' WHEN MODEL0 = 'To be filled by O.E.M.' THEN 'WeyTech' ELSE Model0 END ORDER BY No_Of_Items DESC, model
Download the .rdl file for this report from here: All Computers in specific Collection
this doesn’t work
Hi, how to add OS version or build to the export. I order to know how many x models are with x OS version. And need to be upgraded.