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.

Practice

1. What does the pipe symbol | do in a Kusto Query Language (KQL) query?

easy

A. It defines a variable

B. It connects commands to process data step-by-step

C. It comments out the rest of the line

D. It ends the query

Solution

1. Step 1: Understand the role of the pipe in KQL

   The pipe symbol | is used to chain commands, passing the output of one command as input to the next.

2. Step 2: Compare with other options

   It does not comment, define variables, or end queries; those are different syntax elements.

3. Final Answer:

   It connects commands to process data step-by-step -> Option B

4. Quick Check:

   Pipe = Connect commands [OK]

Hint: Remember: pipe means 'then do this' in KQL [OK]

Common Mistakes:

• Thinking pipe comments code
• Confusing pipe with variable assignment
• Assuming pipe ends the query

2. Which of the following is the correct syntax to filter rows where the column Age is greater than 30 in KQL?

easy

A. Table | select Age > 30

B. Table where Age > 30

C. Table | filter Age > 30

D. Table | where Age > 30

Solution

1. Step 1: Identify the correct filter syntax in KQL

   KQL uses the where keyword after a pipe to filter rows based on a condition.

2. Step 2: Check each option

   Table | where Age > 30 uses | where Age > 30, which is correct. Table where Age > 30 misses the pipe. Table | filter Age > 30 uses filter which is not valid in KQL. Table | select Age > 30 uses select incorrectly.

3. Final Answer:

   Table | where Age > 30 -> Option D

4. Quick Check:

   Filter rows = pipe + where [OK]

Hint: Filter with '| where condition' in KQL [OK]

Common Mistakes:

• Omitting the pipe before where
• Using 'filter' instead of 'where'
• Using 'select' to filter rows

3. Given the query:

StormEvents | where State == "TX" | summarize Count = count() by EventType

What does this query return?

medium

A. The total number of events in Texas grouped by event type

B. All events in Texas without grouping

C. The count of all events in the dataset

D. Events grouped by state and event type

Solution

1. Step 1: Analyze the filter condition

   The query filters rows where the State column equals "TX", so only Texas events remain.

2. Step 2: Understand the summarize operation

   The summarize Count = count() by EventType groups the filtered data by EventType and counts the number of events per type.

3. Final Answer:

   The total number of events in Texas grouped by event type -> Option A

4. Quick Check:

   Filter by state, then group and count by event type [OK]

Hint: Summarize groups and counts after filtering [OK]

Common Mistakes:

• Ignoring the filter and counting all events
• Not recognizing grouping by EventType
• Confusing summarize with select

4. Identify the error in this KQL query:

StormEvents | where State = "CA" | summarize total = count() by EventType

medium

A. Using single equals (=) instead of double equals (==) for comparison

B. Missing pipe before summarize

C. Incorrect use of count() function

D. EventType should be in quotes

Solution

1. Step 1: Check the filter condition syntax

   In KQL, equality comparison requires double equals ==, not single equals =.

2. Step 2: Verify other parts of the query

   The pipe before summarize is present, count() is used correctly, and EventType is a column name that does not need quotes.

3. Final Answer:

   Using single equals (=) instead of double equals (==) for comparison -> Option A

4. Quick Check:

   Comparison uses '==' not '=' [OK]

Hint: Use '==' for comparisons in KQL [OK]

Common Mistakes:

• Using '=' instead of '==' in where clause
• Adding quotes around column names
• Forgetting pipe before summarize

5. You want to find the top 3 states with the highest number of storm events. Which KQL query correctly achieves this?

hard

A. StormEvents | top 3 by State | summarize Count = count()

B. StormEvents | summarize Count = count() by State | sort by Count asc | limit 3

C. StormEvents | summarize Count = count() by State | top 3 by Count desc

D. StormEvents | where Count > 3 | summarize by State

Solution

1. Step 1: Summarize event counts by state

   The query must group events by State and count them using summarize Count = count() by State.

2. Step 2: Select top 3 states by count descending

   Use top 3 by Count desc to get the three states with the highest counts.

3. Final Answer:

   StormEvents | summarize Count = count() by State | top 3 by Count desc -> Option C

4. Quick Check:

   Group by state, count, then top 3 descending [OK]

Hint: Use 'summarize' then 'top' to get highest counts [OK]

Common Mistakes:

• Using top before summarize
• Sorting ascending instead of descending
• Filtering by Count before summarizing