kql-writing

KQL Writing Guide — Microsoft Sentinel

KQL (Kusto Query Language) is used in Microsoft Sentinel, Microsoft Defender, and Azure Data Explorer for querying security logs and building detection rules.

Core Syntax

Table references

SecurityEvent                        // Windows Security Events
DeviceProcessEvents                  // Defender for Endpoint
DeviceNetworkEvents                  // Network connections
DeviceFileEvents                     // File operations
EmailEvents                         // Defender for Office 365
SigninLogs                           // Azure AD sign-ins
AuditLogs                           // Azure AD audit
CommonSecurityLog                   // CEF/Syslog
ThreatIntelligenceIndicator         // TI feed indicators

Operators

| where TimeGenerated > ago(24h)     // Time filter
| where EventID == 4688              // Exact match
| where ProcessCommandLine contains "-enc"  // Substring
| where ProcessCommandLine matches regex @".*-e(nc)?.*"  // Regex
| where SourceIP !in ("10.0.0.1", "10.0.0.2")  // Not in list
| where isnotempty(AccountName)       // Not null/empty
| extend NewColumn = extract(@"pattern", 1, SourceField)  // Extract
| project TimeGenerated, Account, Computer  // Select columns
| summarize count() by bin(TimeGenerated, 1h), Account  // Aggregate
| sort by TimeGenerated desc          // Sort
| take 100                            // Limit results
| join kind=inner (OtherTable) on CommonField  // Join

String operators

Common Detection Patterns

Suspicious process execution (T1059)

DeviceProcessEvents
| where TimeGenerated > ago(24h)
| where FileName in~ ("powershell.exe", "cmd.exe", "wscript.exe", "cscript.exe")
| where ProcessCommandLine contains_cs "-enc"
    or ProcessCommandLine contains "bypass"
    or ProcessCommandLine contains "downloadstring"
| project TimeGenerated, DeviceName, AccountName, FileName, ProcessCommandLine, InitiatingProcessFileName

Suspicious parent-child (T1566.001)

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

Outbound connection to IOC (C2)

let IOC_IPs = dynamic(["203.0.113.42", "198.51.100.10"]);
let IOC_Domains = dynamic(["evil.example.com", "c2.badactor.net"]);
DeviceNetworkEvents
| where TimeGenerated > ago(7d)
| where RemoteIP in (IOC_IPs) or RemoteUrl has_any (IOC_Domains)
| project TimeGenerated, DeviceName, RemoteIP, RemoteUrl, RemotePort, InitiatingProcessFileName

Failed sign-in brute force (T1110)

SigninLogs
| where TimeGenerated > ago(24h)
| where ResultType != "0"
| summarize FailedAttempts = count(), DistinctAccounts = dcount(UserPrincipalName)
    by IPAddress, bin(TimeGenerated, 15m)
| where FailedAttempts > 10
| sort by FailedAttempts desc

Lateral movement — PsExec (T1570)

DeviceProcessEvents
| where TimeGenerated > ago(24h)
| where FileName =~ "psexesvc.exe"
    or (FileName =~ "cmd.exe" and ProcessCommandLine contains "\\\\")
    or ProcessCommandLine contains "-accepteula -s cmd"
| project TimeGenerated, DeviceName, AccountName, FileName, ProcessCommandLine

DNS query for suspicious domain (T1071.004)

DeviceEvents
| where TimeGenerated > ago(24h)
| where ActionType == "DnsQueryResponse"
| extend DnsQuery = extractjson("$.DnsQueryString", AdditionalFields)
| where DnsQuery endswith ".onion.ws"
    or DnsQuery endswith ".tor2web.io"
    or DnsQuery matches regex @"^[a-z0-9]{20,}\."
| project TimeGenerated, DeviceName, DnsQuery

Sentinel Analytics Rule Format

// Rule name: [Descriptive title]
// Description: [What this detects and why]
// MITRE ATT&CK: [Technique IDs]
// Severity: High|Medium|Low|Informational
// Tactics: [InitialAccess, Execution, etc.]

// Query:
[KQL query here]

Best Practices

• Always include a time filter (ago()) to limit query scope
• Use has instead of contains when searching for whole words (faster)
• Prefer in~ over multiple or conditions for case-insensitive list matching
• Use let statements for IOC lists to keep queries readable
• Add project to select only needed columns (reduces result size)
• Test queries on small time windows first before expanding
• Include comments explaining detection logic

Running against a live workspace

This skill authors queries. To run them against a real Microsoft Sentinel workspace — or to hunt interactively — chain /lookup-sentinel. Two rules apply the moment a query targets live data:

1. Verify table availability first. The table references in this guide (and in any published hunting query) assume connectors the target environment may not have. /lookup-sentinel discovers which tables the workspace actually ingests (tables / ingestion / probe) and adapts — e.g. no DeviceProcessEvents (no Defender for Endpoint) means falling back to SecurityEvent EventID 4688, which itself requires command-line auditing. Never ship a hunt referencing unverified tables.
2. A query that runs is not a query that saw. When a fallback table has weaker fidelity (or the preferred table is absent entirely), state what the environment could not observe alongside the results.

For portable analytics rules meant for any environment, keep the canonical table names from this guide and document the connector prerequisite in the rule header comment.

Output Location

Write KQL queries to: data/detection-rules/kql/<technique-id>-<slug>.kql