Microsoft Defender Advanced Hunting: 10 useful KQL queries for IT administrators

Before running the queries: scope, retention and method

Advanced Hunting in Microsoft Defender XDR uses Kusto Query Language to explore security telemetry. Microsoft states that raw device data can be queried for up to 30 days, and timestamps are evaluated in UTC. Available tables still depend on deployed products, licensing and the permissions assigned to your role.

The examples below focus on Microsoft Defender for Endpoint tables. Start with a short time range, measure the volume and tune thresholds to your environment. A match is a lead to validate in the device timeline, not automatic proof of compromise.

• Filter Timestamp early to reduce query cost.
• Use project to return only the columns you need.
• Prefer in~ and has_any over brittle command-line equality checks.
• Document legitimate exclusions instead of silently raising thresholds.
• Confirm ActionType names in the portal schema reference because they can evolve.

1 — Find inactive or unhealthy MDE sensors

This view keeps the latest record for every onboarded device and isolates sensors that are not active or have not reported in more than 24 hours. Tune the delay for intermittently connected laptops and intentionally powered-off servers.

DeviceInfo
| where Timestamp > ago(30d)
| where OnboardingStatus =~ 'Onboarded'
| summarize arg_max(Timestamp, *) by DeviceId
| where SensorHealthState !~ 'Active' or Timestamp < ago(1d)
| project Timestamp, DeviceName, OSPlatform, ClientVersion,
          SensorHealthState, OnboardingStatus, MachineGroup
| order by Timestamp asc

2 — Prioritize internet-facing or highly exposed devices

IsInternetFacing and ExposureLevel provide a short list of devices to review first. Correlate results with firewall rules, NAT, published services and asset value; a classification does not replace network validation.

DeviceInfo
| where Timestamp > ago(7d)
| summarize arg_max(Timestamp, *) by DeviceId
| where OnboardingStatus =~ 'Onboarded'
| where IsInternetFacing == true or ExposureLevel =~ 'High'
| project DeviceName, OSPlatform, OSVersion, IsInternetFacing,
          ExposureLevel, AssetValue, PublicIP, MachineGroup
| order by IsInternetFacing desc, ExposureLevel asc

3 — Measure ASR impact in Audit and Block modes

ASR events are stored in DeviceEvents. This summary separates ActionType values and surfaces the processes most often affected. Microsoft notes that ASR events are throttled to unique processes seen each hour, so use the result as an impact indicator rather than an exhaustive attempt counter.

DeviceEvents
| where Timestamp > ago(30d)
| where ActionType startswith 'Asr'
| summarize Events=count(), Devices=dcount(DeviceId),
            SampleDevices=make_set(DeviceName, 5)
  by ActionType, InitiatingProcessFileName
| order by Events desc

4 — Detect download or encoded PowerShell commands

PowerShell is both legitimate and essential. This query therefore targets common download, in-memory execution and encoding patterns. Compare the account, parent process, device and command line against your administration tools, logon scripts and deployment platforms.

DeviceProcessEvents
| where Timestamp > ago(7d)
| where FileName in~ ('powershell.exe', 'powershell_ise.exe', 'pwsh.exe')
| where ProcessCommandLine has_any
    ('DownloadString', 'DownloadFile', 'WebClient', 'Invoke-WebRequest',
     'FromBase64String', '-EncodedCommand', 'IEX', 'Invoke-Expression')
| project Timestamp, DeviceId, DeviceName, Account=InitiatingProcessAccountUpn,
          FileName, ProcessCommandLine, InitiatingProcessFileName,
          InitiatingProcessCommandLine, SHA1
| top 200 by Timestamp desc

5 — Identify bursts of failed authentication

Many failures from one source address can indicate an expired password, a misconfigured service, password spraying or lateral movement. Grouping events into 15-minute windows makes spikes visible without returning one row per attempt.

DeviceLogonEvents
| where Timestamp > ago(7d)
| where ActionType == 'LogonFailed'
| summarize Failures=count(), Accounts=dcount(AccountName),
            AccountSamples=make_set(AccountName, 10),
            Reasons=make_set(FailureReason, 5)
  by RemoteIP, DeviceName, LogonType, bin(Timestamp, 15m)
| where Failures >= 10
| order by Failures desc

6 — Monitor successful privileged RDP logons

A successful RDP session by a local administrator is not necessarily malicious, but it deserves added visibility on critical servers and outside normal hours. Build an allowlist of approved jump-host addresses or administrative accounts to reduce noise.

DeviceLogonEvents
| where Timestamp > ago(7d)
| where ActionType == 'LogonSuccess'
| where LogonType =~ 'RemoteInteractive'
| where IsLocalAdmin == true
| project Timestamp, DeviceId, DeviceName, AccountDomain, AccountName,
          RemoteIP, RemoteDeviceName, Protocol, LogonType
| order by Timestamp desc

7 — Find LOLBins communicating externally

Signed Windows utilities can be abused to download or execute content. The signal here is the combination of a commonly abused binary and an external network connection. Validate the domain, IP, command line and parent process before reaching a conclusion.

let LolBins = dynamic(['mshta.exe','rundll32.exe','regsvr32.exe',
                       'certutil.exe','bitsadmin.exe','wmic.exe']);
DeviceNetworkEvents
| where Timestamp > ago(7d)
| where InitiatingProcessFileName in~ (LolBins)
| where RemoteIPType =~ 'Public' or isnotempty(RemoteUrl)
| project Timestamp, DeviceId, DeviceName, InitiatingProcessFileName,
          InitiatingProcessCommandLine, RemoteUrl, RemoteIP, RemotePort,
          InitiatingProcessAccountUpn
| top 200 by Timestamp desc

8 — Hunt for common ransomware preparation commands

Microsoft regularly observes service shutdown, shadow-copy deletion, log clearing and boot modification before encryption. Each command can have a legitimate administrative use; multiple signals on the same device significantly increase investigation priority.

DeviceProcessEvents
| where Timestamp > ago(7d)
| where FileName in~ ('vssadmin.exe','wmic.exe','wbadmin.exe',
                       'bcdedit.exe','wevtutil.exe','cipher.exe','sc.exe')
| where ProcessCommandLine has_any
    ('delete shadows', 'shadowcopy delete', 'delete catalog',
     'recoveryenabled no', 'bootstatuspolicy', ' cl ',
     'stop', 'deletejournal', '/w:')
| project Timestamp, DeviceId, DeviceName, FileName, ProcessCommandLine,
          InitiatingProcessAccountUpn, InitiatingProcessFileName, SHA1
| order by Timestamp desc

9 — List severe vulnerabilities with a public exploit

This join combines vulnerabilities found on devices with the TVM knowledge base. It prioritizes high or critical CVEs for which a public exploit is reported. Run these tables in Defender XDR Advanced Hunting; Microsoft notes that TVM tables are not natively ingested into Microsoft Sentinel.

DeviceTvmSoftwareVulnerabilities
| where VulnerabilitySeverityLevel in~ ('High', 'Critical')
| join kind=inner (
    DeviceTvmSoftwareVulnerabilitiesKB
    | where IsExploitAvailable == true
    | project CveId, CvssScore, IsExploitAvailable, PublishedDate
) on CveId
| summarize Devices=dcount(DeviceId), DeviceSamples=make_set(DeviceName, 10),
            Versions=make_set(SoftwareVersion, 10), MaxCvss=max(todouble(CvssScore))
  by CveId, SoftwareVendor, SoftwareName, VulnerabilitySeverityLevel
| order by MaxCvss desc, Devices desc

10 — Build a daily view of priority alerts

AlertInfo unifies alerts from the deployed Defender solutions. This summary surfaces recurring titles, severity and source. It helps identify rising noise or dominant alert types, but incident status and supporting evidence should be reviewed before bulk-closing anything.

AlertInfo
| where Timestamp > ago(14d)
| where Severity in~ ('Medium', 'High')
| summarize Alerts=dcount(AlertId),
            Techniques=make_set(AttackTechniques, 10)
  by bin(Timestamp, 1d), Severity, ServiceSource, Title
| order by Timestamp desc, Alerts desc

Turn a hunt into a custom detection

After validating normal behavior and reducing false positives, some event-based queries can become custom detections. Microsoft recommends retaining Timestamp and, for Endpoint data, DeviceId or DeviceName so the alert and process tree are associated correctly. A rule can generate at most 150 alerts per execution, so an overly broad filter creates noise and can hide the signal.

State queries such as sensor health or the TVM list are usually better suited to a scheduled report than one alert per result. Regularly review thresholds, exclusions and the query resource usage shown in the portal.

Official references and related reading

Advanced Hunting schemas evolve. Check the built-in portal reference before turning an example into a production rule, especially for ActionType values and preview tables.

• Microsoft Learn — Advanced Hunting schema ↗
• Microsoft Learn — KQL query best practices ↗
• Microsoft Learn — DeviceInfo and sensor health fields ↗
• Microsoft Learn — TVM software vulnerabilities table ↗
• Microsoft Learn — Hunt for ransomware ↗
• Microsoft Learn — Create a custom detection ↗
• Related article — ASR rules from Audit to Block →
• Related article — Active Directory security assessment →