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

2. Set the date range manually in the query

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. There was my solution!

// 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. 

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.

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.

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

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

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.

// 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!