Create SCCM Device Collection Based on Installed Software Products & Inventoried Files

The SCCM admin always need to deal with dynamic collection creation based on installed software products. SCCM collect software information from client using different methods such as Software inventory , software file scan, and hardware inventory. We have covered these information in this blog post in details.

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 site database.

Attribute Classes for Software Products

The following attribute classes can be used to create 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 SCCM site database. This class contains information about all software files that were inventories on the client computer.

Software Products

The Software products attribute class uses SMS Provider WMi server class SMS_G_System softwareProduct to query information from SCCM site database. This class contains information about all software products files that contain resource string.

Installed Software

Installed Software attribute class uses SMS provider WMI server class SMS_G_System_INSTALLED_SOFTWARE to query information from SCCM site database. The installed software details comes from Asset Intelligence (AI). AI uses Add Remove Program as a major source of information. However it’s also perform some normalization based on the information from other sources to present final software list.

Installed Applications

Installed Applications attribute class uses SMS provider WMI server class SMS_G_System_ADD_REMOVE_PROGRAMS to query information from site database. The Installed Application details comes directly from Add/Remove Programs from inventories machines.

Installed Applications (64)

The Installed Applications (64) attribute class uses SMS provider WMI server class SMS_G_System_ADD_REMOVE_PROGRAMS_64 to query information from site database. The Installed Applications details comes directly from Add/Remove Programs (x64)from inventories machines.

In this blog post, we will discuss about most commonly used collection queries related with 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 device collection which have reported McsClient.exe version lower than 4.18.215. The version of this file can be used to check Sophos Antivirus version on a client. Sophos Management Communications System (McsClien.exe) is part of Sophos Endpoint security software.

Attributes to select from 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 SCCM collection where McsClient.exe file is present in “C:\Program files (x86)\sophos\Management Communication System\Endpoint” folder.

Attributes to select from 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 comes from Asset Intelligence and AI uses Add Remove Programs as major source of information. This attribute class may include the applications which are not registered in Add / Remove Programs. Let’s see few sample WQL queries to understand how you can use Installed Software attribute class to create collection based on installed software.

SCCM Device Collection for computers where application x is installed

The below WQL query will include all computers where Google chrome is installed in device collection.

Attributes to select from 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 below WQL query will include all computers where Sophos Endpoint Agent is not installed, in device collection. Please note that a subset query need to be used when you trying to create a collection for product not installed.

Attributes to select from 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 below WQL query will include all computers in device collection where Microsoft Visual Studio Professional is installed.

Attributes to select from 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 below WQL query will include all computers in device collection where Microsoft SQL Server Management Studio is installed.

Attributes to select from 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, 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 inventories machines.

SCCM Device Collection for computers where application x is registered in Add / Remove Programs

Attributes to select from Query Designer

Attribute Class : Installed Application

Attribute Name : Product Name

SCCM Collection Query / WQL Query

The below query will add computers in collection where Adobe Acrobat DC is registered in Add/Remove program.

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 application x is not registered in Add / Remove Programs

Attributes to select from 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, 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 inventories machines.

SCCM Device Collection for computers where Microsoft Office 32 bit is installed

Attributes to select from 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 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 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 for 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’s.

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 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