SCCM Report Count computers from collections


Report provide count of computers from the collections.

Full Details:

SELECT v_Collection.Name ,v_Collection.Comment,v_Collection.CollectionID,COUNT(*) as [Members] FROM v_Collection,v_FullCollectionMembership
where v_Collection.CollectionID=v_FullCollectionMembership.CollectionID
group by v_Collection.Name ,v_Collection.Comment,v_Collection.CollectionID
order by v_Collection.Name

5 Responses to "SCCM Report Count computers from collections"

  1. Hi! And sorry for be so late!

    When I use this query, in Excel, it reports me every collection with his members. This is right but i found that this does not show collections with 0 members. How can I fix it?

  2. Hi Eswar, Thanks for your reply,
    I am getting what i wanted from the query below. Now i want to set up prompt and that prompt must give me feasibility to select Laptops or Desktops.
    select distinct vr.User_Name0 as 'Username',vr.Mail0 as 'Email Id',vs.AD_Site_Name0 , vs.Netbios_Name0 ,
    case ch.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'
    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'
    else NULL
    from v_R_User vr
    join v_R_System vs on vr.User_Name0 = vs.User_Name0
    join v_GS_SYSTEM_ENCLOSURE ch on ch.ResourceID = vs.ResourceID
    where vr.Mail0 like ''

    1. to select laptops or desktops ? do you want to select collection (can be either of laptops/desktops) or computer names.
      can you re-read your question once ? prompt for desktops or laptops ?

  3. HI Mate,

    I am looking for report which will give me
    1. Count of laptop and Desktop Separately
    2. Report should contain data on basis of email id

    There are few regions which uses primary email address as and this users are using laptops or desktops
    I tried getting data from enclosure (dont remember the exact view name ) and email address from V_R_USER but results never worked. Can you please help.


Leave a Reply