# SkillPatch skill: kql

A comprehensive skill for writing, debugging, and reviewing Kusto Query Language (KQL) queries. It covers pipe-forward syntax, dynamic type discipline, join patterns, datetime pitfalls, and advanced functions for Azure Data Explorer, Log Analytics, Microsoft Sentinel, and Fabric Real-Time Intelligence. Includes actionable gotchas and fixes to prevent common errors and avoid expensive retry cascades.

You (the agent) were given this URL and asked to use this skill. This is a **public** skill — no SkillPatch account, API key, or prior setup is required. Two ways to use it:

**1. Use it right now** — the skill's complete file tree (SKILL.md + all reference files) is inlined below; read `SKILL.md` first, then follow it, consulting the other files as it directs.

**2. Install the exact package onto disk** (recommended if you can run a shell — this reproduces the skill byte-for-byte, including any binary assets that can't be inlined):

```bash
mkdir -p .claude/skills/kql
curl -sSL https://skillpatch.dev/install_skill/kql | tar -xz -C .claude/skills/
```

(`.claude/skills/` is Claude Code's convention; use whatever directory your agent loads skills from.)


---

## Skill files (5)

- `SKILL.md`
- `references/advanced-patterns.md`
- `references/discovery-queries.md`
- `references/error-recovery.md`
- `references/query-templates.md`


### `SKILL.md`

````markdown
---
name: kql
description: "KQL language expertise for writing correct, efficient Kusto Query Language queries. Covers syntax gotchas, join patterns, dynamic types, datetime pitfalls, regex patterns, serialization, memory management, result-size discipline, and advanced functions (geo, vector, graph). USE THIS SKILL whenever writing, debugging, or reviewing KQL queries — even simple ones — because the gotchas section prevents the most common errors that waste tool calls and cause expensive retry cascades. Trigger on: KQL, Kusto, ADX, Azure Data Explorer, Fabric Real-Time Intelligence, EventHouse, Log Analytics, log analysis, data exploration, time series, anomaly detection, summarize, where clause, join, extend, project, let statement, parse operator, extract function, any mention of pipe-forward query syntax."
---

# KQL Mastery

> **Try it yourself**: All `✅` examples in this skill can be run against the public help cluster:
> `https://help.kusto.windows.net`, database `Samples` (contains `StormEvents`, `SimpleGraph_Nodes`/`Edges`, `nyc_taxi`, and more).

## 1. KQL Basics

Kusto Query Language (KQL) is a pipe-forward query language for exploring data. It is the native query language for Azure Data Explorer (ADX), Microsoft Fabric Real-Time Intelligence (EventHouse), Azure Monitor Log Analytics, Microsoft Sentinel, and other Microsoft data services.

### Pipe-forward syntax

KQL queries are a chain of operators separated by `|`. Data flows left to right:

```kql
StormEvents                          // start with a table
| where State == "TEXAS"             // filter rows
| summarize count() by EventType     // aggregate
| top 5 by count_ desc              // limit results
```

### Query vs management commands

KQL has two execution planes:

| Plane | Starts with | Examples |
|-------|-------------|----------|
| **Query** | Table name, `let`, `print`, `datatable` | `StormEvents \| where State == "TEXAS"` |
| **Management** | `.show`, `.create`, `.set`, `.drop`, `.alter` | `.show tables`, `.show table T schema` |

Management commands can be followed by query operators (the output is tabular), but the entire request runs on the management plane. You cannot start with a query and pipe into a management command.

```kql
// ✅ WORKS — management command piped to query operators
.show tables | project TableName | where TableName has "Events"

// ❌ WRONG — query piped into management command
StormEvents | take 5 | .show tables
```

When in doubt: if the first token starts with `.`, it's a management command. For a full catalog of schema exploration commands, see `references/discovery-queries.md`.

## 2. Dynamic Type Discipline

KQL's `dynamic` type is flexible but strict in certain contexts. A common mistake is using a dynamic column in `summarize by`, `order by`, or `join on` without casting.

**The rule**: Any time you use a dynamic-typed column in `by`, `on`, or `order by`, wrap it in an explicit cast.

```kql
// ❌ ERROR: "Summarize group key ... is of a 'dynamic' type"
StormEvents | summarize count() by StormSummary.Details.Location

// ✅ FIX
StormEvents | summarize count() by tostring(StormSummary.Details.Location)
```

```kql
// ❌ ERROR: "order operator: key can't be of dynamic type"
StormEvents | order by StormSummary.TotalDamages desc

// ✅ FIX
StormEvents | order by tolong(StormSummary.TotalDamages) desc
```

```kql
// ❌ ERROR in join: dynamic join key
StormEvents | join kind=inner (PopulationData) on $left.StormSummary == $right.State

// ✅ FIX — cast both sides
StormEvents
| extend State_str = tostring(StormSummary.Details.Location)
| join kind=inner (PopulationData) on $left.State_str == $right.State
```

**Self-correction**: When you see "is of a 'dynamic' type" in an error, add `tostring()`, `tolong()`, or `todouble()`.

## 3. Join Patterns & Pitfalls

KQL joins have constraints that differ from SQL.

### Equality only
KQL join conditions support **only `==`**. No `<`, `>`, `!=`, or function calls in join predicates.

```kql
// ❌ ERROR: "Only equality is allowed in this context"
StormEvents | join (nyc_taxi) on geo_distance_2points(BeginLon, BeginLat, pickup_longitude, pickup_latitude) < 1000

// ✅ WORKAROUND — pre-bucket into spatial cells, then join on cell ID
StormEvents
| extend cell = geo_point_to_s2cell(BeginLon, BeginLat, 8)
| join kind=inner (nyc_taxi | extend cell = geo_point_to_s2cell(pickup_longitude, pickup_latitude, 8)) on cell
```

For range joins, pre-bin values: `| extend bin_val = bin(Value, 100)`, then join on `bin_val`. Note: values near bin boundaries may land in adjacent bins — consider checking neighboring bins or overlapping the range for precision.

### Left/right attribute matching
Both sides of a join `on` clause must reference **column entities only** — not expressions, not aggregates.

```kql
// ❌ ERROR: "for each left attribute, right attribute should be selected"
StormEvents | join kind=inner (PopulationData) on $left.State

// ✅ FIX — specify both sides explicitly
StormEvents | join kind=inner (PopulationData) on $left.State == $right.State
```

### Cardinality check before large joins
**Always** check cardinality before joining tables with >10K rows. A cross-join explosion was the source of the single `E_RUNAWAY_QUERY` error (25K × 195 = potential 4.8M rows).

```kql
// Before joining, check how many rows each side contributes
StormEvents | summarize dcount(State)        // → 67 distinct states
PopulationData | summarize dcount(State)     // → 52 — safe to join
```

## 4. Regex in KQL

KQL handles regex natively — no need for Python.

### The `extract_all` gotcha
Unlike Python's `re.findall()`, KQL's `extract_all` **requires capturing groups** in the regex:

```kql
// ❌ ERROR: "extractall(): argument 2 must be a valid regex with [1..16] matching groups"
StormEvents | extend words = extract_all(@"[a-zA-Z]{3,}", EventNarrative)

// ✅ FIX — add parentheses around the pattern
StormEvents | extend words = extract_all(@"([a-zA-Z]{3,})", EventNarrative)
```

### Regex toolkit — don't fall back to Python
| Function | Use case | Example |
|----------|----------|---------|
| `extract(regex, group, source)` | Single match | `extract(@"User '([^']+)'", 1, Msg)` |
| `extract_all(regex, source)` | All matches (needs `()`) | `extract_all(@"(\w+)", Text)` |
| `parse` | Structured extraction | `parse Msg with * "User '" Sender "' sent" *` |
| `matches regex` | Boolean filter | `where Url matches regex @"^https?://"` |
| `replace_regex` | Find and replace | `replace_regex(Text, @"\s+", " ")` |

## 5. Serialization Requirements

Window functions need serialized (ordered) input.

```kql
// ❌ ERROR: "Function 'row_cumsum' cannot be invoked. The row set must be serialized."
StormEvents
| where State == "TEXAS"
| summarize DailyCount = count() by bin(StartTime, 1d)
| extend CumulativeCount = row_cumsum(DailyCount)

// ✅ FIX — add | serialize (or | order by, which implicitly serializes)
StormEvents
| where State == "TEXAS"
| summarize DailyCount = count() by bin(StartTime, 1d)
| order by StartTime asc
| extend CumulativeCount = row_cumsum(DailyCount)
```

Functions requiring serialization: `row_number()`, `row_cumsum()`, `prev()`, `next()`, `row_window_session()`.

## 6. Memory-Safe Query Patterns

The most common memory error. Caused by scanning too much data without pre-filtering.

### The progression of safety
```
Safest ──────────────────────────────────────────────── Most dangerous
| count    | take 10    | where + summarize    | summarize (no filter)    | full scan
```

### Rules for large tables (>1M rows)

1. **Always start with `| count`** to understand table size
2. **Always `| where` before `| summarize`** — filter time range, partition key, or category first
3. **Never `dcount()` on high-cardinality columns** without pre-filtering
4. **Check join cardinality** before executing (see Section 3)
5. **Use `materialize()`** for subqueries referenced multiple times

```kql
// ❌ OUT OF MEMORY — large table, no filter, many group-by columns
StormEvents
| summarize dcount(EventType), count() by StartTime, State, Source
| where dcount_EventType > 1

// ✅ SAFE — filter first, then aggregate
StormEvents
| where StartTime between (datetime(2007-04-15) .. datetime(2007-04-16))
| summarize dcount(EventType) by State, Source
| where dcount_EventType > 1
```

### When you see `E_LOW_MEMORY_CONDITION`
The query touched too much data. Your options:
- Add `| where` filters (time range, partition key)
- Reduce the number of `by` columns in `summarize`
- Break into smaller time windows and union results
- Use `| sample 10000` for exploratory work instead of full scans

### When you see `E_RUNAWAY_QUERY`
A join or aggregation produced too many output rows. Check join cardinality — one or both sides is too large.

## 7. Result Size Discipline

Large results slow down analysis. Prevention:

| Query type | Safeguard |
|-----------|-----------|
| Exploratory | Always end with `\| take 10` or `\| take 20` |
| Aggregation | Use `\| top 20 by ...` not unbounded `summarize` |
| Wide rows (vectors, JSON) | `\| project` only needed columns |
| `make_list()` / `make_set()` | Avoid on high-cardinality groups (produces huge cells) |
| Unknown size | Run `\| count` first |

**The vector trap**: Tables with embedding columns (1536-dim float arrays) produce ~30KB per row. Even `| take 20` yields 600KB. Always `| project` away vector columns unless you specifically need them.

## 8. String Comparison Strictness

KQL sometimes requires explicit casts when comparing computed string values — even when both sides are already strings.

```kql
// ❌ ERROR: "Cannot compare values of types string and string. Try adding explicit casts"
StormEvents | where geo_point_to_s2cell(BeginLon, BeginLat, 16) == other_cell

// ✅ FIX — wrap both sides in tostring()
StormEvents | where tostring(geo_point_to_s2cell(BeginLon, BeginLat, 16)) == tostring(other_cell)
```

This is most common with computed values from `geo_point_to_s2cell()` and `strcat()` comparisons. When in doubt, cast with `tostring()`.

## 9. Advanced Functions

KQL handles these natively — no need for Python:

### Vector similarity
```kql
// try it! — cosine similarity on Iris feature vectors
let target = pack_array(5.1, 3.5, 1.4, 0.2);
Iris
| extend Vec = pack_array(SepalLength, SepalWidth, PetalLength, PetalWidth)
| extend sim = series_cosine_similarity(Vec, target)
| top 5 by sim desc
```

### Geo operations
```kql
// Distance between two points (meters)
StormEvents | extend dist = geo_distance_2points(BeginLon, BeginLat, EndLon, EndLat)

// Spatial bucketing for joins
StormEvents | extend cell = geo_point_to_s2cell(BeginLon, BeginLat, 8)
```

### Graph queries
```kql
// Persistent graph model — try it on the help cluster!
graph("Simple")
| graph-match (src)-[e*1..3]->(dst)
  where src.name == "Alice"
  project src.name, dst.name, path_length = array_length(e)

// Transient graph — build inline with make-graph
SimpleGraph_Edges
| make-graph source --> target with SimpleGraph_Nodes on id
| graph-match (src)-[e*1..5]->(dst)
  where src.name == "Alice"
  project src.name, dst.name, path_length = array_length(e)
```

### Time series
```kql
// try it! — create a time series and detect anomalies
StormEvents
| make-series count() default=0 on StartTime step 1d
| extend anomalies = series_decompose_anomalies(count_)
```

For detailed examples and patterns, consult `references/advanced-patterns.md`.

## 10. Self-Correction Lookup Table

When you encounter an error, look it up here before retrying:

| Error message contains | Likely cause | Fix |
|---|---|---|
| `is of a 'dynamic' type` | Dynamic column in `by`/`on`/`order by` | Wrap in `tostring()`/`tolong()` |
| `Only equality is allowed` | Range predicate in join condition | Pre-bucket with S2/H3 cells or `bin()` |
| `extractall(): matching groups` | Missing `()` in regex | Add `()`: `@"(\w+)"` not `@"\w+"` |
| `row set must be serialized` | Window function on unsorted data | Add `\| serialize` or `\| order by` before it |
| `Cannot compare values of types string and string` | Computed string comparison | Add `tostring()` on both sides |
| `Failed to resolve column named 'X'` | Wrong column name or wrong table | Run `.show table T schema` to check column names |
| `E_LOW_MEMORY_CONDITION` | Query touched too much data | Add `\| where` filters, reduce time range, break into steps |
| `E_RUNAWAY_QUERY` | Join/aggregation produced too many rows | Check cardinality before joining; add pre-filters |
| `for each left attribute, right attribute` | Join `on` clause incomplete | Use explicit form: `on $left.X == $right.Y` |
| `needs to be bracketed` | Reserved word used as identifier | Use `['keyword']` syntax |
| `plugin doesn't exist` | Unavailable plugin on this cluster | Fall back to equivalent function or Python |
| `Expected string literal in datetime()` | Bare integer in datetime literal | Use `datetime(2024-01-01)` not `datetime(2024)` |
| `Unexpected token` after `by` | Complex expression in summarize by-clause | `extend` the expression first, then `summarize by` the column |
| `not recognized` / `unknown operator` | Operator not available on this engine | Check operator support; try equivalent (`order by` = `sort by`) |

## 11. Datetime Pitfalls

Datetime literals are a common source of errors. A wrong literal format can cascade into completely different approaches instead of fixing the small issue.

### Literal format
```kql
// ❌ WRONG — bare year is not a valid datetime
StormEvents | where StartTime > datetime(2007)

// ✅ RIGHT — always use full date format
StormEvents | where StartTime > datetime(2007-01-01)
```

### Filtering by year, month, or hour
```kql
// ❌ WRONG — comparing datetime column to integer
StormEvents | where StartTime == 2007

// ✅ RIGHT — use datetime_part() to extract components
StormEvents | where datetime_part("year", StartTime) == 2007

// ✅ ALSO RIGHT — use between with datetime range
StormEvents | where StartTime between (datetime(2007-01-01) .. datetime(2007-12-31T23:59:59))
```

### Time bucketing in summarize
```kql
// This works, but can be harder to read and reuse in complex queries
StormEvents | summarize count() by startofmonth(StartTime)

// Clearer — extend first, then summarize by the computed column
StormEvents
| extend Month = startofmonth(StartTime)
| summarize count() by Month
| order by Month asc
```

### Useful datetime functions
| Function | Purpose | Example |
|----------|---------|---------|
| `bin(ts, 1h)` | Round down to bucket boundary | `bin(Timestamp, 1d)` |
| `startofmonth(ts)` | First day of month | `startofmonth(Timestamp)` |
| `datetime_part("hour", ts)` | Extract component | `datetime_part("year", Timestamp)` |
| `format_datetime(ts, fmt)` | Format as string | `format_datetime(Timestamp, "yyyy-MM")` |
| `ago(1d)` | Relative time | `where Timestamp > ago(1d)` |
| `between(a .. b)` | Range filter (inclusive) | `where Timestamp between (datetime(2024-01-01) .. datetime(2024-01-31T23:59:59))` |
| `todatetime(str)` | Parse string → datetime | `todatetime("2024-01-15T10:30:00Z")` |
| `totimespan(str)` | Parse string → timespan | `totimespan("01:30:00")` |

## 12. Operator Naming & Equality

KQL has subtle differences from SQL syntax.

### Naming conventions

| Entity | Convention | Example |
|--------|-----------|---------|
| Tables | UpperCamelCase | `StormEvents`, `NetworkLogs` |
| Columns | UpperCamelCase | `StartTime`, `EventType` |
| Variables (`let`) | snake_case | `let filtered_events = ...` |
| Built-in functions | snake_case | `format_bytes()`, `geo_distance_2points()` |
| Stored functions | UpperCamelCase | `.create function GetTopUsers` |

### Equality operators
```kql
// In where clauses, == is case-sensitive, =~ is case-insensitive
StormEvents | where State == "TEXAS" | count        // exact match
StormEvents | where State =~ "texas" | count        // case-insensitive

// In joins, use == only
StormEvents | join kind=inner (PopulationData) on State
```

### sort vs order
Both `sort by` and `order by` work identically in KQL — they are aliases. Use whichever you prefer, but be consistent.

### contains vs has
```kql
// contains: substring match (slower)
StormEvents | where EventNarrative contains "tree"   // finds "trees", "treetop" too

// has: term/word match (faster, uses index)
StormEvents | where EventNarrative has "tree"        // matches word boundaries only

// For exact prefix/suffix
StormEvents | where EventType startswith "Thunder"
StormEvents | where Source endswith "Spotter"
```

## 13. Error Recovery Strategy

When a first KQL query fails, the temptation is to abandon the entire approach and try something completely different. The correct response is almost always to **fix the specific error**, not change strategy.

### The pattern to avoid
```
Query 1: extract(@"pattern", 1, col)  → Parse error
Query 2: todynamic(col)               → Different error  
Query 3: parse_json(col)              → Another error
Query 4: Python script                → Works but 10x tokens
```

### The correct pattern
```
Query 1: extract(@"pattern", 1, col)  → Parse error (bad escaping)
Query 2: extract(@"pattern", 1, col)  → Fix the specific escaping issue → Success
```

**Rules for error recovery:**
1. Read the error message carefully — it almost always tells you exactly what's wrong
2. Fix the **specific** syntax/escaping issue, don't switch approaches
3. Use the self-correction table (Section 10) to map errors to fixes
4. Only switch approaches after 2 failed fixes of the same query
5. The `parse` operator is often simpler than `extract()` for structured text:

```kql
// Instead of complex regex on TraceLogs:
// extract(@"file path: \"\"([^\"]+)\"\"", 1, Message)

// Use parse for structured extraction (try it on help cluster, SampleLogs db):
cluster("help").database("SampleLogs").TraceLogs
| where Message has "file path"
| parse Message with * "file path: \"\"" FilePath "\"\"" *
| project Timestamp, FilePath
| take 5
```

## 14. Query Writing Checklist

Before running any KQL query, mentally check:

1. **Pre-filtered?** Large tables have a `| where` before any `| summarize`
2. **Result bounded?** Exploratory queries end with `| take N` or `| top N`
3. **Dynamic columns cast?** Any dynamic column in `by`/`on`/`order by` is wrapped
4. **Regex has groups?** `extract_all` patterns have `()` around what you want to capture
5. **Join cardinality safe?** Both sides checked with `dcount()` before joining
6. **Needed columns only?** Wide tables get `| project` to drop unneeded columns
7. **Datetime literals valid?** Using `datetime(2024-01-01)` not `datetime(2024)` or bare integers
8. **Complex by-expressions?** Use `| extend` first, then `| summarize by` the computed column
9. **Error recovery plan?** If a query fails, fix the specific error — don't change strategy

````


### `references/advanced-patterns.md`

`````markdown
# Advanced KQL Patterns

Reference for specialized KQL features: graph queries, vector similarity, geospatial operations, time series, and external data. Consult this when a task requires these capabilities.

> **Try it yourself**: Examples marked with `// try it!` can be run on the public help cluster:
> `https://help.kusto.windows.net`, database `Samples`.

## Table of Contents

1. [Graph Queries](#1-graph-queries)
2. [Vector Similarity](#2-vector-similarity)
3. [Geospatial Operations](#3-geospatial-operations)
4. [Time Series Analysis](#4-time-series-analysis)
5. [External Data](#5-external-data)
6. [Stored Functions](#6-stored-functions)
7. [Materialized Views & Caching](#7-materialized-views--caching)
8. [Good Query Habits](#8-good-query-habits)

---

## 1. Graph Queries

KQL supports two approaches for building and querying graphs. The `graph-match` syntax is the same for both — only how the graph is constructed differs.
- **Transient graphs** — built inline with the `make-graph` operator. Useful for verifying an approach before committing to persistent graph building, or for smaller graphs (~1M entities).
- **Persistent (snapshot) graphs** — defined as a graph model and queried with `graph("ModelName")`. Best for large graphs and repeated queries where rebuilding the graph each time is too expensive.

### Querying a persistent graph model

Persistent graph models are defined once and queried repeatedly with `graph("ModelName")`. The function picks up the latest snapshot automatically. You can also target a specific snapshot with `graph("ModelName", "SnapshotName")`.

```kql
// try it! — query the persistent "Simple" graph model on the help cluster
graph("Simple")
| graph-match (src)-[e]->(dst)
  where src.name == "Alice"
  project src.name, dst.name, e.lbl
```

### Creating a persistent graph model

Use `.create-or-alter graph_model` to define the schema and how nodes/edges are built from tables:

````kql
// Management command — define the graph model
.create-or-alter graph_model YaccNetwork ```
{
  "Schema": {
    "Nodes": {
      "Application": {"AppName": "string", "HostingIp": "string"}
    },
    "Edges": {
      "Connection": {"Protocol": "string", "Port": "int"}
    }
  },
  "Definition": {
    "Steps": [
      {
        "Kind": "AddNodes",
        "Query": "YaccApplications | project NodeId = AppId, AppName, HostingIp",
        "NodeIdColumn": "NodeId",
        "Labels": ["Application"]
      },
      {
        "Kind": "AddEdges",
        "Query": "YaccConnections | project SourceId = SourceAppId, TargetId = TargetAppId, Protocol, Port",
        "SourceColumn": "SourceId",
        "TargetColumn": "TargetId",
        "Labels": ["Connection"]
      }
    ]
  }
}
```
````

### Ad-hoc graphs with make-graph

For one-time analysis, use the `make-graph` operator to build a transient graph inline:

```kql
// try it! — uses SimpleGraph_Nodes and SimpleGraph_Edges from help cluster
SimpleGraph_Edges
| make-graph source --> target with SimpleGraph_Nodes on id
| graph-match (src)-[e]->(dst)
  where src.name == "Alice"
  project src.name, dst.name, e.lbl
```

### Variable-length traversal

```kql
// try it! — find all paths up to 3 hops from Alice on persistent model
graph("Simple")
| graph-match (start)-[path*1..3]->(target)
  where start.name == "Alice"
  project start.name, target.name, hops = array_length(path)
| take 10
```

### Pre-filtering edges (the key pattern)

Edge properties are not accessible on variable-length paths during `graph-match`. Use `all()` / `any()` for label-based filtering, or pre-filter edges before building the graph.

```kql
// ❌ WRONG — can't access properties on variable-length edge
graph-match (src)-[path*1..3]->(dst)
  where path.IsVulnerable == true

// ✅ RIGHT (persistent) — filter in graph model definition
// In the AddEdges step, set Query to: "Edges | where IsVulnerable == true | project ..."

// ✅ RIGHT (ad-hoc) — pre-filter edges before make-graph
Edges
| where IsVulnerable == true
| make-graph SourceId --> TargetId with Nodes on NodeId
| graph-match (src)-[path*1..3]->(dst)
  project src.Name, dst.Name

// ✅ RIGHT (label-based) — use all() on variable-length paths
graph("MyGraph")
| graph-match (src)-[path*1..3]->(dst)
  where all(path, labels() has "Vulnerable")
  project src.Name, dst.Name
```

### Graph snapshots

For persistent models, create materialized snapshots for faster queries:

```kql
// Create a snapshot (management command, async — not runnable on help cluster)
.make graph_snapshot Simple_20240115
  with (source = graph_model("Simple"))

// try it! — query picks up latest snapshot automatically
graph("Simple")
| graph-match (src)-[e]->(dst)
  where labels(src) has "Person"
  project src.name, dst.name, e.lbl
| take 10
```

### Post-processing graph results

After `graph-match` with a `project` clause, results are tabular and can be piped to any KQL operator:

```kql
// try it! — count relationships per person
graph("Simple")
| graph-match (src)-[e]->(dst)
  project src.name, dst.name, e.lbl
| summarize ConnectionCount = count() by src_name
| top 10 by ConnectionCount desc
```

Use the `graph-to-table` operator when you need to export all nodes and edges from a graph as raw tables (without pattern matching):

```kql
graph("Simple")
| graph-to-table nodes as N, edges as E;
N | take 5
```

---

## 2. Vector Similarity

KQL has native vector operations — **don't export to Python** for cosine similarity.

### series_cosine_similarity

The most common vector operation.

```kql
// try it! — find Iris flowers most similar to a target using feature vectors
let target_vec = pack_array(5.1, 3.5, 1.4, 0.2);
Iris
| extend Vec = pack_array(SepalLength, SepalWidth, PetalLength, PetalWidth)
| extend similarity = series_cosine_similarity(Vec, target_vec)
| top 5 by similarity desc
| project Class, SepalLength, SepalWidth, similarity
```

### Combining vectors

```kql
// try it! — weighted vector combination from Iris feature vectors
let v1 = toscalar(Iris | where Class == "Iris-setosa" | take 1 | project pack_array(SepalLength, SepalWidth, PetalLength, PetalWidth));
let v2 = toscalar(Iris | where Class == "Iris-versicolor" | take 1 | project pack_array(SepalLength, SepalWidth, PetalLength, PetalWidth));
let v3 = toscalar(Iris | where Class == "Iris-virginica" | take 1 | project pack_array(SepalLength, SepalWidth, PetalLength, PetalWidth));
let combined = series_add(
    series_add(
        series_multiply(v1, repeat(0.5, array_length(v1))),
        series_multiply(v2, repeat(0.3, array_length(v2)))
    ),
    series_multiply(v3, repeat(0.2, array_length(v3)))
);
print combined
```

### Performance consideration

Pairwise cosine similarity on 1536-dim vectors is expensive (~20s for large tables). Strategies:

1. **Pre-filter** — narrow candidates before computing similarity
2. **Round-and-join** — bucket vector dimensions to integers, join on buckets for approximate matching
3. **Project away vectors** — never include vector columns in results unless needed

```kql
// Round-and-join for approximate nearest neighbor
let target_rounded = toscalar(
    Vecs | where Word == "test"
    | project r = array_sort_asc(series_multiply(Vec, repeat(100, array_length(Vec))))
);
Vecs
| extend rounded = array_sort_asc(series_multiply(Vec, repeat(100, array_length(Vec))))
| where rounded[0] between (target_rounded[0]-10 .. target_rounded[0]+10)
| extend sim = series_cosine_similarity(Vec, toscalar(Vecs | where Word == "test" | project Vec))
| top 10 by sim desc
```

### Other series functions

| Function | Purpose |
|----------|---------|
| `series_cosine_similarity(a, b)` | Cosine similarity between two vectors |
| `series_pearson_correlation(a, b)` | Pearson correlation |
| `series_dot_product(a, b)` | Dot product |
| `series_add(a, b)` | Element-wise addition |
| `series_multiply(a, b)` | Element-wise multiplication |
| `series_subtract(a, b)` | Element-wise subtraction |
| `series_magnitude(a)` | L2 norm |

---

## 3. Geospatial Operations

### Point-in-polygon

```kql
// try it! — check if storm events occurred within a polygon around NYC
StormEvents
| where geo_point_in_polygon(BeginLon, BeginLat,
    dynamic({"type":"Polygon","coordinates":[[[-74.05,40.65],[-73.85,40.65],[-73.85,40.85],[-74.05,40.85],[-74.05,40.65]]]}))
| summarize count() by EventType
```

Note: `geo_point_in_polygon` is a **scalar function**, not a plugin. It works on free clusters. Don't try to `evaluate` it as a plugin — use it directly in `| where` or `| extend`.

### Distance calculations

```kql
// try it! — distance between start and end points of storm events (meters)
StormEvents
| where isnotempty(BeginLat) and isnotempty(EndLat)
| extend distance_m = geo_distance_2points(BeginLon, BeginLat, EndLon, EndLat)
| project EventType, State, distance_m
| top 5 by distance_m desc

// Filter by distance — storms within 50km of Houston (-95.37, 29.76)
StormEvents
| where geo_distance_2points(BeginLon, BeginLat, -95.37, 29.76) < 50000
| summarize count() by EventType
```

### Spatial bucketing for joins

KQL joins are equality-only, so spatial proximity joins need pre-bucketing:

```kql
// try it! — S2 cells on storm events
StormEvents
| extend cell = geo_point_to_s2cell(BeginLon, BeginLat, 8)
| summarize count() by cell
| top 10 by count_ desc

// Then join on cell IDs (e.g., storm events near taxi pickups)
StormEvents
| extend cell = geo_point_to_s2cell(BeginLon, BeginLat, 5)
| join kind=inner (nyc_taxi | extend cell = geo_point_to_s2cell(pickup_longitude, pickup_latitude, 5)) on cell
| take 10
```

### IP geolocation

```kql
// try it! — check if IPs are in a range (using inline test data)
datatable(ClientIP:string) ["10.1.2.3", "172.16.5.6", "192.168.1.1", "8.8.8.8"]
| where ipv4_is_in_any_range(ClientIP, dynamic(["10.0.0.0/8", "172.16.0.0/12"]))

// Check if an IP is in a specific subnet
print is_private = ipv4_is_in_range("10.1.2.3", "10.0.0.0/8")
```

---

## 4. Time Series Analysis

### Creating time series

```kql
// try it! — basic time series from storm events
StormEvents
| make-series count() default=0 on StartTime step 1d
| render timechart

// Multi-series (one per event type)
StormEvents
| make-series count() default=0 on StartTime step 1d by EventType
| take 5
```

### Anomaly detection

```kql
// try it! — decompose and find anomalies in daily storm counts
StormEvents
| make-series count() default=0 on StartTime step 1d
| extend (anomalies, score, baseline) = series_decompose_anomalies(count_)
| mv-expand StartTime, count_, anomalies, score, baseline
| where anomalies != 0
| take 10
```

### Period detection

```kql
// try it! — find periodic patterns in occupancy sensor data
OccupancyDetection
| make-series avg_temp = avg(Temperature) default=0 on Timestamp step 10m
| extend (periods, scores) = series_periods_detect(avg_temp, 4.0, 1440.0, 2)
| project periods, scores
```

### Sessionization

```kql
// try it! — group trace logs into sessions with 5-minute gap
cluster("help").database("SampleLogs").TraceLogs
| order by Source, Timestamp asc
| extend SessionId = row_window_session(Timestamp, 5m, 1h, Source != prev(Source))
| summarize EventCount = count(), SessionStart = min(Timestamp) by Source, SessionId
| take 5
```

### Sliding window statistics

```kql
// try it! — rolling average on occupancy temperature
OccupancyDetection
| make-series avg_temp = avg(Temperature) on Timestamp step 1h
| extend rolling_avg = series_fir(avg_temp, repeat(1.0/7, 7), false)
| take 1
```

---

## 5. External Data

Load small reference tables (up to ~100 MB) directly into KQL without ingestion using the `externaldata` operator:

```kql
// try it! — load Iris dataset from a public CSV on GitHub
externaldata(SepalLength:real, SepalWidth:real, PetalLength:real, PetalWidth:real, Class:string)
  [h@"https://raw.githubusercontent.com/uiuc-cse/data-fa14/gh-pages/data/iris.csv"]
  with (format="csv", ignoreFirstRecord=true)
| summarize count() by Class
```

**Notes**:
- `externaldata` supports all [ingestion data formats](https://learn.microsoft.com/en-us/kusto/ingestion-supported-formats) (CSV, TSV, JSON, Parquet, Avro, ORC, etc.). For hierarchical formats, specify `ingestionMapping`.
- Not designed for large data volumes — for larger datasets, use **external tables** (see below) or ingest the data into a table.
- For non-tabular content (images, PDFs, JavaScript), use Python or the browser.

### External tables for larger datasets

For data too large for `externaldata` or queried repeatedly, define a persistent [external table](https://learn.microsoft.com/en-us/kusto/query/schema-entities/external-tables). External tables have a stored schema and point to data in Azure Blob Storage, ADLS, Delta Lake, or SQL databases (SQL Server, MySQL, PostgreSQL, Cosmos DB).

```kql
// Create an external table (management command)
.create external table ExternalLogs (Timestamp:datetime, Level:string, Message:string)
  kind=storage
  dataformat=csv
  (h@'https://storage.blob.core.windows.net/logs/2024/;managed_identity=system')
  with (ignoreFirstRecord=true)
```

```kql
// try it! — query the TaxiRides external table on the help cluster
external_table("TaxiRides")
| where pickup_datetime between (datetime(2016-01-01) .. datetime(2016-01-02))
| summarize count() by payment_type
```

```kql
// try it! — discover available external tables
.show external tables
```

**When to use which**:
| Approach | Best for |
|----------|----------|
| `externaldata` | Small reference lookups (≤100 MB), one-off queries |
| External tables | Larger datasets, repeated queries, partitioned data, Delta Lake / SQL sources |
| Ingested tables | Highest performance, full indexing, data you own and query frequently |

---

## 6. Stored Functions

Some databases include pre-built stored functions. The help cluster has several in the Samples database.

### Discovering stored functions

```kql
// try it!
.show functions
```

### Invoking stored functions

```kql
// try it! — call a stored function with a parameter
MyFunction2(5)

// try it! — use a stored function to filter a query pipeline
StormEvents
| where State in (InterestingStates())
| summarize count() by State
```

### Common pitfalls with stored functions

1. **String escaping**: KQL supports both single (`'...'`) and double (`"..."`) quotes for string literals. If a string contains special characters or backslashes, use verbatim syntax `@"..."` or `@'...'`:
   ```kql
   MyCalc(1.0, 2.5, 3.7)
   ```
2. **Argument types**: Ensure you pass the right type. If the function expects `string` and you have `dynamic`, cast with `tostring()`.
3. **Function not found**: Check the database — functions are database-scoped. Use `.show functions` to list available ones.

---

## 7. Materialized Views & Caching

### materialize() for subquery reuse

When a subquery is referenced multiple times, `materialize()` computes it once:

```kql
// try it! — compute once, use twice
let top_states = materialize(
    StormEvents
    | summarize EventCount = count() by State
    | where EventCount > 1000
);
// Use it twice without recomputing
top_states | summarize TotalEvents = sum(EventCount)
| union (top_states | top 3 by EventCount desc)
```

### toscalar() for single-value subqueries

```kql
// try it! — extract a single value to use as a constant
let avg_damage = toscalar(StormEvents | summarize avg(DamageProperty));
StormEvents | where DamageProperty > avg_damage | summarize count()
```

### datatable for inline test data

```kql
// Create test data without touching the database
let test_data = datatable(Name:string, Score:long) [
    "Alice", 95,
    "Bob", 87,
    "Charlie", 92
];
test_data | where Score > 90
```

---

## 8. Good Query Habits

Patterns that prevent wasted time, runaway queries, and unnecessary retries.

### Test transformations on small data first

Use `datatable` or `print` to validate logic before running on full tables. This catches syntax errors and logic bugs without touching real data.

```kql
// Test a regex extraction before running on millions of rows
let sample = datatable(Msg:string) [
    "User 'alice' sent 1024 bytes",
    "User 'bob' sent 512 bytes",
    "Invalid line with no match"
];
sample
| parse Msg with * "User '" Username "' sent " ByteCount " bytes"
| where isnotempty(Username)
```

```kql
// Test a datetime format with print
print result = format_datetime(datetime(2024-03-15T10:30:00Z), "yyyy-MM")
```

### Start with count, then sample, then full query

```kql
// Step 1: How big is this table?
StormEvents | count

// Step 2: What does the data look like?
StormEvents | take 10

// Step 3: Now write the real query with filters
StormEvents
| where StartTime between (datetime(2007-06-01) .. datetime(2007-06-30T23:59:59))
| summarize count() by EventType
| top 20 by count_ desc
```

### Use materialize() to avoid redundant computation

When referencing the same subquery more than once, wrap it in `materialize()` (see Section 7).

### Project early, project often

Drop columns you don't need as early as possible — especially wide columns like vectors, JSON blobs, or free-text fields. This reduces memory pressure and speeds up downstream operators.

```kql
// ❌ Carries all columns through the pipeline
StormEvents | where State == "TEXAS" | summarize count() by EventType

// ✅ Drop unneeded columns immediately
StormEvents | where State == "TEXAS" | project State, EventType | summarize count() by EventType
```

### Order predicates for maximum pruning

Kusto has efficient indexes on `datetime` and `string` term columns. Order your `where` predicates to exploit this:

1. **`datetime` filters first** — enables shard pruning (entire data extents skipped)
2. **`string`/`dynamic` term filters next** — `has`, `==`, `in` use the term index
3. **Numeric filters** — less selective but still cheap
4. **Substring scans last** — `contains`, `matches regex` must scan column data

```kql
// ❌ Substring scan runs first on all data
StormEvents
| where EventNarrative contains "power line"
| where StartTime between (datetime(2007-06-01) .. datetime(2007-06-30T23:59:59))

// ✅ Datetime prunes shards, then term filter, then substring
StormEvents
| where StartTime between (datetime(2007-06-01) .. datetime(2007-06-30T23:59:59))
| where EventType has "Wind"
| where EventNarrative contains "power line"
```

### Join performance hints

```kql
// hint.shufflekey — for high-cardinality join keys (>1M distinct values)
StormEvents
| join hint.shufflekey=EventId kind=inner (StormEvents | project EventId, State) on EventId

// hint.strategy=broadcast — when left side is small (<100 MB)
PopulationData
| join hint.strategy=broadcast kind=inner (StormEvents) on State

// Use lookup instead of join when right side is small
StormEvents
| lookup kind=leftouter (PopulationData) on State
```

### Use `in` instead of left semi join

For filtering by a single column, `in` is simpler and often faster:

```kql
// ❌ Verbose
StormEvents
| join kind=leftsemi (StormEvents | where State == "TEXAS" | project State) on State

// ✅ Simpler and often faster
StormEvents
| where State in (InterestingStates())
```

### Pre-filter dynamic columns with `has`

Parsing dynamic/JSON columns is expensive. Use `has` to filter out non-matching rows before parsing:

```kql
// ❌ Parses JSON for every row
StormEvents
| where StormSummary.Details.Description has "tornado"

// ✅ Term filter first, then parse — skips JSON parsing on non-matching rows
StormEvents
| where StormSummary has "tornado"
| where StormSummary.Details.Description has "tornado"
```

### Summarize with shuffle strategy

When `summarize` has high-cardinality group keys, use `hint.strategy=shuffle` to parallelize across nodes:

```kql
// ❌ Single-node bottleneck with millions of distinct keys
StormEvents | summarize count() by EventId

// ✅ Shuffle distributes work across
...<truncated>
`````


### `references/discovery-queries.md`

````markdown
# KQL Schema Discovery Queries

Reference for schema exploration commands. Use these to understand a database's structure before writing queries.

---

## Table and Column Discovery

### Table Discovery

```kql
// List all tables with row counts and sizes
.show tables details
| project TableName, TotalRowCount, TotalOriginalSize, TotalExtentSize, HotOriginalSize

// List tables (names only)
.show tables
| project TableName

// Table schema (column names, types, folder)
.show table StormEvents schema as json

// Table schema as CSL (for scripting)
.show table StormEvents cslschema

// Compact column listing (CSL format)
.show table StormEvents cslschema
| project TableName, Schema
```

### Column Statistics

```kql
// Column cardinality and statistics (sample-based)
.show table StormEvents column statistics

// Quick column profiling via query
StormEvents
| take 10000
| summarize
    Rows = count(),
    Nulls = countif(isnull(BeginLat)),
    Distinct = dcount(State),
    MinVal = min(StartTime),
    MaxVal = max(StartTime)
```

---

## Function and View Discovery

### Function Discovery

```kql
// List all stored functions
.show functions
| project Name, Parameters, Body = substring(Body, 0, 100), DocString, Folder

// Full function definition
.show function MyFunction2

// Functions in a folder
.show functions
| where Folder == "Demo"
```

### Materialized View Discovery

```kql
// List all materialized views
.show materialized-views
| project Name, SourceTable, Query = substring(Query, 0, 100), IsEnabled, IsHealthy

// View statistics (lag, processed records)
.show materialized-view DailyCovid19 statistics

// View extents
.show materialized-view DailyCovid19 extents
| summarize ExtentCount = count(), TotalRows = sum(RowCount)
```

---

## Policy Discovery

```kql
// Retention policies
.show table StormEvents policy retention

// Caching policies
.show table StormEvents policy caching

// Streaming ingestion policy
.show table StormEvents policy streamingingestion

// Update policies
.show table StormEvents policy update

// All major policies for a table (run individually):
// retention, caching, streamingingestion, update, merge, sharding
```

---

## External Tables and Ingestion Mappings

### External Table Discovery

```kql
// List external tables
.show external tables
| project TableName, TableType, Folder, ConnectionStrings

// External table schema
.show external table TaxiRides schema as json
```

### Ingestion Mapping Discovery

```kql
// CSV mappings for a table
.show table StormEvents ingestion csv mappings

// JSON mappings for a table
.show table StormEvents ingestion json mappings

// All mappings for a table
.show table StormEvents ingestion mappings
```

---

## Graph Model Discovery

```kql
// List all graph models
.show graph_models

// Show a specific graph model definition
.show graph_model Simple

// List graph snapshots
.show graph_snapshots
```

---

## Security Discovery

```kql
// Database-level principals
.show database Samples principals

// Table-level principals
.show table StormEvents principals

// Current identity
print CurrentUser = current_principal(), Cluster = current_cluster_endpoint()
```

---

## Database Overview Script

Run this sequence to get a complete picture of a KQL Database:

```kql
// 1. Database stats (uses current database context)
.show database datastats

// 2. All tables with details
.show tables details
| project TableName, TotalRowCount, TotalOriginalSize, CachingPolicy
| order by TotalRowCount desc

// 3. All functions
.show functions
| project Name, Folder, DocString, Parameters

// 4. All materialized views
.show materialized-views
| project Name, SourceTable, IsEnabled, IsHealthy

// 5. All external tables
.show external tables

// 6. All graph models
.show graph_models
```

---

## Advanced Troubleshooting

Commands for diagnosing ingestion failures, throttling, capacity issues, and cluster health.

### Ingestion Failures

When data isn't showing up after ingestion, check for failures (retained for 14 days):

```kql
// Show all recent ingestion failures
.show ingestion failures

// Filter to a specific table
.show ingestion failures
| where Table == "StormEvents"
| where FailedOn > ago(1d)
| project FailedOn, Table, FailureKind, ErrorCode, Details
| order by FailedOn desc

// Check a specific operation
.show ingestion failures with (OperationId = 'GUID-HERE')
```

Key columns: `FailureKind` (Permanent vs Transient), `ErrorCode`, `Details`, `OriginatesFromUpdatePolicy`.

### Cluster Capacity

Check whether the cluster is running out of capacity for ingestion, merges, exports, or other operations:

```kql
// Show capacity for all resource types
.show capacity

// Show capacity for a specific operation
.show capacity ingestions
.show capacity extents-merge
.show capacity data-export
.show capacity materialized-view
.show capacity extents-partition
```

Returns `Total`, `Consumed`, and `Remaining` for each resource. If `Remaining` is 0, operations are being throttled.

### Cluster Diagnostics

```kql
// Cluster health and node state
.show diagnostics

// Currently running queries (useful for identifying long-running or stuck queries)
.show queries

// Completed commands and their resource usage
.show commands
| where StartedOn > ago(1h)
| project CommandType, StartedOn, Duration, State, ResourceUtilization

// Combined view of commands and queries
.show commands-and-queries
| where StartedOn > ago(1h)
| order by StartedOn desc

// Administrative operations log
.show operations
| where StartedOn > ago(1d)
| where State == "Failed"
```

### Workload Groups

Workload groups control resource governance — per-request limits, rate limits, and concurrency. If queries are being throttled or rejected, check workload group configuration:

```kql
// Show all workload groups and their policies
.show workload_group default

// Monitor requests by workload group
.show commands-and-queries
| where StartedOn > ago(1h)
| summarize count(), avg(Duration), sum(TotalCPU) by WorkloadGroup
```

Built-in workload groups:
- **`default`** — catches all requests not classified elsewhere
- **`internal`** — internal system requests (cannot be modified)
- **`$materialized-views`** — materialized view materialization process

### Troubleshooting Decision Tree

| Symptom | First command | What to look for |
|---------|--------------|------------------|
| Data not appearing after ingestion | `.show ingestion failures` | `FailureKind`, `ErrorCode`, `Details` |
| Queries running slowly or timing out | `.show capacity` | `Remaining` = 0 on any resource |
| Queries being rejected/throttled | `.show workload_group default` | Rate limits, concurrent request caps |
| Cluster unresponsive | `.show diagnostics` | Node health, memory pressure |
| Merges falling behind | `.show capacity extents-merge` | `Remaining` = 0 |
| Materialized views stale | `.show materialized-view DailyCovid19 statistics` | Materialization lag |

````


### `references/error-recovery.md`

````markdown
# Error Recovery Reference

Complete mapping of common KQL error patterns. For each error: the exact message, a real query that triggered it, and the fix.

## Table of Contents

1. [E_LOW_MEMORY_CONDITION](#1-e_low_memory_condition)
2. [Join Errors](#2-join-errors)
3. [Network / Transient](#3-network--transient)
4. [Dynamic Type Errors](#4-dynamic-type-errors)
5. [Unresolved Names](#5-unresolved-names)
6. [Syntax Errors](#6-syntax-errors)
7. [String Comparison](#7-string-comparison)
8. [extract_all Regex Groups](#8-extract_all-regex-groups)
9. [Serialization Required](#9-serialization-required)
10. [Missing Plugins](#10-missing-plugins)
11. [graph-match Syntax](#11-graph-match-syntax)
12. [E_RUNAWAY_QUERY](#12-e_runaway_query)

---

## 1. E_LOW_MEMORY_CONDITION

**Error message**: `Query execution lacks memory resources to complete (80DA0007): Partial query failure: Low memory condition (E_LOW_MEMORY_CONDITION)`

### Pattern A: Unfiltered aggregation on large table

```kql
// ❌ TRIGGERED ERROR — large table, no pre-filter, too many group-by columns
StormEvents
| summarize dcount(EventType), count() by StartTime, State, Source
| where dcount_EventType > 1

// ✅ FIX — filter first, reduce grouping columns
StormEvents
| where StartTime between (datetime(2007-04-15) .. datetime(2007-04-16))
| summarize dcount(EventType) by State, Source
| where dcount_EventType > 1
```

### Pattern B: Join explosion

```kql
// ❌ TRIGGERED ERROR — unconstrained join between two large tables
StormEvents
| join kind=inner (PopulationData) on State

// ✅ FIX — pre-filter both sides, check cardinality first
let filtered_storms = StormEvents | where State == "TEXAS" | summarize by State;
let target_states = PopulationData | project State;
filtered_storms | join kind=inner (target_states) on State
```

### Pattern C: High-cardinality dcount()

```kql
// ❌ TRIGGERED ERROR — Full distinct value enumeration on many columns
StormEvents | summarize dcount(EventType), dcount(Source), dcount(BeginLocation) by bin(StartTime, 1h)

// ✅ FIX — filter first, reduce to one dcount
StormEvents
| where StartTime between (datetime(2007-06-01) .. datetime(2007-06-30T23:59:59))
| summarize dcount(EventType) by bin(StartTime, 1h)
```

**Recovery strategy**: When you see this error, your query is touching too much data. Options:
1. Add `| where` time-range or partition filter
2. Reduce `by` columns in `summarize`
3. Break the query into smaller time windows
4. Use `| sample 10000` for exploration

---

## 2. Join Errors

### 2a. Left/right attribute mismatch

**Error message**: `join: for each left attribute, right attribute should be selected.`

```kql
// ❌ TRIGGERED ERROR — incomplete on clause
StormEvents | join kind=inner PopulationData on $left.State

// ✅ FIX — specify both sides
StormEvents | join kind=inner (PopulationData) on $left.State == $right.State
```

```kql
// ❌ TRIGGERED ERROR — expression in join condition
StormEvents | join kind=inner (PopulationData) on $left.State == $right.tolower(State)

// ✅ FIX — pre-compute the expression, join on the computed column
StormEvents
| join kind=inner (PopulationData | extend StateLower = tolower(State)) on $left.State == $right.StateLower
```

### 2b. Equality only

**Error message**: `join: Only equality is allowed in this context.`

```kql
// ❌ TRIGGERED ERROR — geo-distance in join predicate
StormEvents | join (nyc_taxi) on geo_distance_2points(BeginLon, BeginLat, pickup_longitude, pickup_latitude) < 1000

// ✅ FIX — pre-bucket into spatial cells
StormEvents
| extend cell = geo_point_to_s2cell(BeginLon, BeginLat, 8)
| join kind=inner (nyc_taxi | extend cell = geo_point_to_s2cell(pickup_longitude, pickup_latitude, 8)) on cell
| where geo_distance_2points(BeginLat, BeginLon, pickup_latitude, pickup_longitude) < 1000
```

```kql
// ❌ TRIGGERED ERROR — range join
StormEvents | join (PopulationData) on $left.DamageProperty > $right.Population

// ✅ FIX — bin values and join on bins
StormEvents
| extend damage_bin = bin(DamageProperty, 1000000)
| join kind=inner (PopulationData | extend damage_bin = bin(Population, 1000000)) on damage_bin
| where DamageProperty > Population
```

---

## 3. Network / Transient

**Error message**: `Failed to process network request for the endpoint: https://kvc4bf3...`

Cause: Corrupted cluster URIs or genuine network timeouts.

**Recovery strategy**: 
1. Verify the cluster URI is clean ASCII (no Unicode characters)
2. Retry with a fresh URI from the environment variable
3. If persistent, wait 30 seconds and retry (cluster may be overloaded)

---

## 4. Dynamic Type Errors

### 4a. Summarize by dynamic

**Error message**: `Summarize group key 'Area' is of a 'dynamic' type. Please use an explicit cast`

```kql
// ❌
| summarize count() by Area
// ✅
| summarize count() by tostring(Area)
```

### 4b. Order by dynamic

**Error message**: `order operator: key can't be of dynamic type`

```kql
// ❌
| order by Properties.Score desc
// ✅
| order by todouble(Properties.Score) desc
```

### 4c. Join on dynamic

**Error message**: `join key 'Area' is of a 'dynamic' type. Please use an explicit cast`

```kql
// ❌
| join kind=inner other on Area
// ✅
| extend Area_str = tostring(Area)
| join kind=inner (other | extend Area_str = tostring(Area)) on Area_str
```

---

## 5. Unresolved Names

**Error message**: `Failed to resolve column or scalar expression named 'X'` or `Failed to resolve entity 'X'`

### Common causes

| Cause | Example |
|-------|---------|
| Invented column name | `NewVIN` (doesn't exist; actual is `VIN`) |
| Wrong table | Queried `Traffic` instead of `CarsTraffic` |
| Table not yet ingested or in different database | Table not found |
| Typo | `EstimatedHackersCount` vs actual column name |
| Let-variable scope | Variable defined in different query |

**Recovery strategy**:
1. Run `.show table T schema` to check exact column names
2. Check exact table and column names — KQL is case-sensitive for column names
3. If querying a table that should exist but doesn't, check which database you're connected to
4. Most unresolved names are matchable against the cached schema — look for similar names

---

## 6. Syntax Errors

### 6a. Management command in query pipe

**Error message**: `Unexpected control command`

This error occurs when a query pipes INTO a management command (not the other way around — management commands CAN have query operators piped after them).

```kql
// ✅ WORKS — management output is tabular, can be filtered
.show tables | project TableName | where TableName has "Events"

// ❌ ERROR — query piped into management command
StormEvents | take 5 | .show tables
```

### 6b. Reserved words as identifiers

**Error message**: `The name 'shards' needs to be bracketed as ['shards']`

```kql
// ❌
let shards = ...

// ✅
let ['shards'] = ...
// Or better: rename the variable
let shard_info = ...
```

### 6c. Syntax: Expected comma

Various syntax issues, usually from mixing SQL syntax into KQL:

```kql
// ❌ SQL-style
SELECT * FROM Table WHERE x = 1

// ✅ KQL-style
Table | where x == 1
```

---

## 7. String Comparison

**Error message**: `Cannot compare values of types string and string. Try adding explicit casts`

Despite both sides being strings, KQL sometimes requires explicit casts for computed values.

```kql
// ❌ — S2 cell comparison
StormEvents
| extend startCell = geo_point_to_s2cell(BeginLon, BeginLat, 16)
| join kind=inner (nyc_taxi | extend startCell = geo_point_to_s2cell(pickup_longitude, pickup_latitude, 16)) on startCell

// ✅ — explicit tostring()
StormEvents
| extend startCell = tostring(geo_point_to_s2cell(BeginLon, BeginLat, 16))
| join kind=inner (nyc_taxi | extend startCell = tostring(geo_point_to_s2cell(pickup_longitude, pickup_latitude, 16))) on startCell
```

This occurs most often with `geo_point_to_s2cell()`, `hash()`, and `strcat()` return values.

---

## 8. extract_all Regex Groups

**Error message**: `extractall(): argument 2 must be a valid regex with [1..16] matching groups`

```kql
// ❌ — Missing capturing group
StormEvents | extend words = extract_all(@"[a-zA-Z]{3,}", tolower(EventNarrative))

// ✅ — Add parentheses
StormEvents | extend words = extract_all(@"([a-zA-Z]{3,})", tolower(EventNarrative))
```

Unlike Python's `re.findall()`, KQL's `extract_all` requires at least one `()` group.

---

## 9. Serialization Required

**Error message**: `Function 'row_cumsum' cannot be invoked. The row set must be serialized.`

```kql
// ❌
StormEvents
| where State == "TEXAS"
| summarize DailyCount = count() by bin(StartTime, 1d)
| extend CumulativeCount = row_cumsum(DailyCount)

// ✅ — add order by (implicitly serializes)
StormEvents
| where State == "TEXAS"
| summarize DailyCount = count() by bin(StartTime, 1d)
| order by StartTime asc
| extend CumulativeCount = row_cumsum(DailyCount)
```

Affected functions: `row_number()`, `row_cumsum()`, `prev()`, `next()`, `row_window_session()`.

---

## 10. Missing Plugins

**Error message**: `plugin 'geo_point_in_polygon': plugin doesn't exist.`

Some KQL functions are plugins that may not be enabled on free-tier clusters.

**Recovery strategy**: Use the equivalent non-plugin function or fall back to Python for that specific operation.

| Plugin | Status on free cluster | Alternative |
|--------|----------------------|-------------|
| `geo_point_in_polygon` (as plugin) | ❌ Not available | Use as scalar function: `geo_point_in_polygon(lon, lat, polygon)` |
| `python` | ❌ Not available | Use powershell tool to run Python locally |

---

## 11. graph-match Syntax

**Error message**: `graph-match operator: variable edge 'path' edges don't have property 'IsVulnerable'`

```kql
// ❌ — property access on variable-length edge
graph-match (src)-[path*1..3]->(dst)
  where path.IsVulnerable == true

// ✅ — filter edges before building the graph
let edges = Edges | where IsVulnerable == true;
edges
| make-graph SourceId --> TargetId with Nodes on NodeId
| graph-match (src)-[path*1..3]->(dst)
  project src.Name, dst.Name

// ✅ — or use label-based filtering on persistent graphs
graph("MyGraph")
| graph-match (src)-[path*1..3]->(dst)
  where all(path, labels() has "Vulnerable")
  project src.Name, dst.Name
```

The fix is to pre-filter edges before graph construction.

---

## 12. E_RUNAWAY_QUERY

**Error message**: `Runaway query (E_RUNAWAY_QUERY): Join output block exceeded memory budget`

```kql
// ❌ — unconstrained cross-join on large tables
StormEvents | join kind=inner (nyc_taxi) on $left.EventId == $right.passenger_count

// ✅ — check cardinality first, pre-filter aggressively
// Step 1: StormEvents | summarize dcount(State)  → 67 — OK
// Step 2: PopulationData | count  → check if manageable
// Step 3: Pre-filter, then join
StormEvents
| where State in ("NEW YORK", "TEXAS")
| join kind=inner (PopulationData) on State
| take 5 | project State, EventType, Population
```

**Prevention**: Always `dcount()` both join sides before executing. If left × right > 1M, add filters.

````


### `references/query-templates.md`

````markdown
# KQL Query Templates

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

## Table of Contents

1. [Deduplication](#1-deduplication)
2. [Top-N Analysis](#2-top-n-analysis)
3. [Time Binning & Trends](#3-time-binning--trends)
4. [Pivoting](#4-pivoting)
5. [Running Totals & Window Functions](#5-running-totals--window-functions)
6. [Sessionization](#6-sessionization)
7. [Cardinality Check Before Join](#7-cardinality-check-before-join)
8. [Safe Exploration Pattern](#8-safe-exploration-pattern)
9. [Diffing & Anomaly Detection](#9-diffing--anomaly-detection)
10. [String Extraction Patterns](#10-string-extraction-patterns)

---

## 1. Deduplication

### Exact deduplication
```kql
// Remove exact duplicate rows
Table
| distinct *
```

### Dedup keeping latest per key
```kql
// Keep the most recent record per entity
Table
| summarize arg_max(Timestamp, *) by EntityId
```

### Dedup keeping first per key
```kql
// Keep the earliest record per entity
Table
| summarize arg_min(Timestamp, *) by EntityId
```

### Count duplicates
```kql
// 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
```kql
Table
| summarize EventCount = count() by Category
| top 10 by EventCount desc
```

### Top N by metric, with ties
```kql
Table
| summarize TotalRevenue = sum(Amount) by Customer
| order by TotalRevenue desc
| take 10
```

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

### Bottom N (least common)
```kql
Table
| summarize cnt = count() by Category
| top 10 by cnt asc
```

---

## 3. Time Binning & Trends

### Hourly event counts
```kql
Events
| summarize count() by bin(Timestamp, 1h)
| order by Timestamp asc
```

### Day-over-day comparison
```kql
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
```kql
Events
| extend HourOfDay = datetime_part("hour", Timestamp)
| summarize count() by HourOfDay
| order by HourOfDay asc
```

### Activity by day of week
```kql
Events
| extend DayOfWeek = dayofweek(Timestamp) / 1d
| summarize count() by DayOfWeek
| order by DayOfWeek asc
```

---

## 4. Pivoting

### Counts by category (pivot)
```kql
Events
| evaluate pivot(Category, count(), bin(Timestamp, 1d))
```

### Manual pivot with summarize
```kql
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
```kql
Table
| order by Timestamp asc
| extend RunningTotal = row_cumsum(Value)
```

### Row numbers
```kql
Table
| order by Score desc
| extend Rank = row_number()
```

### Previous/next row comparison
```kql
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
```kql
// 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
```kql
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:**

```kql
// 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

| Kind | Behavior | Use when |
|------|----------|----------|
| `inner` | Only matching rows from both | Default choice |
| `leftouter` | All left rows + matches from right | Need all left rows even without match |
| `leftanti` | Left rows with NO match in right | Finding missing/orphan records |
| `leftsemi` | Left rows that HAVE a match in right | Existence check (like SQL `EXISTS`) |
| `fullouter` | All rows from both sides | Comparing two datasets |

---

## 8. Safe Exploration Pattern

When encountering a new table, always follow this progression:

```kql
// 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
```kql
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
```kql
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
```kql
// Records in A but not in B
TableA | join kind=leftanti TableB on Key
```

### Find new entities (appeared after a cutoff)
```kql
let cutoff = datetime(2023-06-01);
Table
| summarize FirstSeen = min(Timestamp) by EntityId
| where FirstSeen > cutoff
```

---

## 10. String Extraction Patterns

### Extract structured fields
```kql
// 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
```kql
// Split a delimited string and expand into rows
Table
| extend Parts = split(DelimitedField, ",")
| mv-expand Part = Parts to typeof(string)
```

### URL parsing
```kql
Logs
| extend Host = parse_url(Url).Host
| extend Path = parse_url(Url).Path
| extend QueryParams = parse_url(Url).["Query Parameters"]
```

### JSON field extraction
```kql
// 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)
```

````