SCCM Report: Count of laptops desktops at each site
Well this one was a quite good exercise. Got a request from manager to list how many laptops, desktops are there at each site. Site here is country or city
This report is not out of box in SCCM 2007 R2 config manager console.
First place to start is to look at the SCCM Schema from MS site
but that will take some time to study and understand the tables, views and how they’re connected. I would suggest running this query on a copy of SCCM database (reporting db) and making sure it works for you before running it on Production.
In the next post I'll document you how to configure this as a report in SCCM and use subscription to schedule this report. Here is the SQL Query.
,count (CASE dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0
WHEN 8 THEN 'Laptops'
WHEN 9 THEN 'Laptops'
WHEN 10 THEN 'Laptops'
WHEN 11 THEN 'Laptops'
WHEN 12 THEN 'Laptops'
WHEN 14 THEN 'Laptops'
WHEN 18 THEN 'Laptops'
WHEN 21 THEN 'Laptops'
end ) as 'Laptops',
count (CASE dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0
WHEN 3 THEN 'Desktops'
WHEN 4 THEN 'Desktops'
WHEN 5 THEN 'Desktops'
WHEN 6 THEN 'Desktops'
WHEN 7 THEN 'Desktops'
WHEN 15 THEN 'Desktops'
WHEN 16 THEN 'Desktops'
end ) as Desktops
FROM dbo.v_GS_SYSTEM_ENCLOSURE INNER JOIN
dbo.v_R_System ON dbo.v_GS_SYSTEM_ENCLOSURE.ResourceID = dbo.v_R_System.ResourceID INNER JOIN
dbo.v_GS_SYSTEM ON dbo.v_R_System.ResourceID = dbo.v_GS_SYSTEM.ResourceID INNER JOIN
dbo.v_GS_COMPUTER_SYSTEM ON dbo.v_GS_SYSTEM.ResourceID = dbo.v_GS_COMPUTER_SYSTEM.ResourceID
group by dbo.v_R_System.AD_Site_Name0