Find Direct Membership Collections

Direct Membership rules are frequently a topic for debate due to performance, and other challenges. However, if you work in an environment where you share Configuration Manager with several other admins you might not know just how many collections you have with direct memberships, or where they are.

SQL Query

SQL, something most configuration admins are uncomfortable with for some reason or another. In this particular case is probably the fastest and most direct method of acquiring the information you want. If all you want is to know what collections have direct membership rules you can start just by selecting:

SELECT * FROM V_CollectionRuleDirect

This will get you all of the direct membership collection rules in an environment.

A couple things to note here, we are provided the CollectionID, where the rule is attached, the NAME of the rule, the RESOURCEID indicated by the rule, and the resource type. The rule name, and resource type are worth paying special attention to for a few reasons.

Rule Names

When a direct membership is created using a DEVICE – or resource type 5 more on that later – the devices NAME is used as the rule name, and it’s resourceID is whats used in the queries actual language. This is part of why direct memberships are so contentious. As when a machine is re-imaged it may have a different resource ID, causing the rule to lose all purpose and meaning.

ResourceType

In the example above I have a USER based collection called “App-Minecraft” that I am using to deploy Minecraft to users who are a member of a group. It’s important to note GROUPS show up as direct memberships as well and do NOT suffer the same complications as direct membership device rules.

If you’re wondering how I know that resource type 3 is user group, and 5 is system you can use the below query to find out the mapping of ResourceType to its display Name, and its class name view in the database.

SELECT * FROM v_ResourceMap

Stop talking give me the query

With those little bits of information in hand we can easily create a very simple query using the COUNT, GROUP BY, and WHERE to find and sort our collections with direct memberships that are dictated by DEVICES.

SELECT V_CollectionRuleDirect.CollectionID as 'CollectionID'
	, COUNT (V_CollectionRuleDirect.CollectionID) AS 'TOTAL'
 FROM v_CollectionRuleDirect
WHERE v_CollectionRuleDirect.ResourceType = '5'
GROUP BY v_CollectionRuleDirect.CollectionID
ORDER BY TOTAL DESC

However, with a little more nudging around we can add in the collections name to make it easier to find.

SELECT V_CollectionRuleDirect.CollectionID as 'CollectionID'
	, V_Collections.CollectionName as 'Collection Name'
	, COUNT (V_CollectionRuleDirect.CollectionID) AS 'TOTAL'
 FROM v_CollectionRuleDirect
LEFT OUTER JOIN v_Collections on V_Collections.SiteID = v_CollectionRuleDirect.CollectionID
WHERE v_CollectionRuleDirect.ResourceType = '5'
GROUP BY v_CollectionRuleDirect.CollectionID , V_Collections.CollectionName
ORDER BY TOTAL DESC