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

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

    ReplyDelete
  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

    ReplyDelete

  4. Best Carpet Cleaner: We are professional to deal
    with Best Carpet Cleaner of all ranges. we are dealing with top-rated quality brands of cleaner like Hoover, Doctor Rug, Shark, Bissell. Mobile Packages

    ReplyDelete
  5. if you want to learn SEO and have no idea about
    Backlinks,
    on page seo and
    off page seo then please keep connect withus for
    complete guideline.

    ReplyDelete
  6. Great blog you have there! You offer very useful information, thanks!
    Best Laptops For AutoCAD

    ReplyDelete
  7. Best Carpet Cleaner provide the best handheld carpet shampooer along with useful carpet cleaner on tile floors.
    carpet clenaer
    carpet shampooer

    ReplyDelete