Friday, 27 April 2012

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 

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 
                      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


  1. I believe this is one of the such a lot vital information for me.
    And i am happy studying your article. However should remark on few basic
    things, The web site taste is great, the articles is actually excellent
    : D. Excellent job, cheers

    Feel free to visit my web-site ... sims 4 free download

  2. Wow, excellent post. I'd like to draft like this too - taking time and real hard work to make a great article. This post has encouraged me to write some posts that I am going to write soon. MSI Gaming Laptops

  3. Without delay this web site is likely to certainly regularly get outstanding pertaining to every bit of webpage customers, given that painstaking information and likewise medical tests. laptopsgeek