SCCM – SQL Report – Count all computers by model

SELECT        Model0 AS Model, COUNT(*) AS Count, Domain0
FROM            dbo.v_GS_COMPUTER_SYSTEM
GROUP BY Model0, Domain0

If there are Lenovo models in your organisation:

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