Use SCCM to find the excluded apps in Microsoft 365 Apps or Office 365 Proplus for custom reporting

I was asked by a customer to find the devices with excluded apps in C2R products such as Office 365 Proplus or Microsoft 365 Apps or Office 2019 etc.

When you create a configuration file for C2R products such as office 365 proplus/Microsoft 365 Apps, you can define which app in Microsoft 365 Apps product not to be installed such as Word, Excel, PowerPoint, Publisher, Visio, or Skype. If you don't want Publisher installed with those applications, use the ExcludeApp element to remove it

Following are the allowed values to be used in the configuration for Exclude App element.

  • ID="Access"
  • ID="Excel"
  • ID="Groove"
  • ID="Lync"
  • ID="OneDrive"
  • ID="OneNote"
  • ID="Outlook"
  • ID="PowerPoint"
  • ID="Publisher"
  • ID="Teams"
  • ID="Word"

Following is the sample configuration file that I have used to deploy Microsoft 365 Apps that have few apps excluded.

<Configuration>
<Add OfficeClientEdition="64" Channel="Monthly">
         <Product ID="O365ProPlusRetail">
             <Language ID="en-us"/>
             <ExcludeApp ID="OneDrive"/>
             <ExcludeApp ID="Groove"/>
             <ExcludeApp ID="Lync"/>
         </Product>
     </Add>
     <Display Level="Standard" AcceptEULA="TRUE"/>
     <Property Name="AUTOACTIVATE" Value="1" />
     <Logging Level="Standard" Path="C:\windows\o365proplus"/>
</Configuration>

Likewise, there will be multiple configuration files created and deployed to end-users based on the requirement, and at one point in time, it is good to have a report to find out the devices with their list of excluded apps.

When you deploy the C2R product using Endpoint Manager tool, there is no built-in report feature to identify what apps are excluded on specific devices.

How do we inventory the devices with excluded apps in Microsoft 365 apps?

Let's start analysing the data that is stored on the endpoint for office/Microsoft 365 apps.

Following the registry key that have the information about the C2R products.

Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\ClickToRun\Configuration

image

As you can see, there is a lot more information stored in the registry including O365ProPlusRetail.ExcludedApps

This tells us that, on this device, the 3 apps were excluded (onedrive,groove,lync) that we have used in the configuration file above.

How do we bring this data into Configuration Manager? are there built-in tool or inventory that gather the information?

There are some built-in reports available with information about office 365 products but If you want anything custom as we see now, we must extend the inventory and that can be achieved with a popular tool called RegKeyToMOF.

Detailed Steps:

1. Use RegKeyToMOF to generate configuration.mof and inventory.mof, compile the mof for syntax errors.

2. Import the inventory.mof and configuration.mof

3. Verify the mof changes and also on the SQL Side as well.

3. Deploy the client setting to test device collection.

4. Initiate machine policy cycle on the test device, trigger hardware inventory.

5. Monitor the hardware inventory on the site server and check the SQL results if any data received.

1. Use RegKeyToMOF to generate configuration.mof and inventory.mof:

Download the RegKeyToMOF.exe tool from Technet

On a device that you have installed Office 365 proplus/Microsoft 365 Apps or Office 2019, copy the tool and run the tool to generate a custom MOF file.

Browse to HKEY_LOCAL_MACHINE\SOFTWARE\\Microsoft\Office\ClickToRun\Configuration

image

At this point, we can export the configuration.mof, inventory.mof (to import in admin/) using the tool, save the mof files.

The exported mof file contains lot of information and we will need to trim down this to actual requirement.

Following is my configuration.mof and inventory.mof (Trimmed version):

Configuration.mof:

// RegKeyToMOF by Mark Cochrane (with help from Skissinger, SteveRac, Jonas Hettich, Kent Agerlund & Barker)
// this section tells the inventory agent what to collect
// 16/10/2018 3:05:03 PM

#pragma namespace ("\\\\.\\root\\cimv2")
#pragma deleteclass("Configuration", NOFAIL)
[DYNPROPS]
Class Configuration
{
[key] string KeyName;
String ProPlus2019RetailExcludedApps;
String O365ProPlusRetailExcludedApps;

};

[DYNPROPS]
Instance of Configuration
{
KeyName="RegKeyToMOF";
[PropertyContext("Local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Office\\ClickToRun\\Configuration|ProPlus2019Retail.ExcludedApps"),Dynamic,Provider("RegPropProv")] ProPlus2019RetailExcludedApps;

[PropertyContext("Local|HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Office\\ClickToRun\\Configuration|O365ProPlusRetail.ExcludedApps"),Dynamic,Provider("RegPropProv")] O365ProPlusRetailExcludedApps;
};

Black font is something that must match as per the registry key where as brown font is something that can be customised at your convivence and must be same.

Inventory.mof:

// RegKeyToMOF by Mark Cochrane (with help from Skissinger, SteveRac, Jonas Hettich, Kent Agerlund & Barker)
// this section tells the inventory agent what to report to the server
// 16/10/2018 3:05:03 PM
#pragma namespace ("\\\\.\\root\\cimv2\\SMS")
#pragma deleteclass("Configuration", NOFAIL)
[SMS_Report(TRUE),SMS_Group_Name("o365ExcludedApps"),SMS_Class_ID("o365ExcludedApps")]
Class Configuration: SMS_Class_Template
{
[SMS_Report(TRUE),key] string KeyName;
[SMS_Report(TRUE)] String ProPlus2019RetailExcludedApps;
[SMS_Report(TRUE)] String O365ProPlusRetailExcludedApps;
};

Download the mof files from github

If you have added more attributes from the registry, make sure you compile the mof file for any syntax errors.

Compile the mof file using mofcomp.exe filename.mof

image

Once the mof files are validated, we are now ready to make changes on the SCCM server.

2. Import the inventory.mof and configuration.mof:

Copy the mof files to your CAS/Primary site.

Browse to SCCM installed directory, <Installed Dir>\Program Files\Microsoft Configuration Manager\inboxes\clifiles.src\hinv

Take a backup of configuration.mof before making any changes.

Edit the configuration.mof, go to the last line in the file, copy the content from config.mof (custom code) at the bottom of the file.

image

Save the changes.

Now open the SCCM console, go to administration, client settings, edit the default client settings, go to hardware inventory

image

Click on set classes

Click on import, select the inventory.mof file that you have downloaded or created your own.

image

Click on import.

Make sure you untick the o365, because we don't want these changes to be applied to all devices in default client settings.

image

This step will help us to import the settings, create a SQL view etc.

Monitor datalder.log on your site for changes.we will now see that, the SQL view and store procedure is created successfully.

image

By querying select * from v_GS_o365ExcludedApps0, you get empty results.

image

we will now create new client settings or use existing client setting that you want to deploy to test collection for monitoring the results.

On your new or existing test client settings, edit, click on hardware inventory, set classes and tick o365Excludedapps

image

image

We have now completed the changes on the site server.

3. Deploy the client setting to test device collection.

4. Initiate machine policy cycle on the test device, trigger hardware inventory.

we will move on to the client and initiate machine policy cycle for client to receive these changes, initiate hardware inventory.

Initiate hardware inventory and monitor the log InventoryAgent.log

image

Collection: Namespace = \\.\root\cimv2; Query = SELECT __CLASS, __PATH, __RELPATH, KeyName, O365ProPlusRetailExcludedApps, ProPlus2019RetailExcludedApps, VisioPro2019RetailExcludedApps FROM Configuration; Timeout = 600 secs.

we will now move to SQL database and run the query to test the results.

image

select sys.Netbios_Name0,
  exc.O365ProPlusRetailExcludedApp0,
  exc.ProPlus2019RetailExcludedApp0
  from [v_GS_o365ExcludedApps0] exc
  inner join v_R_System_Valid sys
  on sys.ResourceID=exc.ResourceID

image

we have now successfully gathered the required data using the custom inventory report.

For more information about:

Overview of Office Deployment Tool, please refer https://docs.microsoft.com/en-us/deployoffice/overview-office-deployment-tool

Office deployment tool configuration options, please refer https://docs.microsoft.com/en-us/deployoffice/office-deployment-tool-configuration-options

Hope you find this post useful.

Leave a Reply