SCCM Report: Count of laptops desktops at each site

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 

http://www.microsoft.com/downloads/en/details.aspx?FamilyId=87BBE64E-5439-4FC8-BECC-DEB372A40F4A&displaylang=en

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.

SELECT                      dbo.v_R_System.AD_Site_Name0 

  

as SiteNameAD,                 

                      ,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