KQL Search

//Alert when a user successfully signs in from both a new location and using a new MFA method

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

//Cache all authentication methods and locations to memory using the materialize function for the last 6 months
let mfahistory = materialize  (
    SigninLogs
    | where TimeGenerated > ago (180d) and TimeGenerated < ago(1d)
    | where ResultType == 0
    | where AuthenticationRequirement == "multiFactorAuthentication"
    | extend AuthMethod = tostring(MfaDetail.authMethod)
    | where isnotempty(AuthMethod)
    | distinct UserPrincipalName, AuthMethod, Location);
//Find sign ins from the last day that have both a new location and MFA method
mfahistory
| join kind=rightanti  (
    SigninLogs
    | where TimeGenerated > ago (1d)
    | where ResultType == 0
    | where AuthenticationRequirement == "multiFactorAuthentication"
    | extend AuthMethod = tostring(MfaDetail.authMethod)
    | where isnotempty(AuthMethod)
    | distinct 
        UserPrincipalName,
        AuthMethod,
        AppDisplayName,
        Location,
        IPAddress)
    on UserPrincipalName, Location