Computer Make / Model with Lenovo real models

After looking at a couple of reports / SQL queries for Lenovo models, I realized that they have some drawbacks.

Lenovo stores its model in a different class, called Win32_ComputerSystemProduct and the name for the field is "Version". So, for SCCM, we need to enable it in Inventory first, but we're not going to get all inventory immediately, but wait a minute, we actually need just _one_ computer with that model to report to see all of them. And here is what I came up with. First query mimics v_gs_ComputerSystem, second is a report with CollectionID filter.

 

 SELECT ResourceID, Manufacturer0,
 (
 CASE 
 WHEN CSP.Model0 IS NULL THEN CS.Model0
 WHEN CS.Manufacturer0 IN ('LENOVO','IBM') THEN CSP.Model0
 ELSE CS.Model0
 END
 ) AS Model0
 FROM v_GS_COMPUTER_SYSTEM CS LEFT OUTER JOIN
 (
 SELECT Name0 as LenovoModel, Version0 as Model0 FROM v_GS_COMPUTER_SYSTEM_PRODUCT
 group by Name0, Version0
 ) CSP ON CS.Model0 = CSP.LenovoModel

 
 SELECT Manufacturer0, Model0, COUNT(1) As [Count] FROM 
 
 ( SELECT ResourceID, Manufacturer0,
 (
 CASE 
 WHEN CSP.Model0 IS NULL THEN CS.Model0
 WHEN CS.Manufacturer0 IN ('LENOVO','IBM') THEN CSP.Model0
 ELSE CS.Model0
 END
 ) AS Model0
 FROM v_GS_COMPUTER_SYSTEM CS LEFT OUTER JOIN
 (
 SELECT Name0 as LenovoModel, Version0 as Model0 FROM v_GS_COMPUTER_SYSTEM_PRODUCT
 group by Name0, Version0
 ) CSP ON CS.Model0 = CSP.LenovoModel
 ) SmartModel INNER JOIN v_FullCollectionMembership FCM ON SmartModel.ResourceID = FCM.ResourceID
 
 WHERE FCM.CollectionID = @CollectionID
 
 GROUP BY Manufacturer0, Model0
 ORDER BY COUNT(1) Desc