Microsoft Office 365 - Version History Information
MDE Office365version History
Query
let officeversionhistory = (externaldata(ReleaseDate:datetime , Channel:string, Version:string,Build:string)[@'https://raw.githubusercontent.com/alexverboon/Feeds/refs/heads/main/data/office_update_history_2018-present.csv']
with (format="csv", ignoreFirstRecord=true));
DeviceTvmSoftwareInventory
| where SoftwareVendor contains "microsoft"
| where SoftwareName == 'office'
| project DeviceName, SoftwareName, SoftwareVersion, EndOfSupportDate, EndOfSupportStatus
| extend Shortbuild = strcat_array(array_slice(split(SoftwareVersion, "."), 2, -1), ".")
| extend EndOfSupportDate = todatetime(format_datetime(EndOfSupportDate, 'yyyy-MM-dd'))
| join kind=leftouter (officeversionhistory
| extend ReleaseDate = todatetime(format_datetime(ReleaseDate, 'yyyy-MM-dd'))
)
on $left. Shortbuild == $right.Build
| extend MnthsSupported = datetime_diff('month', EndOfSupportDate, ReleaseDate)
| extend MonthsSinceRelease = datetime_diff('month',now(),ReleaseDate)
| summarize TotalDevices = dcount(DeviceName,4) by SoftwareName, SoftwareVersion, EndOfSupportDate,EndOfSupportStatus, Shortbuild, ReleaseDate,Channel, Version, Build,MnthsSupported,MonthsSinceReleaseAbout this query
Microsoft Office 365 - Version History Information
Query Information
Description
Use this KQL query to retreive information about your Microsoft Office installations across MDE managed devices. The query joins endpoint inventory data with public Office update history feed data. The query extend the MDE Software Inventory data with the following information:
- Office deployment Channel
- Release Date
- The total # of months the release was supported until its EOS date
- The total of months since the release of the appropriate version.
Office Update Feed Data
The Office Update history feed is updated every day at 06:00 UTC
Author
- Alex Verboon
References
Defender XDR
Sentinel
n/a, because the DeviceTvmSoftwareInventory is not present in Sentinel.
Explanation
This KQL query is designed to gather information about Microsoft Office installations on devices managed by Microsoft Defender for Endpoint (MDE). Here's a simplified breakdown of what the query does:
-
Data Sources:
- It uses two data sources:
- The MDE software inventory data, which contains information about software installed on devices.
- An external Office update history feed that provides details about Office versions, their release dates, and update channels.
- It uses two data sources:
-
Filtering and Joining:
- The query filters the MDE inventory data to focus only on Microsoft Office software.
- It extracts a part of the Office version number (referred to as "Shortbuild") to match it with the build information from the Office update history feed.
- It performs a left outer join to combine the MDE data with the Office update history based on this "Shortbuild".
-
Data Extension:
- It adds new information to the MDE data, such as:
- The Office deployment channel (e.g., Current, Monthly, Semi-Annual).
- The release date of the Office version.
- The number of months the version was supported until its end-of-support (EOS) date.
- The number of months since the version was released.
- It adds new information to the MDE data, such as:
-
Summarization:
- The query summarizes the data to count the total number of devices with each Office version and provides details like the software version, end-of-support date, support status, release date, and more.
-
Output:
- The result is a summary of Office installations, showing how long each version has been supported and how long it has been since its release, along with the number of devices using each version.
Note: This query is specific to Microsoft Defender for Endpoint and cannot be used in Microsoft Sentinel because the required data table, DeviceTvmSoftwareInventory, is not available in Sentinel.
Details
Alex Verboon
Released: June 14, 2025
Tables
Keywords
Operators