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! :-)
Subscribe
Recent Comments
-
The Realm of the Verbal Processor 