Kusto Query Language (KQL) basics in Azure - Commands & Configuration

Introduction

When you have lots of data in Azure Data Explorer, you need a simple way to ask questions and get answers quickly. Kusto Query Language (KQL) helps you search, filter, and analyze this data easily.

When you want to find specific events in large logs stored in Azure Data Explorer.

When you need to calculate simple statistics like counts or averages from your data.

When you want to filter data by time or other conditions to focus on important parts.

When you want to sort data to see the most recent or highest values first.

When you want to combine data from different tables to get a complete picture.

Commands

This command runs a KQL query on the 'StormEvents' table in the 'exampledb' database of the 'examplecluster'. It filters events where the state is Texas and shows 5 results.

Terminal

az kusto query --cluster-name examplecluster --database exampledb --query "StormEvents | where State == 'TX' | take 5"

Expected Output

Timestamp                 State  EventType  DamageProperty

2023-04-01T12:00:00Z     TX     Tornado    10000

2023-04-02T15:30:00Z     TX     Hail       5000

2023-04-03T09:45:00Z     TX     Flood      20000

2023-04-04T18:20:00Z     TX     Wind       3000

2023-04-05T11:10:00Z     TX     Tornado    15000

→

--cluster-name - Specifies the Azure Data Explorer cluster to query

→

--database - Specifies the database within the cluster

→

--query - The KQL query string to run

This command counts how many events of each type exist and sorts them from most to least frequent.

Terminal

az kusto query --cluster-name examplecluster --database exampledb --query "StormEvents | summarize Count = count() by EventType | order by Count desc"

Expected Output

EventType  Count

Tornado    120

Hail       85

Flood      60

Wind       40

→

--cluster-name - Specifies the Azure Data Explorer cluster to query

→

--database - Specifies the database within the cluster

→

--query - The KQL query string to run

This command filters events from the last 7 days and shows only the time, state, and event type columns.

Terminal

az kusto query --cluster-name examplecluster --database exampledb --query "StormEvents | where Timestamp > ago(7d) | project Timestamp, State, EventType"

Expected Output

Timestamp                 State  EventType

2023-05-01T10:00:00Z     CA     Hail

2023-05-02T14:30:00Z     TX     Tornado

2023-05-03T08:15:00Z     FL     Flood

→

--cluster-name - Specifies the Azure Data Explorer cluster to query

→

--database - Specifies the database within the cluster

→

--query - The KQL query string to run

Key Concept

If you remember nothing else from KQL, remember: you write simple commands to filter, sort, and summarize your data quickly.

Common Mistakes

Using incorrect table or column names in the query

The query will fail because the system cannot find the data you asked for

Check the exact table and column names in your database before writing the query

Forgetting to use quotes around the query string in the command

The command line will misinterpret the query and cause errors

Always put the entire KQL query inside double quotes

Not specifying the correct cluster or database name

The query will run against the wrong place or fail to connect

Double-check the cluster and database names before running the command

Summary

Use the az kusto query command with --cluster-name, --database, and --query flags to run KQL queries.

Filter data with 'where', limit results with 'take', and select columns with 'project'.

Summarize data with 'summarize' and sort results with 'order by' to analyze patterns.