KQL Methods to Display a Per-Day Occurrence in Azure Sentinel

A customer recently wanted to show in a Workbook those users that used MFA to login and format the results so that it showed how many times per day it happened overall. There’s multiple ways to get this done. You can parse the raw output of the TimeGenerated, use format_datetime, or bin with TimeGenerated.

Here’s each of those methods and what the output looks like so you can choose the one that fits your needs most.

Thanks go out to several people both externally and internally for helping source the information to split out per-day occurrences in Azure Sentinel data.

Here’s the parsing route, working with the raw data instead of the displayed result data:

| extend myDAY = split(TimeGenerated, "T", 0) //using split to parse

The following uses the format_datetime against TimeGenerated to display the full date:

| extend myDAY = format_datetime(TimeGenerated, 'yyyy-MM-dd') //using datetime

The next one uses format_datetime to show how to display just the “day”:

| extend myDAY = format_datetime(TimeGenerated, 'dd') //using datetime, just the day

And, lastly, the following utilize bin, but still displays the time with the full date:

| extend myDAY = bin(TimeGenerated, 1d) //using bin, but still displays the time, too

The most current query with all the options is available from my GitHub repo: https://github.com/rod-trent/SentinelKQL/blob/master/SigninLogsByDay%20-%20parsing%20UTC.txt

Have a different method that you use? Let me know.

Here’s each of those methods and what the output looks like so you can choose the one that fits your needs most.

Thanks go out to several people both externally and internally for helping source the information to split out per-day occurrences in Azure Sentinel data.

Here’s the parsing route, working with the raw data instead of the displayed result data:

| extend myDAY = split(TimeGenerated, "T", 0) //using split to parse

The following uses the format_datetime against TimeGenerated to display the full date:

| extend myDAY = format_datetime(TimeGenerated, 'yyyy-MM-dd') //using datetime

The next one uses format_datetime to show how to display just the “day”:

| extend myDAY = format_datetime(TimeGenerated, 'dd') //using datetime, just the day

And, lastly, the following utilize bin, but still displays the time with the full date:

| extend myDAY = bin(TimeGenerated, 1d) //using bin, but still displays the time, too

The most current query with all the options is available from my GitHub repo: https://github.com/rod-trent/SentinelKQL/blob/master/SigninLogsByDay%20-%20parsing%20UTC.txt

Have a different method that you use? Let me know.

Reference: https://azurecloudai.blog/2020/08/11/different-ways-to-use-kql-to-show-a-per-day-occurrence-in-your-azure-sentinel-data/