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.
- 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);
year we get the current year as of today and subtract
1 if lastMonth is
12, otherwise we subtract nothing, sending it
let year = getyear(datetime(now)) - iff(lastmonth == 12, 1, 0);
From there we have all the information we need to set
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!