How to create a collection of computers by installed software.

We’ve all been there, needing to create collections of computers by installed software in SCCM.  Sometimes even needing to list computers that have two or more installed software on them.

So how do you create these collections?  Read on below…

For this example, I am going to create a collection that will list all computers with “iTunes”.  Then I will show you how to add multiple software.

  1. In “Assets and Compliance” go to your Device Collections and right-click and choose “Create Device Collection”.
  2. In the “Create Device Collection Wizard” enter a name for this new collection and you will want to limit the search to either “All Systems” or another collection of your choosing.  I have about 3000 clients in the All Systems collection so I will choose another smaller collection for this example.
    2014-02-20_11-57-31
  3. In the next screen, click on “Add Rule” and then click on “Query Rule”.
    2014-02-20_11-58-36
  4. In the “Query Rule Properties”, enter a name for this query, “All computers with iTunes” and then click on “Edit Query Statement..”
    2014-02-20_12-01-57
  5. 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 "iTunes%"
    and
    SMS_G_System_ADD_REMOVE_PROGRAMS.Version like "%" order by SMS_R_System.Name

    2014-02-20_12-03-35
  6. 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.
    2014-02-20_12-04-34
    2014-02-20_12-06-10
  7. In the Summary step, click on Next.
    2014-02-20_12-06-23
  8. Once complete, click on Close.
    2014-02-20_12-06-45
  9. You will notice that the icon for your new collection is refreshing and that your member count will be “0”.
    2014-02-20_12-07-31
  10. Wait a few moments before right-clicking and selecting “Refresh” or press F5.
    2014-02-20_12-11-58
  11. All done!
    2014-02-20_12-09-50

To add additional software to the collection, you can add a separate query to the collection’s properties by modifying the line in the code which specifies the software to search for, but keep in mind this will result in an “OR” operation meaning it will list computers with either one or the other.  If you want an “AND” operation then just modify the search criteria on the existing query:

  1. Right-click on the collection and click on Properties.
  2. In the ‘Membership Rules” tab click on Edit.
    2014-02-20_12-41-59
  3. You can update the query name in the “Query Rule Properties” window or just click on “Edit Query Statement…”
    2014-02-20_12-42-31
  4. Click on the “Criteria” tab and then select the toolbar button which looks like a yellow star.
    2014-02-20_12-43-40
  5. Choose the “Installed Applications” attribute class and the “Display Name” attribute and then click on OK.
    2014-02-20_12-44-53
  6. Change the operator to “is like” and then type the software title in the “Value” input box and make sure to include the wildcard “%” (not required if you want a specific title and are sure of the spelling).
    2014-02-20_12-46-19
  7. You will notice that the new software title has been added with an “AND” operator.  You can also change the operator to an “OR” using the button second from last which reads “&|”.  Click on OK.
    2014-02-20_12-47-59
  8. Back at the “Query Rules Properties” click on OK to close this window and then click on “Apply” or “OK” in the collection properties window to save the new changes.
  9. Again wait a few minutes before refreshing and it should update the collection membership as long as it finds the specified software on other computers.
    2014-02-20_12-52-18
Advertisements

2 thoughts on “How to create a collection of computers by installed software.

  1. jmulwani

    Can you explain how Installed Application differs from Installled Software?

    There are certain applications that do not show up when we query using Installed Application even though they are visible in the add and remove programs.

    Reply
    1. rcheing Post author

      Hi there! Excellent question. Installed Software has information contained within the SMS_InstalledSoftware WMI class which aggregates software information as well as license information and is used by Asset Intelligence. Installed Software is configured by the Software Inventory section in the client agent settings. However, Installed Applications displays the “software” picked up by the Hardware Inventory pass. To configure the Hardware Inventory to pickup the software in add/ remove programs, make sure to enable Win32Reg_AddRemovePrograms and Win32Reg_AddRemovePrograms64 classes under Client Settings, Hardware Inventory, Set Classes. I hope this helps.

      Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s