Query previous month's Azure Firewall logs with KQL

Published: | 3 min read

Intro

Few days ago I had to craft a Log Analytics query with KQL, which gets previous month’s Azure Firewall logs for specific source and target IPs. Setting the previous month dynamically ended up being a bit more complicated than I initially thought.

Dynamically set the previous month in KQL

I started to craft the query by first finding out how to get the previous month. Then I needed to handle the January > December “edge-case” for the month. I used KQL’s ternary equivalent iff function for this.

If the current month is January, so the previousMonth will be 0, I need to switch that to 12 to set startMonth to December.

To set the startYear I used the getyear function, and again used the iff function to see if I needed to subtract one from the year or not.

To create the startDate scalar value, I used the make_datetime function.

After all this, the endDate was easy to get with the endofmonth function.

// Get the previous month's number id
let previousMonth = getmonth(datetime(now)) - 1;
// Handle January > December (12)
let startMonth = iff(previousMonth == 0, 12, previousMonth);
// Get the year and again, handle January > December
let startYear = getyear(datetime(now)) - iff(startMonth == 12, 1, 0);
// Create date scalar value from string components
let startDate = make_datetime(startYear, startMonth, 01);
// Create end date
let endDate = endofmonth(startDate);

Basic Azure Firewall query in KQL

A basic query to filter out blocked traffic for specific “source” IP 10.10.100.5 and “target” IP 10.11.100.5 is as easy as:

AzureDiagnostics
| where Category == "AzureFirewallNetworkRule" or Category == "AzureFirewallApplicationRule"
| where msg_s has "10.10.10.5" and msg_s has "10.11.100.5"
| where msg_s has_cs "Deny"
| order by TimeGenerated desc
| project TimeGenerated,msg_s

The “source” and “target” are in quotes, since technically speaking they are in same log field and there is no difference between them querywise. If I am only interested in one IP or FQDN, I can clean the where clause to include only one filter.

Get firewall log events for the previous month dynamically with KQL

Doing the previous work, it is easy to combine these two to get the final result:

let previousMonth = getmonth(datetime(now)) - 1;
let startMonth = iff(previousMonth == 0, 12, previousMonth);
let startYear = getyear(datetime(now)) - iff(startMonth == 12, 1, 0);
let startDate = make_datetime(startYear, startMonth, 01);
let endDate = endofmonth(startDate);

AzureDiagnostics
| where Category == "AzureFirewallNetworkRule" or Category == "AzureFirewallApplicationRule"
// set the time range
| where TimeGenerated between(startDate .. endDate)
| where msg_s has "10.12.120.72" and msg_s has "bing"
| where msg_s has_cs "Deny"
| order by TimeGenerated desc
| project TimeGenerated,msg_s

I wish one day KQL will have more date and time related functions built-in, so I don’t need to do the same acrobatics for this simple need. But as of now, here’s how I did this with the tools I had.

Further reading