Microsoft Defender's Advenced Hunting: A Personal KQL Query List
Posted on October 13, 2025 • 3 minutes • 497 words
Table of contents
This page contains personal queries for Microsoft Defender’s Advanced Hunting. A personal test tenant with E5 license package is used for the execution. The test tenant typically includes a Windows Server 2022, an Ubuntu server, a Windows 11 device, and a Mac device.
This page will be updated over time.
The list, like other content on this website, aims to contribute to defense in cyberspace.
Since EDR Onboarded No Full Scan Performed Device List
This query combines the DeviceInfo and DeviceTvmInfoGathering schemas using DeviceId, and filters for devices marked as Onboarded. Then, the AdditionalFields column is parsed three times to extract the Full Scan Status value, which is visible on the Device pages within Microsoft Defender.
DeviceTvmInfoGathering
| where Timestamp > ago(30d)
| join kind=inner (
DeviceInfo
| where OnboardingStatus == "Onboarded"
| project DeviceId, OSPlatform
) on DeviceId
| extend AvScanResults = parse_json(AdditionalFields.AvScanResults)
| where AvScanResults != ""
| extend FullScanStatus = parse_json(tostring(parse_json(tostring(parse_json(AdditionalFields).AvScanResults)).Full)).ScanStatus
| extend FullScanTimestamp = parse_json(tostring(parse_json(tostring(parse_json(AdditionalFields).AvScanResults)).Full)).Timestamp
| where FullScanTimestamp == ""
| distinct DeviceName, OSPlatform
The query parses the EDR full scan status from a device log:
Since EDR Onboarding, Full & Quick Scan Statuses of Servers
This query lists total number of onboarded servers. After that it parse and count the full and quick scan statuses.
let ValidDevices =
DeviceInfo
| where OnboardingStatus == "Onboarded"
| where SensorHealthState == "Active"
| where OSPlatform != "Windows10" and OSPlatform != "Windows11"
| distinct DeviceName, DeviceId;
DeviceTvmInfoGathering
| where Timestamp > ago(30d)
| join kind=inner (
ValidDevices
) on DeviceName
| extend AvScanResults = parse_json(AdditionalFields.AvScanResults)
| where AvScanResults != ""
| extend FullScanStatus = parse_json(tostring(parse_json(tostring(parse_json(AdditionalFields).AvScanResults)).Full)).ScanStatus
| extend FullScanTimestamp = parse_json(tostring(parse_json(tostring(parse_json(AdditionalFields).AvScanResults)).Full)).Timestamp
| extend QuickScanStatus = parse_json(tostring(parse_json(tostring(parse_json(AdditionalFields).AvScanResults)).Quick)).ScanStatus
| extend QuickScanTimestamp = parse_json(tostring(parse_json(tostring(parse_json(AdditionalFields).AvScanResults)).Quick)).Timestamp
| summarize
FullScanned = countif(FullScanStatus == "Completed"),
CancelledFullScan = countif(FullScanStatus == "Cancelled"),
NotFullScanned = countif(FullScanStatus == ""),
QuickScanned = countif(QuickScanStatus == "Completed"),
CancalledQuickScan = countif(QuickScanStatus == "Cancelled"),
NotQuickScanned = countif(QuickScanStatus == "")
| extend TotalServers = toscalar(ValidDevices | summarize dcount(DeviceId))
| extend CompletedFullScanPercentage = (toreal(FullScanned) / TotalServers) * 100
| extend CompletedFullScanPercentage = round(CompletedFullScanPercentage, 1)
| extend CompletedQuickScanPerecentage = (toreal(QuickScanned) / TotalServers) * 100
| extend CompletedQuickScanPerecentage = round(CompletedQuickScanPerecentage, 1)
That is the expected result:
MFA Coverage Rates per Application in Azure Entra ID Sign-in Logs
To calculate MFA coverage rates, we access the AADSignInEventsBeta schema and filter all sign-in logs from Entra, Entra ID, or Azure AD. We focus on the last 30 days and narrow the results to interactiveUser logon types. Next, we exclude logs that do not contain an AccountUpn before counting single- and multi-factor authentications. The final output is a table showing MFA coverage rates per application.
AADSignInEventsBeta
| where Timestamp > ago(30d)
| where LogonType == '["interactiveUser"]'
| where AccountUpn != ""
| summarize
SingleFactorCount = countif(AuthenticationRequirement == "singleFactorAuthentication"),
MultiFactorCount = countif(AuthenticationRequirement == "multiFactorAuthentication")
by Application
| extend TotalCount = SingleFactorCount + MultiFactorCount
| extend MFACoverage = (toreal(MultiFactorCount) / TotalCount) * 100
| extend MFACoverage = round(MFACoverage, 2)
| sort by MFACoverage desc
That is the expected outcome of the query:
