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

Answer 3

Hello Varun Kalia,

You can use the below KQL query implemented to retrieve the logs during required time stamp. (Eg: For a month)
Reference Kusto function: make_datetime()

let Numberofthelastmonth = getmonth(datetime(now)) - 1;
let lastmonth = iff(Numberofthelastmonth == 0, 12, Numberofthelastmonth);
let year = getyear(datetime(now)) - iff(lastmonth == 12, 1, 0);
let Startdate = make_datetime(year, lastmonth, 01);
let Enddate = endofmonth(Startdate);
AzureActivity
| where TimeGenerated between(Startdate .. Enddate)

Note: In the above query, I used the "AzureActivity" table for better understanding, as there aren't many logs available in the Audit Logs table. The output should behave similarly in both cases.

Refer Blog by Stephane Lapointe for more detailed information on the similar requirement.

Alternatively, you can manually hardcode the start date and end date values of a specific month and directly retrieve the logs in the below way.

Reference Kusto operator: between

AuditLogs 
| where TimeGenerated between(startofday(datetime(2025-02-01)) .. endofday(datetime(2025-02-28)))

Hope this helps.

If the answer is helpful, please click Accept Answer and kindly upvote it. If you have any further questions about this answer, please click Comment.