Query Details
Audit Find Users Failing New Password SSPR
Query
//Find users who have failed 3 or more times to set a new password during a SSPR flow. Worth reaching out to them to give them a hand or see if you can onboard them to something like Windows Hello for Business //Data connector required for this query - Azure Active Directory - Audit Logs AuditLogs | where LoggedByService == "Self-service Password Management" | extend User = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName) | extend ['User IP Address'] = tostring(parse_json(tostring(InitiatedBy.user)).ipAddress) | sort by TimeGenerated asc | summarize ['SSPR Actions']=make_list_if(ResultReason, ResultReason has "User submitted a new password") by CorrelationId, User, ['User IP Address'] | where array_length(['SSPR Actions']) >= 3 | sort by User desc
Explanation
This query is looking for users who have failed to set a new password three or more times during a self-service password reset (SSPR) flow. The purpose is to identify these users and offer assistance or suggest using Windows Hello for Business as an alternative. The query uses the Azure Active Directory - Audit Logs data connector and filters for events logged by the "Self-service Password Management" service. It extracts the user's email and IP address from the audit logs, sorts the results by time, and then summarizes the number of times a user has submitted a new password. Finally, it filters for users who have failed three or more times and sorts the results by user email in descending order.
Details
Matt Zorich
Released: June 18, 2022
Tables
Keywords
Operators