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 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"
Comments