Query Details
No Loginto AA Din90days
Query
//AAD users that haven’t performed a successful/failed login to AAD in the last 90 days
IdentityInfo
| where TimeGenerated > ago(30d)
| summarize arg_max(TimeGenerated, *) by AccountObjectId
| join kind=leftanti (
SigninLogs
| where TimeGenerated > ago(90d)
) on $left.AccountObjectId == $right.UserId
Explanation
This query is looking for Azure Active Directory (AAD) users who have not logged in successfully or failed to log in to AAD in the last 90 days. It retrieves the identity information of users, filters it based on the time generated in the last 30 days, and then finds the latest record for each user. It then compares this information with the sign-in logs, filtering them based on the time generated in the last 90 days. Finally, it returns the users who do not have any matching sign-in logs.
Details
Rod Trent
Released: August 3, 2021
Tables
IdentityInfoSigninLogs
Keywords
AAD,Users,Login,TimeGenerated,AccountObjectId,SigninLogs,UserId
Operators
whereagosummarizearg_maxbyjoinkind=leftantion$left.$right.==