Create collection of computers based on software installed

About:

To get list of machines which have a specific software installed on them. I will use in this example to list all computers with AnyConnect application installed.

 

 

Steps:

  • Open SCCM Console
  • In “Assets and Compliance” go to your Device Collections and right-click and choose “Create Device Collection”.

In the “Create Device Collection Wizard” enter a name for this new collection and choose “all system” in limiting collection.

 

  • In the next screen, click on “Add Rule” and then click on “Query Rule”.
     
  • In the “Query Rule Properties”, enter a name for this query, “All computers with AnyConnect” and then click on “Edit Query Statement..” In this example, I will search for any computer with AnyConnect application installed on it.
     
  • In the next window, you’ll want to click on “Show Query Language” and then copy/paste the below code and click on OK:

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 “%anyconnect%”

and

SMS_G_System_ADD_REMOVE_PROGRAMS.Version like “%” order by SMS_R_System.Name

 

  • Once back in the “Query Rule Properties” window, click on OK to close and go back to the “Create Device Collection Wizard” where you can add more direct rules, query rules or include/ exclude collections.  For now, click on Next.