KQL / Microsoft Defender Cheat Sheet

Kusto Query Language (KQL) is used across Microsoft Sentinel, Defender XDR, Azure Monitor, and Azure Data Explorer (ADX). Queries pipe data through operators left-to-right - each | feeds the result of the previous step into the next.

Versions: Microsoft Sentinel (2024+) · Defender XDR · Azure Data Explorer · Azure Monitor Logs

KQL Fundamentals

Basic query structure

KQL

TableName
| where TimeGenerated > ago(24h)
| where ColumnName == "value"
| project TimeGenerated, Column1, Column2, Column3
| sort by TimeGenerated desc
| take 100

Count rows

KQL

SecurityEvent | count

Distinct values in a column

KQL

SecurityEvent
| distinct Account

Rename and add columns with `project` and `extend`

KQL

DeviceProcessEvents
| project Timestamp, DeviceName, FileName, ProcessCommandLine
| extend CommandLength = strlen(ProcessCommandLine)

Conditional column with `iff`

KQL

SecurityEvent
| extend IsPrivileged = iff(TargetUserName contains "admin", true, false)

Case expression

KQL

SecurityEvent
| extend Severity = case(
    EventID == 4625, "Failed Logon",
    EventID == 4648, "Explicit Credential Use",
    EventID == 4720, "Account Created",
    "Other"
)

Top N results by a column

KQL

DeviceProcessEvents
| summarize Count = count() by FileName
| top 20 by Count desc

Time Filtering

Relative time ranges

KQL

| where TimeGenerated > ago(1h)    // last hour
| where TimeGenerated > ago(7d)    // last 7 days
| where TimeGenerated > ago(30m)   // last 30 minutes

Absolute time range

KQL

| where TimeGenerated between (datetime(2024-06-01) .. datetime(2024-06-30))

Specific day

KQL

| where TimeGenerated >= startofday(ago(1d))
  and  TimeGenerated <  startofday(now())

Bin by time (for trend charts)

KQL

SecurityEvent
| where TimeGenerated > ago(7d)
| summarize Count = count() by bin(TimeGenerated, 1h)
| render timechart

Bin by day

KQL

SigninLogs
| summarize Failures = count() by bin(TimeGenerated, 1d), UserPrincipalName
| render timechart

String Operations

Equality and contains

KQL

| where FileName == "powershell.exe"
| where ProcessCommandLine contains "-EncodedCommand"
| where ProcessCommandLine has "IEX"           // faster than contains for whole words
| where AccountName startswith "svc-"
| where AccountName endswith "-admin"

Case-insensitive matching

KQL

| where tolower(FileName) == "powershell.exe"

Regex match

KQL

| where ProcessCommandLine matches regex @"(?i)(mimikatz|sekurlsa|lsadump)"
| where Url matches regex @"https?://\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}"

Multiple values with `in`

KQL

| where EventID in (4624, 4625, 4648, 4672, 4720)
| where FileName in~ ("powershell.exe", "cmd.exe", "wscript.exe", "cscript.exe")
// in~  is case-insensitive

Exclusion with `!in` and `!contains`

KQL

| where AccountName !in ("system", "network service", "local service")
| where ProcessCommandLine !contains "legitimate_script.ps1"

Extract with regex

KQL

DeviceProcessEvents
| extend Domain = extract(@"([a-zA-Z0-9\-]+\.[a-zA-Z]{2,})", 1, ProcessCommandLine)

Parse a structured string

KQL

CommonSecurityLog
| parse Message with * "src=" SrcIP " " * "dst=" DstIP " " *

Split a string into an array

KQL

| extend Parts = split(ProcessCommandLine, " ")
| extend FirstArg = tostring(Parts[0])

String length and manipulation

KQL

| extend CmdLen      = strlen(ProcessCommandLine)
| extend CmdUpper    = toupper(ProcessCommandLine)
| extend CmdTrimmed  = trim(" ", ProcessCommandLine)
| extend CmdReplace  = replace_string(ProcessCommandLine, "\\\\", "\\")

Aggregations & Statistics

Count by column

KQL

SecurityEvent
| summarize Count = count() by EventID
| sort by Count desc

Multiple aggregations at once

KQL

DeviceNetworkEvents
| summarize
    TotalConnections = count(),
    UniqueRemoteIPs  = dcount(RemoteIP),
    UniqueRemotePorts = dcount(RemotePort)
  by DeviceName

Collect values into a set or list

KQL

DeviceProcessEvents
| summarize
    CommandLines = make_set(ProcessCommandLine, 50),
    ParentProcesses = make_list(InitiatingProcessFileName, 20)
  by FileName, DeviceName

Percentiles (useful for beaconing / anomaly detection)

KQL

DeviceNetworkEvents
| summarize
    p50 = percentile(BytesSent, 50),
    p95 = percentile(BytesSent, 95),
    p99 = percentile(BytesSent, 99)
  by RemoteIP

Standard deviation (spot outliers)

KQL

DeviceNetworkEvents
| summarize
    AvgBytes = avg(BytesSent),
    StdDev   = stdev(BytesSent),
    Count    = count()
  by DeviceName, RemoteIP
| where StdDev > 0
| extend CoV = StdDev / AvgBytes  // coefficient of variation - high = erratic, low = regular

Count distinct (approximate for large datasets)

KQL

SigninLogs
| summarize UniqueUsers = dcount(UserPrincipalName) by AppDisplayName

Joins & Lookups

Inner join - match rows in both tables

KQL

let SuspiciousIPs = externaldata(IP: string)
    [@"https://your-storage/blocklist.csv"] with (format="csv");
DeviceNetworkEvents
| join kind=inner SuspiciousIPs on $left.RemoteIP == $right.IP

Left outer join - keep all left rows, enrich where match found

KQL

SecurityEvent
| where EventID == 4625
| join kind=leftouter (
    SecurityEvent
    | where EventID == 4624
    | project SuccessAccount = Account, SuccessTime = TimeGenerated
  ) on Account

Semi join - “where a matching row exists in another table”

KQL

DeviceProcessEvents
| where FileName == "powershell.exe"
| join kind=leftsemi (
    DeviceNetworkEvents
    | where RemotePort in (80, 443, 4444, 8080)
  ) on DeviceId

lookup - enrich with a reference dataset

KQL

let RiskScores = datatable(FileName: string, RiskScore: int)
    [ "mimikatz.exe", 100,
      "psexec.exe",   70,
      "nc.exe",       80 ];
DeviceProcessEvents
| lookup RiskScores on FileName
| where isnotempty(RiskScore)

`let` Statements & Reusable Logic

Define a variable

KQL

let Threshold = 10;
let LookbackPeriod = 7d;
SigninLogs
| where TimeGenerated > ago(LookbackPeriod)
| summarize Failures = count() by UserPrincipalName
| where Failures > Threshold

Define a reusable sub-query

KQL

let FailedLogins =
    SecurityEvent
    | where EventID == 4625
    | summarize FailCount = count() by Account, IpAddress;
let SuccessLogins =
    SecurityEvent
    | where EventID == 4624
    | summarize SuccessCount = count() by Account, IpAddress;
FailedLogins
| join kind=inner SuccessLogins on Account
| where FailCount > 10 and SuccessCount > 0

Tabular function (reusable parameterised query)

KQL

let GetFailedLogons = (lookback: timespan, threshold: int) {
    SecurityEvent
    | where TimeGenerated > ago(lookback)
    | where EventID == 4625
    | summarize Count = count() by Account
    | where Count > threshold
};
GetFailedLogons(1d, 20)

Common Defender & Sentinel Tables

Table	Source	What it contains
`SecurityEvent`	Windows via MMA/AMA	Windows Security Event Log (4624, 4625, 4720, etc.)
`Syslog`	Linux via MMA/AMA	Linux syslog and auth.log entries
`SigninLogs`	Entra ID	Interactive user sign-ins
`AADNonInteractiveUserSignInLogs`	Entra ID	Non-interactive sign-ins (OAuth tokens, legacy auth)
`AADServicePrincipalSignInLogs`	Entra ID	Service principal and managed identity sign-ins
`AuditLogs`	Entra ID	Directory change events (user/group/role/app changes)
`DeviceProcessEvents`	Defender for Endpoint	Process creation events on enrolled devices
`DeviceNetworkEvents`	Defender for Endpoint	Network connections initiated by enrolled devices
`DeviceFileEvents`	Defender for Endpoint	File creation, modification, deletion on enrolled devices
`DeviceLogonEvents`	Defender for Endpoint	Logon/logoff events on enrolled devices
`DeviceRegistryEvents`	Defender for Endpoint	Registry key read/write/delete events
`DeviceEvents`	Defender for Endpoint	Generic device events (PowerShell, WMI, AMSI, etc.)
`SecurityAlert`	All Defender products	All generated security alerts
`SecurityIncident`	Microsoft Sentinel	Incidents (groups of correlated alerts)
`AlertEvidence`	Defender XDR	Entities (IPs, files, users) linked to an alert
`CloudAppEvents`	Defender for Cloud Apps	M365 and connected SaaS app activity
`OfficeActivity`	M365	SharePoint, Teams, Exchange, OneDrive audit logs
`EmailEvents`	Defender for Office 365	Emails received, sent, blocked
`EmailAttachmentInfo`	Defender for Office 365	Attachment metadata for emails
`EmailUrlInfo`	Defender for Office 365	URLs found in emails
`UrlClickEvents`	Defender for Office 365	Safe Links clicks and verdicts
`IdentityLogonEvents`	Defender for Identity	AD authentication events
`IdentityQueryEvents`	Defender for Identity	LDAP/Kerberos/SAMR queries against AD
`IdentityDirectoryEvents`	Defender for Identity	AD object changes (groups, GPOs, accounts)
`BehaviorAnalytics`	Sentinel UEBA	User and entity anomaly scores
`ThreatIntelligenceIndicator`	Threat Intelligence	IOCs (IPs, domains, hashes, URLs)
`CommonSecurityLog`	CEF via AMA	Third-party firewall/IDS/proxy logs in CEF format
`AzureActivity`	Azure Resource Manager	Control-plane audit log - all ARM operations (create, delete, role assignments, policy changes)
`AzureDiagnostics`	Azure resources	Diagnostic logs from Azure services (Key Vault access, NSG flow logs, SQL audit, App Service, etc.)
`AzureMetrics`	Azure Monitor	Resource metrics (CPU, memory, request counts, latency) at configurable granularity
`StorageBlobLogs`	Azure Storage	Blob read/write/delete operations - useful for data-exfiltration hunting

See also: Azure - Azure Monitor & Log Analytics for workspace setup, diagnostic settings, and Log Analytics CLI commands. PowerShell - Microsoft Sentinel for watchlist and automation rule management.

Threat Hunting - Processes 🔬

Suspicious PowerShell - encoded commands or download cradles

KQL

DeviceProcessEvents
| where TimeGenerated > ago(7d)
| where FileName in~ ("powershell.exe", "pwsh.exe")
| where ProcessCommandLine matches regex @"(?i)(-enc|-encodedcommand|IEX|Invoke-Expression|DownloadString|DownloadFile|WebClient|hidden)"
| project Timestamp, DeviceName, AccountName, ProcessCommandLine
| sort by Timestamp desc

LOLBins - living-off-the-land binaries used to run code

KQL

DeviceProcessEvents
| where TimeGenerated > ago(1d)
| where FileName in~ (
    "certutil.exe", "mshta.exe", "wscript.exe", "cscript.exe",
    "regsvr32.exe", "rundll32.exe", "msiexec.exe", "odbcconf.exe",
    "installutil.exe", "regasm.exe", "regsvcs.exe", "msconfig.exe",
    "xwizard.exe", "syncappvpublishingserver.exe"
  )
| project Timestamp, DeviceName, AccountName, FileName, ProcessCommandLine, InitiatingProcessFileName

Processes spawned by Office apps (macro execution)

KQL

DeviceProcessEvents
| where TimeGenerated > ago(7d)
| where InitiatingProcessFileName in~ ("winword.exe", "excel.exe", "powerpnt.exe", "outlook.exe")
| where FileName in~ ("cmd.exe", "powershell.exe", "wscript.exe", "cscript.exe", "mshta.exe")
| project Timestamp, DeviceName, AccountName, InitiatingProcessFileName, FileName, ProcessCommandLine

Base64-encoded commands in process arguments

KQL

DeviceProcessEvents
| where TimeGenerated > ago(7d)
| where ProcessCommandLine matches regex @"[A-Za-z0-9+/]{100,}={0,2}"
| extend DecodedAttempt = base64_decode_tostring(extract(@"([A-Za-z0-9+/]{100,}={0,2})", 1, ProcessCommandLine))
| project Timestamp, DeviceName, FileName, ProcessCommandLine, DecodedAttempt

New services or scheduled tasks created

KQL

DeviceEvents
| where TimeGenerated > ago(1d)
| where ActionType in ("ServiceInstalled", "ScheduledTaskCreated")
| project Timestamp, DeviceName, AccountName, ActionType, AdditionalFields

Credential dumping indicators - LSASS access

KQL

DeviceEvents
| where TimeGenerated > ago(1d)
| where ActionType == "CreateRemoteThreadApiCall"
| where FileName =~ "lsass.exe"
| project Timestamp, DeviceName, InitiatingProcessFileName, InitiatingProcessCommandLine

See also: Security - nmap, netcat, and post-exploitation reference for host-level investigation once a suspicious process is identified.

Threat Hunting - Network 🔬

Connections to uncommon ports (potential C2 or exfiltration)

KQL

DeviceNetworkEvents
| where TimeGenerated > ago(1d)
| where RemotePort !in (80, 443, 53, 22, 25, 587, 465, 8080, 8443)
| where RemoteIPType == "Public"
| summarize Count = count() by DeviceName, RemoteIP, RemotePort, InitiatingProcessFileName
| where Count < 5  // low count = potentially unusual, not noisy
| sort by Count asc

Beaconing detection - regular periodic outbound connections

KQL

DeviceNetworkEvents
| where TimeGenerated > ago(24h)
| where RemoteIPType == "Public"
| sort by DeviceName asc, RemoteIP asc, RemotePort asc, TimeGenerated asc
| serialize
| extend Interval   = TimeGenerated - prev(TimeGenerated),
         PrevDevice = prev(DeviceName),
         PrevIP     = prev(RemoteIP),
         PrevPort   = prev(RemotePort)
| where PrevDevice == DeviceName and PrevIP == RemoteIP and PrevPort == RemotePort
| summarize
    ConnectionCount = count(),
    AvgInterval     = avg(Interval),
    StdDevInterval  = stdev(Interval / 1s)  // stdev of timespan returns real (seconds)
  by DeviceName, RemoteIP, RemotePort
| where ConnectionCount > 20
| where StdDevInterval < 30  // very regular = suspicious (threshold in seconds)

DNS over HTTPS / large DNS responses (tunnelling)

KQL

DeviceNetworkEvents
| where TimeGenerated > ago(1d)
| where RemotePort == 443
| where RemoteIPType == "Public"
| summarize
    BytesSent     = sum(SentBytes),
    BytesReceived = sum(ReceivedBytes),
    Count         = count()
  by DeviceName, RemoteIP, InitiatingProcessFileName
| where BytesSent > 10000000  // >10 MB sent to a single IP

Lateral movement - SMB/RDP/WinRM to internal hosts

KQL

DeviceNetworkEvents
| where TimeGenerated > ago(1d)
| where RemotePort in (445, 3389, 5985, 5986)
| where RemoteIPType == "Private"
| summarize Count = count() by DeviceName, RemoteIP, RemotePort, InitiatingProcessFileName
| sort by Count desc

Connections to TI-matched IPs

KQL

ThreatIntelligenceIndicator
| where TimeGenerated > ago(14d)
| where isnotempty(NetworkIP)
| join kind=inner (
    DeviceNetworkEvents
    | where TimeGenerated > ago(1d)
  ) on $left.NetworkIP == $right.RemoteIP
| project Timestamp, DeviceName, RemoteIP, RemotePort, ThreatType, ConfidenceScore, InitiatingProcessFileName

Threat Hunting - Identity & Authentication 🔬

Failed logins - brute-force or password spray

KQL

SigninLogs
| where TimeGenerated > ago(1d)
| where ResultType != "0"  // 0 = success
| summarize
    Failures    = count(),
    UniqueUsers = dcount(UserPrincipalName),
    UniqueIPs   = dcount(IPAddress)
  by IPAddress, AppDisplayName
| where Failures > 50
| sort by Failures desc

Password spray pattern - one IP, many users, few attempts each

KQL

SigninLogs
| where TimeGenerated > ago(1d)
| where ResultType != "0"
| summarize
    FailedUsers    = dcount(UserPrincipalName),
    TotalAttempts  = count()
  by IPAddress
| where FailedUsers > 20 and TotalAttempts < FailedUsers * 3

Impossible travel - same user, two locations within short window

KQL

SigninLogs
| where TimeGenerated > ago(1d)
| where ResultType == "0"
| summarize
    Locations = make_set(Location),
    IPs       = make_set(IPAddress),
    LogonTimes = make_list(TimeGenerated)
  by UserPrincipalName
| where array_length(Locations) > 1

MFA fatigue - many MFA prompts in a short period

KQL

SigninLogs
| where TimeGenerated > ago(1h)
| where AuthenticationRequirement == "multiFactorAuthentication"
| where ResultType in ("50074", "50076", "500121")  // MFA denied or timed out
| summarize MFADenials = count() by UserPrincipalName, IPAddress
| where MFADenials > 10

Legacy authentication protocols (no MFA support)

KQL

SigninLogs
| where TimeGenerated > ago(7d)
| where ClientAppUsed in ("Exchange ActiveSync", "IMAP4", "POP3", "SMTP Auth", "Other clients")
| summarize Count = count() by UserPrincipalName, ClientAppUsed, IPAddress
| sort by Count desc

Windows failed logons (EventID 4625) - workstation

KQL

SecurityEvent
| where TimeGenerated > ago(1d)
| where EventID == 4625
| summarize Failures = count() by TargetAccount, IpAddress, LogonType
| where Failures > 10
| sort by Failures desc

Account created outside business hours

KQL

AuditLogs
| where TimeGenerated > ago(7d)
| where OperationName == "Add user"
| extend Hour = hourofday(TimeGenerated)
| where Hour !between (8 .. 18)
| project TimeGenerated, InitiatedBy, TargetResources

Admin role assignments

KQL

AuditLogs
| where TimeGenerated > ago(7d)
| where OperationName in ("Add member to role", "Add eligible member to role")
| extend Actor  = tostring(InitiatedBy.user.userPrincipalName)
| extend Target = tostring(TargetResources[0].displayName)
| extend Role   = tostring(TargetResources[0].modifiedProperties[0].newValue)
| project TimeGenerated, Actor, Target, Role

See also: Azure - Entra ID for managing users, roles, and Conditional Access policies. PowerShell - Microsoft Sentinel for bulk watchlist operations and incident automation.

Threat Hunting - Email 🔬

Emails with malicious verdicts delivered to inbox

KQL

EmailEvents
| where TimeGenerated > ago(7d)
| where ThreatTypes has_any ("Malware", "Phish", "High confidence phish")
| where DeliveryAction == "Delivered"
| project Timestamp, SenderFromAddress, RecipientEmailAddress, Subject, ThreatTypes, UrlCount, AttachmentCount

Phishing links clicked by users

KQL

UrlClickEvents
| where TimeGenerated > ago(7d)
| where ActionType == "ClickAllowed"
| where ThreatTypes has "Phish"
| project Timestamp, AccountUpn, Url, IsClickedThrough, IPAddress

Malicious attachments - by file type

KQL

EmailAttachmentInfo
| where TimeGenerated > ago(7d)
| where ThreatTypes has "Malware"
| extend Extension = tostring(split(FileName, ".")[-1])
| summarize Count = count() by Extension, ThreatTypes
| sort by Count desc

Bulk mail from a single sender (potential compromise)

KQL

EmailEvents
| where TimeGenerated > ago(1d)
| where SenderFromDomain !endswith "yourdomain.com"
| summarize Count = count() by SenderFromAddress, SenderFromDomain
| where Count > 100
| sort by Count desc

Threat Hunting - Azure Activity 🔬

Mass resource deletion or modification

KQL

AzureActivity
| where TimeGenerated > ago(1d)
| where ActivityStatusValue == "Success"
| where OperationNameValue has_any ("delete", "deallocate", "stop")
| summarize
    OperationCount = count(),
    Operations     = make_set(OperationNameValue, 20)
  by Caller, CallerIpAddress
| where OperationCount > 20
| sort by OperationCount desc

Privilege escalation - role assignment writes

KQL

AzureActivity
| where TimeGenerated > ago(7d)
| where OperationNameValue =~ "Microsoft.Authorization/roleAssignments/write"
| where ActivityStatusValue == "Success"
| extend Props = todynamic(Properties)
| project TimeGenerated, Caller, CallerIpAddress, ResourceGroup, SubscriptionId,
          RoleDefinitionId = tostring(Props.requestbody)

Security control changes (policy, NSG, Defender)

KQL

AzureActivity
| where TimeGenerated > ago(7d)
| where OperationNameValue has_any (
    "Microsoft.Security/",
    "Microsoft.Authorization/policyAssignments",
    "Microsoft.Network/networkSecurityGroups"
  )
| where ActivityStatusValue == "Success"
| project TimeGenerated, Caller, CallerIpAddress, OperationNameValue, ResourceGroup
| sort by TimeGenerated desc

Failed ARM operations by caller (misconfiguration or denial pattern)

KQL

AzureActivity
| where TimeGenerated > ago(1d)
| where ActivityStatusValue == "Failed"
| summarize
    FailureCount = count(),
    Operations   = make_set(OperationNameValue, 10)
  by Caller, CallerIpAddress, ResourceGroup
| where FailureCount > 10
| sort by FailureCount desc

Key Vault secret access audit

KQL

AzureDiagnostics
| where TimeGenerated > ago(1d)
| where ResourceType == "VAULTS"
| where OperationName in ("SecretGet", "SecretList", "KeyGet", "KeyDecrypt")
| where ResultType == "Success"
| project TimeGenerated,
          Identity   = identity_claim_oid_g,
          Operation  = OperationName,
          SecretId   = id_s,
          CallerIP   = CallerIPAddress
| sort by TimeGenerated desc

Activity from a new or unexpected caller IP

KQL

AzureActivity
| where TimeGenerated > ago(30d)
| where ActivityStatusValue == "Success"
| summarize
    FirstSeen = min(TimeGenerated),
    LastSeen  = max(TimeGenerated),
    OpCount   = count()
  by Caller, CallerIpAddress
| where FirstSeen > ago(2d)   // IP not seen before the last 2 days
| sort by FirstSeen desc

See also: Security - Incident Response for host-level triage commands once a suspicious caller is identified.

Alerts & Incidents

All active incidents by severity

KQL

SecurityIncident
| where TimeGenerated > ago(7d)
| where Status != "Closed"
| summarize Count = count() by Severity, Classification
| sort by Count desc

Incidents with the most alerts

KQL

SecurityIncident
| where TimeGenerated > ago(30d)
| extend AlertCount = array_length(AlertIds)
| sort by AlertCount desc
| project TimeGenerated, Title, Severity, Status, AlertCount, Owner
| take 20

Unassigned high/medium incidents

KQL

SecurityIncident
| where TimeGenerated > ago(7d)
| where Status == "New"
| where Severity in ("High", "Medium")
| where isnull(Owner) or isempty(tostring(Owner.assignedTo))
| project TimeGenerated, Title, Severity, AlertIds

All alerts for a specific device

KQL

SecurityAlert
| where TimeGenerated > ago(30d)
| extend Entities = todynamic(Entities)
| mv-expand Entity = Entities
| where Entity.Type == "host"
| where tolower(tostring(Entity.HostName)) contains "device-name-here"
| project TimeGenerated, AlertName, Severity, Description

Alert volume trend by provider

KQL

SecurityAlert
| where TimeGenerated > ago(30d)
| summarize Count = count() by bin(TimeGenerated, 1d), ProductName
| render timechart

Entities linked to a specific alert name

KQL

AlertEvidence
| where TimeGenerated > ago(7d)
| where AlertId in (
    SecurityAlert
    | where AlertName contains "Brute Force"
    | project SystemAlertId
  )
| project Timestamp, AlertId, EntityType, EvidenceRole, RemoteIP, AccountName, DeviceName

Useful Patterns

`mv-expand` - expand an array column into individual rows

KQL

SecurityIncident
| mv-expand AlertIds
| extend AlertId = tostring(AlertIds)
| join kind=inner SecurityAlert on $left.AlertId == $right.SystemAlertId
| project IncidentTitle = Title, AlertName, Severity

`parse_json` - read dynamic/JSON columns

KQL

DeviceEvents
| where ActionType == "ScheduledTaskCreated"
| extend TaskDetails = parse_json(AdditionalFields)
| extend TaskName    = tostring(TaskDetails.TaskName)
| extend TaskAction  = tostring(TaskDetails.TaskAction)
| project Timestamp, DeviceName, TaskName, TaskAction

`bag_keys` - discover all keys in a dynamic field

KQL

SigninLogs
| take 1
| extend Keys = bag_keys(todynamic(DeviceDetail))

`parse_url` - extract parts of a URL

KQL

DeviceNetworkEvents
| extend Parsed   = parse_url(RemoteUrl)
| extend Hostname = tostring(Parsed.Host)
| extend Path     = tostring(Parsed.Path)
| extend Scheme   = tostring(Parsed.Scheme)

`ipv4_is_private` - filter public vs private IPs

KQL

DeviceNetworkEvents
| where not(ipv4_is_private(RemoteIP))
| where RemoteIP != "127.0.0.1"

`geo_info_from_ip_address` - enrich with GeoIP (Sentinel)

KQL

SigninLogs
| extend GeoInfo  = geo_info_from_ip_address(IPAddress)
| extend Country  = tostring(GeoInfo.country)
| extend City     = tostring(GeoInfo.city)
| where Country !in ("United Kingdom", "United States")

`externaldata` - load a blocklist from blob storage

KQL

let BlockedDomains = externaldata(Domain: string)
    [@"https://<storage>.blob.core.windows.net/<container>/blocklist.txt"]
    with (format="txt", ignoreFirstRecord=false);
DeviceNetworkEvents
| where TimeGenerated > ago(1d)
| where RemoteUrl has_any (BlockedDomains)

Watchlist lookup (Sentinel)

KQL

let WatchlistIPs = _GetWatchlist("MaliciousIPs") | project SearchKey;
DeviceNetworkEvents
| where TimeGenerated > ago(1d)
| where RemoteIP in (WatchlistIPs)

Cross-workspace queries (Sentinel multi-workspace)

KQL

// Query a named workspace by resource ID or alias
workspace("secondary-workspace").SecurityEvent
| where TimeGenerated > ago(1h)
| where EventID == 4625
 
// Union across multiple workspaces (multi-tenant / MSSPs)
union
    workspace("workspace-emea").SigninLogs,
    workspace("workspace-apac").SigninLogs
| where TimeGenerated > ago(1h)
| summarize Failures = countif(ResultType != "0") by UserPrincipalName
 
// Cross-cluster query (ADX)
cluster("mycluster.westeurope").database("mydb").MyTable
| take 10

Persisted functions (save reusable KQL)

KQL

// Define once in ADX / Sentinel workspace as a saved function:
// Name:       GetSuspiciousProcesses
// Parameters: lookback:timespan = 1d
DeviceProcessEvents
| where TimeGenerated > ago(lookback)
| where FileName in~ ("mimikatz.exe", "psexec.exe", "nc.exe", "meterpreter.exe")
| project Timestamp, DeviceName, AccountName, FileName, ProcessCommandLine
 
// Invoke it later (lookback is optional - defaults to 1d)
GetSuspiciousProcesses(7d)

`arg()` - query Azure Resource Graph from Log Analytics

KQL

// Correlate log data with live resource metadata
let VMs = arg("").Resources
    | where type =~ "microsoft.compute/virtualmachines"
    | project vmId = tolower(id), tags, location, sku = properties.hardwareProfile.vmSize;
DeviceNetworkEvents
| where TimeGenerated > ago(1h)
| extend vmId = tolower(DeviceId)
| join kind=leftouter VMs on vmId
| project Timestamp, DeviceName, RemoteIP, location, sku

Render options

KQL

| render timechart    // line chart over time
| render barchart     // bar chart
| render piechart     // pie chart
| render table        // explicit table (default)
| render scatterchart // scatter plot (good for anomalies)

Sentinel Analytic Rules

Analytic rules run as scheduled queries. These patterns produce low-noise, actionable alerts.

Rule query structure

KQL

// 1. Define constants at the top with let - makes tuning easy
let LookbackPeriod = 1h;
let FailThreshold  = 10;
let ExcludedAccounts = dynamic(["health-check", "monitoring-svc"]);
 
// 2. Filter aggressively early - reduces cost and latency
SecurityEvent
| where TimeGenerated > ago(LookbackPeriod)
| where EventID == 4625
| where TargetAccount !in (ExcludedAccounts)
 
// 3. Aggregate to get an entity-level signal (not per-event noise)
| summarize
    FailCount = count(),
    FirstSeen = min(TimeGenerated),
    LastSeen  = max(TimeGenerated),
    SourceIPs = make_set(IpAddress, 10)
  by TargetAccount, Computer
| where FailCount > FailThreshold
 
// 4. Project only the columns needed for entity mapping
| project TargetAccount, Computer, FailCount, FirstSeen, LastSeen, SourceIPs

Suppress known-good activity with a watchlist

KQL

let TrustedIPs   = _GetWatchlist("TrustedRanges") | project SearchKey;
let SvcAccounts  = _GetWatchlist("ServiceAccounts") | project SearchKey;
DeviceNetworkEvents
| where TimeGenerated > ago(1h)
| where RemoteIPType == "Public"
| where RemoteIP !in (TrustedIPs)
| where InitiatingProcessAccountName !in (SvcAccounts)
| where RemotePort !in (80, 443)

Correlate events with threat intelligence

KQL

let TIIndicators =
    ThreatIntelligenceIndicator
    | where TimeGenerated > ago(14d)
    | where isnotempty(NetworkIP)
    | where ConfidenceScore > 50
    | summarize by NetworkIP;
DeviceNetworkEvents
| where TimeGenerated > ago(1h)
| join kind=inner TIIndicators on $left.RemoteIP == $right.NetworkIP
| project Timestamp, DeviceName, RemoteIP, RemotePort, InitiatingProcessFileName

Multi-stage correlation (alert chaining)

KQL

// Stage 1: suspicious recon
let ReconDevices =
    DeviceProcessEvents
    | where TimeGenerated > ago(30m)
    | where FileName in~ ("whoami.exe", "ipconfig.exe", "net.exe", "nltest.exe")
    | summarize ReconCount = count() by DeviceId, DeviceName
    | where ReconCount > 5;
// Stage 2: lateral movement from those same devices
DeviceNetworkEvents
| where TimeGenerated > ago(1h)
| where RemotePort in (445, 3389, 5985)
| join kind=inner ReconDevices on DeviceId
| project Timestamp, DeviceName, RemoteIP, RemotePort, ReconCount

See also: PowerShell - Microsoft Sentinel for automation rules, watchlist management, and incident enrichment via the Sentinel REST API.

Anti-patterns

⚠️ No time filter on queries - a query without | where TimeGenerated > ago(...) scans the entire table (potentially months of data), is extremely slow, and can exhaust query limits. Always scope the time range first.
🔬 contains over has for whole-word matches - contains does a character-level substring scan; has uses the inverted index and is orders of magnitude faster for whole-token matching. Use has for single words and has_any for sets.
🔬 take N as a “sample” - take returns arbitrary rows in no guaranteed order; it is not a random or representative sample. Use sample N for random sampling or top N by for intentional ranking.
🚨 String concatenation to build KQL - building query strings by concatenating user input enables KQL injection. Use declare query_parameters with typed parameters for any dynamic values.
⚠️ Joining two large unfiltered tables - joining unfiltered high-volume tables can produce enormous intermediate datasets and time out. Filter both sides with time and column predicates before the join.
🔬 mv-expand on a high-cardinality array without subsequent scoping - mv-expand on a large array column multiplies row count dramatically. Always add a where, take, or top after mv-expand to bound the result set.