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! :-)
11 Comments »
Leave a Reply
Subscribe
Recent Comments
-
The Realm of the Verbal Processor 
[…] https://verbalprocess.wordpress.com/2008/02/26/subselect-query-in-sccm/ […]
Pingback by Jarvis likes subselect queries! And, who doesn't, really? - Rod Trent at myITforum.com | February 26, 2008
The happy dance? I’ve done those several times. I usually call those my “a-ha!” moments. When something finally, finally clicks!
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?
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.
Thank you SO much for this! You have saved my life with this one! Definitely a huge ah-ha moment here :)
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”)
Both of these solutions don’t work. I continually get syntax errors… any ideas?
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.
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
Thanks a TON
[…] 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