KQL query examples for Microsoft Entra ID with Log Analytics (updated June 2025)

Entra ID - Sign-in

List sign-ins

SigninLogs
  // Query only successfull sign-ins
  | where ResultType == 0
  | where UserPrincipalName startswith "xxxx"

List sign-ins from IPs

let IPs = datatable(IPAddress: string) ["xxxx", "xxx"];
IPs
| join kind=leftouter (SigninLogs | summarize SignInCount = count() by IPAddress) on IPAddress
| project IPAddress, SignInCount = iff(isnull(SignInCount), 0, SignInCount)

List sign-ins from non-France IPs

SigninLogs
| where tostring(LocationDetails.countryOrRegion) != "FR"

List sign-ins grouped by IPs with user, location and first seen/last

SigninLogs
| where UserPrincipalName in~ ("[email protected]", "[email protected]")
| summarize Connections = count(), FirstSeen = min(TimeGenerated), LastSeen = max(TimeGenerated) by 
    User = UserPrincipalName, 
    IP = IPAddress, 
    Country = tostring(LocationDetails.countryOrRegion), 
    City = tostring(LocationDetails.city)

List sign-ins without MFA (any method)

This query lists all successful single-factor sign-ins regardless of the authentication method used. If the AuthenticationDetails array is empty (often the case for B2B scenarios), the method falls back to the CrossTenantAccessType value.

SigninLogs
// Only successful sign-ins
| where ResultType == 0
// Exclude Windows and Auth Broker apps
| where AppDisplayName !in ("Windows Sign In", "Microsoft Authentication Broker")
// Parse AuthenticationDetails array
| extend details = parse_json(AuthenticationDetails)
// Extract authenticationMethod from first step if available, else use CrossTenantAccessType
| extend authenticationMethod = iif(array_length(details) > 0, tostring(details[0].authenticationMethod),
                          iif(isnotempty(CrossTenantAccessType), tostring(CrossTenantAccessType), ""))
// Keep only single-factor authentication attempts
| where AuthenticationRequirement == "singleFactorAuthentication"
// Exclude methods already satisfied (e.g. SSO)
| where authenticationMethod != "Previously satisfied"
// Add UserName and UPN suffix for entity correlation
| extend UserName = split(UserPrincipalName, "@")[0], UserUPNSuffix = split(UserPrincipalName, "@")[1]
// Extract device information
| extend DeviceId = tostring(DeviceDetail.deviceId)
| extend DeviceOperatingSystem = tostring(DeviceDetail.operatingSystem)
// Project and reorder columns
| project-reorder TimeGenerated, UserPrincipalName, UserName, UserUPNSuffix, AuthenticationRequirement, authenticationMethod, AuthenticationProtocol, DeviceId, DeviceOperatingSystem

List sign-ins without MFA (only password method)

This version filters sign-ins strictly using the "Password" method. It only includes records where AuthenticationDetails is not empty and the first step is clearly marked as "Password".

SigninLogs
// Only successful sign-ins
| where ResultType == 0
// Exclude Windows and Auth Broker apps
| where AppDisplayName !in ("Windows Sign In", "Microsoft Authentication Broker")
// Parse AuthenticationDetails array
| extend details = parse_json(AuthenticationDetails)
// Extract authenticationMethod from first step if available, else use CrossTenantAccessType
| extend authenticationMethod = iif(array_length(details) > 0, tostring(details[0].authenticationMethod),
                          iif(isnotempty(CrossTenantAccessType), tostring(CrossTenantAccessType), ""))
// Keep only single-factor authentication attempts
| where AuthenticationRequirement == "singleFactorAuthentication"
// Limit to password only authentication
| where authenticationMethod == "Password"
// Add UserName and UPN suffix for entity correlation
| extend UserName = split(UserPrincipalName, "@")[0], UserUPNSuffix = split(UserPrincipalName, "@")[1]
// Extract device information
| extend DeviceId = tostring(DeviceDetail.deviceId)
| extend DeviceOperatingSystem = tostring(DeviceDetail.operatingSystem)
// Project and reorder columns
| project-reorder TimeGenerated, UserPrincipalName, UserName, UserUPNSuffix, AuthenticationRequirement, authenticationMethod, AuthenticationProtocol, DeviceId, DeviceOperatingSystem

List sign-ins without MFA (only password method) and excluded sign-ins coming from either a trusted network location or a compliant device

SigninLogs
  // Query only successfull sign-ins
  | where ResultType == 0
  // Ignore login to Windows and Microsoft Authentication Broker
  | where AppDisplayName != "Windows Sign In" and AppDisplayName != "Microsoft Authentication Broker"  // Limit to password only authentication
  // Limit to password only authentication
  | extend authenticationMethod = tostring(parse_json(AuthenticationDetails)[0].authenticationMethod)
  | where authenticationMethod == "Password"
  // Limit to non MFA sign-ins
  | where AuthenticationRequirement == "singleFactorAuthentication"
  // Remove all sign-ins coming from either a trusted network location or a compliant device
  | where NetworkLocationDetails == "[]" and DeviceDetail.isCompliant != true
  // Add UserName and UserUPNSuffix for strong entity match
  | extend UserName = split(UserPrincipalName,'@',0)[0], UserUPNSuffix = split(UserPrincipalName,'@',1)[0]
  | extend DeviceId = tostring(DeviceDetail.deviceId)
  | extend DeviceOperatingSystem = tostring(DeviceDetail.operatingSystem)
  | project-reorder TimeGenerated, UserPrincipalName, AuthenticationRequirement, authenticationMethod, AuthenticationProtocol

List sign-ins with MFA

SigninLogs
  // Query only successfull sign-ins
  | where ResultType == 0
  // Ignore login to Windows
  | where AppDisplayName != "Windows Sign In"
  // Limit to password only authentication
  | extend authenticationStepRequirement = tostring(parse_json(AuthenticationDetails)[0].authenticationStepRequirement)
  | where AuthenticationRequirement == "multiFactorAuthentication"
  | project TimeGenerated , UserPrincipalName

List sign-ins with MFA from IPs

let allowedIPs = dynamic(["xxx.xxx.xxx.xxx", "yyy.yyy.yyy.yyy"]);
SigninLogs
  // Query only successfull sign-ins
  | where ResultType == 0
  // Ignore login to Windows
  | where AppDisplayName != "Windows Sign In"
  // Limit to password only authentication
  | extend authenticationStepRequirement = tostring(parse_json(AuthenticationDetails)[0].authenticationStepRequirement)
  | where AuthenticationRequirement == "multiFactorAuthentication"
  | where IPAddress in (allowedIPs)
| summarize count() by UserPrincipalName, IPAddress

List sign-ins with MFA type

SigninLogs
| where AuthenticationRequirement == "multiFactorAuthentication"
| where ResultType == 0
| extend Step = parse_json(AuthenticationDetails)[1]
| extend MFAMethod = tostring(Step.authenticationMethod)
| where MFAMethod != "Previously satisfied" and isnotempty(MFAMethod)
| project TimeGenerated, UserPrincipalName, AppDisplayName, MFAMethod, IPAddress, ConditionalAccessStatus

Chart of MFA types used

For your information, you can also retrieve this data in Entra ID (but it's limited to a maximum of 30 days): https://entra.microsoft.com/#view/Microsoft_AAD_IAM/AuthenticationMethodsMenuBlade/~/AuthMethodsActivity > Usage tab.

SigninLogs
| where AuthenticationRequirement == "multiFactorAuthentication"
| where ResultType == 0
| project AuthenticationDetails
| extend ['MFA Method'] = tostring(parse_json(AuthenticationDetails)[1].authenticationMethod)
| summarize Count=count()by ['MFA Method']
| where ['MFA Method'] != "Previously satisfied" and isnotempty(['MFA Method'])
| sort by Count desc
| render barchart with (title="Types of MFA Methods used")

Result:

List conditional access policy used

SigninLogs
// Additional Toggle to determine CA result for success/failure login
//| where ResultType == "0"
| where ConditionalAccessPolicies != "[]"
| mv-expand ConditionalAccessPolicies
| extend CADisplayName = tostring(ConditionalAccessPolicies.displayName)
| extend CAResult = tostring(ConditionalAccessPolicies.result)
| summarize Count=count() by CADisplayName, CAResult
| sort by CADisplayName asc

List the use of conditional access policies and their status (Success/Failure)

SigninLogs
| mv-expand todynamic(ConditionalAccessPolicies)
| extend CAResult=tostring(ConditionalAccessPolicies.result), CAName=tostring(ConditionalAccessPolicies.displayName)
| summarize TotalCount=count(),ResultSet=make_set(CAResult) by CAName
| where not(ResultSet has_any ("success","failure"))
| sort by CAName asc

List successful sign-ins using a specific conditional access policy

Replace xxx with the name of your conditional access policy.

SigninLogs
| mv-expand ConditionalAccessPolicies
| where ConditionalAccessPolicies.displayName == "xxx"
| where tostring(ConditionalAccessPolicies.result) == "success"
| project 
    TimeGenerated,
    UserPrincipalName,
    AppDisplayName,
    IPAddress,
    ConditionalAccessPolicyName = ConditionalAccessPolicies.displayName,
    ConditionalAccessResult = ConditionalAccessPolicies.result

List sign-in using SMS

SigninLogs
| where isnotempty(AuthenticationDetails)
| extend AuthDetails = parse_json(AuthenticationDetails)
| mv-expand AuthDetails
| extend AuthMethod = tostring(AuthDetails.authenticationMethod)
| where AuthMethod == "SMS Sign-in"

List sign-in using device-code

SigninLogs
| where  AuthenticationProtocol == "deviceCode"

List sign-in using QRCode

SigninLogs
| where isnotempty(AuthenticationDetails)
| extend AuthDetails = parse_json(AuthenticationDetails)
| mv-expand AuthDetails
| where tostring(AuthDetails.authenticationMethod) == "QR code pin"

Check https://itpro-tips.com/kql-query-examples-for-microsoft-entra-id/#qr-code-added-to-the-user-by-an-administrator-for-qr-code-sign-in to identify when the QRCode was added by admin.

List users, number of sign-ins and last sign-in date for a specific app in Entra ID

In the following example, I use the Application ID 14d82eec-204b-4c2f-b7e8-296a70dab67e, which is Microsoft Graph Command Line Tools.

SigninLogs
| where AppId == "14d82eec-204b-4c2f-b7e8-296a70dab67e"
| summarize SignInCount = count(), LastSignIn = max(TimeGenerated) by UserPrincipalName
| order by SignInCount desc

Resolve Entra ID / Azure AD SignIn errors

When you encounter a sign-in error, the ResultDescription often shows Other, which isn't very helpful.
Fabien Bader maintains a comprehensive list of Entra ID error codes with descriptions that you can leverage.
Note: Full credit for the following KQL goes to him and his article: https://cloudbrothers.info/en/entra-id-azure-ad-signin-errors/

let ResolvedErrorCodes = externaldata(code: string, Message: string)
['https://raw.githubusercontent.com/f-bader/EntraID-ErrorCodes/main/EntraIDErrorCodes.json']
with (format='multijson');
SigninLogs
| where ResultType != 0
| join kind=leftouter ResolvedErrorCodes on $left.ResultType == $right.code
| extend ResultDescription = iff(ResultDescription == "Other", iff(isempty(Message), "Other", Message), ResultDescription)
| project-away Message, code
| project-reorder TimeGenerated, ResultType, ResultDescription

Entra ID - Audit logs

List conditional access policies changes

AuditLogs
| where ActivityDisplayName == "Update policy"
| project ActivityDateTime, ActivityDisplayName, TargetResources[0].displayName, InitiatedBy.user.userPrincipalName

List device registration policies changes

AuditLogs
| where OperationName == "Set device registration policies"
| project TimeGenerated, ActivityDisplayName, AdditionalDetails[0].value, InitiatedBy.user.userPrincipalName   

List Windows LAPS password changes

AuditLogs
| where OperationName == "Update device local administrator password"
| project TimeGenerated, TargetResources[0].displayName, Result

List who has added an application in Entra ID

Replace xxx with the name of the application you are searching for.

AuditLogs
| where TimeGenerated >= ago(1000d)
| where OperationName in ("Add application", "Add service principal")
| where TargetResources has "xxxxxxx"
| extend AppDisplayName = tostring(TargetResources[0].displayName)
| extend AppId = tostring(TargetResources[0].id)
| extend CreatedBy = tostring(InitiatedBy.user.userPrincipalName)
| project TimeGenerated, OperationName, AppDisplayName, AppId, CreatedBy

List of successful Self-Service Password Reset (SSPR) events with methods used for validation, ClientType and OnPremisesAgent

For your information, you can also retrieve this data in Entra ID (but it's limited to a maximum of 30 days): https://entra.microsoft.com/#view/Microsoft_AAD_IAM/AuthenticationMethodsMenuBlade/~/RegistrationAndResetLogs > Filter Activity type: Reset or https://entra.microsoft.com/#view/Microsoft_AAD_IAM/AuthenticationMethodsMenuBlade/~/AuthMethodsActivity > Usage tab

AuditLogs
// Filter for successful self-service password reset events
| where OperationName contains "Reset password (self-service)"
| where ResultDescription == "Successfully completed reset."
| where Result == "success"
// Extract user principal name and IP address of the initiator
| extend UserPrincipalName = tostring(InitiatedBy.user.userPrincipalName)
| extend IpAddress = tostring(InitiatedBy.user.ipAddress)
// Expand the AdditionalDetails array to access key/value pairs
| mv-expand AdditionalDetails
| extend Key = tostring(AdditionalDetails["key"]), Value = tostring(AdditionalDetails["value"])
// Aggregate key/value pairs into a dynamic object (bag)
| summarize Details = make_bag(pack(Key, Value)) by TimeGenerated, UserPrincipalName, IpAddress
// Extract specific fields from the bag
// Use replace to remove brackets and quotes from the MFA method string (stored as JSON array)
| extend ClientType = tostring(Details["ClientType"]),
         MethodsUsedForValidation = replace(@'[\[\]"]', '', tostring(Details["MethodsUsedForValidation"])),
         OnPremisesAgent = tostring(Details["OnPremisesAgent"])
| project TimeGenerated, UserPrincipalName, IpAddress, ClientType, MethodsUsedForValidation, OnPremisesAgent, Details

Chart of successful Self-Service Password Reset (SSPR) by methods used for validation

AuditLogs
| where OperationName contains "Reset password (self-service)"
| where ResultDescription == "Successfully completed reset."
| where Result == "success"
| extend UserPrincipalName = tostring(InitiatedBy.user.userPrincipalName)
| extend IpAddress = tostring(InitiatedBy.user.ipAddress)
| mv-expand AdditionalDetails
| extend Key = tostring(AdditionalDetails["key"]), Value = tostring(AdditionalDetails["value"])
| summarize Details = make_bag(pack(Key, Value)) by TimeGenerated, UserPrincipalName, IpAddress
// Extract and clean up the MFA method field
// Replace removes brackets and quotes from the string (original format: ["Mobile phone SMS"])
| extend Method = replace(@'[\[\]"]', '', tostring(Details["MethodsUsedForValidation"]))
| summarize Count = count() by Method
| render columnchart with (title="SSPR events by method")

Result:

QR code added to the user by an administrator for QR code sign-in

AuditLogs
| where Category == "UserManagement"
| where ActivityDisplayName == "Admin updated security info"
| where ResultDescription == "Admin changed QRcode Pin Authentication Method for user"