Understanding Microsoft Defender for Endpoint's Vulnerability Management with KQL

Introduction

Today, I'll break down a powerful KQL query designed to identify software vulnerabilities across an organization's device inventory. This query demonstrates how to effectively leverage Microsoft Defender for Endpoint data to create a comprehensive vulnerability assessment report.

The Query in Context

This query combines data from multiple tables to correlate vulnerability findings with device metadata and software installation evidence. It's particularly valuable for security teams needing to prioritize and assign remediation tasks.

Breaking Down the Components

Time Range Definition

let lookback = 1d;

We start by defining a one-day lookback period to focus on recent data.

Device Collection

let DeviceList =
    DeviceInfo
    | where
        Timestamp >= ago(lookback)
        and MachineGroup !contains "Azure"
    | distinct DeviceName;

This section filters devices from the last 24 hours, excluding any defined in the machine groups.

Antivirus Scan Information

let ScanInformation =
    DeviceEvents
    | where
        Timestamp >= ago(lookback)
        and DeviceName in (DeviceList)
        and ActionType contains "AntivirusScan"
    | extend
        ScanType = tostring(todynamic(AdditionalFields).["ScanTypeIndex"]),
        ScanTime = Timestamp
    | project DeviceName, ScanTime, ActionType, ScanType;

Here we collect antivirus scan events, extracting the scan type and timestamp.

Vulnerability Data Collection

let Vulnerabilities =
    DeviceTvmSoftwareVulnerabilities
    | where DeviceName in~ (DeviceList)
    | where VulnerabilitySeverityLevel in~ ("Critical", "High", "Medium");

let VulnerabilitiesKB =
    DeviceTvmSoftwareVulnerabilitiesKB;

Device Metadata and Ownership

let DeviceLastUp =
    DeviceInfo
    | where
        Timestamp >= ago(lookback)
        and DeviceName in~ (DeviceList)
    | extend
        UserName = (tostring(parse_json(LoggedOnUsers)[0].UserName)),
        DeviceManualTags = array_sort_asc(todynamic(DeviceManualTags))
    | parse
        kind=regex flags = s DeviceManualTags with *
        "Cost Owner: " CostOwner:string
        '","Subscription: ' Subscription:string
    // Additional parsing...

This fascinating section extracts ownership information from device tags using regex parsing. It shows how organizations can embed valuable metadata in device tags

The Core Data Processing

The main query joins all these tables together and processes the data:

• Join vulnerability data with device metadata
• Add details from the vulnerability KB
• Include scan information
• Enrich with software installation evidence
• Parse registry and file path information
• Handle ownership fallbacks

Final Output

| distinct
    DeviceName
    , CostOwner
    , Subscription
    , UserName
    , SoftwareVendor
    , SoftwareName
    , SoftwareVersion
    , tostring(InstallPath)
    , tostring(RegistryEntry)
| order by DeviceName

The final result is a concise report that includes:

Device identification Ownership information (who's responsible for remediation) Software details (vendor, name, version) Installation locations (file path and registry entries).