SCCM Report – Count All Computers by Model

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:

sccm report Count Computer by Model

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

2 thoughts on “SCCM Report – Count All Computers by Model”

  1. 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.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.