> ## Content Index
> Fetch the complete content index at: https://codeisahighway.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# How to dynamically set last month date range in KQL query and Log Analytics
- URL: https://codeisahighway.com/how-to-dynamically-set-last-month-date-range-in-kql-query-and-log-analytics/
- Published: 2022-02-01T16:49:42.000Z
- Updated: 2026-07-17T08:25:48.000Z
- Author: Stephane Lapointe
- Tags: KQL, Log Analytics, #Import 2023-07-11 16:44, #Import 2026-09-04 10:31, #Import 2026-09-04 13:56

I wanted to improve an audit query we are running every month because I hate doing things manually over and over again. Before today, we were setting the date range for the previous month manually and I was searching a way to automate this instead. I found a way...

## Manually setting query date range in Log Analytics

There are a few ways that you can set a date range for your query in Log Analytics.

1. Set the date range using the time range control in the UI

![](https://codeisahighway.com/content/images/2022/02/image.png)

2\. Set the date range manually in the query

```KQL
AzureActivity
| where TimeGenerated between (datetime('2022-01-01') .. datetime('2022-01-31'))
```

But they all imply manual intervention to update the query (every month) in our case. There must be a better way I asked myself, and there is!

## Automatically determine last month's date range

I was searching for a way, in the query itself to set the date to last month relative to the date the query execute. Let's say I am February 1st and I execute the query, I want the date range to be 2022-01-01 .. 2022-01-31.

Searching the web sent me directly to [StackOverflow](https://stackoverflow.com/questions/69164108/how-to-write-a-kusto-query-to-get-previous-month-logs-in-sentinel?ref=codeisahighway.com). There was my solution!

```KQL
// StackOverflow code I found at https://stackoverflow.com/questions/69164108/how-to-write-a-kusto-query-to-get-previous-month-logs-in-sentinel

// The Easy 'Manual' Way
AuditLogs
| where TimeGenerated >= datetime('2021-08-01') and TimeGenerated <= datetime('2021-08-31')
// Automated Way
let lastmonth = getmonth(datetime(now)) -1;
let year = getyear(datetime(now)); 
let monthEnd = endofmonth(datetime(now),-1); 
AuditLogs
| where TimeGenerated >= make_datetime(year,lastmonth,01) and TimeGenerated <= monthEnd
```

Not so fast... it is working, sometimes, but not exactly fit my needs. If you run this in January, we want it to shift the range to December of last year and a few small fixes like this. The solution in StackOverflow was a great starting point though.

## Let's do this!

First we determine `lastMonthNumber`, we determine the current month and subtract `1` from the number. 

```KQL
let lastmonthNumber = getmonth(datetime(now)) - 1;
```

This will work for all of the months, except January. In January, `getmonth()` will return `1` and we cannot magically switch it to `12` by subtracting `1`. We need the help of the `iff()` function here. If `lastMonthNumber == 0`, it means we are *currently* in January, we need to change it to `12` ourselves to point to December instead.

```KQL
let lastmonth = iff(lastmonthNumber == 0, 12, lastmonthNumber);
```

For `year` we get the current year as of today and subtract `1` if lastMonth is `12`, otherwise we subtract nothing, sending it `0`.

```KQL
let year = getyear(datetime(now)) - iff(lastmonth == 12, 1, 0);

```

From there we have all the information we need to set `dateStart` and `dateEnd`. 

```KQL
let dateStart = make_datetime(year, lastmonth, 01);
let dateEnd = endofmonth(dateStart);
```

We just need to use them against `TimeGenerate` in our case as follows:

`| where TimeGenerated between(dateStart .. dateEnd)`

Here is what I ended up with.

```KQL
// Automatic date calc to get full month
let lastmonthNumber = getmonth(datetime(now)) - 1;
let lastmonth = iff(lastmonthNumber == 0, 12, lastmonthNumber);
let year = getyear(datetime(now)) - iff(lastmonth == 12, 1, 0);
let dateStart = make_datetime(year, lastmonth, 01);
let dateEnd = endofmonth(dateStart);
AzureActivity
| where TimeGenerated between(dateStart .. dateEnd)
```

Hope it helps! Happy KQL!