Query-Based Collections: Why does it seem easier to include resources than exclude?
I'm hoping someone can explain this to me and why I'm having such difficulties. Our Active Directory setup has an OU where all computers are placed, but it's divided up into Sub-OU's for each of our 12 office locations (call them Location A, Location B, Location C, etc.), and those Sub-OU's are divided into a Desktop OU, Laptop OU, and Virtual Machine OU. I'm trying to deploy a package to all machines on our network that are Windows 7 Enterprise or Windows 7 Professional, but not in Office Sub-OU "Location B". I create a new collection, and make it query based, using a query that reads very similar to how I wrote the query in English. When I look at the collection after building it, I see machines in there that I know are in Location B. I refresh, update the collection over and over, but those computers stay in the collection when they should not be. Is there something wrong in my logic? The other odd thing is that I end up with more machines in this collection than I do in a collection of just Windows 7 Enterprise and Professional machines. At the same time, if I create a collection of machines that are Windows 7 Enterprise or Windows 7 Professional, AND in Location A and Location C, I get the results that I expect. So, it seems to get the results I'm wanting, I'll have to make a query to include those two operating system editions and 11 of 12 office locations, instead of the simpler query to include the two operating system editions and exclude 1 office location. What gives? Brett
July 17th, 2012 5:36pm
If want to exclude something you need to use subselect queries, here is an example how to use sub-select: http://www.myitforum.com/articles/1/view.asp?id=179
July 17th, 2012 5:52pm
use your favorite search engine and look for Configmgr Subselect Queries. Whenever you want to "exclude" something, almost 95% or more of the time you'll want to use a subselect, where part of the "where" clause ends up being this type of logic: Where resourceid not in ( Select Resourceid from sms_r_system where OU like 'mydomain.com/all computers/Location B%' ) If you are NOT using a subselect... then yes, you will need to explicitly say Location A% or Location c%, and not the root "all computers" only. you also said something about your logic and getting more machines than all win7... you didn't post your query, but I'll bet you have an OR in your where clause; and don't have ( ) around the right where clause stuff, so that somehow you end up with "or ou like 'whatever' ", regardless of OS. Also, BIG tip here: use that "Limit to a Collection" when you create your collection query for the OU stuff, to limit to just your already-existing Windows 7 Collection (that you said you have, so I know you have one). Standardize. Simplify. Automate.
July 17th, 2012 5:56pm
OK, I have used that technique before when doing a collection based on computers missing a piece of software. If this is the proper way to exclude resources, why does Microsoft even have the "is not in" or "is not like" or "is not" as options if they don't work?
July 17th, 2012 5:57pm
lol, because those are still valid for situations when there are NOT multiple "rows" of data for something. For example, if you are looking for "all servers which are not domain controllers", since you will only get one returned value for computersystem.domainrole, you can legitimately use "where domainrole is not in ("0, 1, 4, 5"), and you know you'll only get servers (2 or 3), or, of course, machines that haven't submitted hardware inventory at all yet. (so it's still not ideal... I'm just telling you why it's still a valid wql query option). OU is multiple rows of data; so is the contents of add/remove programs. But there are lots of situations where there is only 1 instance (or null) for a data point. You just happen to have (apparently) the most experience with multi-row situations, like ARP and OU.Standardize. Simplify. Automate.
July 17th, 2012 7:25pm
Microsoft does not have "is not in" or "is not like" or "is not". These are all standard constructs and operators from the SQL language (which WQL is a subset of) and has nothing to do with Microsoft. SQL is the industry standard database query language. SQL is a set based language that defines a set of rules of what to include and thus it is easier to define inclusions than exclusions but using subselects is not really a difficult thing to do.Jason | http://blog.configmgrftw.com | Twitter @JasonSandys
July 17th, 2012 7:25pm
try this with subselected quiries http://eskonr.com/2011/12/sccm-collection-sub-selected-quiries/Please click on "vote as Helpful" if you feel this post helpful to you. Eswar Koneti | My Tech blog: www.eskonr.com | Linkedin: Eswar Koneti
July 17th, 2012 11:37pm