SCCM - Use SCCM Baselines to Detect Office Installation Version and Bitness

Scenario

You have a mix bag of Office 2019 and Office 2016 installs some are 32 or 64bit. You need to create a Device collections in SCCM that reflects what machines have which office version installed and what bitness as well.

What i tried:

My first instinct was to create a device collection using a combination of the below queries:

select distinct 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 Plus 2019%"

select distinct 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 Plus 2019%"

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.FilePath = "C:\\Program Files (x86)\\Microsoft Office\\root\\Office16"

There are numerus reason why the above does not work correctly when you have a mix bag of bitness and installed versions. Also if you have had machines updated from 2016 to 2019 64 or 32 bit, not all folders and paths are cleaned up, therefor making devices appear in the collection that does not have the appropriate installed version.

Because office 2016 and 2019 are kind of treated as rolling releases there are similarities in path. Bitness on the other hand, when looking in ADD_REMOVE_PROGRAMS.DisplayName the installer for office can be 32 bit while the office app its self is 64bit.

This is not the whole picture but is enough for you to understand why this can be a problem.

---

The Solution

Enter SCCM Baselines.

Using PowerShell & SCCM Baselines one can look at an exact location to see if a file exists, and then create a device collection if it is true.

PowerShell scripts used:

How to create the Configuration items and Configuration Baselines:

I assume you already know how to create and use baselines so that wont be covered.

However if you are new to baselines look here: Create configuration items in Configuration Manager.

You will want to create a new compliance rule for each item you want to detect with the PowerShell script. (See below screenshots)

The Compliance rules for each setting should be:

• Rule type: Value
• The setting must comply with the following rule: The value returned by the specified sctript:
• Operator: Equals
• For the following Values: true

Check Report Noncompliance if this setting instance is not found

After creating the compliance rule you will want to create the baseline.

1. Create configuration baseline
2. add Configuration item
3. Select the configuration item you created earlier. You will need to create a baseline for each one you created earlier.
4. After you have created all the baselines you can deploy each to the collection you want to target.