/build/static/layout/Breadcrumb_cap_w.png

collections membership

Hello,

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

Thank you.

0 Comments   [ + ] Show comments

Answers (3)

Posted by: Lucid 11 years ago
Purple Belt
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'
Posted by: Jsaylor 11 years ago
Second Degree Blue Belt
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

Posted by: august606 11 years ago
Senior Yellow Belt
0
Thank you very much for eveyrone's help!
Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ