Eventhouse Consumption Cli

Update Check — ONCE PER SESSION (mandatory)

The first time this skill is used in a session, run the check-updates skill before proceeding.

- GitHub Copilot CLI / VS Code: invoke the check-updates skill.

- Claude Code / Cowork / Cursor / Windsurf / Codex: compare local vs remote package.json version.

- Skip if the check was already performed earlier in this session.

CRITICAL NOTES

1. To find the workspace details (including its ID) from workspace name: list all workspaces and, then, use JMESPath filtering

2. To find the item details (including its ID) from workspace ID, item type, and item name: list all items of that type in that workspace and, then, use JMESPath filtering

eventhouse-consumption-cli — Read-Only KQL Queries via CLI

Table of Contents

---

Tool Stack

Connection

Step 1 — Discover KQL Database Query URI

# Get workspace ID (if not known)
WS_ID=$(az rest --method GET \
  --url "https://api.fabric.microsoft.com/v1/workspaces" \
  --resource "https://api.fabric.microsoft.com" \
  | jq -r '.value[] | select(.displayName=="MyWorkspace") | .id')

# List KQL Databases and get connection properties
az rest --method GET \
  --url "https://api.fabric.microsoft.com/v1/workspaces/${WS_ID}/kqlDatabases" \
  --resource "https://api.fabric.microsoft.com" \
  | jq '.value[] | {name: .displayName, id: .id, queryUri: .properties.queryServiceUri, dbName: .properties.databaseName}'

Step 2 — Set Connection Variables

CLUSTER_URI="https://<cluster>.kusto.fabric.microsoft.com"
DB_NAME="MyKqlDatabase"

Step 3 — Verify Connection

Important — body file pattern: KQL queries contain | (pipe) characters which break shell

escaping in both bash and PowerShell. Always write the JSON body to a temp file and reference

it with --body @<file>. This is the recommended approach for all az rest KQL calls.

On PowerShell, use @{db="X";csl="..."} | ConvertTo-Json -Compress | Out-File $env:TEMP\kql_body.json -Encoding utf8NoBOM then --body "@$env:TEMP\kql_body.json".

# Write body to temp file (avoids pipe escaping issues)
cat > /tmp/kql_body.json << 'EOF'
{"db":"MyKqlDatabase","csl":"print Message = 'Connected successfully', Cluster = current_cluster_endpoint(), Timestamp = now()"}
EOF

az rest --method POST \
  --url "${CLUSTER_URI}/v1/rest/query" \
  --resource "https://kusto.kusto.windows.net" \
  --headers "Content-Type=application/json" \
  --body @/tmp/kql_body.json \
  | jq '.Tables[0].Rows'

---

Agentic Exploration

"Chat With My Data" — Discovery Sequence

When the user asks to explore or query an Eventhouse without specifying tables:

Step 1 → .show tables                                    // discover tables
Step 2 → .show table <TABLE> schema as json              // understand columns + types
Step 3 → <TABLE> | take 10                               // see sample data
Step 4 → <TABLE> | summarize count() by bin(Timestamp, 1h) | render timechart  // shape of data
Step 5 → Formulate targeted query based on user's question

Schema-Aware Query Generation

After schema discovery, generate queries using actual column names and types:

// Example: user asks "show me errors in the last hour"
// After discovering table "AppEvents" with columns: Timestamp, Level, Message, Source
AppEvents
| where Timestamp > ago(1h)
| where Level == "Error"
| summarize ErrorCount = count() by Source, bin(Timestamp, 5m)
| order by ErrorCount desc

---

Running Queries

Via az rest

Always use the temp-file pattern for --body — KQL pipes (|) break inline shell escaping.

# Run a KQL query
cat > /tmp/kql_body.json << 'EOF'
{"db":"MyDB","csl":"Events | where Timestamp > ago(1h) | count"}
EOF

az rest --method POST \
  --url "${CLUSTER_URI}/v1/rest/query" \
  --resource "https://kusto.kusto.windows.net" \
  --headers "Content-Type=application/json" \
  --body @/tmp/kql_body.json \
  | jq '.Tables[0].Rows'

Output Formatting

# Pretty-print results as a table with jq
cat > /tmp/kql_body.json << 'EOF'
{"db":"MyDB","csl":".show tables"}
EOF

az rest --method POST \
  --url "${CLUSTER_URI}/v1/rest/query" \
  --resource "https://kusto.kusto.windows.net" \
  --headers "Content-Type=application/json" \
  --body @/tmp/kql_body.json \
  | jq '.Tables[0] | [.Columns[].ColumnName] as $cols | .Rows[] | [$cols, .] | transpose | map({(.[0]): .[1]}) | add'

# Save results to file
cat > /tmp/kql_body.json << 'EOF'
{"db":"MyDB","csl":"Events | where Timestamp > ago(1h) | summarize count() by EventType"}
EOF

az rest --method POST \
  --url "${CLUSTER_URI}/v1/rest/query" \
  --resource "https://kusto.kusto.windows.net" \
  --headers "Content-Type=application/json" \
  --body @/tmp/kql_body.json \
  --output-file results.json

---

Monitoring

// Active queries
.show queries

// Recent commands (last hour)
.show commands
| where StartedOn > ago(1h)
| project StartedOn, CommandType, Text = substring(Text, 0, 80), Duration, State
| order by StartedOn desc

// Ingestion failures (for context when data seems stale)
.show ingestion failures
| where FailedOn > ago(24h)
| summarize count() by ErrorCode
| top 5 by count_

---

Must / Prefer / Avoid / Troubleshooting

Must

• Always include time filters — where Timestamp > ago(...) must be present on time-series tables.
• Discover schema before querying — run .show tables and .show table T schema as json first.
• Use `has` for term search — indexed and fast; only fall back to contains for substring needs.
• Verify cluster URI — KQL Database URIs are per-item; always resolve via Fabric REST API.

Prefer

• `az rest` for CLI query sessions; Fabric KQL MCP server for agent-integrated workflows.
• `project` early to drop unneeded columns before aggregation.
• `materialize()` when a sub-expression is used multiple times.
• `take 100` for initial exploration; avoid full table scans.
• `render timechart` for time-series; render piechart for distribution.

Avoid

• `contains` on large tables — full scan, not indexed. Use has or has_cs.
• `join` without filtering both sides first — causes memory explosion.
• *`SELECT ** equivalent (project` all columns) on wide tables.
• Missing `bin()` in time-series summarize — produces one row per unique timestamp.
• Hardcoded cluster URIs — always resolve from Fabric REST API or environment variables.

Troubleshooting

---

Examples

Example 1: Discover and Query

# 1. Set connection variables (after discovering URI via Step 1)
CLUSTER_URI="https://<your-cluster>.kusto.fabric.microsoft.com"
DB_NAME="SalesDB"

# 2. Discover tables
cat > /tmp/kql_body.json << EOF
{"db":"${DB_NAME}","csl":".show tables"}
EOF
az rest --method POST \
  --url "${CLUSTER_URI}/v1/rest/query" \
  --resource "https://kusto.kusto.windows.net" \
  --headers "Content-Type=application/json" \
  --body @/tmp/kql_body.json \
  | jq '.Tables[0].Rows'

# 3. Explore schema
cat > /tmp/kql_body.json << EOF
{"db":"${DB_NAME}","csl":".show table Orders schema as json"}
EOF
az rest --method POST \
  --url "${CLUSTER_URI}/v1/rest/query" \
  --resource "https://kusto.kusto.windows.net" \
  --headers "Content-Type=application/json" \
  --body @/tmp/kql_body.json \
  | jq '.Tables[0].Rows'

# 4. Sample data
cat > /tmp/kql_body.json << EOF
{"db":"${DB_NAME}","csl":"Orders | take 10"}
EOF
az rest --method POST \
  --url "${CLUSTER_URI}/v1/rest/query" \
  --resource "https://kusto.kusto.windows.net" \
  --headers "Content-Type=application/json" \
  --body @/tmp/kql_body.json \
  | jq '.Tables[0].Rows'

// 5. Analytical query (via az rest --body @file)
Orders
| where OrderDate > ago(30d)
| summarize
    TotalOrders = count(),
    TotalRevenue = sum(Amount)
    by bin(OrderDate, 1d)
| render timechart

Example 2: Cross-Database Query

// Query across KQL databases in the same Eventhouse
let orders = database("SalesDB").Orders | where OrderDate > ago(7d);
let products = database("CatalogDB").Products;
orders
| join kind=inner (products) on ProductId
| summarize Revenue = sum(Amount) by ProductName
| top 10 by Revenue desc

Example 3: Export Results to File

# Run query and save results to JSON
cat > /tmp/kql_body.json << 'EOF'
{"db":"MyDB","csl":"Events | where Timestamp > ago(1d) | summarize count() by EventType"}
EOF

az rest --method POST \
  --url "${CLUSTER_URI}/v1/rest/query" \
  --resource "https://kusto.kusto.windows.net" \
  --headers "Content-Type=application/json" \
  --body @/tmp/kql_body.json \
  --output-file results.json

# Convert to CSV with jq
cat results.json \
  | jq -r '.Tables[0] | (.Columns | map(.ColumnName)), (.Rows[]) | @csv' > results.csv

---

Agent Integration Notes

• This skill is read-only — it does not create, alter, or drop database objects.
• For authoring operations (table management, ingestion, policies), delegate to eventhouse-authoring-cli.
• For cross-workload orchestration (Spark + SQL + KQL), delegate to the FabricDataEngineer agent.
• The Fabric KQL MCP server (fabric-kql in mcp-setup/mcp-config-template.json) can be used as an alternative to az rest for agent-integrated query execution.

KQL Schema Discovery Queries

Reference for schema exploration commands used during agentic discovery workflows.

---

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 MyTable schema as json

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

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

Column Statistics

// Column cardinality and statistics (sample-based)
.show table MyTable column statistics

// Quick column profiling via query
MyTable
| take 10000
| summarize
    Rows = count(),
    Nulls = countif(isnull(ColumnName)),
    Distinct = dcount(ColumnName),
    MinVal = min(ColumnName),
    MaxVal = max(ColumnName)

---

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 MyFunction

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

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 MyView statistics

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

---

Policy Discovery

// Retention policies
.show table MyTable policy retention

// Caching policies
.show table MyTable policy caching

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

// Streaming ingestion policy
.show table MyTable policy streamingingestion

// Update policies
.show table MyTable policy update

---

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 MyExternalTable schema as json

Ingestion Mapping Discovery

// CSV mappings for a table
.show table MyTable ingestion csv mappings

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

// All mappings for a table
.show table MyTable ingestion mappings

---

Security Discovery

// Database-level principals
.show database MyDatabase principals

// Table-level principals
.show table MyTable 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