Kusto Query Language (KQL) basics in Azure - Mini Project: Build & Apply

Kusto Query Language (KQL) basics

📖 Scenario: You are working with Azure Data Explorer to analyze server logs. You want to learn how to write basic Kusto Query Language (KQL) queries to filter and summarize data.

🎯 Goal: Build a simple KQL query step-by-step that filters log entries by severity, selects specific columns, and summarizes the count of errors by source.

📋 What You'll Learn

Create a table variable with sample log data

Add a filter condition to select only error logs

Project specific columns from the filtered data

Summarize the count of errors grouped by source

💡 Why This Matters

🌍 Real World

Analyzing logs in Azure Data Explorer to monitor application health and troubleshoot errors.

💼 Career

Basic KQL skills are essential for cloud engineers and data analysts working with Azure monitoring and diagnostics.

1

Create sample log data table

Create a table called Logs with these exact columns and rows using the datatable operator:
Timestamp: datetime, Source: string, Severity: string, Message: string
Include these rows:
2024-06-01T10:00:00Z, 'App1', 'Info', 'Startup complete'
2024-06-01T10:05:00Z, 'App2', 'Error', 'Null reference exception'
2024-06-01T10:10:00Z, 'App1', 'Error', 'Timeout occurred'

Azure

# Create the Logs table with sample data
# Your code here

Hint

Use the datatable operator to create a table with columns and rows.

2

Filter logs to only errors

Add a filter to the Logs table to select only rows where Severity equals 'Error'. Use the where operator with Severity == 'Error'.

Azure

Logs = datatable(Timestamp: datetime, Source: string, Severity: string, Message: string) [
    datetime(2024-06-01T10:00:00Z), 'App1', 'Info', 'Startup complete',
    datetime(2024-06-01T10:05:00Z), 'App2', 'Error', 'Null reference exception',
    datetime(2024-06-01T10:10:00Z), 'App1', 'Error', 'Timeout occurred'
]

Errors = Logs
| where Severity == 'Error'  # Filter only error logs

Hint

Use | where Severity == 'Error' to filter rows.

3

Select columns Timestamp, Source, and Message

From the Errors table, select only the columns Timestamp, Source, and Message using the project operator.

Azure

Logs = datatable(Timestamp: datetime, Source: string, Severity: string, Message: string) [
    datetime(2024-06-01T10:00:00Z), 'App1', 'Info', 'Startup complete',
    datetime(2024-06-01T10:05:00Z), 'App2', 'Error', 'Null reference exception',
    datetime(2024-06-01T10:10:00Z), 'App1', 'Error', 'Timeout occurred'
]

Errors = Logs
| where Severity == 'Error'

ErrorDetails = Errors
| project Timestamp, Source, Message  # Select specific columns

Hint

Use | project Timestamp, Source, Message to select columns.

4

Summarize count of errors by source

From the ErrorDetails table, use the summarize operator to count the number of errors for each Source. Name the count column ErrorCount.

Azure

Logs = datatable(Timestamp: datetime, Source: string, Severity: string, Message: string) [
    datetime(2024-06-01T10:00:00Z), 'App1', 'Info', 'Startup complete',
    datetime(2024-06-01T10:05:00Z), 'App2', 'Error', 'Null reference exception',
    datetime(2024-06-01T10:10:00Z), 'App1', 'Error', 'Timeout occurred'
]

Errors = Logs
| where Severity == 'Error'

ErrorDetails = Errors
| project Timestamp, Source, Message

ErrorSummary = ErrorDetails
| summarize ErrorCount = count() by Source  # Count errors by source

Hint

Use | summarize ErrorCount = count() by Source to group and count errors.

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