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

Azure Sentinel 1 Minute

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
Using Extend to parse the raw output

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

| extend myDAY = format_datetime(TimeGenerated, 'yyyy-MM-dd') //using datetime
Using datetime to display full date

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
Using datetime to display 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
Using bin with TimeGenerated

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.

[Want to discuss this further? Hit me up on Twitter or LinkedIn]

Author

Published