How To Find the Collection Membership Information of a specific Client Machine

How To Find the Collection Membership Information of a specific Client Machine

  • Comments 3
  • Likes

imageMany times during the course of troubleshooting you may come across a situation where you need to find out how many Collections a specific Client machine is a member of. Here is how you can find that information, along with the Collection Names and the ID's:

Run the following query in SQL against the SMS Database:

select v_FullCollectionMembership.CollectionID As 'Collection ID', v_Collection.Name As 'Collection Name', v_R_System.Name0 As 'Machine Name' from v_FullCollectionMembership
JOIN v_R_System on v_FullCollectionMembership.ResourceID = v_R_System.ResourceID
JOIN v_Collection on v_FullCollectionMembership.CollectionID = v_Collection.CollectionID
Where v_R_System.Name0='ClientMachineName'

Note: Replace ClientMachineName with the name of the Client Machine in question.

Additionally, you can also make a Custom Report to get this information if you intend to use this frequently:

The SQL Statement For this Report would be as follows:

select v_FullCollectionMembership.CollectionID As 'Collection ID', v_Collection.Name As 'Collection Name', v_R_System.Name0 As 'Machine Name' from v_FullCollectionMembership
JOIN v_R_System on v_FullCollectionMembership.ResourceID = v_R_System.ResourceID
JOIN v_Collection on v_FullCollectionMembership.CollectionID = v_Collection.CollectionID
Where
v_R_System.Name0=@Comp

Click on Prompts while providing the SQL Statement, and Create a new prompt named 'Comp' without the quotes. Provide a SQL Statement for the prompt as follows:

select Name0 from v_R_System

Note that this should work for both SMS 2003 and Configuration Manager 2007.

Vinay Pamnani | Configuration Manager Support Engineer

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • Using WQL you can use one of these two queries:

    1) displays all collection memberships (like the SQL syntax above)

    SELECT * from SMS_CollectionMember_a where ResourceID='system-resourceID'

    2) displays only membership in collections that do have an advertisement attached

    SELECT * from SMS_ClientAdvertisementStatus where ResourceID ='system-resourceID'

  • Wow! Really?! I just can't right-click the machine and get that from Properties? I have actually write a SQL query to do this? What a sorry, sorry management product.

  • Is there anyway I can do this on the actual client? Is there a VBS script or command line tool to do this from the workstation instead of running a query against the DB?