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.
[Want to discuss this further? Hit me up on Twitter or LinkedIn]
You must log in to post a comment.