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

Need a 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

Need a 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

Need a 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

Need a hint?

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