Query to find all hardware models – including Lenovo

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.

Leave a Reply

Up Next:

What .exe was blocked in Windows Firewall?

What .exe was blocked in Windows Firewall?