SCCM Dashboard Queries 4: Machine Types in a collection

So sometimes your manager might ask you "how many laptops/desktops are there at site X?"

You probably have a collection setup for site X but how do you answer your managers question quickly and easily?

Better yet, let him answer that question on his own!

This is a view I use to show how many Notebooks/Desktops there are in a collection based on the enclosure chassistypes attribute.

Your mileage may vary but have a look and see what you think.

View: Machine Types for Collection X
Query:

DECLARE @CollectionViewName As nvarchar(100)
SET @CollectionViewName = (SELECT MemberClassName FROM v_Collection WHERE Name = @ViewName)
EXEC ('SELECT CASE ChassisTypes0 WHEN ''8'' THEN ''Notebooks'' WHEN ''9'' THEN ''Notebooks'' WHEN ''10'' THEN ''Notebooks'' ELSE ''Desktops'' END AS "Workstation Type", count(distinct sys.name0) as ClientCount from
v_GS_SYSTEM_ENCLOSURE ENC INNER JOIN (SELECT DISTINCT ResourceId FROM dbo.' + @CollectionViewName + ') v ON ENC.ResourceId = v.ResourceId INNER JOIN v_R_System SYS ON ENC.ResourceID = SYS.ResourceID WHERE enc.chassistypes0 <> ''12'' and sys.client0=1 AND sys.obsolete0=0 AND active0=1 GROUP BY CASE ChassisTypes0 WHEN ''8'' THEN ''Notebooks'' WHEN
''9'' THEN ''Notebooks'' WHEN ''10'' THEN ''Notebooks'' ELSE ''Desktops'' END ORDER BY 2 desc')
 
 

I filter out chassistype 12 as that is a 'docking station' so I dont want to see those. I use a pie chart to display the data but tweak it as you like.