ConfigMgr Collection Query – Workstations with Office 2010 Installed

by

The following WQL query statement can be used to list all workstations which have Microsoft Office 2010 (all versions) installed WITHOUT any service packs installed.

To use you will need to create a new collection and add as a Membership Query Rule.

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 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 like "Microsoft Office%2010%" and SMS_G_System_ADD_REMOVE_PROGRAMS.Version < "14.0.6023") and SMS_R_System.Obsolete = 0 and SMS_R_System.Client = 1

How to create a new collection in SCCM

  1. With the Configuration manager Console window open
  2. Right-click the ‘Collections’ node and select ‘New Collection’
  3. SCCM-NewCollection1
  4. Enter in an appropriate name for the collection, for example “Workstations with Office 2010 (WITHOUT SP1)”
  5. SCCM-NewCollection2
  6. Click ‘Next’
  7. Click on the ‘Query Rule’icon (it looks like a yellow cylinder)
  8. SCCM-NewCollection3
  9. Enter an appropriate name for the query and limit to your workstation collection (if required)
  10. SCCM-NewCollection4
  11. Click on the ‘Edit Query Statement’ button
  12. Select the ‘Show Query Language’ button
  13. Delete any text already in the ‘Query Statement’ box and copy and paste the query above into the box
  14. SCCM-NewCollection5
  15. Click ‘OK’ and then ‘OK’ to return to the New Collection Wizard
  16. Click ‘Next’,‘Next’,‘Next’ and then ‘Finish’
  17. You may need to refresh the collection before you’re able to see the clients listed, this is done by right-clicking on the new collection and selecting ‘Update Collection Membership’