Questions about how to use subselect queries seem to come up a lot, and I can never find a good example to use when I go looking for a subselect query. I found this example from Curtis Sawin (a senior Microsoft consultant) today for creating a collection of computers that did not have Project 2003 installed and I figured that I would post it to my blog so that the next time I needed an example of this I wouldn't have to go looking through my bag of tricks (aka collection of snippets spread around about four different computers!).
So, the next time I need a good example of a subselect query I'll just come check this post out...you're welcome to do the same.
From Curtis:
"The reason you need to use a subselected values query is that computers have several entries in ARP, thus all computers have at least one entry in ARP that doesn't equal "Project 2003." Thus, you need to look for resources that have Project, and then look for computers that don't fall in to that query."
And:
"Checking for computers that are not in the results of another query will also return discovered computers that are not ConfigMgr clients (and thus have no ARP data). The way to work around this is to include the query "client = 1" to ensure you're only getting ConfigMgr clients."
And the resulting subselect query:
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.ResourceId not in (select SMS_R_System.ResourceId from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "<Software Title>") and SMS_R_System.Client = 1
NOTE: I've modified the query a little to make it a generic software title subselect query so you'll need to change the <Software Title> text between the quotation marks to the display name for the software title you're after—don't forget to get rid of the <>'s.
Here are some screen shots of what you should be seeing in your collection properties now:
Subselect query criteria screen shot
Subselect query WQL statement screen shot (yours shouldn't say <Software Title>)
Here's the handy C&P method of creating a collection based on a subselect query:
- Right-click Collections
- Click New Collection from the context menu
- Give the collection a name and a comment if you want to
- On the Membership Rules page of the New Collection Wizard, click the database query button
- Give the query a name
- Click Edit Query Statement...
- Click Show Query Language
- C&P the subselect query above to overwrite the default select * from SMS_R_System (make sure you change <Software Title> to a real software title!)
- Click OK
- Click OK
- Finish the wizard
Viola! A new collection based on a subselect query! Update the collection membership and hopefully you see what you're looking for!
Afraid you'll lose this little subselect query? Here's an easy way to keep it handy when you need it. Navigate down the console to Queries under Computer management and create a new query using the WQL I posted earlier (leave <Software Title> in it) and name it something inventive like "Subselect Query By Software Title".
Now, the next time you want to create a collection of computers without a software title installed, you can click Import Query Statement (as opposed to Edit Query Statement… above in step 6) and select your pre-made subselect query. Modify the collection's query statement as necessary and you're off with just a few clicks. Your query down at the bottom isn't changed when you modify the query statement for the collection and so you can re-use the snippet as often as your heart desires.