Query Details
Identity MFA Count Per User
Query
//Calculate how often your users are actively challenged for MFA vs when it was previously satisfied per day //Return users who are challenged over the threshold per day //Data connector required for this query - Azure Active Directory - Signin Logs let threshold = 5; SigninLogs | where TimeGenerated > ago(90d) | where AuthenticationRequirement == "multiFactorAuthentication" | extend x=todynamic(AuthenticationDetails) | mv-expand x | project TimeGenerated, x, UserPrincipalName | extend MFAResultStep = tostring(x.authenticationStepResultDetail) | summarize MFARequired=countif(MFAResultStep == "MFA completed in Azure AD"), PreviouslySatisfied=countif(MFAResultStep == "MFA requirement satisfied by claim in the token") by UserPrincipalName, startofday(TimeGenerated) | where MFARequired >= threshold
Explanation
This query calculates how often users are challenged for multi-factor authentication (MFA) compared to when it was previously satisfied, on a daily basis. It returns users who are challenged more than a specified threshold per day. The query uses Azure Active Directory - Signin Logs as the data source and filters the logs based on a time range and the authentication requirement being MFA. It then expands the authentication details, selects relevant columns, and calculates the count of MFA requirements and previously satisfied MFA for each user and day. Finally, it filters the results to only include users who have been challenged for MFA more times than the specified threshold.
Details
Matt Zorich
Released: June 17, 2022
Tables
Keywords
Operators