Exclude a sub-OU from a Collection in SCCM

GeekyDee
GeekyDee used Ask the Experts™
on
Hey all!
Got a bit of an SCCM conundrum for the elite technorati here:

Is it possible to exclude an Active directory sub-OU (nested?) from a collectin in SCCM?  I have been trying to do it via the collection query (see below), but everything I try still has the sub-OU machines listed in the collection.  In contrast, if I run the reverse of the last part of the query by itself, I get he list of the machines I want excluded.  Any ideas?  Thanks bunches!
Diana
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 like "abc.xyz.com/alphaOU" 
or SMS_R_System.IPAddresses like "10.1.%" 
or SMS_R_System.IPAddresses like "10.2.%" 
or SMS_R_System.IPAddresses like "10.3.%" 
or SMS_R_System.IPAddresses like "10.15.%" 
and SMS_R_System.SystemOUName != "abc.xyz.com/alphaOU/betaOU/deltaOU\excludedOU"

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Awarded 2009
Top Expert 2010

Commented:
change this from a like to an = where SMS_R_System.SystemOUName like "abc.xyz.com/alphaOU"
Like is if that string is contained in the OUname and it will be as the other OU is a sub OU of this one.

Author

Commented:
Ok, tried that but still getting the same results.  Should I try to put parentheses around the or staments group?
Diana
The trick for SCCM collectin exclusions is a second select statement.
e.g.
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 like "abc.xyz.com/alphaOU"
or SMS_R_System.IPAddresses like "10.1.%"
or SMS_R_System.IPAddresses like "10.2.%"
or SMS_R_System.IPAddresses like "10.3.%"
or SMS_R_System.IPAddresses like "10.15.%"
and SMS_R_System.ResourceID Not In (Select ResourceID from SMS_R_System Where SystemOUName like  "abc.xyz.com/alphaOU/betaOU/deltaOU\excludedOU")
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

Commented:
Jon,
Thank you for the suggestion and sorry for the delay.  I was trying to do that last night and hopefully this points me in the right direction, although after trying it, I am getting getting a blank result.  If I run the subquery you have listed, Iit returns a list of the machines I want excluded, the way it is supposed to work. However, when I run the full query, it is blank; heck, when I run the last where statement by itself (without the or statements there in the way) against the current collection, it comes up blank.  I am beginning to think SCCM or SQL (WQL?) does not want to allow it to work.
Diana
Hi Diana,

I did some experimenting, and this works find for me, it includes all resources in the OU called Workstations, and excludes the ones in Workstations\Sub-OU. (BTW I note that your excluded OU in the query above as a backslash instead of a foward slash new the 'excludedOU')

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 like "D.LOCAL/WORKSTATIONS" And ResourceID Not IN (select ResourceID from  SMS_R_System where SMS_R_System.SystemOUName like "D.LOCAL/WORKSTATIONS/SUB-OU")

Author

Commented:
Jon,
That worked but still no joy with the inclusion of the IP limiters.  I am sure that can be gotten around just by adding more queries to the collection rules for each subnet and utilizing the last AND sub-query in the subnet query collection rule.  You are the best  :)
Diana

Author

Commented:
Jon,
Got it!  Thanks bunches for all the help hon, you deserve every point!  And in case anyone else needs it here is the finished product:
Diana
select sys.ResourceID,
sys.ResourceType,
sys.Name,
sys.SMSUniqueIdentifier,
sys.ResourceDomainORWorkgroup,
sys.Client
from SMS_R_System sys
where (sys.SystemOUName like "abc.xyz.com/alphaOU" And ResourceID Not IN (select ResourceID from  SMS_R_System where SMS_R_System.SystemOUName like "%ExcludedOU")) 
or (sys.IPAddresses like "10.1.%" And ResourceID Not IN (select ResourceID from  SMS_R_System where SMS_R_System.SystemOUName like "%ExcludedOU")) 
or (sys.IPAddresses like "10.2.%" And ResourceID Not IN (select ResourceID from  SMS_R_System where SMS_R_System.SystemOUName like "%ExcludedOU")) 
or (sys.IPAddresses like "10.3.%" And ResourceID Not IN (select ResourceID from  SMS_R_System where SMS_R_System.SystemOUName like "%ExcludedOU")) 
or (sys.IPAddresses like "10.10.%"And ResourceID Not IN (select ResourceID from  SMS_R_System where SMS_R_System.SystemOUName like "%ExcludedOU"))

Open in new window

Author

Commented:
While not complete, it showed a comprehensive knowledge of the issue at hand and wasan  essential component of the complete solution

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial