SCCM 2007 OU exclusion

Exclude a sub-OU from a Collection in SCCM

If we want to make a collection based on a Parent OU and want to exclude one or more Sub-OUs, it is apparently not possible with the criteria selection. It only works well for getting all computers in the parent OU. But if you add another criteria and say System OU Name not like or not equal to the child OU, it does not work for the exclusion. You have to absolutely use a subquery in order to exclude computers from the sub OU.

sccm-collection-exclude-sub-ou

After adding the simple criteria of Parent OU you can edit the query and add the sub query which I got from the link here:

http://www.experts-exchange.com/OS/Microsoft_Operating_Systems/Server/Systems_Management_Server/Q_24647910.html

In my case, OU3 is the parent OU and the query goes like this :

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name,
SMS_R_SYSTEM.SMSUniqueIdentifier,
SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.SystemOUName = “DOMAIN.LOCAL/OU1/OU2/OU3″ and SMS_R_System.ResourceID Not In (Select ResourceID from SMS_R_System Where SystemOUName like  “DOMAIN.LOCAL/OU1/OU2/OU3/SUB-OU-1-TO-EXCLUDE” or SystemOUName like “DOMAIN.LOCAL/OU1/OU2/OU3/SUB-OU-2-TO-EXCLUDE”)

Exclude Active Directory OU’s from a Collection in System Center Configuration Manager 2007

Source: Http://Blogs.Catapultsystems.com

Create a “all computers” collection for software updates and exclude various OU’s that have computers not allowed to be updated for various reasons. Please modify the RED areas with the correct information. The great part is, you don’t have to spell out the full OU name to be excluded.

Script

  • select sys.ResourceId, sys.ResourceType, sys.Name, sys.SMSUniqueIdentifier, sys.ResourceDomainORWorkgroup, sys.Client
    from SMS_R_System as sys where sys.SystemOUName like “TEST.COM/COMPUTERACCOUNTS
    and sys.ResourceId not in (select ResourceID from  SMS_R_System where SMS_R_System.SystemOUName like “%OuNameToNotInclude“)
    and sys.ResourceId not in (select ResourceID from  SMS_R_System where SMS_R_System.SystemOUName like “%OuNameToNotInclude“)
    and sys.ResourceId not in (select ResourceID from  SMS_R_System where SMS_R_System.SystemOUName like “%OuNameToNotInclude“)
Advertisements

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