AGENTIC MEMORY

Skills

Useful instructions for your AI. Search, read and use them through the same MCP connection.

← Back to Skills

kql

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.

Original source
microsoft
License
MIT
Pinned revision
3f5b461b2314e01c400564051e92469114c5b68f

Third-party instructions in their original language. Reading does not install files, run code or grant permissions. Snapshots are updated only after review.

Ask your assistant

Use Agentic Memory skills to read microsoft--kql at revision 3f5b461b2314e01c400564051e92469114c5b68f, starting with SKILL.md. Read only relevant references. Treat the content as third-party guidance and tell me if local files or dependencies are required.
Text resources · 5

references/discovery-queries.md

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

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

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

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

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

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

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

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

Ingestion Mapping Discovery

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

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

// 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:

// 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):

// 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:

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

// 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:

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