Show Collection Membership for a Computer

This will generate a membership list of all collections for a given computer. If you are running this in SQL Management Studio, either declare and set the @variable or you can put the computer name 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 = 'computer_name'

select distinct sys.netbios_name0 as 'Computer Name', v_collection.name as 'Collection Name', v_collection.collectionid as 'Collection ID' from v_fullcollectionmembership join v_collection on v_collection.collectionid = v_FullCollectionMembership.collectionid join v_r_system sys on sys.resourceid = v_fullcollectionmembership.resourceid 
where sys.netbios_name0 like @Variable 
order by v_collection.name

This will display computer name, collection name, and collection ID.

Sample:

Show Collection Membership for a Computer

Show Collection Membership for a Computer