ConfigMgr Console
Creating a Subselect Query for WQL
Garth JonesTopics: ConfigMgr Console, Systems Management
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.
Additional ConfigMgr / SCCM Resources
Learn more about how to better use ConfigMgr / SCCM.
Overview
Reporting
- How Can I Install Report Builder?
- How to Install a SCCM Reporting Services Point
- Dynamic Images to SSRS Report for SCCM
- Editing SCCM Reports with Report Builder
- Fixing the SCCM Reporting Services Point
Scripting
Security/Permissions
Software
External Integration
