KQL Search

Query Details

HomeAI ToolsDevice Query

Identity MFA Percentageperapp

Query

//Calculate the percentage of signins to each of your Azure AD applications that used MFA

//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 MFA Count']=countif(AuthenticationRequirement == "multiFactorAuthentication"),
    ['Total non MFA Count']=countif(AuthenticationRequirement == "singleFactorAuthentication")
    by AppDisplayName
| project
    AppDisplayName,
    ['Total Signin Count'],
    ['Total MFA Count'],
    ['Total non MFA Count'],
    MFAPercentage=(todouble(['Total MFA Count']) * 100 / todouble(['Total Signin Count']))
| sort by ['Total Signin Count'] desc, MFAPercentage asc

Explanation

This query calculates the percentage of sign-ins to each Azure AD application that used multi-factor authentication (MFA). It uses the Azure Active Directory - Signin Logs data connector. The query filters the sign-in logs for the past 30 days and only includes successful sign-ins. It then groups the sign-ins by the application and calculates the total sign-in count, total MFA count, and total non-MFA count for each application. Finally, it calculates the MFA percentage by dividing the total MFA count by the total sign-in count and sorts the results by the total sign-in count in descending order and the MFA percentage in ascending order.

Details

Matt Zorich profile picture

Matt Zorich

Released: June 17, 2022

Tables

SigninLogs

Keywords

SigninLogs,TimeGenerated,ResultType,AuthenticationRequirement,AppDisplayName,TotalSigninCount,TotalMFACount,TotalnonMFACount,MFAPercentage

Operators

whereago==summarizecount()countif()byprojecttodouble()sort bydescasc

Actions