Creating dynamic device collections in SCCM (Configuration Manager) is a powerful way to manage software deployments, compliance, and reporting. Whether you need to identify machines with a specific application installed, target updates, or audit software usage, SCCM provides multiple inventory methods—software inventory, file scanning, and hardware inventory—that can be leveraged to build precise queries. In this guide, we’ll walk through how to create collections based on installed software products and inventoried files, using WQL queries for maximum flexibility
A collection or query created in Configuration Manager uses WMI Query Language (WQL) to request objects from the SMS Provider WMI Schema, which in turn retrieves the data from the site database.
- Attribute Classes for Software Products
- SCCM device collection based on Inventoried Software File
- SCCM device collection based on Installed Software Products
- SCCM device collection based on Software Registered in Add / Remove Programs
- SCCM device collection based on 32-bit and 64-bit Applications
- Related Posts:
Attribute Classes for Software Products
The following attribute classes can be used to create an SCCM collection based on installed software products and inventoried files.
Software Files
Software Files Attribute class uses SMS Provider WMI Server class SMS_G_System_SoftwareFiles to query information from the SCCM site database. This class contains information about all software files that were inventoried on the client computer.
Software Products
The Software products attribute class uses the SMS Provider WMi server class SMS_G_System softwareProduct to query information from the SCCM site database. This class contains information about all software product files that contain resource strings.
Installed Software
Installed Software attribute class uses the SMS provider WMI server class SMS_G_System_INSTALLED_SOFTWARE to query information from the SCCM site database. The installed software details comes from Asset Intelligence (AI). AI uses the Add Remove Programs as a major source of information. However, it also performs some normalization based on the information from other sources to present the final software list.
Installed Applications
Installed Applications attribute class uses the SMS provider WMI server class SMS_G_System_ADD_REMOVE_PROGRAMS to query information from the site database. The Installed Application details come directly from Add/Remove Programs from inventory machines.
Installed Applications (64)
The Installed Applications (64) attribute class uses the SMS provider WMI server class SMS_G_System_ADD_REMOVE_PROGRAMS_64 to query information from the site database. The Installed Applications details come directly from Add/Remove Programs (x64)from inventory machines.
In this blog post, we will discuss the most commonly used collection queries related to Software Files & Software Products.
Software Files attribute class | SMS_G_System_SoftwareFile
Related post: If you are looking for step by step process to create a dynamic / query-based collection, then follow this article: How to Create Dynamic Collection in SCCM
Other posts in this series:
- SCCM Collection Queries for Operating System Attribute Class
- Collection Queries for System Resource (SMS_R_System) Attribute Class
SCCM device collection based on Inventoried Software File
To create a device collection based on software file, version or path, the software inventory must be configured to collect the inventory information about respective executables from client computers.
SCCM Device Collection based on File Name & Version
The below WQL query will include all workstations in the device collection that have reported McsClient.exe version lower than 4.18.215. The version of this file can be used to check the Sophos Antivirus version on a client. Sophos Management Communications System (McsClien.exe) is part of Sophos Endpoint security software.
Attributes to select from the Query Designer
Attribute Class: Software File
Attribute Name: File Name & File Version
SCCM Collection Query / WQL Query
select * from SMS_R_System inner join SMS_G_System_SoftwareFile on SMS_G_System_SoftwareFile.ResourceId = SMS_R_System.ResourceId where SMS_G_System_SoftwareFile.FileName = "McsClient.exe" and SMS_G_System_SoftwareFile.FileVersion < "4.18.215"SCCM device collection based on Software File Name & Path
The below WQL query will include all devices in the SCCM collection where McsClient.exe file is present in “C:\Program files (x86)\sophos\Management Communication System\Endpoint” folder.
Attributes to select from the Query Designer
Attribute Class: Software File
Attribute Name: File Name & File Path
SCCM Collection Query / WQL Query
select SMS_R_System.ResourceId, SMS_R_System.ResourceType, SMS_R_System.Name, SMS_R_System.SMSUniqueIdentifier, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.Client from SMS_R_System inner join SMS_G_System_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SoftwareFile.FileName = "McsClient.exe" and SMS_G_System_SoftwareFile.FilePath = "C:\\Program Files (x86)\\Sophos\\Management Communications System\\Endpoint\\"SCCM device collection based on Installed Software Products
As explained above, the installed software details come from Asset Intelligence, and AI uses Add Remove Programs as a major source of information. This attribute class may include the applications that are not registered in Add / Remove Programs. Let’s see a few sample WQL queries to understand how you can use the Installed Software attribute class to create a collection based on installed software.
SCCM Device Collection for computers where application X is installed
The WQL query below will include all computers where Google Chrome is installed in the device collection.
Attributes to select from the Query Designer
Attribute Class: Installed Software
Attribute Name: Product Name
SCCM Collection Query / WQL Query
select SMS_R_System.ResourceId, SMS_R_System.ResourceType, SMS_R_System.Name, SMS_R_System.SMSUniqueIdentifier, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.Client from SMS_R_System inner join SMS_G_System_INSTALLED_SOFTWARE on SMS_G_System_INSTALLED_SOFTWARE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_INSTALLED_SOFTWARE.ProductName like "Google Chrome%"SCCM Device Collection for computers where application X is not installed
The WQL query below will include all computers where Sophos Endpoint Agent is not installed in the device collection. Please note that a subset query needs to be used when you are trying to create a collection for a product not installed.
Attributes to select from the Query Designer
Attribute Class: Installed Software
Attribute Name: Product Name
SCCM Collection Query / WQL Query
select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.ResourceId not in (select SMS_R_System.ResourceId from SMS_R_System inner join SMS_G_System_INSTALLED_SOFTWARE on SMS_G_System_INSTALLED_SOFTWARE.ResourceId = SMS_R_System.ResourceId where SMS_G_System_INSTALLED_SOFTWARE.ProductName like "Sophos Endpoint Agent")
SCCM Device Collection for computers where Microsoft Visual Studio Professional is installed
The WQL query below will include all computers in the device collection where Microsoft Visual Studio Professional is installed.
Attributes to select from the Query Designer
Attribute Class: Installed Software
Attribute Name: Product Name
SCCM Collection Query / WQL Query
select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_INSTALLED_SOFTWARE on SMS_G_System_INSTALLED_SOFTWARE.ResourceId = SMS_R_System.ResourceId where SMS_G_System_INSTALLED_SOFTWARE.ProductName like "Microsoft Visual Studio Professional%"SCCM Device Collection for computers where Microsoft SQL Server Management Studio is installed
The WQL query below will include all computers in the device collection where Microsoft SQL Server Management Studio is installed.
Attributes to select from the Query Designer
Attribute Class: Installed Software
Attribute Name: Product Name
SCCM Collection Query / WQL Query
select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_INSTALLED_SOFTWARE on SMS_G_System_INSTALLED_SOFTWARE.ResourceId = SMS_R_System.ResourceId where SMS_G_System_INSTALLED_SOFTWARE.ProductName like "Microsoft SQL Server Management Studio%"
SCCM device collection based on Software Registered in Add / Remove Programs
As explained above, the Installed Application attribute class contains the information about software registered in Add / Remove Programs. The Installed Application details comes directly from Add/Remove Programs from inventory machines.
SCCM Device Collection for computers where application X is registered in Add / Remove Programs
Attributes to select from the Query Designer
Attribute Class: Installed Application
Attribute Name: Product Name
SCCM Collection Query / WQL Query
The query below will add computers in collection where Adobe Acrobat DC is registered in the Add/Remove Programs.
select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceId = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Adobe Acrobat DC"
SCCM Device Collection for computers where the application X is not registered in Add / Remove Programs
Attributes to select from the Query Designer
Attribute Class: Installed Application
Attribute Name: Product Name
SCCM Collection Query / WQL Query
select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.ResourceId not in (select SMS_R_SYSTEM.ResourceID from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceId = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Adobe Acrobat DC")
SCCM device collection based on 32-bit and 64-bit Applications
As explained above, the Installed Applications and Installed Applications (64) attribute class contains the information about software registered in Add/Remove Programs for 32-bit and 64-bit applications, respectively. The Installed Applications details comes directly from Add/Remove Programs from inventory machines.
SCCM Device Collection for computers where Microsoft Office 32-bit is installed
Attributes to select from the Query Designer
Attribute Class: Installed Applications
Attribute Name: Display Name
SCCM Collection Query / WQL Query
select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where (SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "Microsoft Office Professional%" OR SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "Microsoft Office Enterprise%" OR SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "Microsoft Office Standard%")SCCM Device Collection for computers where Microsoft Office 64-bit is installed
Attributes to select from the Query Designer
Attribute Class: Installed Applications (64)
Attribute Name: Display Name
SCCM Collection Query / WQL Query
select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS_64 on SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceID = SMS_R_System.ResourceId where (SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName like "Microsoft Office Professional%" OR SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName like "Microsoft Office Enterprise%" OR SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName like "Microsoft Office Standard%")
SCCM Device Collection for computers where Microsoft Office 64-bit is installed (Using Office Product Info attribute class)
While the Installed Applications / Installed Applications (64) attribute classes can be used to create a collection for any 32-bit or 64-bit applications, there is another attribute class dedicated to Microsoft Office Products Information. The “Office Product Info” (SMS_G_System_OFFICE_PRODUCTINFO) attribute class can also be used to create query based collection for Office products based on versions, architecture and other criteria.
Let’s see the example below to create a device collection for all computers where Office Professional 64-bit is installed.
Attributes to select from the Query Designer
Attribute Class: Office Product Info
Attribute Name: Display Name / Architecture
SCCM Collection Query / WQL Query
select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_OFFICE_PRODUCTINFO on SMS_G_System_OFFICE_PRODUCTINFO.ResourceID = SMS_R_System.ResourceId where SMS_G_System_OFFICE_PRODUCTINFO.ProductName like "Microsoft Office Professional%" and SMS_G_System_OFFICE_PRODUCTINFO.Architecture = "x64"Related Posts:
- How to Create Dynamic Collection in SCCM
- Enclosure Chassis Types Value & Description | ConfigMgr | SCCM
- ConfigMgr: Maintenance Window for member of specific collection – SQL Query
- SCCM Dynamic Collection for Windows 10 / 11 Devices
- SCCM Device Collection Equivalents in Microsoft Intune for App Deployment
- SCCM Dynamic Collection Based on Device Type | Collection Queries
- SCCM Collection Based on Operating System Optional Features
- SCCM Device Collection Based on Installed Software Products & Inventoried Files
- SCCM Device Collection Based on Computer Hardware Details
- SCCM Collection Queries for Opearting System Attribute Class
- SCCM Collection Queries for System Resource (SMS_R_System) Attribute Class
- SCCM Dynamic Collection using Application Deployment Status
- SCCM Dynamic Collection Based on Configuration Baseline Compliance Status
- How to Create , View and Organize SCCM Maintenance Windows
- How to Provision Windows 10 / 11 Device using Intune and Windows Autopilot
Subscribe to Techuisitive Newsletter
Be the first to know about our new blog posts. Get our newsletters directly in your inbox and stay up to date about Modern Desktop Management technologies & news.