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

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

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
  | extend authenticationStepRequirement = tostring(parse_json(AuthenticationDetails)[0].authenticationStepRequirement)
  | where AuthenticationRequirement != "multiFactorAuthentication"
  // Remove all signins 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, authenticationStepRequirement, 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 and make chart

SigninLogs
| where AuthenticationRequirement == "multiFactorAuthentication"
| 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")

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 conditional access policies changes

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

List sign-in using device-code

SigninLogs
| where  AuthenticationProtocol == "deviceCode"

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 Users, sign-In ount and last sign-In date for a specific app in Entra ID

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

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