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