KQL (Kusto Query Language) in Azure — Complete Guide

1. What is KQL?

KQL (Kusto Query Language) is a read-only query language developed by Microsoft to retrieve, filter, analyze, and summarize large volumes of data.

It is primarily used with Azure Monitor Logs, Log Analytics Workspace, Application Insights, Azure Data Explorer, and Microsoft Sentinel.

KQL is similar to SQL, but it is designed for analyzing logs, telemetry, events, and time-series data.

Simple Definition:

KQL is a query language used to search and analyze logs stored in a Log Analytics Workspace to identify errors, monitor performance, and troubleshoot Azure resources.

Real-Time Example

Suppose you have an ASP.NET Core Web API hosted in Azure App Service.

Your application generates the following logs:

Using KQL, you can answer questions such as:

• How many requests failed?

• Which API is taking more time?

• How many HTTP 500 errors occurred?

• What errors occurred in the last 30 minutes?

• Which Azure resource was modified?

2. How KQL Works in Azure

         Azure Resources
                |
     +----------+----------+
     |          |          |
     v          v          v
 App Service   Azure VM   Azure SQL
     |          |          |
     +----------+----------+
                |
                v
      Diagnostic Settings /
      Application Insights /
      Monitoring Agents
                |
                v
      Log Analytics Workspace
                |
                v
           Log Tables
                |
       +--------+--------+
       |        |        |
       v        v        v
   AppRequests AzureActivity AzureMetrics
       |        |        |
       +--------+--------+
                |
                v
           KQL Queries
                |
                v
       Results / Charts /
          Alerts

KQL does not collect logs itself. It queries data that has already been collected.

3. KQL vs SQL

Since SQL Server and KQL have similar concepts, understanding their differences is useful.

SQL Example:

SELECT TOP 10 *
FROM ApplicationLogs
WHERE StatusCode = 500
ORDER BY CreatedOn DESC;

Equivalent KQL Example:

AppRequests
| where ResultCode == "500"
| order by TimeGenerated desc
| take 10

The pipe (|) is one of the most important features of KQL.

4. Understanding KQL Syntax

A typical KQL query:

AppRequests
| where TimeGenerated > ago(1h)
| where Success == false
| project TimeGenerated, Name, ResultCode
| order by TimeGenerated desc
| take 10

Explanation

How the Query Executes

           AppRequests
                |
                v
       Filter Last 1 Hour
                |
                v
       Filter Failed Requests
                |
                v
       Select Required Columns
                |
                v
       Sort Latest First
                |
                v
         Return 10 Rows

Each pipe passes the previous operation's result to the next operation.

5. Important KQL Operators

5.1 take — Retrieve Records

Returns a specified number of records.

AppRequests
| take 10

Explanation: Returns any 10 records from the AppRequests table. The order is not guaranteed.

5.2 where — Filter Records

Filters records based on a condition.

AppRequests
| where Success == false

Explanation: Retrieves failed application requests.

Multiple conditions:

AppRequests
| where Success == false
    and TimeGenerated > ago(1h)

Explanation: Retrieves failed requests from the last hour.

5.3 project — Select Columns

Similar to selecting columns in SQL.

AppRequests
| project TimeGenerated,
          Name,
          ResultCode,
          DurationMs

Explanation: Displays only the specified columns.

5.4 sort by — Sort Records

Sorts results in ascending or descending order.

AppRequests
| sort by TimeGenerated desc

Explanation: Displays the latest requests first.

Ascending:

AppRequests
| sort by TimeGenerated asc

5.5 count — Count Records

Counts the total number of records.

AppRequests
| count

Explanation: Returns the number of records in the selected query scope.

Count failed requests:

AppRequests
| where Success == false
| count

5.6 summarize — Group and Aggregate

Similar to SQL GROUP BY.

AppRequests
| summarize TotalRequests = count()
    by ResultCode

Example Result:

Explanation: Groups requests by HTTP status code and calculates the number of requests in each group.

5.7 distinct — Unique Values

Returns unique values.

AppRequests
| distinct ResultCode

Explanation: Returns the different HTTP status codes present in the records.

5.8 extend — Create Calculated Columns

Creates a new calculated column without removing existing columns.

AppRequests
| extend DurationSeconds = DurationMs / 1000.0

Explanation: Converts request duration from milliseconds to seconds.

5.9 top — Retrieve Top Records

Returns the highest or lowest records based on a column.

AppRequests
| top 5 by DurationMs desc

Explanation: Retrieves the five slowest recorded requests.

5.10 render — Visualize Results

Displays query results as charts in supported KQL interfaces.

AppRequests
| summarize TotalRequests = count()
    by bin(TimeGenerated, 5m)
| render timechart

Explanation: Displays the number of requests every five minutes as a time-series chart.

6. KQL Comparison and Logical Operators

Example: Multiple Conditions

AppRequests
| where ResultCode in ("400", "404", "500")
| where DurationMs > 1000

This retrieves requests with the specified status codes that took more than one second.

7. KQL String Operators

KQL provides several operators for searching text.

Example 1: Search API Requests

AppRequests
| where Name contains "Articles"

Returns requests whose names contain Articles.

Example 2: Search Exceptions

AppExceptions
| where OuterMessage contains "timeout"

Returns exceptions containing the word timeout in the outer exception message.

Example 3: Case-Insensitive Equality

AppRequests
| where Name =~ "GET /api/articles"

Returns records whose request name matches regardless of letter case.

Performance Tip: Prefer has for whole-term searches and == for exact matches when appropriate. They can be more efficient than substring searches.

8. KQL Date and Time Functions

Time filtering is especially important when analyzing Azure logs.

Example 1: Last 30 Minutes

AppRequests
| where TimeGenerated >= ago(30m)

Example 2: Last 7 Days

AppRequests
| where TimeGenerated >= ago(7d)

Example 3: Group Requests Every 5 Minutes

AppRequests
| where TimeGenerated >= ago(1h)
| summarize TotalRequests = count()
    by bin(TimeGenerated, 5m)
| order by TimeGenerated asc

Explanation: Divides the last hour into five-minute intervals and counts requests in each interval.

9. Aggregation Functions in KQL

Aggregation functions calculate summarized values.

Example: Analyze API Performance

AppRequests
| where TimeGenerated >= ago(1h)
| summarize
    TotalRequests = count(),
    AverageDuration = avg(DurationMs),
    MaximumDuration = max(DurationMs),
    MinimumDuration = min(DurationMs)
    by Name
| order by AverageDuration desc

Explanation: Groups requests by API name and calculates request count, average duration, maximum duration, and minimum duration.

Example: Calculate the 95th Percentile

AppRequests
| where TimeGenerated >= ago(1h)
| summarize P95Duration = percentile(DurationMs, 95)
    by Name

The 95th percentile indicates the duration at or below which approximately 95% of the measured requests fall.

This is useful for identifying slow API response times that averages may hide.

10. KQL Join Operator

KQL supports joining data from multiple tables.

Suppose you want to correlate application requests with their dependency calls.

AppRequests
| where TimeGenerated >= ago(1h)
| project OperationId, Name, DurationMs
| join kind=inner (
    AppDependencies
    | where TimeGenerated >= ago(1h)
    | project OperationId,
              DependencyName = Name,
              DependencyDuration = DurationMs
) on OperationId

Explanation:

• AppRequests contains HTTP request telemetry.

• AppDependencies contains outgoing dependency telemetry.

• OperationId correlates telemetry belonging to the same distributed operation.

• join combines matching records.

A request can have multiple dependency calls, so one request may appear in multiple result rows.

11. Practical KQL Queries for Azure Monitoring

These examples are particularly useful for an ASP.NET Core Web API hosted in Azure App Service.

Scenario 1: Find Failed API Requests

AppRequests
| where TimeGenerated >= ago(24h)
| where Success == false
| project TimeGenerated,
          Name,
          ResultCode,
          DurationMs
| order by TimeGenerated desc

Use Case: Identify failed API requests during the last 24 hours.

Scenario 2: Find Slow API Requests

AppRequests
| where TimeGenerated >= ago(24h)
| where DurationMs > 3000
| project TimeGenerated,
          Name,
          DurationMs
| order by DurationMs desc

Use Case: Find requests taking longer than three seconds.

Scenario 3: Count HTTP 500 Errors

AppRequests
| where TimeGenerated >= ago(24h)
| where ResultCode == "500"
| summarize TotalErrors = count()

Use Case: Identify how many HTTP 500 responses occurred.

Scenario 4: Find Application Exceptions

AppExceptions
| where TimeGenerated >= ago(24h)
| project TimeGenerated,
          ExceptionType,
          OuterMessage
| order by TimeGenerated desc

Use Case: Investigate application exceptions.

Scenario 5: Find Failed SQL Dependencies

AppDependencies
| where TimeGenerated >= ago(24h)
| where Success == false
| where Type == "SQL"
| project TimeGenerated,
          Name,
          Target,
          DurationMs
| order by TimeGenerated desc

Use Case: Investigate failed SQL dependency operations.

Scenario 6: Find Azure Resource Changes

AzureActivity
| where TimeGenerated >= ago(24h)
| where OperationNameValue contains "write"
| project TimeGenerated,
          OperationNameValue,
          Caller,
          ResourceGroup
| order by TimeGenerated desc

Use Case: Identify Azure resource management changes.

Scenario 7: Find Failed Azure Management Operations

AzureActivity
| where TimeGenerated >= ago(24h)
| where ActivityStatusValue == "Failure"
| project TimeGenerated,
          OperationNameValue,
          Caller,
          ResourceGroup
| order by TimeGenerated desc

Use Case: Investigate failed Azure resource operations.

Scenario 8: Analyze Azure Metrics

AzureMetrics
| where TimeGenerated >= ago(1h)
| summarize AverageValue = avg(Average)
    by MetricName, bin(TimeGenerated, 5m)
| order by TimeGenerated desc

Use Case: Analyze metric values exported through Azure Monitor Diagnostic Settings.

Note: These examples use workspace-based Application Insights tables (AppRequests, AppDependencies, AppExceptions) and Azure Monitor tables (AzureActivity, AzureMetrics). Each table must be populated through the appropriate telemetry or diagnostic configuration.

12. How to Execute KQL in Azure Portal

 

 

 

Step 1: Open Azure Portal.

Step 2: Search for Log Analytics workspaces.

Step 3: Select your workspace.

Step 4: Click Logs.

Step 5: Enter this query:

AzureActivity
| take 10

Step 6: Click Run.

Step 7: Review the results in the results pane.

Step 8: Modify the query to filter, summarize, or visualize your data.

If you have not configured Activity Log export, the AzureActivity table may be unavailable or empty.

13. KQL Best Practices

1. Filter by time early: Use where TimeGenerated >= ago(...) to reduce the amount of data scanned.

2. Select required columns: Use project to display only necessary fields.

3. Use aggregation: Prefer summarize for reports and performance analysis.

4. Avoid unnecessary joins: Joining large tables can increase query cost and execution time.

5. Use appropriate string operators: Prefer efficient exact-match and term-search operators.

6. Limit large results: Use take or top when exploring data.

7. Use meaningful aliases: Names such as TotalRequests and AverageDuration improve readability.

8. Use bin() for time-series analysis: Group timestamps into meaningful intervals.

9. Review the query time range: Azure Portal's time-range selector can affect the results.

10. Monitor query performance: Large datasets and complex queries can increase resource consumption.

14. Important KQL Interview Questions and Answers

15. Quick Revision — SQL vs KQL Syntax

16. Practice KQL Without an Azure Subscription

You can practice many KQL operations directly using the datatable operator.

datatable(
    Name:string,
    StatusCode:int,
    DurationMs:int
)
[
    "Articles API", 200, 120,
    "Articles API", 500, 2500,
    "Login API", 200, 180,
    "Login API", 401, 90,
    "Categories API", 500, 3200
]
| where StatusCode == 500
| project Name, DurationMs
| order by DurationMs desc

Expected Result:

This example works without existing log tables because datatable creates sample data inside the query.

You can also explore Microsoft's Azure Data Explorer to practice KQL.

Final Summary

KQL is one of the most important skills for Azure monitoring and troubleshooting.

For an Azure Administrator or .NET Tech Lead, the most useful operators to master first are:

where, project, summarize, extend, sort, take, top, join, bin, and render.

Once you understand these operators, you can analyze Azure Activity Logs, App Service failures, Application Insights telemetry, SQL dependency performance, and exported Azure metrics using a consistent query language.