KQL Search

Query Details

HomeAI ToolsDevice Query

Identity Auth Strength MFASFA Percentage

Query

// Calculate the percentage of sign ins requiring authentication strengths, MFA and single factor auth to all of your applications

//Data connector required for this query - Azure Active Directory - Signin Logs

SigninLogs
| where TimeGenerated > ago(30d)
| where ResultType == 0
| summarize
    ['Total Signin Count']=count(),
    ['Total Authentication Strength Count']=countif(AuthenticationRequirementPolicies has 'Authentication Strength(s)' and AuthenticationRequirement == "multiFactorAuthentication"),
    ['Total MFA Count']=countif(AuthenticationRequirementPolicies !has 'Authentication Strength(s)' and AuthenticationRequirement == "multiFactorAuthentication"),
    ['Total non MFA Count']=countif(AuthenticationRequirement == "singleFactorAuthentication")
    by AppDisplayName
| project
    AppDisplayName,
    ['Total Signin Count'],
    ['Total Authentication Strength Count'],
    ['Authentication Strength Percentage']=(todouble(['Total Authentication Strength Count']) * 100 / todouble(['Total Signin Count'])),
    ['Total MFA Count'],
    ['MFA Percentage']=(todouble(['Total MFA Count']) * 100 / todouble(['Total Signin Count'])),
    ['Total non MFA Count'],
    ['Non MFA Percentage']=(todouble(['Total non MFA Count']) * 100 / todouble(['Total Signin Count']))
| sort by ['Total Signin Count'] desc

Explanation

This query calculates the percentage of sign-ins that require different authentication strengths (MFA and single factor authentication) for all applications. It uses the Azure Active Directory - Signin Logs data connector. The query filters the sign-in logs for the past 30 days and then summarizes the counts for each application. It calculates the total sign-in count, the count of sign-ins requiring authentication strength, the count of sign-ins requiring MFA, and the count of sign-ins not requiring MFA. It also calculates the percentages for each category. The results are sorted in descending order based on the total sign-in count.

Details

Matt Zorich profile picture

Matt Zorich

Released: November 29, 2022

Tables

SigninLogs

Keywords

SigninLogs,TimeGenerated,ResultType,AuthenticationRequirementPolicies,AuthenticationRequirement,AppDisplayName,TotalSigninCount,TotalAuthenticationStrengthCount,TotalMFACount,TotalnonMFACount,AuthenticationStrengthPercentage,MFAPercentage,NonMFAPercentage

Operators

where>ago==summarizecount()countif()has!hasbyprojecttodouble()*/sort bydesc

Actions