Visualise Time Of Last Password Reset
Visualization Accounts Longest Period Without Password Reset
Query
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)
// put the results into bins of 10 days
| summarize TotalAccounts = count() by bin(DaysSinceLastPasswordChange, 10)
| sort by DaysSinceLastPasswordChange asc
| render columnchart with(xtitle="Days since last password change", ytitle="Total accounts")About this query
Visualise Time Of Last Password Reset
Query Information
Description
Visualise the time of which a password reset has last taken place, the information is grouped in buckets of 10 days. 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 visualize when the last password reset occurred for user accounts, grouping the data into 10-day intervals. Here's a simplified breakdown of what the query does:
-
Data Source: It starts by looking at sign-in events from the last 30 days.
-
Latest Event per Account: For each user account, it identifies the most recent sign-in event.
-
Filter for Password Change: It filters out accounts that have a recorded timestamp for the last password change.
-
Calculate Days Since Last Change: It calculates how many days have passed since each account's password was last changed.
-
Group into 10-Day Buckets: It groups these accounts into buckets, each representing a range of 10 days since the last password change.
-
Count Accounts: It counts how many accounts fall into each 10-day bucket.
-
Sort and Visualize: It sorts the results by the number of days since the last password change and displays the data in a column chart. The x-axis represents the days since the last password change, and the y-axis represents the total number of accounts.
The purpose of this visualization is to identify accounts that haven't had their passwords changed in a long time, which might indicate they don't comply with the current password policy, potentially posing a security risk.
Details
Bert-Jan Pals
Released: October 20, 2024
Tables
Keywords
Operators