Hello,

Could someone post a query to determine all collections that a workstation is a member of?

Thank you.
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
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'
Answered 08/26/2010 by: Lucid
Purple Belt

Please log in to comment
0
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

Answered 08/27/2010 by: Jsaylor
Second Degree Blue Belt

Please log in to comment
0
Thank you very much for eveyrone's help!
Answered 08/30/2010 by: august606
Senior Yellow Belt

Please log in to comment
Answer this question or Comment on this question for clarity