Time range (for last month) in Kusto Query language in Logs Analytics Workspace

Question

Hi,

We use Log Analytics Workspace to collect logs for our customer tenants under a resource hosted in Azure. Previously we would select Time Range feature to select the hits per tenant for the last calendar month and I was looking to set it in the query.

When I set it in the query with the following syntax, the result is double of what I received while using Time Range-

let lastmonth = getmonth(datetime(now)) -1;
let monthEnd = endofmonth(datetime(now),-1); 
AuditLogs
| where TimeGenerated >= make_datetime(lastmonth) and TimeGenerated <= monthEnd

Could someone please review this and let me know what could be the issue?

Appreciate your assistance.

Kind regards.

Answer 1

Hello Varun , Welcome to MS Q&A

When using Log Analytics Workspace, if your query returns double the results compared to using the Time Range feature, it might be due to how the query is structured. If the time filter is applied after a union of several tables, the query may scan all the data instead of just the specified time range, leading to more records than expected.

To ensure the query only processes the relevant records, apply the time filter within each subquery before performing the union. This way, the query will only consider data from the specified time frame, preventing the retrieval of additional records.

For more detailed guidance, you can refer to the following resources:

• Optimize log queries in Azure Monitor
• Log Analytics tutorial

Please let us know if any further questions

Kindly accept if it helps

Thanks
Deepanshu

Answer 2

Hello Varun Kalia,

Try out running the below Kusto query.

It will retrieve audit logs from the beginning of the previous month to the start of the current month.

AuditLogs
| where TimeGenerated >= startofmonth(datetime_add('month', -1, now())) and TimeGenerated < startofmonth(now())

Hope this helps!

Please reply if there are any challenges.

Please do not forget to "Accept the answer” and “upvote it” wherever the information provided helps you, this can be beneficial to other community members.it would be greatly appreciated and helpful to others.

Thanks