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

4 comments:

  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

    ReplyDelete