How to get office 365 proplus activation status and excluded apps etc using SCCM Configmgr

 

Introduction:

We are in midst of completing office 365 project .As part of this project ,one of the primary activity is migration of Microsoft office to office 365 proplus. For office 365 proplus deployment ,we are using Powershell App deployment toolkit that provide GUI ,customize what to remove and other benefits compared with standard proplus that you download in ConfigMgr/configuration tool.

As you know ,office 365 proplus activation is not usual method (KMS) instead, it use o365 license and user must activate the product using the license that they get it.

After installing the proplus ,devices will perform auto activate if your UPN (eswar.koneti@eskonr.com) that is being used in Azure AD and also your on-prem domain login (eswar.koneti@apac.eskonr.com) same . In my case ,they are not same hence auto activation will not work.

If your cloud UPN and on-prem UPN is same then you can need to adjust your xml file for proplus installation with <Property Name="AUTOACTIVATE" Value="1" /> .

I have been travelling across Asia for office 365 project training and deployments .We have deployed proplus to thousands of users across asia and all going fine.

Problem:

As part of this project ,we want to monitor deployments along with the activation status of the proplus and make sure all the devices that got proplus installed are activated successfully. For some reason if the proplus not activated ,features of office apps (excel,word ,outlook) will be limited and functionality will be reduced.

If the activation is KMS based then it would connect to KMS server on certain ports for activation but office 365 proplus ,it is not the case .

How do we use Configmgr to get activation status for office 365 proplus ?

In this blog post ,we will see where does the activation results store on the windows PC and how to collect that information into SCCM for reporting purpose.

we can also use office 365 portal to know the activation status of users that you assigned license but office 365 portal wont give you the device information directly unless you use graph API to pull the information.

we can login to   https://admin.microsoft.com/AdminPortal/Home#/reportsUsage and look at office activations .

If we want to use SCCM ,we must first locate where does the activation of proplus store in registry ?

On windows device that installed with proplus ,you can navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\ClickToRun\Configuration for 32bit proplus ,you see few keys with O365ProPlusRetail.EmailAddress,O365ProPlusRetail.ExcludedApps etc as shown below.

In the new model of Configuration manager current branch,office proplus information  is part of default inventory and all the information about proplus ,channel and other information which is available in the registry key /wmi is already being collected into SCCM database except the activation and other information .

Inventory information about proplus configuration is stored in v_GS_OFFICE365PROPLUSCONFIGURATIONS  .You can use this SQL view to query information about proplus versions,channel and lot more.

With this default inventory of proplus ,we don't get any activation details for which ,we will alter configuration.mof and also import mof to client agent settings to pull the activation status from registry to SCCM database.

Since we already know where does the activation information store in registry ,we will use RegKeyToMOF.exe tool to get MOF content .

Download RegKeyToMOFv33a.exe from TechNet

double click the exe file ,browse to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\ClickToRun\Configuration

de-select 64bit unless you installed 64 bit proplus.

Click on Save MOF ,it will prompt for location to save the MOF files.

MOF files are now stored in to the location you specified as above.

What next ? we need to edit the MOF files and delete unnecessary content from it ,as most of the information is already being collected by hardware inventory and is available in v_GS_OFFICE365PROPLUSCONFIGURATIONS.

In this blog post,i will go with email address ONLY .If you want other information that you are interested in ,you can enable it so.

I have deleted all files in the folder except CM12Config.mof and CM12Import.mof

After deleting all the content except O365ProPlusRetail.EmailAddress ,my MOF files looks like below.

configuration.mof:

Save the MOF file .

Now we will try to compile the MOF file to make sure ,it is valid before we copy the code to configuration.mof file into SCCM Server location <SCCM install location folder: >\inboxes\clifiles.src\hinv.

To compile MOF file ,open cmd and run mofcomp.exe filename.mof

As you can see above, the MOF content could not process correctly and is because special character in the content .

CM12Config.mof (11): error SYNTAX 0X80044002: Expected semicolon or '='

If you look at it carefully ,the MOF content contains special character in the string value that fail to process at line 11 i.e String O365ProPlusRetail.EmailAddress;

we will make some changes to the mof file by removing the special characters .

Change the string value from O365ProPlusRetail.EmailAddress to Emailaddress or ActivationAddress or something that you are interested in.

I have made the change in 2 places which are highlighted in red colour. Please make sure ,both the red colour values are same. Thanks to Garth Jones who helped me in getting rid of this syntax error.

Modified content:

// 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 EmailAddress;
};

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

Now mofcomp the file again

MOF File processed successfully (leave about access denied ). We will now copy the modified content to configuration.mof file located in <SCCM install location folder: >\inboxes\clifiles.src\hinv

Go to end of the file and paste the code into it.

Both the arrows marked must be same string.

Save the MOF file. It will now start processing .For monitoring ,read datalder.log .

We have another MOF file to import to client agent ,hardware inventory to collect the inventory from clients.

keeping only required information and delete other information ,mof file looks like this.

we will now make some changes to this file to get it working . Following are the changes:

#pragma namespace ("\\\\.\\root\\cimv2\\SMS")
#pragma deleteclass("Configuration", NOFAIL)
[SMS_Report(TRUE),SMS_Group_Name("o365 - Configuration"),SMS_Class_ID("o365Config")]
Class Configuration: SMS_Class_Template
{
[SMS_Report(TRUE),key] string KeyName;
[SMS_Report(TRUE)] String EmailAddress;
};

Red colour string must be same as configuration.mof file content and brown colour string can be anything that you like to see the SQL view in database for querying (v_GS_O365Config0)

do mofcomp.exe to check if the MOF file has been successfully parsed

we will now import this mof to inventory settings.

Go to your SCCM console ,administration ,client agent settings ,default client settings (you cannot import MOF file directly to custom client device settings),hardware inventory ,set classes ,import ,choose the import file.

After it import ,make sure you uncheck both as we don't this to be enabled in the default client settings instead ,we will create custom client agent settings or edit the custom one that you already have for inventory collection.

Monitor the log dataldr.log to see if the changes are processed and view created or not.

You can now create custom client agent settings and enable the classes .

With this ,we have successfully created MOF file,applied the MOF files to collect the office 365 proplus activation status.

Download the MOF files for office proplus activation from here

Wait for clients to download the policy and run the hardware inventory .

Here is different SQL codes  to check the activation results for proplus .Make changes where required.

--select top 10 values from the office activations view
select top 10 * from v_gs_o365config0

--get count of activations and no-activations for proplus installation

select count(distinct case when o.emailaddress0 is not NULL then o.emailaddress0 else '0'  end ) 'Total Activations',
count(case when o.emailaddress0 is NULL then '1' end ) 'Not Activated'
from v_gs_o365config0 O
inner join v_Add_Remove_Programs arp on o.ResourceID=arp.ResourceID
where arp.DisplayName0 ='Microsoft Office 365 ProPlus - en-us'

--get list of devices with usesr names that are installed with proplus but not activated
 
select sys.name0,sys.User_Name0,u.Mail0,u.Full_User_Name0
from v_gs_o365config0 O
inner join v_Add_Remove_Programs arp on o.ResourceID=arp.ResourceID
inner join v_r_system sys on sys.ResourceID=o.ResourceID
inner join v_r_user u on u.User_Name0=sys.User_Name0
where arp.DisplayName0 ='Microsoft Office 365 ProPlus - en-us'
and o.EmailAddress0 is not NULL

Hope it helps!