KQL Escape Room: Break Out with Your Query Skills

Ready to test your query prowess? Let’s dive in!

Welcome to the KQL Escape Room! This virtual adventure challenges your Kusto Query Language (KQL) skills to solve puzzles and unlock stages. Each stage introduces a new KQL concept, from defining variables with let to combining datasets with union. You’ll work with a sample dataset, experiment with queries, and sharpen your skills to break out. Ready to test your query prowess? Let’s dive in!

Get the datasets for this KQL activity: https://github.com/rod-trent/KQL_Escape_Room

The Setup: Your Virtual Escape Room

You’re locked in a digital vault filled with data clues. To escape, you must solve three KQL challenges, each unlocking a door to the next stage. The vault contains a sample dataset called SecurityLogs, which tracks system access events. Here’s a peek at the data:

You’ll write KQL queries to analyze this dataset and unlock each stage. Let’s get started with Stage 1!

Stage 1: Define Variables with let

Challenge: The first door requires you to identify users who logged in from the "Office" location. To make your query reusable, define a variable for the location using the let statement. Write a query that returns the distinct UserId values for "Office" logins.

KQL Concept: The let statement allows you to define variables, making queries modular and easier to maintain. For example, let location = "Office"; assigns the value "Office" to the variable location.

Your Task:

• Use let to define a variable targetLocation set to "Office".

• Filter SecurityLogs for EventType == "Login" and Location == targetLocation.

• Return distinct UserId values.

Sample Query:

let targetLocation = "Office";
SecurityLogs
| where EventType == "Login" and Location == targetLocation
| summarize by UserId

Try It: Run the query against the SecurityLogs dataset. You should get U101. If you do, the first door unlocks! Experiment by changing targetLocation to "Remote" or "Cafe" to see different results.

Why It Works: The let statement simplifies the query by storing the location in a variable. This makes it easy to update the location without rewriting the entire query.

Stage 2: Combine Datasets with union

Challenge: The second door requires you to combine two datasets: SecurityLogs and a new table, AuditLogs, which tracks admin actions. Find all events (from both tables) that occurred on a "Laptop" device. Use the union operator to merge the datasets.

New Dataset: Here’s the AuditLogs table:

KQL Concept: The union operator combines rows from multiple tables, even if they have different schemas. For example, SecurityLogs | union AuditLogs merges the two tables, aligning common columns like Timestamp and UserId.

Your Task:

• Use union to combine SecurityLogs and AuditLogs.

• Filter for events where Device == "Laptop".

• Return Timestamp, UserId, EventType, and Device.

Sample Query:

SecurityLogs
| union AuditLogs
| where Device == "Laptop"
| project Timestamp, UserId, EventType, Device

Try It: Run the query. You should see events from both tables involving "Laptop" devices, like U101’s login and U201’s admin actions. If the results match, the second door opens! Try filtering for "Desktop" to explore other devices.

Why It Works: union seamlessly merges the tables, allowing you to query across datasets as if they were one. The project operator selects only the columns you need, keeping the output clean.

Stage 3: Aggregate with summarize

Challenge: The final door requires you to analyze user activity. Calculate the number of events per UserId across both SecurityLogs and AuditLogs. Use the summarize operator to group and count events, and sort the results by count in descending order.

KQL Concept: The summarize operator groups data by one or more columns and applies aggregation functions, like count(). For example, summarize EventCount = count() by UserId counts events per user.

Your Task:

• Combine SecurityLogs and AuditLogs using union.

• Use summarize to count events by UserId.

• Sort results by event count in descending order using order by.

Sample Query:

SecurityLogs
| union AuditLogs
| summarize EventCount = count() by UserId
| order by EventCount desc

Try It: Run the query. You should see U101 and U201 with two events each, followed by U102, U103, and U202 with one event each. If the counts are correct, the final door unlocks, and you escape! Experiment by adding by Device to summarize to see events grouped by both user and device.

Why It Works: summarize aggregates data efficiently, and order by ensures the results are sorted for easy analysis. Combining with union lets you analyze across datasets.

Congratulations, You Escaped!

You’ve mastered the KQL Escape Room by wielding let, union, and summarize to unlock all three stages. These skills—defining variables, combining datasets, and aggregating data—are core to KQL and powerful for analyzing logs, security events, or any large dataset in Azure Data Explorer.

Next Steps:

• Experiment: Try modifying the queries. For example, use let to define a time range or add more columns to summarize.

• Learn More: Explore advanced KQL operators like join or parse in the KQL documentation.

• Challenge Yourself: Create your own KQL escape room with new datasets and puzzles!

Share your escape time or custom queries in the comments below. Happy querying, and see you in the next data adventure!