KQL Search

Query Details

HomeAI ToolsDevice Query

Windows Recent Devices Missing Full Scan

Query

// All credit goes to Felix Brand - Taken from his post (https://www.linkedin.com/posts/felix-brand_defenderxdr-kql-microsoft-activity-7228749401849040896-0sNk)
// Microsoft now recommends (https://lnkd.in/ewBryvhF) to have one initial full scan when you onboard your system into MDE. But MDE does not trigger a full scan after onboarding, this KQL query combined with a custom detection rule can trigger a scan. 
// Once a scan is completed on an endpoint, it will not appear in this query again.

let AvModeDescription = dynamic({"0":"Normal", "1":"Passive", "4":"EDR Block"});
let TimeRange = ago(1d);
DeviceTvmInfoGathering
| where Timestamp > TimeRange
| extend AdditionalFields = parse_json(AdditionalFields)
| extend AvMode =  tostring(AvModeDescription[tostring(AdditionalFields.["AvMode"])])
| extend FullScanStatus = coalesce(extractjson("$.Full.ScanStatus", tostring(AdditionalFields.AvScanResults)),"Not available")
| where isnotempty( AvMode ) and AvMode has "Normal"
| where FullScanStatus !has "Completed"
| join DeviceEvents on DeviceName
| summarize arg_max(Timestamp, *) by DeviceName, DeviceId
| project DeviceName, DeviceId, FullScanStatus, ReportId, Timestamp
| take 50

Explanation

This KQL (Kusto Query Language) query is designed to identify devices that have not yet completed a full antivirus scan after being onboarded into Microsoft Defender for Endpoint (MDE). Here's a simplified breakdown of what the query does:

  1. Define Variables:

    • AvModeDescription: A mapping of antivirus modes to their descriptions (e.g., "Normal", "Passive", "EDR Block").
    • TimeRange: Sets the time range to the last 24 hours (ago(1d)).

  2. Filter Data:

    • The query starts by looking at the DeviceTvmInfoGathering table for records from the last 24 hours.
    • It extracts additional fields from JSON data, specifically the antivirus mode (AvMode) and the full scan status (FullScanStatus).

  3. Filter Conditions:

    • It filters for devices in "Normal" antivirus mode.
    • It further filters out devices where the full scan status is "Completed".

  4. Join and Summarize:

    • The query joins the filtered data with the DeviceEvents table on DeviceName.
    • It summarizes the data to get the most recent record for each device (arg_max(Timestamp, *)).

  5. Select and Limit Results:

    • The final output includes the device name, device ID, full scan status, report ID, and timestamp.
    • It limits the results to the top 50 devices.

In essence, this query helps identify up to 50 devices that are in "Normal" antivirus mode but have not yet completed a full scan, allowing administrators to take action to ensure these scans are performed.

Details

Nathan Hutchinson profile picture

Nathan Hutchinson

Released: February 2, 2026

Tables

DeviceTvmInfoGatheringDeviceEvents

Keywords

DeviceTvmInfoGatheringDeviceEvents

Operators

letdynamicago|whereextendparse_jsontostringcoalesceextractjsonisnotemptyhas!hasjoinsummarizearg_maxbyprojecttake

Actions