SCCM Report Include Membership Collection Rule | ConfigMgr Query

0
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 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
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 Include rule. With the following SCCM custom report, you will be able to find out the list collections that referenced one particular collection.

Include Membership collection Rule
Include Membership collection Rule

SQL Query

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.
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 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
SCCM Report Include Membership collection Rule | ConfigMgr Query
Collection NameRule TypeReferenced Collection IDReferenced Collection Name
Missing Secuirty Updates PatchesIncludeMEM00014HTMD Collection

References

LEAVE A REPLY

Please enter your comment!
Please enter your name here

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