Interrogate logs using basic Kusto Query Language (KQL) queries

In today’s world of DevOps, capturing and analyzing logs plays a crucial role in identifying issues, troubleshooting, and gaining insights into system behavior. Microsoft provides a powerful log analytics platform called Azure Monitor that integrates with various services to collect, store, and analyze logs. Within Azure Monitor, Kusto Query Language (KQL) is used to interact with logs and perform queries.

Getting Started with KQL Queries:

To begin, we need to understand the KQL syntax and structure. KQL provides a query language for querying logs stored in Azure Monitor. Here is a simple KQL query to retrieve logs:

AzureActivity
| where ResourceGroupName == "myResourceGroup"

In the above example, we are querying the AzureActivity table to retrieve all logs from a specific resource group. The “|” (pipe) character is used to chain together multiple commands or operators in KQL.

Filtering Logs:

KQL allows us to filter logs based on specific conditions. Let’s say we want to filter Azure Activity logs for a particular time range and a specific operation name:

AzureActivity
| where TimeGenerated >= datetime(2022-01-01) and TimeGenerated < datetime(2022-01-08) | where OperationName == "Microsoft.Compute/virtualMachines/write"

In the above example, we use the "where" operator to filter logs based on the TimeGenerated property. The datetime function is used to specify the time range. We further filter logs based on the OperationName property.

Aggregating Logs:

KQL enables us to aggregate logs and extract meaningful information from them. Here's an example where we calculate the count of logs grouped by a specific property:

AzureActivity
| summarize count() by Caller

In the above query, we use the "summarize" operator to aggregate logs and calculate the count of logs for each Caller. This helps us identify which caller generates the most activities.

Joining Logs:

Sometimes, we might need to join logs from multiple tables to gain more insights. KQL provides various operators to join tables. Let's consider an example where we join AzureActivity and SecurityAlert tables:

AzureActivity
| join kind=inner (SecurityAlert) on $left.CorrelationId == $right._ResourceId
| project Caller, Title

In the above query, we use the "join" operator to join logs from AzureActivity and SecurityAlert tables based on the CorrelationId and \_ResourceId properties, respectively. We then use the "project" operator to retrieve specific properties (Caller and Title) from the joined logs.

Advanced Queries:

KQL offers several advanced features to perform complex log analysis. Some of the commonly used features include aggregations, time series analysis, machine learning functions, and more. Here's an example where we calculate the 90th percentile of response times:

AppRequests
| summarize percentiles(DurationMs, 50, 90, 95) by bin(TimeGenerated, 1h)

In this query, we use the "summarize" operator along with the "percentiles" function to calculate the 50th, 90th, and 95th percentiles of response times for each hour.

Conclusion:

In this article, we've explored the basics of Kusto Query Language (KQL) and learned how to interrogate logs using KQL queries. We covered filtering, aggregating, joining, and advanced querying techniques to extract valuable insights from logs stored in Azure Monitor.

KQL provides a powerful way to analyze logs and gain actionable insights, making it an essential skill for designing and implementing Microsoft DevOps solutions. By mastering KQL, you can effectively troubleshoot issues, optimize performance, and enhance your overall DevOps processes.