Query Details
Audit Find Users Failing SSPR
Query
//Detect users who are trying to use self service password reset but failing as they don't have any authentication methods listed //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(ResultReason) by CorrelationId, User, ['User IP Address'] | where ['SSPR Actions'] has "User's account has insufficient authentication methods defined. Add authentication info to resolve this" | sort by User desc
Explanation
This query is looking for users who are attempting to use self-service password reset but are failing because they do not have any authentication methods listed. It uses the Azure Active Directory - Audit Logs data connector. The query filters for events logged by the "Self-service Password Management" service, extracts the user and IP address information, sorts the results by time, and then summarizes the actions taken during the password reset attempts. It filters for actions where the result reason is "User's account has insufficient authentication methods defined. Add authentication info to resolve this" and sorts the final results by user in descending order.
Details
Matt Zorich
Released: June 17, 2022
Tables
AuditLogs
Keywords
AuditLogs,LoggedByService,Self-servicePasswordManagement,InitiatedBy.user,UserPrincipalName,ipAddress,TimeGenerated,ResultReason,CorrelationId
Operators
whereextendtostringparse_jsonsort bysummarizehas