KQL Search

Query Details

HomeAI ToolsDevice Query

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 counting the number of sign-ins per day for users who have multi-factor authentication enabled. It splits the sign-ins by day and displays the count for each day. The query also includes different ways to extract the day from the timestamp, such as using datetime formatting, binning, or splitting the timestamp string. The results are ordered by the timestamp in ascending order and only the day and count columns are displayed.

Details

Rod Trent profile picture

Rod Trent

Released: August 11, 2020

Tables

SigninLogs

Keywords

SigninLogs,AuthenticationRequirement,TimeGenerated,myDAY,count_

Operators

wheresummarizecount()bybinextendformat_datetimeorder byproject

Actions