I needed to find all the hardware models in our environment. Lenovo stores their model name in ‘version’ instead of ‘model’ like other vendors. I ran the following SQL code on our SCCM/MECM database to get the data I needed. This code can be converted to a report in SSRS or used in PowerShell.
Note: Change the collectionID
with CTE AS
(
SELECT GCS.Manufacturer0
,CASE WHEN GCS.Manufacturer0 = 'LENOVO' THEN CSP.Version00 ELSE GCS.Model0 END AS 'Model'
FROM v_GS_COMPUTER_SYSTEM GCS
JOIN COMPUTER_SYSTEM_PRODUCT_DATA CSP on GCS.ResourceID = CSP.MachineID
JOIN fn_rbac_FullCollectionMembership('Disabled') fcm ON GCS.ResourceID=fcm.ResourceID
WHERE fcm.CollectionID='<collectionID>'
)
select Manufacturer0,model,count(model) as count from cte group by Manufacturer0,model
order by count DESC
If you want to run this in PowerShell be sure to use the Invoke-Sqlcmd2 module.