Pocket Guide to Office 365 Monitoring in Microsoft Endpoint Manager (SCCM)

By | March 19, 2023

With the critical vulnerabilities in Word (CVE-2023-21716) and Outlook (CVE-2023-23397) the update status of Office 365 has come into focus.

The easiest way to check the version status is to go to \Software Library\Overview\Office 365 Client Management in the console. The dashboard behaves a bit beastly but if you succeed to get it loading it will display a nice overview.

If you want to see the status per client, the queries below may be helpful. You can compare this to the M365 update history

SQL:

SELECT v_R_System.Name0,
v_R_System.User_Name0,
v_R_System.Active0,
v_GS_OFFICE365PROPLUSCONFIGURATIONS.VersionToReport0,
v_GS_OFFICE365PROPLUSCONFIGURATIONS.Platform0,
v_GS_OFFICE365PROPLUSCONFIGURATIONS.LastScenario0,
v_GS_OFFICE365PROPLUSCONFIGURATIONS.LastScenarioResult0,
v_GS_OFFICE365PROPLUSCONFIGURATIONS.CDNBaseUrl0,
CASE v_GS_OFFICE365PROPLUSCONFIGURATIONS.CDNBaseUrl0
WHEN 'http://officecdn.microsoft.com/pr/55336b82-a18d-4dd6-b5f6-9e5095c314a6' THEN 'Monthly Enterprise Channel'
WHEN 'http://officecdn.microsoft.com/pr/492350f6-3a01-4f97-b9c0-c7c6ddf67d60' THEN 'Current Channel'
WHEN 'http://officecdn.microsoft.com/pr/64256afe-f5d9-4f86-8936-8840a6a4f5be' THEN 'Current Channel Preview'
WHEN 'http://officecdn.microsoft.com/pr/7ffbc6bf-bc32-4f92-8982-f9dd17fd3114' THEN 'Semi-Annual Enterprise Channel'
WHEN 'http://officecdn.microsoft.com/pr/b8f9b850-328d-4355-9145-c59439a0c4cf' THEN 'Semi-Annual Enterprise Channel (Preview)'
WHEN 'http://officecdn.microsoft.com/pr/5440fd1f-7ecb-4221-8110-145efaa6372f' THEN 'Beta Channel'
ELSE ''
END AS Channel
FROM v_R_System
INNER JOIN v_GS_OFFICE365PROPLUSCONFIGURATIONS ON v_GS_OFFICE365PROPLUSCONFIGURATIONS.ResourceId = v_R_System.ResourceId

Run this in SQL Server Management Studio with your CM database selected.

This image has an empty alt attribute; its file name is Versions2-1024x291.jpg

To get a list of the versions in the field:

select v_GS_OFFICE365PROPLUSCONFIGURATIONS.VersionToReport0, COUNT(*) from v_GS_OFFICE365PROPLUSCONFIGURATIONS
where v_GS_OFFICE365PROPLUSCONFIGURATIONS.VersionToReport0 not like 'NULL'
group by v_GS_OFFICE365PROPLUSCONFIGURATIONS.VersionToReport0
order by COUNT(*) desc

WQL:

Create a new query in Monitories->Queries, select Edit Query Statement, click Show Query Language and overwrite the default query with the code below:

select SMS_R_System.Name, SMS_R_System.LastLogonUserName, SMS_G_System_OFFICE365PROPLUSCONFIGURATIONS.VersionToReport, SMS_G_System_OFFICE365PROPLUSCONFIGURATIONS.CDNBaseUrl, SMS_G_System_OFFICE365PROPLUSCONFIGURATIONS.Platform, SMS_G_System_OFFICE365PROPLUSCONFIGURATIONS.LastScenario, SMS_G_System_OFFICE365PROPLUSCONFIGURATIONS.LastScenarioResult from SMS_R_System inner join SMS_G_System_OFFICE365PROPLUSCONFIGURATIONS on SMS_G_System_OFFICE365PROPLUSCONFIGURATIONS.ResourceId = SMS_R_System.ResourceId

This image has an empty alt attribute; its file name is Versions3-1024x106.jpg

The CDN Base Url has to be translated into the channel:

Monthly Enterprise Channel
CDNBaseUrl = http://officecdn.microsoft.com/pr/55336b82-a18d-4dd6-b5f6-9e5095c314a6

Current Channel
CDNBaseUrl = http://officecdn.microsoft.com/pr/492350f6-3a01-4f97-b9c0-c7c6ddf67d60

Current Channel (Preview)
CDNBaseUrl = http://officecdn.microsoft.com/pr/64256afe-f5d9-4f86-8936-8840a6a4f5be

Semi-Annual Enterprise Channel
CDNBaseUrl = http://officecdn.microsoft.com/pr/7ffbc6bf-bc32-4f92-8982-f9dd17fd3114

Semi-Annual Enterprise Channel (Preview)
CDNBaseUrl = http://officecdn.microsoft.com/pr/b8f9b850-328d-4355-9145-c59439a0c4cf

Beta Channel
CDNBaseUrl = http://officecdn.microsoft.com/pr/5440fd1f-7ecb-4221-8110-145efaa6372f

CMPivot:

CMPivot comes with a build-in query Office365ProPlusConfigurations. This gives you an immediate and accurate result for clients that are online.

This image has an empty alt attribute; its file name is Versions5-1024x306.jpg

Registry:

If you investigate a client, you’ll find this information in HKLM\SOFTWARE\Microsoft\Office\ClickToRun\Configuration

This image has an empty alt attribute; its file name is Versions4-1024x690.jpg

For Updates that are in preparation, see the entries UpdateToVersion and UpdatesReadyToApply in HKLM\SOFTWARE\Microsoft\Office\ClickToRun\Updates

For the applied Policies, see HKLM:\SOFTWARE\Policies\Microsoft\office\16.0\common\officeupdate\

Report:

Since the default dashboards don’t offer a detailed status per machine, here’s a simple report based on the SQL query above for that purpose:

https://github.com/Maxwitat/Offic365Report

Links:

Manage updates to Microsoft 365 Apps with Microsoft Configuration Manager https://learn.microsoft.com/en-us/deployoffice/updates/manage-microsoft-365-apps-updates-configuration-manager

Update history for Microsoft 365 Apps (listed by date)

https://learn.microsoft.com/en-us/officeupdates/update-history-microsoft365-apps-by-date

How to manage Office 365 ProPlus Channels for IT Pros https://techcommunity.microsoft.com/t5/microsoft-365-blog/how-to-manage-office-365-proplus-channels-for-it-pros/ba-p/795813

Windows and Office Support Matrix https://query.prod.cms.rt.microsoft.com/cms/api/am/binary/RE2OqRI

Custom Reports https://github.com/ankitshukla073/Microsoft-Office-Client-Management 

Leave a Reply

Your email address will not be published. Required fields are marked *