Sentinel Aux logs - query cost optimization

Question

Sentinel Aux logs - query cost optimization

Sándor Tőkési 271

Hey folks,

I'm curious whether there is any way to decrease the cost of Aux logs queries. If you run a query on an Auxiliary table, you have to pay for the data you scanned through.

Is there any KQL function/operator that can be used to decrease the cost of the query if we have a given amount of data in a specific table (so data is in the table and this cannot be changed)?

Examples:

Using the TimeGenerated filter, if I set it to > ago(1h), will it actually only scan the last 1 hour of data, or will it go through the last 24h-s (default settings on the GUI) and then return the results from the last 1 hour? (scanned data is 1h or 24h?)
Even if TimeGenerated actually decreases the cost, will any other 'where' filter do the same? I assume any where filter scans all the data and returns only the matches.
What about the 'limit' operator? If I use the 'limit 5' will I have to pay for only 5 events, or will it scan the whole table and then return with 5 events?
What about materialize? If I want to use the same data over and over again in one query. Would creating a variable and storing materialized data in it actually be cheaper than just using the Aux table itself multiple times? (again, inside one query).

I have my own assumptions of course, but I'm curious whether Microsoft has an official statement or whether somebody has some proof for these things and some other recommendation.

Accepted answer

0 additional answers

Your answer

Answer 1

Andrew Blumhardt 10,046 Microsoft Employee

Great question. Optimizing KQL queries for cost in Aux logs is essential, especially at scale. Using a TimeGenerated filter early in your query is the most effective way to reduce scanned data and lower cost.

Other where filters don’t help as much since they apply after the scan. Limit only reduces returned rows, not the scanned volume.

Use project early to trim unused columns, and materialize can help avoid repeating large scans in a single query. Stick to time filters, minimal columns, and avoid unnecessary joins to keep queries efficient and cost-effective.

Sándor Tőkési 271 Reputation points

2025-05-11T17:30:05.5966667+00:00

Thanks Andrew.

Just to be clear, by your last section, do you mean project actually decreases the size of the scanned data? So, if I use project immediately after the table name, will it only use those columns, and thus I only have to pay for scanning those specific columns? Or what did you mean by that part?

(I know parquet is designed to process only required columns, but I was not aware of Sentinel or Aux logs working this way.
Andrew Blumhardt 10,046 Reputation points Microsoft Employee

2025-05-13T04:08:00.2766667+00:00

Good point. Per the docs, time range is the only true filter for cost optimization. It is not clear if other operators that are used in other cost savings strategies would be effective without further testing.

"The charge for a query on Basic and Auxiliary tables is based on the amount of data the query scans, which depends on the size of the table and the query's time range."

https://learn.microsoft.com/en-us/azure/azure-monitor/logs/basic-logs-query?tabs=portal-1#pricing-model
Raja Pothuraju 22,640 Reputation points Microsoft External Staff Moderator

2025-05-14T22:01:38.0233333+00:00

Hello @Sándor Tőkési,

Just checking in to see if the below answer provided by @Andrew Blumhardt helped.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Share via

Sentinel Aux logs - query cost optimization

0 additional answers

Your answer