Exclude a sub-OU from a Collection in SCCM
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
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"
ASKER
Ok, tried that but still getting the same results. Should I try to put parentheses around the or staments group?
Diana
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.SMSUniqueIden
SMS_R_SYSTEM.ResourceDomai
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/betaO
e.g.
select SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name,
SMS_R_SYSTEM.SMSUniqueIden
SMS_R_SYSTEM.ResourceDomai
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/betaO
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
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
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"))
ASKER
While not complete, it showed a comprehensive knowledge of the issue at hand and wasan essential component of the complete solution
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.