# 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 all nodes
StormEvents | summarize hint.strategy=shuffle count() by EventId
```

### Query materialized views efficiently

Use the `materialized_view()` function to query only the pre-aggregated part (faster), rather than the full view which may include un-materialized delta records:

```kql
// try it! — queries only the materialized (pre-computed) part — fastest
materialized_view("DailyCovid19") | take 5

// Queries materialized + delta records — complete but slower
DailyCovid19 | take 5
```

# 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 |

# 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.

# 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)
```