/kql

KQL Query Templates

Reusable query patterns for common data investigation operations. Copy and adapt these.

Table of Contents

1. Deduplication
2. Top-N Analysis
3. Time Binning & Trends
4. Pivoting
5. Running Totals & Window Functions
6. Sessionization
7. Cardinality Check Before Join
8. Safe Exploration Pattern
9. Diffing & Anomaly Detection
10. String Extraction Patterns

1. Deduplication

Exact deduplication

// Remove exact duplicate rows
Table
| distinct *

Dedup keeping latest per key

// Keep the most recent record per entity
Table
| summarize arg_max(Timestamp, *) by EntityId

Dedup keeping first per key

// Keep the earliest record per entity
Table
| summarize arg_min(Timestamp, *) by EntityId

Count duplicates

// Find records that appear more than once
Table
| summarize cnt = count() by Col1, Col2, Col3
| where cnt > 1
| order by cnt desc

2. Top-N Analysis

Top N by count

Table
| summarize EventCount = count() by Category
| top 10 by EventCount desc

Top N by metric, with ties

Table
| summarize TotalRevenue = sum(Amount) by Customer
| order by TotalRevenue desc
| take 10

Top N per group

// Top 3 events per category
Table
| summarize EventCount = count() by Category, EventType
| partition by Category (top 3 by EventCount desc)

Bottom N (least common)

Table
| summarize cnt = count() by Category
| top 10 by cnt asc

3. Time Binning & Trends

Hourly event counts

Events
| summarize count() by bin(Timestamp, 1h)
| order by Timestamp asc

Day-over-day comparison

Events
| where Timestamp > ago(14d)
| summarize count() by Day = bin(Timestamp, 1d)
| order by Day asc
| extend PrevDayCount = prev(count_)
| extend DayOverDayChange = count_ - PrevDayCount
| serialize  // needed for prev()

Activity by hour of day

Events
| extend HourOfDay = datetime_part("hour", Timestamp)
| summarize count() by HourOfDay
| order by HourOfDay asc

Activity by day of week

Events
| extend DayOfWeek = dayofweek(Timestamp) / 1d
| summarize count() by DayOfWeek
| order by DayOfWeek asc

4. Pivoting

Counts by category (pivot)

Events
| evaluate pivot(Category, count(), bin(Timestamp, 1d))

Manual pivot with summarize

Events
| summarize
    TypeA = countif(Type == "A"),
    TypeB = countif(Type == "B"),
    TypeC = countif(Type == "C")
    by bin(Timestamp, 1h)

5. Running Totals & Window Functions

Running total

Table
| order by Timestamp asc
| extend RunningTotal = row_cumsum(Value)

Row numbers

Table
| order by Score desc
| extend Rank = row_number()

Previous/next row comparison

Table
| order by Timestamp asc
| extend PrevValue = prev(Value), NextValue = next(Value)
| extend Delta = Value - PrevValue

Remember: All these require serialized input — use | order by or | serialize before calling.

6. Sessionization

Gap-based sessions

// Group events into sessions (30-min idle gap)
Events
| order by UserId, Timestamp asc
| extend SessionStart = row_window_session(Timestamp, 30m, 24h, UserId != prev(UserId))
| summarize
    SessionStart = min(Timestamp),
    SessionEnd = max(Timestamp),
    EventCount = count()
    by UserId, SessionStart

Sequential numbering within groups

Events
| order by UserId, Timestamp asc
| extend SeqNum = row_number(1, UserId != prev(UserId))

7. Cardinality Check Before Join

Always run this before joining large tables:

// Step 1: Check left side cardinality
TableA | summarize LeftRows = count(), LeftDistinctKeys = dcount(JoinKey)

// Step 2: Check right side cardinality
TableB | summarize RightRows = count(), RightDistinctKeys = dcount(JoinKey)

// Step 3: Estimate output size
// If LeftDistinctKeys × RightDistinctKeys > 1M, add filters before joining

// Step 4: Safe join with pre-filtering
TableA
| where Timestamp > ago(1d)  // narrow the time window
| join kind=inner (
    TableB | where IsActive == true  // narrow the right side too
) on JoinKey

Join kind selection guide

8. Safe Exploration Pattern

When encountering a new table, always follow this progression:

// Step 1: How big is it?
Table | count

// Step 2: What does it look like?
Table | take 5

// Step 3: What's the time range?
Table | summarize min(Timestamp), max(Timestamp)

// Step 4: What are the key dimensions?
Table | summarize dcount(Col1), dcount(Col2), dcount(Col3)

// Step 5: What's the value distribution?
Table
| summarize count() by Col1
| top 10 by count_ desc

Never skip to a complex query without running Steps 1-2 first. Knowing the table size prevents memory errors; seeing sample rows prevents wrong assumptions about column values.

9. Diffing & Anomaly Detection

Find outliers by standard deviation

Table
| summarize avg_val = avg(Value), stdev_val = stdev(Value)
| join kind=inner Table on true()
| where Value > avg_val + 3 * stdev_val or Value < avg_val - 3 * stdev_val

Compare two time periods

let period1 = Table | where Timestamp between (datetime(2023-01-01) .. datetime(2023-01-31));
let period2 = Table | where Timestamp between (datetime(2023-02-01) .. datetime(2023-02-28));
period1 | summarize P1_Count = count() by Category
| join kind=fullouter (period2 | summarize P2_Count = count() by Category) on Category
| extend Change = P2_Count - P1_Count, ChangePercent = round(100.0 * (P2_Count - P1_Count) / P1_Count, 1)

Find records that appear in one table but not another

// Records in A but not in B
TableA | join kind=leftanti TableB on Key

Find new entities (appeared after a cutoff)

let cutoff = datetime(2023-06-01);
Table
| summarize FirstSeen = min(Timestamp) by EntityId
| where FirstSeen > cutoff

10. String Extraction Patterns

Extract structured fields

// Parse key=value pairs
Logs | parse Message with * "user=" User " " * "action=" Action " " *

// Extract with regex
Logs | extend IP = extract(@"(\d+\.\d+\.\d+\.\d+)", 1, Message)

// Extract all matches (remember: needs capturing groups!)
Logs | extend AllIPs = extract_all(@"(\d+\.\d+\.\d+\.\d+)", Message)

Split and expand

// Split a delimited string and expand into rows
Table
| extend Parts = split(DelimitedField, ",")
| mv-expand Part = Parts to typeof(string)

URL parsing

Logs
| extend Host = parse_url(Url).Host
| extend Path = parse_url(Url).Path
| extend QueryParams = parse_url(Url).["Query Parameters"]

JSON field extraction

// Direct property access
Logs | extend UserId = tostring(Properties.userId)

// Nested JSON
Logs | extend City = tostring(Properties.address.city)

// Parse JSON string
Logs | extend Parsed = parse_json(JsonString) | extend Name = tostring(Parsed.name)