Config Manager Contusions: Finding Advertisements Pointed at Active Directory OUs

If you have a large "enterprise" environment, which has System Center Configuration Manager in it, and it's been in place for a long time, you could have a rather wide sprawling infrastructure, replete with Collections that point to things you completely forgot about.  Case in point: Collections based on Query rules which point at AD Organizational Units (OU).

So, when one of your engineers asks the respectable question: "Do we push any apps at computers in OU ", you're left scratching your head about how to compile a layer of potential Group Policy software installations with another layer of potential Configuration Manager Advertisements.  Say hello to one more Rubik's cube of the IT world.

Thankfully, Microsoft spent just enough time on their SQL back-end for Config Manager to make a lot of chores surprisingly simple with just a few minutes poking around in SQL Management Studio.

Here's one example to demonstrate how to identify which Collections are built on query-rules that point at AD OUs, and (AND) which have Advertisements pointed at them...

[T-SQL Query]

SELECT 
    v_Collection.Name, 
    v_Advertisement.AdvertisementID, 
    v_Advertisement.AdvertisementName, 
    v_CollectionRuleQuery.QueryExpression
FROM v_CollectionRuleQuery INNER JOIN
    v_Collection ON 
    v_CollectionRuleQuery.CollectionID = v_Collection.CollectionID 
INNER JOIN dbo.v_Advertisement ON 
    v_Collection.CollectionID = v_Advertisement.CollectionID
WHERE (v_CollectionRuleQuery.QueryExpression LIKE '%SYSTEM_OU_NAME%')
ORDER BY AdvertisementName

[/T-SQL Query]

Once you start scratching the SQL surface, it's hard to stop.