Introduction to Log Analytics Queries

Azure Log Analytics is a powerful tool for collecting, analyzing, and acting on telemetry data from your Azure and on-premises environments. The core of interacting with this data lies in writing effective queries using the Kusto Query Language (KQL).

This section will guide you through the fundamentals of KQL, common query patterns for developers, and best practices for efficient data analysis. Whether you're troubleshooting an application, monitoring performance, or identifying security threats, Log Analytics queries are your essential tool.

Kusto Query Language (KQL) Basics

KQL is a read-only query language designed for exploring data and discovering patterns. It's structured as a pipeline of data transformations, where each step refines the dataset. Here are some fundamental operators:

Key Operators

  • | (Pipe): Separates commands and passes the result of the previous command to the next.
  • take / limit: Returns a specified number of rows.
  • where: Filters records based on a condition.
  • project: Selects columns and renames them.
  • extend: Creates new calculated columns.
  • summarize: Aggregates data using functions like count(), avg(), sum().
  • sort by / order by: Sorts records.
  • mv-expand: Expands array-valued columns into multiple rows.

Example: Basic Query

To see the latest 10 logs from the AppExceptions table:

AppExceptions
| take 10

To filter for exceptions of type 'System.NullReferenceException':

AppExceptions
| where ExceptionType == "System.NullReferenceException"
| take 20

Common Query Examples for Developers

Here are some practical query examples to help you monitor and troubleshoot your applications.

1. Application Errors Over Time

Count of exceptions by type over the last hour:

AppExceptions
| where Timestamp > ago(1h)
| summarize count() by ExceptionType, bin(Timestamp, 5m)
| render timechart

2. Request Latency

Average request duration for successful requests (200 status code):

requests
| where success == true and resultCode startswith "2"
| summarize avg(duration) / 1000000 by operation_Name // duration is in microseconds, convert to ms
| render barchart with (title="Average Latency for Successful Requests")

3. Slowest Requests

Identify the top 5 slowest requests:

requests
| top 5 by duration desc
| project timestamp, url, duration, resultCode, operation_Name

4. User Session Analysis

Count distinct users over time:

traces
| where timestamp > ago(24h)
| summarize dcount(user_Id) by bin(timestamp, 1h)
| render timechart with (title="Unique Users per Hour")

5. Custom Event Tracking

Find all occurrences of a specific custom event:

customEvents
| where name == "UserLoggedIn"
| project timestamp, user_Id, properties
| take 50

Query Performance and Optimization

Writing efficient queries is crucial for faster insights and lower costs. Here are some tips:

  • Filter Early: Use where clauses as early as possible in your query to reduce the dataset processed by subsequent operators.
  • Limit Data: Use take or limit when you only need a sample, or to quickly check query structure.
  • Project Selectively: Use project to select only the columns you need. Avoid * if possible.
  • Avoid `search` for Large Datasets: Prefer table-specific queries (e.g., AppExceptions | ...) over broad search *.
  • Use `materialized views` (Advanced): For frequently run complex queries, consider pre-aggregating data into materialized views.
  • Understand Data Schema: Familiarize yourself with the tables available in your Log Analytics workspace (e.g., requests, traces, AppExceptions, dependencies) and their schemas.

Best Practices for Log Analytics Queries

  • Name Your Queries: Save frequently used queries with descriptive names in the Azure portal.
  • Use Comments: Add comments (//) to explain complex logic within your queries.
  • Parameterize Queries: Use parameters for dates, times, or filter values to make queries reusable.
  • Test on Samples: Before running a query against a large time range, test it on a smaller sample to ensure correctness and performance.
  • Leverage `render` operator: Visualize your data with render timechart, render barchart, etc., for easier interpretation.
  • Be Mindful of Time Ranges: Always specify a time range using ago() or explicit timestamps to control query scope and cost.

Next Steps

Now that you have a grasp of Log Analytics queries, you can explore more advanced topics:

Explore Log Analytics Tutorial