Kusto Query Language (KQL) basics in Azure - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is Kusto Query Language (KQL)?

KQL is a simple, read-only language used to query large sets of data quickly in Azure Data Explorer and other Microsoft services.

beginner

How do you filter rows in KQL?

Use the where keyword followed by a condition to filter rows that meet that condition.

beginner

What does the project operator do in KQL?

It selects specific columns from the data, similar to choosing which columns to see in a table.

intermediate

Explain the use of the summarize operator in KQL.

It groups data and calculates aggregates like count, sum, or average for each group.

beginner

How do you sort results in KQL?

Use the order by keyword followed by the column name and direction (asc or desc).

Which KQL operator filters rows based on a condition?

A project

B where

C summarize

D order by

The where operator filters rows that meet a specified condition.

What does the project operator do?

A Selects columns

B Filters rows

C Groups data

D Sorts data

The project operator selects specific columns to include in the output.

Which operator would you use to calculate the total count of rows grouped by a column?

A summarize

B project

C where

D order by

The summarize operator groups data and calculates aggregates like count.

How do you sort query results in descending order by a column named 'Timestamp'?

A order by Timestamp asc

B sort Timestamp desc

C order by Timestamp desc

D sort Timestamp asc

Use order by Timestamp desc to sort results descending by 'Timestamp'.

Which of these is NOT a valid KQL operator?

A where

B project

C summarize

D filter

KQL uses where for filtering, not filter.

Describe how you would write a KQL query to find all records where the 'Status' column equals 'Error' and only show the 'Timestamp' and 'Message' columns.

Think about filtering first, then choosing columns.

Explain how to group data by a column and count the number of records in each group using KQL.

Use summarize with count and specify the grouping column.

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