Kusto Query Language (KQL) basics in Azure - Step-by-Step Execution

Process Flow - Kusto Query Language (KQL) basics

Start Query

↓

Read Table

↓

Apply Filters

↓

Project Columns

↓

Sort or Aggregate

↓

Return Results

KQL queries start by reading a table, then filter rows, select columns, optionally sort or aggregate, and finally return results.

Execution Sample

Azure

StormEvents
| where State == "TX"
| project StartTime, EventType
| sort by StartTime desc

This query reads the StormEvents table, filters for Texas events, selects StartTime and EventType columns, and sorts results by StartTime descending.

Process Table

Step	Action	Input Rows	Output Rows	Details
1	Read Table StormEvents	All rows	All rows	Load all data from StormEvents
2	Filter where State == "TX"	All rows	Filtered rows	Keep only rows where State is TX
3	Project StartTime, EventType	Filtered rows	Same rows	Select only StartTime and EventType columns
4	Sort by StartTime desc	Projected rows	Same rows	Order rows by StartTime newest first
5	Return Results	Sorted rows	Sorted rows	Output final result set

💡 Query completes after returning sorted filtered and projected rows.

Status Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	Final
Rows	N/A	All rows	Filtered rows (State == TX)	Projected rows (StartTime, EventType only)	Sorted rows by StartTime desc	Sorted filtered projected rows

Key Moments - 3 Insights

Why does the number of rows decrease after the filter step?

Because the filter keeps only rows where State equals TX, removing all others as shown in step 2 of the execution table.

Does projecting columns change the number of rows?

No, projecting only selects columns but keeps the same number of rows, as seen in step 3 where output rows remain the same.

What does sorting do to the data?

Sorting changes the order of rows but does not add or remove rows, as shown in step 4 where rows are ordered by StartTime descending.

Visual Quiz - 3 Questions

Look at the execution table, after which step are only Texas events kept?

A Step 1

B Step 3

C Step 2

D Step 4

💡 Hint

Check the 'Action' and 'Details' columns in the execution table for step 2.

At which step does the query reduce columns to only StartTime and EventType?

A Step 3

B Step 2

C Step 4

D Step 5

💡 Hint

Look at the 'Project' action in the execution table.

If we remove the filter step, what happens to the number of rows after step 2?

A Rows decrease to only Texas events

B Rows stay the same as all rows are kept

C Rows increase

D Rows become zero

💡 Hint

Filtering is what reduces rows; without it, all rows remain.

Concept Snapshot

Kusto Query Language (KQL) basics:
- Start with a table name
- Use | to chain commands
- 'where' filters rows
- 'project' selects columns
- 'sort by' orders rows
- Results show filtered, selected, sorted data

Full Transcript

This visual execution traces a simple Kusto Query Language query. It starts by reading all rows from the StormEvents table. Then it filters rows to keep only those where the State is TX. Next, it projects only the StartTime and EventType columns, keeping the same number of rows. After that, it sorts the rows by StartTime in descending order. Finally, it returns the sorted, filtered, and projected results. Key moments include understanding that filtering reduces rows, projecting changes columns but not rows, and sorting changes order but not row count.

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