The Realm of the Verbal Processor

Jarvis's Ramblings

Subselect Query in SCCM

Update (1/30/2010):  We recently discussed subselect queries at the Minnesota System Center User Group. That discussion prompted me to write an updated post on subselect queries.

Please refer to the following post for better information on how to create subselect queries that are faster and more efficient.

https://verbalprocessor.com/2010/01/30/better-subselect-queries/

Original Post:

One of the things that I have fought with in SMS/SCCM is a “NOT” query. Example is needing a query that shows me all computers that do not have Office 2007 installed. If you create your query to look in Add/Remove Programs and find a “Display Name” that is not “Microsoft Office Professional Plus 2007”, you will not get what you might expect. The reason is that it finds a computer that has something like Adobe Acrobat installed. Well…that is a display name in Add/Remove Programs that isn’t Office 2007…so that computer gets returned by the query.

To get the expected results you have to run one query that selects all computers that DO have Office 2007 (or whatever other program you are interested in…for that matter it doesn’t have to be a program…could be anything you want to query on), then you run another query that gives you all of the computers that are NOT in the first query. This is called a subselect query. This query ends up looking like the following:

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_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceId = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = “Microsoft Office Professional Plus 2007”)

I am not a database guy. I am not a really proficient query writer. Subselect queries confuse the heck out of me. I have fought trying to write subselect queries for a couple of years now. I have really struggled with them.

Today while setting up something in SCCM, I needed a subselect query. I need to find computers that are a member of a particular AD OU, and if they don’t already have certain programs, I want the programs to automatically install. That requires a subselect query. They confuse me. I went to myITforum.com to find an example that I could then modify.

In my searching, I came across this thread. In that thread, someone linked to an article written by Rod Trent about how to create subselect queries. WOW is that a good article! What makes me feel really stupid though is that there has been a way since SMS 2003 to create a subselect query by pointing and clicking…I had no idea and wish I had known before today. It made me really happy to find it and see it work. I actually got out of my chair and started dancing in the office. The people around me looked at me funny, but honestly I was happy enough that I didn’t care!

And for those of you who know me…go ahead and try to get that image of me doing the happy dance out of your head! Good Luck! :-)

Advertisements

February 26, 2008 - Posted by | ConfigMgr | , , ,

11 Comments »

  1. The happy dance? I’ve done those several times. I usually call those my “a-ha!” moments. When something finally, finally clicks!

    Comment by Sherry Kissinger | February 26, 2008

  2. Can you use a subselect query on a query that has two Criterion? For example. I have a query that says Like Adobe Reader and >=8.1
    Could I use a subselect query to on that query that would show me the opposite?

    Comment by Dustin | January 6, 2009

  3. Short answer is “yes”. Posting this from my phone, so I can’t really expand much on that. Hopefully you are able to figure it out.

    Comment by Jarvis | January 6, 2009

  4. Thank you SO much for this! You have saved my life with this one! Definitely a huge ah-ha moment here :)

    Comment by Nick Clark | May 11, 2009

  5. I had to use something like this instead…. Otherwise there was syntax errors…

    SELECT DISTINCT
    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
    INNER JOIN
    SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId
    WHERE SMS_R_System.ResourceId NOT IN (SELECT
    ResourceID
    FROM
    SMS_G_System_ADD_REMOVE_PROGRAMS
    WHERE DisplayName = “Sophos Anti-Virus”)

    Comment by Austin | July 31, 2009

  6. Both of these solutions don’t work. I continually get syntax errors… any ideas?

    Comment by Barry | September 3, 2009

  7. Did you follow Rod’s post word for word? Even if it doesn’t make sense at first…if you follow it word for word, I have never seen it not work. If you skip/overlook even one step…it will fail every time.

    You can try posting back what the resulting query looks like (after following the steps in Rod’s post) along with the errors that you are getting and I’ll try to look at it. Things are really busy right now, so I can’t promise how timely I will be.

    Alternatively, you could try posting the question on the ConfigMgr forum on myITforum.com. The best guys in the world at ConfigMgr are answering questions on that forum.

    Comment by Jarvis | September 3, 2009

  8. Thanks Austin
    This has been an issue for a long time for me, much appreciated.
    I just had to make sure the text looked allright after pasting it in else sccm complaines.
    Cool stuff

    Comment by Ingi | September 16, 2009

  9. Thanks a TON

    Comment by Harshwardhan | January 4, 2010

  10. […] demonstrating how to create a subselect query using the ConfigMgr GUI. This was something that I blogged about almost two years ago…mainly as a thank you to Rod Trent for a fantastic post that he had […]

    Pingback by Better Subselect Queries « The Realm of the Verbal Processor - Rod Trent at myITforum.com | February 1, 2010


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s