KQL Search

Query Details

HomeAI ToolsDevice Query

MDE Office365version History

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](https://github.com/alexverboon/Feeds/blob/main/data/office_update_history_2018-present.csv) is updated every day at 06:00 UTC

#### Author

- **Alex Verboon**

#### References

- [Update history for Microsoft 365 Apps (listed by date)](https://learn.microsoft.com/en-us/officeupdates/update-history-microsoft365-apps-by-date)

## Defender XDR

```kql
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,MonthsSinceRelease
```

## 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:

  1. 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.

  2. 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".

  3. 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.

  4. 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.

  5. 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 profile picture

Alex Verboon

Released: June 14, 2025

Tables

DeviceTvmSoftwareInventory

Keywords

Devices

Operators

letexternaldatawithwherecontains==projectextendstrcat_arrayarray_slicesplittodatetimeformat_datetimejoinkind=leftouterondatetime_diffnowsummarizedcountby

Actions