Top N Accounts Longest Period Without Password Reset
Query
let LatestNChanges = 100;
AADSignInEventsBeta
| where Timestamp > ago(30d)
// Collect the last event for each account
| summarize arg_max(Timestamp, *) by AccountObjectId
| where isnotempty(LastPasswordChangeTimestamp)
// Calculate the period between now and the last password change
| extend DaysSinceLastPasswordChange = datetime_diff('day', now(), LastPasswordChangeTimestamp)
| project-rename LastSignIn = Timestamp
| project LastSignIn, AccountObjectId, AccountUpn, ErrorCode, DaysSinceLastPasswordChange, IsExternalUser, IsGuestUser, IsManaged
// Select the top n accounts
| top LatestNChanges by DaysSinceLastPasswordChangeAbout this query
Top N Accounts Longest Period Without Password Reset
Query Information
Description
List the top N (based on LatestNChanges) with the longest time between now and their last password reset. While password expiration requirements do more harm than good it is still recommended to take a look at the accounts from which the password has not changed for years. This is due to the changes in the password policy, if the policy has been changed after the latest password change of that account is it likely that the account does not adhere to the currenct password policy. Every next password policy is in most cases an improvement, therefore it is expected that accounts that have not changed their password after the latest policy update do not meet the current complexity requirements.
Risk
If a password has not been changed for years, it might be that the account does not adhere to the current password policy. This can have potential impact, since the password complexity is most likely weaker then expected.
References
Defender XDR
Explanation
This query is designed to identify the top N user accounts that have gone the longest without a password reset. Here's a simplified breakdown of what the query does:
-
Define a Limit: It sets a limit (
LatestNChanges) to the number of accounts to be listed, which is 100 in this case. -
Filter Recent Sign-Ins: It looks at sign-in events from the past 30 days.
-
Identify Latest Event per Account: For each account, it finds the most recent sign-in event.
-
Check for Password Change: It ensures that the account has a recorded timestamp for the last password change.
-
Calculate Time Since Last Password Change: It calculates how many days have passed since the last password change for each account.
-
Select Relevant Information: It selects and renames certain fields to display, such as the last sign-in time, account ID, user principal name (UPN), error code, days since the last password change, and whether the account is external, a guest, or managed.
-
List Top Accounts: Finally, it lists the top N accounts (up to 100) that have the longest period since their last password change, sorted by the number of days since the last password change.
The purpose of this query is to highlight accounts that might not comply with the current password policy due to not having changed their passwords for a long time, potentially posing a security risk.
Details
Bert-Jan Pals
Released: October 20, 2024
Tables
Keywords
Operators