Correlating Logs Across Azure Services with KQL

The Log-Whisperer's Guide to Uniting Azure's Chatty Services

Applications often span multiple Azure services, generating logs in various locations such as Azure Application Insights, Log Analytics, and Blob Storage. Correlating these logs is critical for gaining a unified view of system behavior, troubleshooting issues, and identifying performance bottlenecks. Azure's Kusto Query Language (KQL) provides a powerful way to join and analyze logs across these services. In this blog post, we’ll explore how to use KQL to correlate logs from Azure Application Insights, Log Analytics, and Blob Storage, enabling comprehensive insights into your Azure environment.

Why Correlate Logs Across Azure Services?

Logs from different Azure services capture distinct aspects of an application:

• Azure Application Insights: Tracks application performance, telemetry, and user interactions, such as requests, exceptions, and traces.

• Log Analytics: Collects infrastructure and diagnostic logs from Azure resources, including VMs, containers, and Azure Monitor.

• Blob Storage: Stores raw or archival logs, such as audit logs, application logs, or custom data exports.

Correlating these logs helps you:

• Trace an issue from a user request (Application Insights) to infrastructure events (Log Analytics) and archived data (Blob Storage).

• Identify patterns or anomalies across services.

• Build end-to-end monitoring dashboards for holistic observability.

KQL, used by Azure Monitor and Azure Data Explorer, is ideal for querying and joining these disparate log sources due to its flexibility and performance.

Prerequisites

Before diving into KQL queries, ensure you have:

1. Access to Logs:

   • Application Insights instance with telemetry data.

   • Log Analytics workspace with resource logs.

   • Blob Storage account with logs exported (e.g., via Diagnostic Settings or custom exports).

2. Azure Data Explorer or Log Analytics Workspace: To run KQL queries.

3. Permissions: Read access to Application Insights, Log Analytics, and Blob Storage.

4. External Data Setup: For Blob Storage, logs must be accessible via Azure Data Explorer or ingested into a Log Analytics workspace.

Step 1: Understanding Log Sources and Schemas

Each Azure service stores logs in a specific format, and KQL queries rely on understanding their schemas:

• Application Insights: Logs are stored in tables like requests, traces, exceptions, and customEvents. Key columns include timestamp, operation_Id, and correlation_Id.

• Log Analytics: Logs are stored in tables like AzureDiagnostics, Heartbeat, or custom tables. Common columns include TimeGenerated, ResourceId, and OperationName.

• Blob Storage: Logs are typically stored as JSON, CSV, or text files. To query them with KQL, you need to ingest them into Azure Data Explorer or map them as external tables.

To explore schemas, run simple KQL queries in the Log Analytics or Azure Data Explorer query editor:

// Application Insights: View recent requests
requests | take 10

// Log Analytics: View Azure Diagnostics
AzureDiagnostics | take 10

For Blob Storage logs, you’ll need to set up an external table or ingest the data. We’ll cover this in Step 3.

Step 2: Joining Logs from Application Insights and Log Analytics

To correlate logs between Application Insights and Log Analytics, you can use common fields like operation_Id, correlation_Id, ResourceId, or timestamp. For example, let’s say you want to correlate failed requests in Application Insights with diagnostic logs in Log Analytics to investigate an issue.

Here’s a sample KQL query to join the two:

// Join Application Insights requests with Log Analytics diagnostics
let FailedRequests = requests
| where success == false
| project timestamp, operation_Id, url, client_IP;
FailedRequests
| join kind=inner (
    AzureDiagnostics
    | where OperationName == "Request"
    | project TimeGenerated, ResourceId, OperationName, CorrelationId
) on $left.operation_Id == $right.CorrelationId
| project timestamp, url, client_IP, ResourceId, OperationName
| order by timestamp desc

Explanation:

• requests: Filters failed requests from Application Insights.

• AzureDiagnostics: Queries diagnostic logs from Log Analytics.

• join kind=inner: Matches records where operation_Id (from Application Insights) equals CorrelationId (from Log Analytics).

• project: Selects relevant columns for analysis.

• order by: Sorts results by timestamp for clarity.

This query helps you trace a failed request to the underlying resource or operation logged in Log Analytics.

Step 3: Querying Blob Storage Logs with KQL

Blob Storage logs are not natively queryable with KQL, so you need to either:

1. Ingest Logs into Azure Data Explorer: Use tools like Azure Data Factory or Event Hubs to ingest Blob Storage logs into a table.

2. Query as External Data: Map Blob Storage files as an external table in Azure Data Explorer.

Here’s how to set up an external table for JSON logs in Blob Storage:

1. Create an External Table: In Azure Data Explorer, define an external table that points to your Blob Storage files:

.create external table BlobLogs (
    Timestamp: datetime,
    LogLevel: string,
    Message: string,
    CorrelationId: string
)
kind=blob
dataformat=json
( 
    h@'https://<storage-account>.blob.core.windows.net/<container>;<sas-token>'
)

2. Query the External Table: Once the external table is set up, you can query it like any other KQL table:

BlobLogs
| where LogLevel == "Error"
| project Timestamp, Message, CorrelationId
| take 10

3. Correlate with Application Insights and Log Analytics: Join the Blob Storage logs with Application Insights and Log Analytics using a common field like CorrelationId:

let AppErrors = requests
| where success == false
| project timestamp, operation_Id, url;
let BlobErrors = BlobLogs
| where LogLevel == "Error"
| project Timestamp, Message, CorrelationId;
AppErrors
| join kind=inner BlobErrors on $left.operation_Id == $right.CorrelationId
| join kind=inner (
    AzureDiagnostics
    | project TimeGenerated, ResourceId, CorrelationId
) on $left.operation_Id == $right.CorrelationId
| project timestamp, url, Message, ResourceId
| order by timestamp desc

Explanation:

• AppErrors: Filters failed requests from Application Insights.

• BlobErrors: Filters error logs from Blob Storage.

• join: Combines the datasets using operation_Id and CorrelationId.

• The final result correlates errors across all three services, providing a unified view.

Step 4: Best Practices for Log Correlation

To ensure effective log correlation with KQL:

1. Use Consistent Correlation IDs: Ensure your application and services propagate a common correlation_Id or operation_Id across logs. This is critical for joining datasets.

2. Standardize Timestamps: Align timestamp formats across services (e.g., UTC) to avoid mismatches during joins.

3. Optimize Query Performance:

   • Use where clauses early to filter data.

   • Limit the time range with where timestamp > ago(1h).

   • Avoid excessive joins on large datasets.

4. Automate Ingestion: For Blob Storage, automate log ingestion into Azure Data Explorer or Log Analytics using Azure Data Factory or Event Grid.

5. Visualize Results: Export KQL query results to Azure Dashboards or Power BI for real-time monitoring.

Step 5: Example Use Case

Suppose you’re troubleshooting a spike in failed API requests. You can:

1. Query Application Insights to identify failed requests:

requests
| where success == false
| summarize Count = count() by url, bin(timestamp, 5m)
| order by timestamp desc

2. Correlate with Log Analytics to check for resource issues:

AzureDiagnostics
| where ResourceType == "VIRTUALMACHINES" and ResultType == "Failed"
| project TimeGenerated, ResourceId, CorrelationId

3. Check Blob Storage logs for application-specific errors:

BlobLogs
| where LogLevel == "Error" and Message contains "API"
| project Timestamp, Message, CorrelationId

4. Join the datasets to trace the issue end-to-end:

let FailedRequests = requests
| where success == false
| project timestamp, operation_Id, url;
FailedRequests
| join kind=inner (
    BlobLogs
    | where LogLevel == "Error"
    | project Timestamp, Message, CorrelationId
) on $left.operation_Id == $right.CorrelationId
| join kind=inner (
    AzureDiagnostics
    | where ResultType == "Failed"
    | project TimeGenerated, ResourceId, CorrelationId
) on $left.operation_Id == $right.CorrelationId
| project timestamp, url, Message, ResourceId

This query might reveal that failed requests correlate with VM failures and specific error messages in Blob Storage, pointing to a resource contention issue.

TLDR

Using KQL to correlate logs across Azure Application Insights, Log Analytics, and Blob Storage empowers you to gain deep insights into your application’s behavior. By leveraging common fields like correlation_Id and carefully structuring your queries, you can trace issues across services, identify root causes, and improve system reliability. Whether you’re troubleshooting incidents or building proactive monitoring, KQL’s flexibility makes it an essential tool for Azure log analysis.