SCCM Report Include Membership Collection Rule | ConfigMgr Query

Let’s find the ConfigMgr Include Membership collection Rule using Custom Report, SQL query, and report builder. We have many other SQL queries and custom reports we shared with the HTMD Forum community.

An Include Membership Rule adds members from another collection to the device collection where the rule is applied. Membership rules cannot be added to default collections. Your target collection must have an ID starting with the site code, not SMS. It is possible to include a default collection, in which case the ID of the included collection can start with SMS.

In my previous post, I discussed how to populate machines in a collection using system descriptions and then including them in different collections for bulk deployment. Sometimes, it is required to determine that an individual collection is part of how many other collections use rules.

Index
Include Membership Collection Rule
SQL Query
Result of SQL Query
SCCM Report Include Membership Collection Rule | ConfigMgr Query – Table 1

Include Membership Collection Rule

In this post, I will share a Configuration Manager SQL query that provides you with the details of the above statement. As you can see below, I have two collections.

Patch My PC
  • HTMD Collection
  • Missing Security Updates Patches
SCCM Report Include Membership Collection Rule | ConfigMgr Query - Fig.2
SCCM Report Include Membership Collection Rule | ConfigMgr Query – Fig.1

The Missing Security Updates Patches collection referenced the HTMD collection using the Include rule. The following SCCM custom report lists the collections that reference one particular collection.

SCCM Report Include Membership Collection Rule | ConfigMgr Query - Fig.2
SCCM Report Include Membership Collection Rule | ConfigMgr Query – Fig.2

SQL Query

This SQL Query will help you use ‘When‘ and ‘then‘ syntax. You must have the appropriate access to launch the SQL management studio.

Adaptiva
  • Open the SQL Management Studio.
  • Connect your Database Engine.
  • Right-click on your database CM_XXX and click on ‘New Query.’
  • Copy the following SQL query to find the application deployment report with collection details.
  • Click on the Execute button.
SCCM Report Include Membership Collection Rule | ConfigMgr Query - Fig.3
SCCM Report Include Membership Collection Rule | ConfigMgr Query – Fig.3
SELECT COL.Name AS 'Collection Name'
,CASE (CR.RuleType)
WHEN '3' THEN 'Include'
WHEN '4' THEN 'Exclude'
WHEN '2' THEN 'Query'
WHEN '1' THEN 'Direct'
END AS 'Rule Type'
,CR.ReferencedCollectionID AS 'Referenced Collection ID'
,CR.QueryName AS 'Referenced Collection Name'
FROM v_Collection COL
JOIN Collection_Rules CR ON COL.CollID = CR.CollectionID
WHERE CR.RuleType IN ('3') and QueryName='HTMD Collection'

Result of SQL Query

The above SQL Query provides us with the result to determine whether the collection is part of how many other application collections.

  • In the QueryName, provide the collection name for which you want the result.

In this scenario, I tried to find out if the ‘HTMD Collection‘ collection is part of how many other collections.

NOTE! – You can also use ‘ReferenceCollectionID‘ instead of ‘Queryname‘ and provide the Collection ID.

SCCM Report Include Membership Collection Rule | ConfigMgr Query - Fig.4
SCCM Report Include Membership Collection Rule | ConfigMgr Query – Fig.4
Collection NameRule TypeReferenced Collection IDReferenced Collection Name
Missing Security Updates PatchesIncludeMEM00014HTMD Collection
SCCM Report Include Membership Collection Rule | ConfigMgr Query – Table 2

References

We are on WhatsApp. To get the latest step-by-step guides and news updates, Join our Channel. Click here –HTMD WhatsApp.

Author

Ankit Shukla, an IT professional with over seven years of experience, has worked on SCCM/MECM since 2012. During this time, he has extensively dealt with various aspects of the tool, such as migration, infrastructure designing, OSD, custom SQL reporting, and client-side troubleshooting. Ankit’s blog posts reflect his hands-on experience addressing issues and providing practical solutions. The primary aim of his posts is to cater to the audience, comprised of support team members and SCCM admins in organizations.

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.