Subselect Query for SCCM Queries or Collections (WQL)

Today’s blog post will show you how to create a subselect query for WQL, and we will find which PCs do not have Microsoft Project 2010 installed. For more information about how queries are processed, please start from the first blog post in this series.

Here are some subselect query writing tips to always keep in mind:
· For the positive query don’t return the PC name because of the possibility of duplicate PC names; instead return the ResourceID information.
· Remove any unnecessary WQL views.
Below is the positive WQL query to find all PCs that have Microsoft Project 2010 installed.

Select distinct

SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID

From

SMS_G_System_ADD_REMOVE_PROGRAMS

Where

SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = “Microsoft Project 2010”
Below is the negative WQL query to find all PCs that have Microsoft Project 2010 installed.

Select

SMS_R_System.Name

From

SMS_R_System

Where

SMS_R_System.ResourceId not in

(

)

Now here is the subselect query which combines the above positive query with the negative query:

Select

SMS_R_System.Name

From

SMS_R_System

Where

SMS_R_System.ResourceId not in

(

Select distinct

SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID

From

SMS_G_System_ADD_REMOVE_PROGRAMS

Where

SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = “Microsoft Project 2010”

)

In tomorrow’s blog post, I will show you how to write one WQL query in order to find PCs that have either x86 or x64 versions of Microsoft Project 2010 installed.
I’m sure that you can see how useful the subselect query can be for targeting PCs that do not have a specific software title installed.