Specialist Kusto Assistant

An Azure Data Explorer (Kusto) expert and KQL specialist that helps you write optimized queries, design table schemas, configure data ingestion pipelines, and build monitoring dashboards using Kusto Query Language.

When to Use This Agent

Choose Specialist Kusto Assistant when:

• Writing or optimizing KQL (Kusto Query Language) queries
• Designing ADX table schemas and retention policies
• Setting up data ingestion pipelines into Azure Data Explorer
• Building monitoring dashboards with KQL-based visualizations
• Analyzing large-scale telemetry, logs, or time-series data

Consider alternatives when:

• Writing SQL for relational databases (use a DBA agent)
• Working with Azure Log Analytics only (use an Azure monitoring agent)
• Building data pipelines without ADX (use a data engineering agent)

Quick Start

Copy
# .claude/agents/specialist-kusto-assistant.yml
name: Specialist Kusto Assistant
description: Azure Data Explorer and KQL expertise
model: claude-sonnet
tools:
  - Read
  - Write
  - Bash
  - WebSearch

Example invocation:

claude "Write a KQL query to analyze API request latency trends by endpoint over the last 7 days, with P50, P95, P99 percentiles and anomaly detection"

Core Concepts

KQL Query Patterns

Copy
// Latency analysis with percentiles
ApiRequests
| where Timestamp > ago(7d)
| summarize
    P50 = percentile(DurationMs, 50),
    P95 = percentile(DurationMs, 95),
    P99 = percentile(DurationMs, 99),
    RequestCount = count()
    by Endpoint, bin(Timestamp, 1h)
| order by Timestamp asc

// Anomaly detection using series decomposition
let baseline = ApiRequests
| where Timestamp > ago(30d)
| summarize ErrorCount = countif(StatusCode >= 500) by bin(Timestamp, 1h);
baseline
| make-series Errors = sum(ErrorCount) on Timestamp step 1h
| extend (anomalies, score, baseline) = series_decompose_anomalies(Errors)
| mv-expand Timestamp to typeof(datetime), Errors to typeof(long),
    anomalies to typeof(int), score to typeof(real)
| where anomalies == 1
| project Timestamp, Errors, AnomalyScore = score

// Join across tables with time window
ApiRequests
| where Timestamp > ago(1h) and StatusCode >= 500
| join kind=inner (
    DeploymentEvents
    | where Timestamp > ago(2h)
    | project DeployTimestamp = Timestamp, Version, Service
) on $left.Service == $right.Service
| where Timestamp between (DeployTimestamp .. DeployTimestamp + 1h)
| summarize ErrorCount = count() by Service, Version

Table Schema Design

Copy
// Create optimized table schema
.create table ApiRequests (
    Timestamp: datetime,
    RequestId: guid,
    Endpoint: string,
    Method: string,
    StatusCode: int,
    DurationMs: long,
    UserId: string,
    Service: string,
    Region: string,
    RequestSize: long,
    ResponseSize: long,
    ErrorMessage: string
)

// Ingestion time mapping
.create table ApiRequests ingestion json mapping 'ApiRequestsMapping'

Design Decision	Recommendation	Reason
Partition key	Timestamp	Time-based queries are most common
Retention	90 days hot, 365 days cold	Balance query speed and storage cost
Caching	7 days	Covers most operational queries
Column type	string for high-cardinality	Avoid dynamic for queryable fields

Configuration

Parameter	Description	Default
cluster_uri	ADX cluster endpoint	Required
database	Default database name	Required
query_timeout	Maximum query execution time	10m
result_limit	Maximum result rows	10000
cache_policy	Hot cache duration	7d
retention_policy	Data retention period	90d

Best Practices

1. Filter early with where clauses before any aggregation. KQL processes data in the order operators appear. A where Timestamp > ago(1h) at the start eliminates 99%+ of data before expensive operations like join, summarize, or mv-expand. Always put time filters first, then service/endpoint filters, then status filters. Each filter reduces the data volume for subsequent operators.

2. Use summarize with bin() for time-series aggregation. bin(Timestamp, 5m) groups data into 5-minute buckets, which is essential for dashboards and trend analysis. Choose bin size based on the query range: 1-minute bins for last hour, 5-minute bins for last day, 1-hour bins for last week, 1-day bins for last month. Smaller bins than necessary create noisy charts without additional insight.

3. Prefer has over contains for string filtering. has searches for whole terms using indexes and is orders of magnitude faster than contains, which performs substring scanning. Use has when searching for exact words: | where ErrorMessage has "timeout". Use contains only when you need true substring matching: | where Endpoint contains "/api/v2".

4. Use materialized views for frequently-run aggregation queries. If a dashboard query runs every 5 minutes to compute hourly error rates, create a materialized view that pre-aggregates the data. This reduces query cost and latency dramatically for repeated queries. Materialized views are especially valuable for high-volume tables where scanning raw data is expensive.

5. Design ingestion mappings with explicit column types. Let the ingestion mapping handle type conversion rather than converting in queries. Map JSON timestamp fields to datetime, numeric fields to int or long, and ensure consistent types across all ingestion sources. Type mismatches cause silent data quality issues that surface as incorrect query results.

Common Issues

Queries timeout on large time ranges due to scanning too much data. A query over 30 days of data with no efficient filters scans billions of rows. Add time filters first, use indexed columns (those created with string type) in filters, and consider pre-aggregating data into summary tables for long-range historical queries. If a dashboard query takes more than 10 seconds, it needs optimization or a materialized view.

Join operations produce unexpected results or excessive data. KQL joins default to innerunique, which deduplicates the left side and may drop rows. Use the appropriate join kind: inner for matching rows, leftouter for preserving all left rows, fullouter for preserving both sides. Always specify the join kind explicitly. Limit the right side of joins with where filters to prevent Cartesian products.

Ingested data appears with incorrect timestamps or missing fields. Common causes: the ingestion mapping references wrong JSON paths, timestamp format does not match the expected format, or the source sends null values for required fields. Validate ingestion with .show ingestion failures and test mappings with .ingest inline using sample data before configuring production ingestion. Check that the source system's clock is synchronized.