Table results of MFA method used with UPN and timestamp detail

// A list of successfully completed MFA sign-ins, showing the MFA method used, the user (UPN), and the policy or reason that triggered MFA SigninLogs | where AuthenticationRequirement == "multiFactorAuthentication" | where ResultType == 0 | extend ['MFA Method'] = tostring(parse_json(AuthenticationDetails)[1].authenticationMethod) | extend ['MFA Requirement'] = tostring(parse_json(AuthenticationRequirementPolicies)[0].detail) | where ['MFA Method'] != "Previously satisfied" and isnotempty(['MFA Method']) | extend TimeGenerated | project ['MFA Method'], UserPrincipalName, ['MFA Requirement'], TimeGenerated

Explanation

This query is designed to identify users who are still using SMS as their method for multi-factor authentication (MFA). Here's a simple breakdown of what the query does:

Source Data: It starts by looking at the SigninLogs table, which contains records of sign-in attempts.
Filter for MFA: It filters the records to only include those where multi-factor authentication was required (AuthenticationRequirement == "multiFactorAuthentication") and the sign-in was successful (ResultType == 0).
Extract MFA Method: It extracts the MFA method used during the sign-in from the AuthenticationDetails field and assigns it to a new column called MFA Method.
Extract MFA Requirement: It also extracts details about the MFA requirement from the AuthenticationRequirementPolicies field and assigns it to a new column called MFA Requirement.
Exclude Previously Satisfied MFA: It excludes any records where the MFA method was "Previously satisfied" and ensures that the MFA Method field is not empty.
Select Relevant Information: Finally, it selects and displays the MFA Method, the user's principal name (UserPrincipalName), the MFA Requirement, and the timestamp (TimeGenerated) of the sign-in.

The goal is to identify and list users who are still using SMS as their MFA method by examining the MFA Method column in the results.

KQL Search