Applied KQL

Helping others get things done using Kusto Query Language (KQL).

KQL query breakdown: Devices with End of Support (EOS) software installed

by

A digital art landscape representing end of support software applications and the computers they are installed on, visualized as a high-resolution image.

End of support software apps and the computers they are installed on.

Identify unsupported software and the devices they are installed on.

Prerequisites for using this KQL query

To use this KQL query, your devices must be licensed for Defender Threat and Vulnerability Management. You can run this query from Defender > Hunting > Advanced hunting.

KQL query to list End of Support (EOS) software and the devices on which it is installed

DeviceTvmSoftwareInventory
| where EndOfSupportStatus == "EOS Version" or EndOfSupportStatus == 
"EOS Software"
| summarize TotalDevices = count(), EOS_Devices = make_set(DeviceName) 
by SoftwareName, SoftwareVersion
| sort by TotalDevices

// credit Bert-JanP/Hunting-Queries-Detection-Rules (github.com)

This returns a result set with columns SoftwareName, SoftwareVersion, TotalDevices, and EOS_Devices, which is an array of all named devices.

Deconstructing the KQL query

Read on to understand HOW the query above works.

This query helps us identify software that has reached its end of support, along with the total number of affected devices and the specific devices where this software is installed. The results are sorted by the number of affected devices.

DeviceTvmSoftwareInventory

DeviceTvmSoftwareInventory is the name of the table we’re querying. It contains information about software installed on various devices.

| where EndOfSupportStatus == "EOS Version" or EndOfSupportStatus == "EOS Software"

The where clause filters the data. It selects only the rows where the EndOfSupportStatus column has a value of either “EOS Version” or “EOS Software”, which indicates software that has reached its end of support.

| summarize TotalDevices = count(), EOS_Devices = make_set(DeviceName) by SoftwareName, SoftwareVersion

The summarize operator aggregates data. Here’s what it does:

  • TotalDevices = count(): This calculates the total number of devices (rows) that match our filter criteria.
  • EOS_Devices = make_set(DeviceName): This creates a set (a unique list) of device names for all the devices that meet the end-of-support criteria. Each device name appears only once in the set.
  • We group these summaries by the SoftwareName and SoftwareVersion.
| sort by TotalDevices

Finally, we use sort by to sort the summarized results by the total number of devices. The software with the highest number of affected devices will appear first.

Reference