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

This page is lengthy; use the button in the bottom-right corner to open the table of contents and navigate more easily.

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-ins in report-only mode for conditional access policies

SigninLogs
| where ConditionalAccessStatus == "reportOnly"
| project TimeGenerated, UserPrincipalName, AppDisplayName, ConditionalAccessPolicies
| order by TimeGenerated desc

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

List failed sign-ins with their reasons

SigninLogs
| where ResultType != 0
| extend Heure = format_datetime(TimeGenerated, 'HH:mm')
| summarize Count=count() by UserPrincipalName, Heure, ResultDescription, ResultType
| sort by Count desc nulls last

List of successful and failed sign-ins by location

SigninLogs
| summarize Successful=countif(ResultType==0), Failed=countif(ResultType!=0) by Location

List of failed MFA challenge

SigninLogs
| where ResultType == 50074
| project UserDisplayName, Identity,UserPrincipalName, ResultDescription,  AppDisplayName, AppId, ResourceDisplayName
| summarize FailureCount=count(), FailedResources=dcount(ResourceDisplayName), ResultDescription=any(ResultDescription) by UserDisplayName

Pivot table of conditional access policy outcomes over the last 30 days

Credits to https://learnsentinel.blog/2022/05/09/azure-ad-conditional-access-insights-auditing-with-microsoft-sentinel/

SigninLogs
| where TimeGenerated > ago(30d)
| extend CAPolicies = parse_json(ConditionalAccessPolicies)
| mv-expand bagexpansion=array CAPolicies
| evaluate bag_unpack(CAPolicies)
| extend
    PolicyOutcome = tostring(column_ifexists('result', "")),
    PolicyName = column_ifexists('displayName', "")
| evaluate pivot(PolicyOutcome, count(), PolicyName)

Conditional access policies without success, failure, or unknown outcomes in the last 30 days

Credits to https://learnsentinel.blog/2022/05/09/azure-ad-conditional-access-insights-auditing-with-microsoft-sentinel/

SigninLogs
| where TimeGenerated > ago(30d)
| project TimeGenerated, ConditionalAccessPolicies
| mv-expand ConditionalAccessPolicies
| extend PolicyResult = tostring(ConditionalAccessPolicies.result)
| extend PolicyName = tostring(ConditionalAccessPolicies.displayName)
| summarize PolicyResultsSet = make_set(PolicyResult) by PolicyName
| where PolicyResultsSet !has "success"
    and PolicyResultsSet !has "failure"
    and PolicyResultsSet !has "unknownFutureValue"
| sort by PolicyName asc

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 "xxx"
| 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 who has created an object (user, group, device)

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

AuditLogs
| where TimeGenerated >= ago(1000d)
| where OperationName in ("Add group", "Add user", "Add device")
| where TargetResources has "xxx"
| extend ObjectDisplayName = tostring(TargetResources[0].displayName)
| extend ObjectID = tostring(TargetResources[0].id)
| extend CreatedBy = tostring(InitiatedBy.user.userPrincipalName)
| project TimeGenerated, OperationName, ObjectDisplayName, ObjectID, CreatedBy

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

List membership changes for a specific group

let groupName = "xxx"
| where OperationName in ("Add member to group", "Remove member from group")
| extend GroupName = case(
    OperationName == "Add member to group",
        tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[1].newValue))),
    OperationName == "Remove member from group",
        tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[1].oldValue))),
    ""
)
| where GroupName == groupName
| extend InitiatedByUser = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| extend Member = tostring(TargetResources[0].userPrincipalName)
| project TimeGenerated, OperationName, GroupName, InitiatedByUser, Member
| order by TimeGenerated desc

List membership changes for dynamic Entra ID groups

AuditLogs
| where Category == "GroupManagement"
| where OperationName in ("Add member to group", "Remove member from group")
| where parse_json(tostring(InitiatedBy.app)).displayName == "Microsoft Approval Management"
| extend GroupName = case(
    OperationName == "Add member to group",
        tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[1].newValue))),
    OperationName == "Remove member from group",
        tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[1].oldValue))),
    ""
)
| extend InitiatedByUser = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| extend MemberUser = tostring(TargetResources[0].userPrincipalName)
| project TimeGenerated, OperationName, GroupName, InitiatedByUser, MemberUser
| order by TimeGenerated desc

List of

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"

Use insights and reporting for conditional access policies

You can also use Insights and Reporting in Microsoft Entra ID > Conditional Access (https://entra.microsoft.com/#view/Microsoft_AAD_ConditionalAccess/ConditionalAccessBlade/~/InsightsAndReporting/menuId/Policies/fromNav/) to get useful information about conditional access. This relies on Log Analytics data, so you must already be ingesting Entra ID data into Log Analytics (also a prerequisite for the KQL queries shown earlier).

One interesting point is that you can access the underlying KQL query by clicking the button highlighted in orange in the screenshot above. This gives: