Show Membership for a Collection

Version: SCCM 2012 SP1 CU2

This query will generate a list of members (device machines) for a given collection ID.

If you are running this in SQL Management Studio, either declare and set the @variable or you can put the collection ID directly in the where section. If you will use SSRS, then this will serve as a parameter for your report.

DECLARE @Variable nvarchar(15)
SET @Variable = 'CollectionID'

select CM.CollectionID, Coll.Name as 'CollName', CM.Name, CM.Domain from v_ClientCollectionMembers CM
join v_Collection Coll on Coll.CollectionID=CM.CollectionID
where CM.CollectionID=@variable
order by CM.name

Sample:

Show Membership for a Collection

Show Membership for a Collection