KQL Fundamentals

Operators

Operator Purpose Example

Operator	Purpose	Example
`where`	Filter rows	`where TimeGenerated > ago(1h)`
`project`	Select columns	`project TimeGenerated, UserPrincipalName, ResultType`
`extend`	Add computed column	`extend Duration = EndTime - StartTime`
`summarize`	Aggregate	`summarize count() by UserPrincipalName`
`sort by`	Order results	`sort by TimeGenerated desc`
`take` / `limit`	Limit rows	`take 10`
`join`	Combine tables	`join kind=inner (Table2) on CommonColumn`
`union`	Combine tables vertically	`union SecurityEvent, Syslog`
`render`	Visualize	`render timechart`
`let`	Declare variable	`let threshold = 10;`
`parse`	Extract fields from text	`parse Message with * "user=" User " "*`
`mv-expand`	Expand array to rows	`mv-expand IPAddresses`

where

Filter rows

where TimeGenerated > ago(1h)

project

Select columns

project TimeGenerated, UserPrincipalName, ResultType

extend

Add computed column

extend Duration = EndTime - StartTime

summarize

Aggregate

summarize count() by UserPrincipalName

sort by

Order results

sort by TimeGenerated desc

take / limit

Limit rows

take 10

join

Combine tables

join kind=inner (Table2) on CommonColumn

union

Combine tables vertically

union SecurityEvent, Syslog

render

Visualize

render timechart

let

Declare variable

let threshold = 10;

parse

Extract fields from text

parse Message with * "user=" User " "*

mv-expand

Expand array to rows

mv-expand IPAddresses

Time Filters

// Relative time
| where TimeGenerated > ago(24h)
| where TimeGenerated > ago(7d)
| where TimeGenerated > ago(30m)

// Absolute range
| where TimeGenerated between (datetime(2026-04-01) .. datetime(2026-04-02))

// Business hours only
| where hourofday(TimeGenerated) between (8 .. 17)

// Weekdays only
| where dayofweek(TimeGenerated) between (1d .. 5d)

Aggregation Patterns

// Count by field
| summarize count() by UserPrincipalName

// Count with time bins
| summarize count() by bin(TimeGenerated, 1h)

// Multiple aggregations
| summarize
    TotalEvents = count(),
    UniqueUsers = dcount(UserPrincipalName),
    FirstSeen = min(TimeGenerated),
    LastSeen = max(TimeGenerated)
    by IPAddress

// Top N
| summarize count() by UserPrincipalName
| top 10 by count_

String Operations

// Contains (case-insensitive)
| where UserPrincipalName contains "admin"

// Exact match
| where ResultType == "0"

// Starts with
| where UserPrincipalName startswith "svc-"

// Regex match
| where UserPrincipalName matches regex @"admin\d+"

// Not contains
| where UserPrincipalName !contains "test"

// In list
| where ResultType in ("50126", "50053", "53003")

Join Patterns

// Inner join — sign-in failures with user details
SigninLogs
| where ResultType != "0"
| join kind=inner (
    IdentityInfo
    | project UserPrincipalName, Department, JobTitle
) on UserPrincipalName

// Leftanti — users who NEVER signed in
IdentityInfo
| join kind=leftanti (
    SigninLogs
    | where TimeGenerated > ago(30d)
) on UserPrincipalName

// Time-window join — events within 5 minutes of each other
Table1
| join kind=inner (Table2) on $left.IP == $right.IP
| where abs(datetime_diff('minute', TimeGenerated, TimeGenerated1)) < 5

Let Statements

// Reusable variables
let lookback = 24h;
let threshold = 5;
let suspiciousIPs = dynamic(["1.2.3.4", "5.6.7.8"]);

SigninLogs
| where TimeGenerated > ago(lookback)
| where IPAddress in (suspiciousIPs)
| summarize FailureCount = countif(ResultType != "0") by UserPrincipalName
| where FailureCount > threshold

Output & Visualization

// Time chart
| summarize count() by bin(TimeGenerated, 1h)
| render timechart

// Bar chart
| summarize count() by UserPrincipalName
| top 10 by count_
| render barchart

// Pie chart
| summarize count() by ResultType
| render piechart