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