Query Details
Identity Visual Std Devof MFA Failures
Query
//Visualize the standard deviation of MFA failures per day using toscalar() //Data connector required for this query - Azure Active Directory - Signin Logs //Microsoft Sentinel query let AverageMFAFailures = toscalar(SigninLogs | where TimeGenerated > ago(60d) | where ResultType == 500121 | summarize Count=count() by bin(TimeGenerated, 1d) | summarize avg(Count)); SigninLogs | where TimeGenerated > ago(60d) | where ResultType == 500121 | summarize Count=count() by bin(TimeGenerated, 1d) | extend Deviation = (Count - AverageMFAFailures) / AverageMFAFailures | project-away Count //Visualize the deviation per day | render columnchart with (title="Deviation of MFA failures per day") //Advanced Hunting query //Data connector required for this query - Advanced Hunting with Azure AD P2 License let AverageMFAFailures = toscalar(AADSignInEventsBeta | where Timestamp > ago(30d) | where ErrorCode == 500121 | summarize Count=count() by bin(Timestamp, 1d) | summarize avg(Count)); AADSignInEventsBeta | where Timestamp > ago(30d) | where ErrorCode == 500121 | summarize Count=count() by bin(Timestamp, 1d) | extend Deviation = (Count - AverageMFAFailures) / AverageMFAFailures | project-away Count //Visualize the deviation per day | render columnchart with (title="Deviation of MFA failures per day")
Explanation
The query calculates the standard deviation of MFA failures per day and visualizes it using a column chart. It uses either the Azure Active Directory - Signin Logs or the Advanced Hunting with Azure AD P2 License data connectors, depending on the query type. The query filters the data to include only MFA failures (ResultType == 500121 or ErrorCode == 500121) within a specific time range (60 days or 30 days). It then groups the data by day and calculates the count of MFA failures for each day. The average count of MFA failures is calculated and stored in the variable "AverageMFAFailures". The query then calculates the deviation of MFA failures for each day by subtracting the average count and dividing it by the average count. Finally, it projects the deviation and visualizes it using a column chart.
Details
Matt Zorich
Released: August 14, 2023
Tables
Keywords
Operators