How to Find Log Ingestion Data Across All Log Analytics Workspaces Using KQL

If you're managing multiple Log Analytics workspaces in Azure Monitor (like one of my customers recently), keeping track of log ingestion data is crucial for monitoring costs and optimizing resource usage. Azure Monitor provides a powerful query language, KQL (Kusto Query Language), that allows you to analyze data across multiple workspaces. In this blog post, I'll explore how to use a specific KQL query to retrieve log ingestion data across all Log Analytics workspaces over the last 30 days.

The Challenge: Tracking Log Ingestion Across Workspaces

When working with multiple Log Analytics workspaces, it can be challenging to aggregate data usage across all of them. This is especially important when you want to monitor billable data ingestion to manage costs effectively. Azure Monitor allows you to query across multiple workspaces using the union operator, which combines data from all specified sources.

The query I'll discuss is designed to:

1. Aggregate data ingestion across all workspaces.

2. Filter for billable data only.

3. Summarize the total data ingested in gigabytes (GB) over the last 30 days.

   
   

The KQL Query

Here’s the KQL query that accomplishes this task:

union withsource = SourceWorkspace *
| where TimeGenerated >= startofday(ago(30d)) // Adjust the time range as needed
| where IsBillable == true // Only include billable data
| summarize TotalDataIngestedGB = sum(Quantity) / 1024 by bin(TimeGenerated, 30d)
| order by TimeGenerated asc

What you should see is something like this as an output:

Breaking Down the Query

Let’s break down the query step by step to understand how it works:

1. union withsource = SourceWorkspace *:

   • This combines data from all Log Analytics workspaces available to your query. The withsource parameter adds a column (SourceWorkspace) to indicate the workspace each record originated from.

2. where TimeGenerated >= startofday(ago(30d)):

   • Filters the data to include only logs generated in the last 30 days. You can adjust the time range as needed by modifying the ago(30d) value.

3. where IsBillable == true:

   • Filters the data to include only billable logs. This is crucial for cost analysis, as non-billable data does not contribute to your Azure Monitor costs.

4. summarize TotalDataIngestedGB = sum(Quantity) / 1024 by bin(TimeGenerated, 30d):

   • Aggregates the total data ingested (in gigabytes) by summing up the Quantity field and dividing by 1024 (to convert from megabytes to gigabytes). The bin(TimeGenerated, 30d) groups the data into 30-day intervals.

5. order by TimeGenerated asc:

   • Sorts the results in ascending order of time, making it easier to analyze trends over the specified period.
     

Why This Query is Useful

This query is particularly useful for:

• Cost Management: By focusing on billable data, you can identify trends in data ingestion and take steps to optimize costs.

• Usage Analysis: Understanding how much data is being ingested across all workspaces helps you monitor resource usage and plan for scaling.

• Centralized Monitoring: Instead of querying each workspace individually, this query provides a unified view of log ingestion across all workspaces.

Tips for Running the Query

1. Permissions:

   • Ensure you have the necessary permissions to query across all Log Analytics workspaces. You may need to configure Azure Monitor to allow cross-workspace queries.

2. Adjusting the Time Range:

   • Modify the ago(30d) value to analyze data for a different time period. For example, use ago(7d) for the last 7 days or ago(90d) for the last 90 days.

3. Visualizing the Data:

   • Use Azure Monitor’s built-in visualization tools to create charts or graphs based on the query results. This can help you identify trends and anomalies more easily.

     
     

Conclusion

Tracking log ingestion data across multiple Log Analytics workspaces is essential for effective cost management and resource optimization. The KQL query here in this post allows you to aggregate and analyze billable data ingestion over the last 30 days, giving you valuable insights into your Azure Monitor usage. By harnessing the power of KQL and Azure Monitor, you can take control of your log data, optimize costs, and ensure your resources are being used efficiently. Happy querying!