KQL query examples for Microsoft Entra ID

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