Jaa

Sub-Select Query - The Holy Grail of SMS Collections

  • Artikkeli

Learned a cool trick this week for building SMS/SCCM collections that will return a list of computers that "do not have something".  Building a collection that returns a group computers with a particular file or program on them is easy enough.  You create a query that returns the systems you want and then import the query when you are defining the collection. 

How do you build a collection of systems that do not have have something.  If you build a query that says "give me every computer that does not have Adobe Reader installed" the query will return every computer listed in your database.  This will occur because every system will have at least one entry (actually dozens) in Add Remove Programs that does not match "Adobe Acrobat".  Therefore every system evaluates as true and is selected as part of your query. 

What you really need is a query that says "I need every computer that is not in the 'Computers with Adobe Reader' collection" .  What you need is a Sub-Select query.  Lets take an example:

My customer needed to deploy Microsoft Office Infopath 2003 on systems that did not have the product installed.  The first thing we did was create a query that showed us all the systems that had InfoPath installed.  Easy enough:

select SMS_R_System.NetbiosName from  SMS_R_System inner join SMS_G_System_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SoftwareFile.FileName = "infopath.exe" and SMS_G_System_SoftwareFile.FileVersion like "12.0.62%"

When we defined the query and named it "Computers with InfoPath".  We only had it return the computer name (SMS_R_System.NetbiosName ) and the Criteria was the file name and the version.  Click apply to save you changes then click Edit Query Statement > Show Query Language and copy the code out and paste it into Notepad for now.

Next step is to create a second query and name it "Computers without InfoPath.  Again we only selected the computer name for display.  For Criteria we do something different.  For Criterion Type field, select "SubSelected Values", for the Operator field, select "is not in" and for the Subselect field, paste the code pasted into Notepad in the step above.  Click OK twice and click apply to save you changes.  Now click Edit Query Statement > Show Query Language and you should have a query that looks like this:

select SMS_G_System_SYSTEM.Name from  SMS_R_System inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SYSTEM.Name not in (Select SMS_G_System_SYSTEM.Name From SMS_R_System Inner join SMS_G_System_SYSTEM On SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId Inner join SMS_G_System_SoftwareFile On SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId Where SMS_G_System_SoftwareFile.FileName = "infopath.Exe" And SMS_G_System_SoftwareFile.FileVersion LIKE "12%") and SMS_R_System.Client = 1

Notice the code in blue above is from our original query of "Computers with InfoPath" and the new query says "return every computer who's name is not in the following query".

Comments

  • Anonymous
    October 10, 2008
    PingBack from http://www.simplynetdev.com/sub-select-query-the-holy-grail-of-sms-collections/

  • Anonymous
    December 04, 2011
    This is AWESOME! I got asked to do this exact thing today and was struggling with the query language. It worked perfectly I can't thank you enough :)

  • Anonymous
    October 19, 2016
    Thanks thanks thanks, helped a lot ! :)

  • Anonymous
    April 09, 2017
    The comment has been removed