SCCM Report Include Membership Collection Rule | ConfigMgr Query

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

Introduction

In my previous post on how to populate machines in a collection using system description and then including it in different collections for bulk deployment or sometimes there is a requirement to find out that an individual collection is a part of how many other collections use including rule.

In this post, I will be sharing a Configuration Manager SQL query which will provide you with the details for the above statement. I have two collections as you can see below.

  • HTMD Collection
  • Missing Security Updates Patches
SCCM Report Include Membership collection Rule | ConfigMgr Query
Include Membership collection Rule – SCCM Report Include Membership collection Rule | ConfigMgr Query

The Missing Security Updates Patches collection referenced HTMD collection using the Include rule. With the following SCCM custom report, you will be able to find out the list of collections that referenced one particular collection.

Patch My PC
Include Membership collection Rule
Include Membership collection Rule

SQL Query

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

  • 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 report of application deployment with collection details.
  • Click on the Execute button.
SCCM Report Include Membership collection Rule | ConfigMgr Query
SCCM Report Include Membership collection Rule | ConfigMgr Query
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 the result to find out the collection is part of how many other application collections.

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

In this scenario, I wanted 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.

Adaptiva
SCCM Report Include Membership collection Rule | ConfigMgr Query
SCCM Report Include Membership collection Rule | ConfigMgr Query
Collection NameRule TypeReferenced Collection IDReferenced Collection Name
Missing Security Updates PatchesIncludeMEM00014HTMD Collection
SCCM Report Include Membership collection Rule | ConfigMgr Query

References

Leave a Comment

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