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.