< Blog

Subselect Query for SCCM Queries or Collections (WQL)

By Garth Jones

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.

Subselect Query for SCCM Queries or Collections (WQL)

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.