Report provide count of computers from the collections.
Full Details: http://social.technet.microsoft.com/Forums/en-US/configmgradminconsole/thread/f0cccbd7-7616-4d06-a3a6-31d4c583e2f0
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 Comments
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?
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
END
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 '%@abc.com%'
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 ?
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 @xyz.com 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.
did you try this ? http://eskonr.com/2010/11/sccm-collection-to-list-all-the-laptop-computers-2/ .this post talks about collection but you can create SQL with this info.