KQL Search

Query Details

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:

  1. SigninLogs: This is the data source containing records of sign-ins.

  2. where AuthenticationRequirement == "multiFactorAuthentication": Filters the logs to only include sign-ins that required multi-factor authentication.

  3. 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.

  4. extend myDAY = format_datetime(TimeGenerated, 'yyyy-MM-dd'): Creates a new column myDAY that formats the date in 'yyyy-MM-dd' format, making it easier to read and understand.

  5. order by TimeGenerated asc: Sorts the results in ascending order based on the time the sign-ins were generated.

  6. project myDAY, count_: Selects only the myDAY and 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.