What Collections is your Client a Member of?

  • Article

Many 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 SQL query in 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'

You will need to replace ClientMachineName with the name of the Client Machine in question.

Here is the output of SQL when I ran the query for one of my clients:

image

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

Comments

  • Anonymous
    January 01, 2003
    I'm glad to see the information on creating the report with a prompt.  That was confusing the first report or two I made.

  • Anonymous
    January 01, 2003
    I'm glad to see the information on creating the report with a prompt.  That was confusing the first report or two I made.