Signin Logs By Day Parsing UTC
Query
//Sign-ins - how many per day - different ways to get the day split out
SigninLogs
| where AuthenticationRequirement == "multiFactorAuthentication"
| summarize count() by bin(TimeGenerated, 1d)
| extend myDAY = format_datetime(TimeGenerated, 'yyyy-MM-dd') //using datetime
//| extend myDAY = format_datetime(TimeGenerated, 'dd') //using datetime, just the day
//| extend myDAY = bin(TimeGenerated, 1d) //using bin, but still displays the time, too
//| extend myDAY = split(TimeGenerated, "T", 0) //using split to parse
| order by TimeGenerated asc
| project myDAY, count_Explanation
This query is analyzing sign-in logs to count the number of sign-ins that required multi-factor authentication, broken down by day. Here's a simple breakdown of what each part of the query does:
-
SigninLogs: This is the data source containing records of sign-ins.
-
where AuthenticationRequirement == "multiFactorAuthentication": Filters the logs to only include sign-ins that required multi-factor authentication.
-
summarize count() by bin(TimeGenerated, 1d): Groups the data by day (using a 1-day bin) and counts the number of sign-ins for each day.
-
extend myDAY = format_datetime(TimeGenerated, 'yyyy-MM-dd'): Creates a new column
myDAYthat formats the date in 'yyyy-MM-dd' format, making it easier to read and understand. -
order by TimeGenerated asc: Sorts the results in ascending order based on the time the sign-ins were generated.
-
project myDAY, count_: Selects only the
myDAYand the count of sign-ins for output, effectively showing the number of sign-ins per day.
The commented-out lines provide alternative methods for extracting or formatting the day from the TimeGenerated field, but they are not active in this query.
Details
Rod Trent
Released: August 11, 2020
Tables
Keywords
Operators