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.
.jpg "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.
[…] How a “Not Equal To” Query is Processed The Subselect Query Subselect Query for Reports (SQL) Subselect Query for SCCM Queries or Collections (WQL) […]