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.
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 using include rule.
In this post, I will be sharing a Configuration Manager SQL query which will provide you the details for the above statement. I have two collections as you can see below.
- HTMD Collection
- Missing Security Updates Patches
The Missing Security Updates Patches collection referenced HTMD collection using Include rule. With the following SCCM custom report, you will be able to find out the list collections that referenced one particular collection.
This SQL Query will help you in using ‘When’ and ‘then‘ syntax.
- 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.
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 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.
|Collection Name||Rule Type||Referenced Collection ID||Referenced Collection Name|
|Missing Secuirty Updates Patches||Include||MEM00014||HTMD Collection|