Systems Management

SELECT
v_R_System.Netbios_Name0,
v_FullCollectionMembership.CollectionID
FROM
v_R_System
JOIN v_FullCollectionMembership on v_R_System.ResourceID = v_FullCollectionMembership.ResourceID
WHERE
v_R_System.Netbios_Name0 = 'XXXXXXXXXXX'

I can't believe MS didn't bother to include something as useful as a report that will display all collections a computer is a part of. I'm also surprised I haven't ever run into a situation where I'd want this kind of data, so I went ahead and wrote a report to hand out the data you're looking for. I attached a mof (rename the .txt to .mof, then import it into your reports in SCCM/SMS) for convenience's sake, but here's how the SQL looks:

SELECT COL.Name as "Collection name", FCM.CollectionID as "Collection ID", SYS.Netbios_Name0 as "Computer Name"
FROM v_R_System SYS
JOIN v_FullCollectionMembership FCM on SYS.ResourceID = FCM.ResourceID
LEFT JOIN v_Collection COL on FCM.CollectionID=COL.CollectionID
WHERE
SYS.Netbios_Name0 = @computername

It turned out that v_FullCollectionMembership doesn't contain an entry for the collection's friendly name, just the collection ID, thus the side-trip to v_collection to grab the friendly collection name, so you aren't stuck tracking down real collection names.

@computername is a prompt with the following sql behind it:

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

Standard input filter stuff here, you can see examples like it littered throughout the default reports.

Attachment

Thank you very much for eveyrone's help!