Kusto Query Language (KQL)

What KQL Is and Where You Use It

KQL is a read-only query language designed for large-scale structured and semi-structured data. Unlike Splunk SPL (which is search-first), KQL is table-operator-first — each query starts with a table name and pipes (|) through operators.

You will encounter KQL in these Microsoft security tools:

Core KQL Operators — The 80/20

Pipe Syntax — How KQL Queries Flow

Every KQL query follows the same pattern:

TableName
| operator1 parameter1, parameter2
| operator2 parameter1
| project column1, column2
| take 10

The pipe (|) passes the result of one operator to the next. This is the same piped workflow as Splunk SPL.

Time Operators and Filters

Time Filter Syntax

TableName
| where TimeGenerated > ago(24h)
| where TimeGenerated between (datetime(2026-05-01) .. datetime(2026-05-07))

ago() Offsets

bin() — Time Binning

// Group events into 1-hour buckets
SecurityEvent
| where TimeGenerated > ago(24h)
| summarize FailedLogins = count() by bin(TimeGenerated, 1h), TargetAccount
| order by FailedLogins desc

Time Chart

SecurityEvent
| where TimeGenerated > ago(7d)
| where EventID in (4625, 4624)
| summarize Events = count() by bin(TimeGenerated, 4h), EventID
| render timechart

let Statements — Variables and Functions

let is one of KQL’s most powerful features. It lets you define variables, functions, and named expressions.

Simple Variable

let threshold = 10;
let timeWindow = 1h;
SecurityEvent
| where TimeGenerated > ago(timeWindow)
| summarize count() by Account
| where count_ > threshold

Named Function

// Define a reusable function
let FailedLogins = (days:int = 7) {
    SecurityEvent
    | where TimeGenerated > ago(days * 1d)
    | where EventID == 4625
    | summarize FailedCount = count() by Account, IPAddress = IpAddress
};
// Use the function
FailedLogins(30)
| where FailedCount > 100

Multi-Statement Queries

// Define a lookup table inline
let KnownMaliciousIPs = datatable(IP:string, Threat:string, Severity:string)[
    "185.220.101.0", "C2_Infrastructure", "HIGH",
    "91.121.86.0",   "C2_Infrastructure", "HIGH",
    "5.188.62.0",    "CobaltStrike_Beacon", "CRITICAL"
];
// Use the lookup
SecurityEvent
| where TimeGenerated > ago(1h)
| join kind=inner KnownMaliciousIPs on $left.IpAddress == $right.IP
| project TimeGenerated, Account, IpAddress, Threat, Severity

Joins — Combining Multiple Data Sources

Join Types

Join Example — Correlating Logons with Process Creation

// Find all users who successfully logged on and then ran PowerShell
let SuccessfulLogons = SecurityEvent
    | where TimeGenerated > ago(1h)
    | where EventID == 4624
    | project TimeGenerated, Account, Computer, LogonId;
let PowerShellEvents = SecurityEvent
    | where TimeGenerated > ago(1h)
    | where EventID == 4688
    | where ProcessName contains "powershell"
    | project TimeGenerated, Account, Computer, CommandLine, LogonId;
SuccessfulLogons
| join kind=inner (
    PowerShellEvents
) on LogonId
| project-away LogonId
| order by TimeGenerated desc

Common Security Queries in KQL

1. Failed Logins — Top Sources

SecurityEvent
| where TimeGenerated > ago(1d)
| where EventID == 4625
| summarize FailedAttempts = count() by Account, IpAddress
| order by FailedAttempts desc
| take 20

2. Brute Force Detection (see Sigma Rules for equivalent detection logic)

let threshold = 10;
let timeWindow = 5m;
SecurityEvent
| where TimeGenerated > ago(1h)
| where EventID == 4625
| summarize FailedAttempts = count() by Account, IpAddress, bin(TimeGenerated, timeWindow)
| where FailedAttempts > threshold
| project TimeGenerated = bin(TimeGenerated, timeWindow), Account, IpAddress, FailedAttempts
| order by FailedAttempts desc

3. Suspicious PowerShell

SecurityEvent
| where TimeGenerated > ago(1d)
| where EventID == 4688
| where CommandLine contains "powershell"
| where CommandLine contains "-EncodedCommand" or CommandLine contains "Invoke-" or CommandLine contains "DownloadString" or CommandLine contains "IEX"
| project TimeGenerated, Computer, Account, CommandLine, NewProcessName
| order by TimeGenerated desc

4. New Account Creation (enrich with Sysmon Event ID 1 for process ancestry)

SecurityEvent
| where TimeGenerated > ago(7d)
| where EventID == 4720
| project TimeGenerated, TargetAccount, Account, Computer
| order by TimeGenerated desc

5. Admin Group Modifications

SecurityEvent
| where TimeGenerated > ago(7d)
| where EventID in (4728, 4732, 4746, 4751, 4756)
| project TimeGenerated, Account, TargetAccount, Computer, EventID
| order by TimeGenerated desc

6. Audit Policy Changes (Logging Disabled)

SecurityEvent
| where TimeGenerated > ago(7d)
| where EventID == 4719
| project TimeGenerated, Account, Computer, AuditPolicyChanges
| order by TimeGenerated desc

7. Network Connection Monitoring (Event 5156)

SecurityEvent
| where TimeGenerated > ago(4h)
| where EventID == 5156
| summarize Connections = count() by Application, SourceAddress, DestAddress, DestPort, Protocol
| where DestPort !in (53, 80, 443, 3389)
| order by Connections desc

8. Windows Defender Detections

DeviceEvents
| where TimeGenerated > ago(7d)
| where ActionType == "WindowsDefenderDetection"
| project TimeGenerated, DeviceName, ThreatName, Severity, FileName
| order by TimeGenerated desc

Microsoft Sentinel-Specific Queries

Hunting Query — Failed Logins Cross-Computer

let FailedLogons = SecurityEvent
    | where TimeGenerated > ago(1d)
    | where EventID == 4625
    | summarize FailedCount = count() by Account, Computer, IpAddress;
let SuccessfulLogons = SecurityEvent
    | where TimeGenerated > ago(1d)
    | where EventID == 4624
    | distinct Account, Computer;
FailedLogons
| join kind=leftanti SuccessfulLogons on Account, Computer
| where FailedCount > 5
| project Account, Computer, IpAddress, FailedCount
| order by FailedCount desc

Analytics Rule Query — Multiple Failed Logins Then Success

// Identify brute force success — failed logins followed by a successful login
let timeWindow = 15m;
let threshold = 5;
let FailedLogins = SecurityEvent
    | where TimeGenerated > ago(30m)
    | where EventID == 4625
    | summarize FailedCount = count() by Account, Computer, bin(TimeGenerated, timeWindow);
let SuccessfulLogins = SecurityEvent
    | where TimeGenerated > ago(30m)
    | where EventID == 4624
    | where Account !endswith "$"
    | distinct Account, Computer;
FailedLogins
| where FailedCount > threshold
| join kind=inner SuccessfulLogins on Account, Computer
| project Computer, Account, FailedCount, TimeGenerated = bin(TimeGenerated, timeWindow)
| order by TimeGenerated desc

Sentinel Watchlist Lookup

let watchlist = (_GetWatchlist('HighValueUsers')
    | project Account = SearchKey);
SecurityEvent
| where TimeGenerated > ago(1d)
| where EventID == 4688
| where Account in (watchlist)
| project TimeGenerated, Account, Computer, ProcessName, CommandLine
| order by TimeGenerated desc

Performance Tips

KQL vs. SPL — Quick Reference

Related

• Azure Sentinel — detection and response for T1654 techniques
• CyberChef — detection and response for T1654 techniques
• Log Sources Overview — covers the log sources overview concepts

Sources

• Kusto Query Language Documentation — Microsoft
• Microsoft Sentinel Advanced Hunting
• KQL Quick Reference Card
• MITRE ATT&CK T1654