A bit of code from MyITForum.com with a report that lists all collections for a specific machine.
http://www.myitforum.com/forums/All_Collections_for_a_specific_computer/m_163312/tm.htm
select FCM.SiteCode as 'SMS Site',
FCM.CollectionID,
COL.Name as 'Collection'
from v_R_System SYS
join v_FullCollectionMembership FCM on FCM.ResourceID = SYS.ResourceID
join v_Collection COL on COL.CollectionID = FCM.CollectionID
where SYS.Name0 = @Computer
Prompt Query:
begin
if (@__filterwildcard = '')
SELECT DISTINCT SYS.Netbios_Name0 from v_R_System SYS WHERE SYS.Client0=1 ORDER By SYS.Netbios_Name0
else
SELECT DISTINCT SYS.Netbios_Name0 from v_R_System SYS WHERE SYS.Client0=1
and SYS.Netbios_Name0 like @__filterwildcard
ORDER By SYS.Netbios_Name0
end
