Azure Data Explorer (Kusto) Query & Analytics

Skill Activation Triggers

• "Query my Kusto database for [data pattern]"
• "Show me events in the last hour from Azure Data Explorer"
• "Analyze logs in my ADX cluster"
• "Run a KQL query on [database]"
• "What tables are in my Kusto database?"
• "Show me the schema for [table]"
• "List my Azure Data Explorer clusters"
• "Aggregate telemetry data by [dimension]"
• "Create a time series chart from my logs"

• Mentions "Kusto", "Azure Data Explorer", "ADX", or "KQL"
• Log analytics or telemetry analysis requests
• Time series data exploration
• IoT data analysis queries
• SIEM or security analytics tasks
• Requests for data aggregation on large datasets
• Performance monitoring or APM queries

Overview

• Query Execution: Run KQL queries against massive datasets
• Schema Exploration: Discover tables, columns, and data types
• Resource Management: List clusters and databases
• Analytics: Aggregations, time series, anomaly detection, machine learning

Core Workflow

1. Discover Resources: List available clusters and databases in subscription
2. Explore Schema: Retrieve table structures to understand data model
3. Query Data: Execute KQL queries for analysis, filtering, aggregation
4. Analyze Results: Process query output for insights and reporting

Query Patterns

Pattern 1: Basic Data Retrieval

Events
| where Timestamp > ago(1h)
| take 100

Pattern 2: Aggregation Analysis

Events
| summarize count() by EventType, bin(Timestamp, 1h)
| order by count_ desc

Pattern 3: Time Series Analytics

Telemetry
| where Timestamp > ago(24h)
| summarize avg(ResponseTime), percentiles(ResponseTime, 50, 95, 99) by bin(Timestamp, 5m)
| render timechart

Pattern 4: Join and Correlation

Events
| where EventType == "Error"
| join kind=inner (
    Logs
    | where Severity == "Critical"
) on CorrelationId
| project Timestamp, EventType, LogMessage, Severity

Pattern 5: Schema Discovery

kusto_table_schema_get

Key Data Fields

• Timestamp: Time of event (datetime) - use

  ago()

  ,

  between()

  ,

  bin()

  for time filtering
• EventType/Category: Classification field for grouping
• CorrelationId/SessionId: For tracing related events
• Severity/Level: For filtering by importance
• Dimensions: Custom properties for grouping and filtering

Result Format

• Columns: Field names and data types
• Rows: Data records matching query
• Statistics: Row count, execution time, resource utilization
• Visualization: Chart rendering hints (timechart, barchart, etc.)

KQL Best Practices

• Filter early: Use

  where

  before joins and aggregations
• Limit result size: Use

  take

  or

  limit

  to reduce data transfer
• Time filters: Always filter by time range for time series data
• Indexed columns: Filter on indexed columns first

• Use

  summarize

  for aggregations instead of

  count()

  alone
• Use

  bin()

  for time bucketing in time series
• Use

  project

  to select only needed columns
• Use

  extend

  to add calculated fields

• ago(timespan)

  : Relative time (ago(1h), ago(7d))

• between(start .. end)

  : Range filtering

• startswith()

  ,

  contains()

  ,

  matches regex

  : String filtering

• parse

  ,

  extract

  : Extract values from strings

• percentiles()

  ,

  avg()

  ,

  sum()

  ,

  max()

  ,

  min()

  : Aggregations

Best Practices

• Always include time range filters to optimize query performance
• Use

  take

  or

  limit

  for exploratory queries to avoid large result sets
• Leverage

  summarize

  for aggregations instead of client-side processing
• Store frequently-used queries as functions in the database
• Use materialized views for repeated aggregations
• Monitor query performance and resource consumption
• Apply data retention policies to manage storage costs
• Use streaming ingestion for real-time analytics (< 1 second latency)
• Integrate with Azure Monitor for operational insights

MCP Tools Used

kusto_cluster_list

kusto_database_list

kusto_query

kusto_table_schema_get

• subscription

  : Azure subscription ID or display name

• cluster

  : Kusto cluster name (e.g., "mycluster")

• database

  : Database name

• query

  : KQL query string (for query operations)

• table

  : Table name (for schema operations)

• resource-group

  : Resource group name (for listing operations)

• tenant

  : Azure AD tenant ID

Fallback Strategy: Azure CLI Commands

CLI Command Reference

az kusto cluster list --resource-group <rg-name>

az kusto database list --cluster-name <cluster> --resource-group <rg-name>

az kusto cluster show --name <cluster> --resource-group <rg-name>

az kusto database show --cluster-name <cluster> --database-name <db> --resource-group <rg-name>

KQL Query via Azure CLI

az rest --method post \
  --url "https://<cluster>.<region>.kusto.windows.net/v1/rest/query" \
  --body "{ \"db\": \"<database>\", \"csl\": \"<kql-query>\" }"

When to Fallback

• MCP tool returns timeout error (queries > 60 seconds)
• MCP tool returns "service unavailable" or connection errors
• Authentication failures with MCP tools
• Empty response when database is known to have data

Common Issues

• Access Denied: Verify database permissions (Viewer role minimum for queries)
• Query Timeout: Optimize query with time filters, reduce result set, or increase timeout
• Syntax Error: Validate KQL syntax - common issues: missing pipes, incorrect operators
• Empty Results: Check time range filters (may be too restrictive), verify table name
• Cluster Not Found: Check cluster name format (exclude ".kusto.windows.net" suffix)
• High CPU Usage: Query too broad - add filters, reduce time range, limit aggregations
• Ingestion Lag: Streaming data may have 1-30 second delay depending on ingestion method

Use Cases

• Log Analytics: Application logs, system logs, audit logs
• IoT Analytics: Sensor data, device telemetry, real-time monitoring
• Security Analytics: SIEM data, threat detection, security event correlation
• APM: Application performance metrics, user behavior, error tracking
• Business Intelligence: Clickstream analysis, user analytics, operational KPIs