Query Details
Last Login
Query
//Shows active accounts and days from last login
SigninLogs
| where TimeGenerated > ago(365d)
| where ResultType == "0"
| summarize arg_max(TimeGenerated, *) by UserPrincipalName
| project TimeGenerated, UserPrincipalName, UserType, ['Days Since Last Logon']=toint(datetime_diff("day", now(),TimeGenerated))
Explanation
This query retrieves active accounts and the number of days since their last login. It filters the SigninLogs table to only include entries from the past year and where the ResultType is "0" (indicating a successful login). It then groups the entries by UserPrincipalName and selects the latest TimeGenerated value for each group. Finally, it projects the TimeGenerated, UserPrincipalName, UserType, and calculates the number of days since the last logon.
Details
Rod Trent
Released: December 5, 2022
Tables
SigninLogs
Keywords
SigninLogs,TimeGenerated,UserPrincipalName,UserType
Operators
whereago==summarizearg_maxbyprojecttointdatetime_diffnow()