Endpoint Insights
Creating a Subselect Query for WQL
Garth JonesToday’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.
Additional Resources
Learn more about how to successfully use ConfigMgr / SCCM through our many posts dedicated to Systems Management:
ConfigMgr / SCCM Overview
External Integration
Reporting
- How to Install a SCCM Reporting Services Point
- Dynamic Images to SSRS Report for SCCM
- Editing SCCM Reports with Report Builder
Scripting
Security/Permissions
Software
Troubleshooting
