KQL Fundamentals for Telemetry Analysis

1. Introduction to Kusto Query Language (KQL)

Before we start writing queries, let's get a high-level overview of what KQL is and where it fits into the Azure ecosystem. It's a powerful, read-only query language designed for analyzing large volumes of structured, semi-structured, and unstructured data.

As the video explains, services like Application Insights are built on top of a powerful backend that uses KQL. We will be writing our queries in the Log Analytics workspace associated with your Application Insights resource.

For practice, we'll use the official Azure Log Analytics demo environment, which is pre-populated with sample data. You can access it here: https://aka.ms/lademo. Open this in a new tab to follow along with the exercises.

2. Foundational KQL Syntax: The Pipeline Model

The fundamental structure of a KQL query is a pipeline. You start with a data source (a table) and then pipe (|) its output through a series of operators that filter, shape, and aggregate the data.

TableName | operator1 | operator2 | ...

This is different from SQL's SELECT ... FROM ... WHERE structure but is incredibly intuitive for data exploration.

Let's summarize the key operators from the reading:

• | (pipe): Separates commands, passing the output of the previous command as the input to the next.
• take <number>: Returns a specified number of arbitrary rows. This is great for quickly sampling data without performance-heavy sorting. It's like SQL's TOP N but without a guaranteed order.
• project <columns>: Selects the columns you want to see, similar to the SELECT clause in SQL. You can also use it to rename columns (NewName = OldName) and create simple calculated columns.

Practice Exercise 1 (5 minutes)

Go to the Log Analytics demo environment. In the query editor, run the following queries. Observe how the pipeline refines the data at each step.

1. Start by looking at the raw requests table (the UI will automatically limit results):

   requests
   

2. Now, get a small sample of 10 records:

   requests
   | take 10
   

3. Finally, select and rename a few specific columns from that sample:

   requests
   | take 10
   | project RequestTime = timestamp, HttpMethod = client_Method, Url = url, ResponseCode = resultCode
   

3. Filtering and Sorting Data

Just like in SQL, the most common operations are filtering data to find what you need and then ordering it.

• where <condition>: This is KQL's equivalent of the WHERE clause in SQL. It filters rows based on a boolean expression.
• Time Filters: Since you're working with telemetry, filtering by time is crucial for performance and relevance. The ago() function is a common way to do this (e.g., timestamp > ago(1h) for data from the last hour).
• top <number> by <column>: Sorts the entire dataset by a specific column and returns the top N records. This is more efficient than using sort and then take.
• sort by <column> [asc | desc]: Sorts the entire result set. Use this when you need all the data sorted, not just the top records.

Best Practice: Always place your time filter (where timestamp > ...) as early as possible in the query pipeline, right after the table name. This dramatically reduces the amount of data the subsequent operators have to process.

Practice Exercise 2 (5 minutes)

In the demo environment, let's build a query to find the 5 slowest successful requests from the last day.

requests
| where timestamp > ago(1d)      // Filter to the last 24 hours
| where success == true          // Filter for successful requests
| top 5 by duration desc         // Find the 5 slowest (highest duration)
| project timestamp, url, duration

Notice the order: we filter the dataset down to a smaller, relevant set of rows before performing the more expensive top operation.

4. Aggregating and Calculating Data

Exploring individual log entries is useful, but the real power comes from aggregation—summarizing data to spot trends.

• summarize <aggregation> by <column>: This is KQL's version of GROUP BY. It groups rows that have the same values in the by column and then computes an aggregation over each group.
  • Common aggregations: count(), dcount() (distinct count), avg(), sum(), min(), max(), percentiles().
• extend <NewColumn> = <expression>: Adds a new column to the result set while keeping all existing columns. This is useful for performing calculations on a per-row basis.
• bin(<column>, <size>): A powerful function often used with summarize. It groups a continuous value (like a timestamp or a number) into buckets of a specified size. For example, bin(timestamp, 1h) groups all timestamps into the hour they belong to.
• render <charttype>: A special operator that visualizes the results. It must be the last command in the pipeline. Common types are timechart, piechart, and barchart.

To see these powerful operators in action, let's watch a few more clips.

Practice Exercise 3 (5 minutes)

Let's write a query that embodies a very common monitoring pattern: counting the number of requests per hour over the last 24 hours and displaying it as a time chart.

In the demo environment, run this query:

requests
| where timestamp > ago(24h)
| summarize count() by bin(timestamp, 1h)
| render timechart

This single query gives you a 24-hour trend chart of your application's traffic. Now, try changing it to count failed requests (where success == false) or to see the average duration per hour (summarize avg(duration) by bin(timestamp, 1h)).

Conclusion

In this lesson, you've learned the fundamentals of the Kusto Query Language, giving you the power to transform raw telemetry data into actionable insights.

Key Takeaways:

• KQL uses a pipeline syntax (|) to chain operators together, making it ideal for interactive data exploration.
• Always start queries with a table name and filter by time as early as possible.
• Use take to sample data, project to select columns, and where to filter rows.
• Use top and sort to order your results efficiently.
• Use summarize to perform aggregations (like SQL's GROUP BY) and extend to create new calculated columns.
• Combine summarize with bin() and render timechart to easily create time-series visualizations.

As a final takeaway, here is a handy cheat sheet that summarizes many of the operators we've discussed today.

Next Steps

Now that you can write queries to find specific metrics or error conditions in your logs, the next logical step is to automate this process. In our next lesson, you will learn how to create alert rules in Azure Monitor that run KQL queries on a schedule and notify you when certain thresholds are breached.