/bundles/itninjaweb/img/Breadcrumb_cap_w.png
Hello,

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

Thank you.
0 Comments   [ - ] Hide 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.
Answer this question or Comment on this question for clarity

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