KQL for Application Insights: Filtering and Analyzing Logs

Welcome back! In our last lesson, we focused on instrumenting your .NET application to send rich, custom telemetry to Application Insights. You now know how to capture specific business events and performance metrics, adding a crucial layer of context to the automatically collected data.

Generating this data is the first step; the next is turning it into actionable insight. Today, we'll dive into the powerful language used to query all this information: the Kusto Query Language (KQL). Our goal is to write basic KQL queries to filter and analyze request and exception logs in Application Insights.

Given your extensive background in SQL, you'll find some familiar concepts here, like filtering and aggregation. However, KQL's core philosophy is different, built around a "pipeline" of data transformations. We'll start by mapping your existing knowledge to this new paradigm and then build practical queries for troubleshooting common application issues.

1. KQL: A New Way of Thinking About Queries

At its core, KQL is a read-only query language designed by Microsoft for sifting through massive volumes of log and telemetry data with high speed. It's the engine behind Azure Monitor Logs (which Application Insights uses), Azure Data Explorer, and Microsoft Sentinel.

Unlike SQL's declarative SELECT...FROM...WHERE structure, KQL uses a data-flow model where data passes through a series of operators, each refining the result set from the previous step. This is often compared to using pipes (|) in a command-line shell like Bash or PowerShell.

Kusto Query Language (KQL) — A Practical, No‑BS Guide for ...

This article from dev.to provides a fantastic, no-nonsense introduction to KQL, specifically for engineers familiar with SQL. It's the perfect starting point to bridge your existing knowledge.

Focus on the introduction and the "KQL vs SQL (the quick mental model)" table. Pay close attention to the fundamental differences in query style (pipes vs. declarative statements) and purpose.

The key takeaway is this fundamental pattern:

Source_Table
| operator_1 // e.g., filter by time
| operator_2 // e.g., select specific columns
| operator_3 // e.g., summarize the results

The order matters. You want to filter your data as early as possible to ensure subsequent operators are working on a smaller, more relevant dataset. This is a crucial performance principle in KQL.

2. The Core KQL Operators

While KQL has a rich set of operators, a handful of them will form the backbone of 90% of the queries you write for Application Insights. Let's cover the essentials.

Kusto Query Language (KQL) Overview

This video from John Savill offers a clear, conceptual overview of the fundamental KQL operators. We'll watch specific segments that demonstrate each core building block. The examples use a generic StormEvents table, which is great for learning the syntax without the complexity of Application Insights schemas just yet.

As you watch, notice how each command is chained to the next using the pipe (|) operator. Source and take: Understand how a query starts with a table name and how take (or limit) is used to grab a small, arbitrary sample of records. project: See how to select and rename specific columns, which is analogous to SQL's SELECT clause. where: Learn how to filter the data based on conditions, just like SQL's WHERE clause. summarize: This is KQL's equivalent of GROUP BY with aggregate functions like count(). extend: Discover how to create new, calculated columns in your result set. sort by: Learn to order your final results using sort by or its synonym order by.

To quickly map these concepts to your SQL knowledge, this cheat sheet is incredibly useful:

SQL Equivalent	KQL Operator and Example
`SELECT TOP 100 name, url FROM requests`	`requests
`SELECT * FROM requests WHERE success = 0`	`requests
`SELECT name, AVG(duration) FROM requests GROUP BY name`	`requests
`SELECT name, duration FROM requests ORDER BY duration DESC`	`requests
`SELECT name, (duration / 1000) AS duration_sec`	`requests

Note the use of == for equality comparison and the case-sensitivity of table names, column names, and string literals in KQL.

3. Analyzing Application Logs in Practice

Now let's apply these operators to the tables you'll use most for troubleshooting: requests and exceptions.

Application Insights stores telemetry in a structured format. When you navigate to Logs in your Application Insights resource, you'll see a schema pane listing all available tables.

This image shows the Application Insights Logs UI. On the left, you can see tables like `requests`, `exceptions`, and `traces`. The main window contains a KQL query and its results, demonstrating how to query the `exceptions` table to count occurrences grouped by method.

Analyzing Request Logs

The requests table contains detailed information about every incoming request to your application, including its URL, duration, success status, and response code.

Common Goal: Find the slowest operations.

This query finds the top 10 slowest requests in the last day and shows their key details.

requests
| where timestamp > ago(1d) // 1. Filter to a reasonable time range first!
| order by duration desc    // 2. Order by the duration column
| take 10                   // 3. Get the top 10
| project timestamp, name, duration, success, resultCode // 4. Select relevant columns

Common Goal: Find the top failing operations.

This query calculates the failure rate for each operation (name) in the last 24 hours and shows the ones with the most failures.

requests
| where timestamp >= ago(24h)
| summarize failures = countif(success == false), total = count() by name
| extend failureRate = todouble(failures) * 100 / total
| order by failures desc

summarize ... by name: Groups all requests by their operation name.
countif(success == false): A conditional count, summing only the failed requests for each group.
extend failureRate = ...: Calculates a new failureRate column for each operation.

This is another example from the Logs UI, showing how a `where` clause can be used to filter the `requests` table for logs containing a specific `OrderId` in its custom dimensions, helping to trace a single transaction.

Analyzing Exception Logs

The exceptions table logs every exception captured by the Application Insights SDK, both unhandled exceptions and those you tracked manually with TrackException().

Common Goal: Find the most frequent exceptions.

This is often the first query you run when investigating a spike in errors. It groups exceptions by their type and the innermost error message to identify the most common problems.

exceptions
| where timestamp > ago(1d)
| summarize exceptionCount = count() by type, innermostMessage, operation_Name
| order by exceptionCount desc
| take 20

summarize ... by type, innermostMessage, operation_Name: This gives you a clear picture of what exception is happening, why (the message), and where (in which operation).

For more hands-on examples, you can refer to the following resource. You don't need to read it all now, but it's an excellent reference to bookmark.

Troubleshooting with Azure Application Insights Using KQL

This blog post provides a list of practical, copy-and-paste KQL queries for common troubleshooting scenarios in Application Insights.

Skim the query examples under the code block section. Notice the patterns for analyzing requests, dependencies, and exceptions. These are the exact types of queries you will use for the AZ-204 exam and in real-world diagnostics.

Conclusion

In this lesson, you've bridged your SQL expertise to the world of Kusto Query Language. You've seen how KQL's pipeline-based syntax is uniquely suited for exploring log and telemetry data. By mastering a few core operators, you can effectively diagnose performance issues and errors in your applications.

Key Takeaways:

KQL uses a pipeline model where data flows from a source table through a series of operators separated by |.
Filtering early with where timestamp > ago(...) is critical for performance.
The essential operators for log analysis are:
- where: To filter data based on conditions.
- project: To select and shape columns.
- summarize: To aggregate data (e.g., count(), avg()) with a by clause.
- extend: To create new calculated columns.
- order by: To sort the final results.
The requests and exceptions tables are your primary sources for troubleshooting application performance and reliability.

The queries you've learned to write are not just for interactive analysis. They form the foundation for automated monitoring. In our next and final lesson of this module, we will see how to use these KQL queries to create availability tests and alerts, allowing Azure to notify you automatically when your application isn't behaving as expected.