Query: Identify ConfigMgr (SCCM) collections based on Active Directory OU membership

The question came up recently on how to identify which collections are based on Active Directory OU membership in Microsoft System Center Configuration Manager (SCCM) Current Branch

There are tons of guides on how to setup a collection based on OU membership.. but now that you have.. how many have you created?

This is quite simple direct SQL query:

SELECT [CollectionID]
      ,[RuleName]
      ,[QueryID]
      ,[QueryExpression]
      ,[LimitToCollectionID]
  FROM [SMS_ABC].[dbo].[v_CollectionRuleQuery]
  where QueryExpression like ‘%System_OU%’

Issues:

*  not all collection memberships need to be based on System OU.  Varitations will be needed based on requirements

* The above query is based on a view.  If you want to see the SQL you would query the table Collection_Rules_SQL

Useful Tip

The SQL table Collection_Rules_SQL also has the WQL versions of the same query.  This is extremely useful for figuring out the SQL tables involved with any WQL query from collections or the Query node.  You can also get this translation from the SMSProv.log file but that is like searching for a needle in a hay stack while the hack stack is getting bigger and may disappear in a roll over.

smsprovlog-wql-to-sql

A nice bit of documentation at Creating Custom Reports by Using SQL Server Views in System Center 2012 Configuration Manager

Otherwise, happy spelunking!

Advertisements

Author: shauncassells

Shaun Cassells, Senior Solutions Engineer, 1E Shaun is a Microsoft MVP Windows Insider MVP and executive at Central Texas Systems Management User Group (CTSMUG). Shaun is a frequent speaker at conferences like MMS, Gartner, TechEd Europe, TechEd North America, IT Dev Connections, System Center Rallies, and Systems Management User Groups (SMUGs). Shaun is an award winning blogger syndicated from www.shauncassells.com. Shaun specializes in analysis, optimization, and design of Tactical Solutions to Strategic Business Goals. Prior to joining 1E in 2010, he worked for a Global 100 company as the Configuration Manager Service Owner and Architect. Recently as a Principle Consultant at 1E, he accomplished design, review, and improvements to a variety of Configuration Manager environments from the very small to sites with a half million seats. He provided leadership that created a patented automated application rationalization and usage based OSD mapping solution in use by multiple fortune 500 companies. These days you can find Shaun traveling to help discover, review, and improve business productivity across the world.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s