How to write a Kusto query to get previous month logs in sentinel?

Question

| where TimeGenerated > ago(30d) only gives me the last 30 days logs and I'm searching for a query to get previous month logs from a table, so I can export it directly into Power BI.

Answer 1

Here is how you can do it below. I am showing two ways. The 'easy' way is to just hand jam the dates in for the month. The harder way requires you to use the make_datetime function.

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

https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/make-datetimefunction

Answer 2

Just wanted to add on to @Ken W MSFT's great query, by suggesting this for the automation

let time_start = startofmonth(datetime(now), -1); let time_end = endofmonth(datetime(now),-1); AuditLogs | where TimeGenerated between (time_start .. time_end)