Search code examples
azurepowerbipowerquerykqlazure-sentinel

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

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

Solution

  • 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