Every identity investigation starts with the same table. SigninLogs records every interactive authentication to your Entra ID tenant: who tried to sign in, from where, with what device, whether MFA was required, whether Conditional Access allowed or blocked the attempt, and exactly why it succeeded or failed.
The problem isn't access to the data. The problem is knowing which queries to run and what the results mean. Most SOC analysts learn KQL from documentation examples that demonstrate syntax, not investigation technique. A query that returns a result set isn't the same thing as a query that answers a question.
These 10 queries answer specific investigation questions. They're organized in the order you'd run them during the first 30 minutes of an identity investigation. Each query includes what you're looking for, what a result means, and what to do next.
Query 1: Failed sign-ins for a specific user
The first thing you check when a user reports suspicious activity or you receive an Identity Protection alert. This shows every failed attempt against the account in the last 7 days.
// Investigation: failed sign-ins for a specific user
// What it answers: is someone targeting this account?
SigninLogs
| where TimeGenerated > ago(7d)
| where UserPrincipalName =~ "user@contoso.com"
| where ResultType != "0"
| project TimeGenerated, IPAddress, Location, ResultType, ResultDescription,
AppDisplayName, Browser = tostring(DeviceDetail.browser), UserAgent
| sort by TimeGenerated descResultType != "0" filters to failures only. The value 0 means success in Entra ID sign-in logs. Everything else is a failure, and the ResultType code tells you why.
The codes that matter most: 50126 is wrong password. 50074 is MFA required but not completed. 53003 is blocked by Conditional Access. 50053 is account lockout. If you see a burst of 50126 followed by a single 0 (success), that's a successful password spray. If you see repeated 50074 codes, someone has the password but can't get past MFA.
The equivalent in Splunk SPL:
index=azure sourcetype="azure:signinlogs"
| search UserPrincipalName="user@contoso.com" ResultType!="0"
| table _time, IPAddress, Location, ResultType, ResultDescription,
AppDisplayName, DeviceDetail.browser
| sort - _timeQuery 2: Password spray detection across all users
A password spray hits many accounts with a few passwords each, staying under lockout thresholds per account. A single-user query won't catch it. This query looks across all users for the spray pattern.
// Detection: password spray pattern across the tenant
// What it answers: is someone spraying passwords against multiple accounts?
SigninLogs
| where TimeGenerated > ago(24h)
| where ResultType in ("50126", "50053")
| summarize
FailedAttempts = count(),
TargetedAccounts = dcount(UserPrincipalName),
Accounts = make_set(UserPrincipalName, 10)
by IPAddress, bin(TimeGenerated, 1h)
| where TargetedAccounts > 5 and FailedAttempts > 10
| sort by TargetedAccounts descThe threshold TargetedAccounts > 5 catches sprays hitting more than five accounts from the same IP in an hour. In a production tenant, a legitimate user doesn't fail authentication against five different accounts. Adjust the threshold based on your environment. A tenant with shared kiosks may need a higher value.
If this returns results, the next step is checking whether any of those IPs also produced a successful sign-in (ResultType == "0"). A spray with a subsequent success is a compromised account.
Query 3: Successful sign-ins from new locations
After you've checked for attacks against the account, check for attacks that succeeded. This query finds sign-ins from countries the user hasn't authenticated from in the previous 30 days.
// Investigation: sign-ins from locations the user hasn't used before
// What it answers: did someone authenticate from somewhere unexpected?
let known_locations = SigninLogs
| where TimeGenerated between (ago(30d) .. ago(1d))
| where ResultType == "0"
| where UserPrincipalName =~ "user@contoso.com"
| distinct Location;
SigninLogs
| where TimeGenerated > ago(1d)
| where ResultType == "0"
| where UserPrincipalName =~ "user@contoso.com"
| where Location !in (known_locations)
| project TimeGenerated, IPAddress, Location, AppDisplayName,
DeviceDetail, ConditionalAccessStatusA sign-in from a new country isn't automatically malicious. People travel. But a sign-in from Nigeria at 3 AM when the user is based in London and was active in London two hours ago is a strong indicator. Cross-reference the timing with the user's normal sign-in pattern from Query 1.
Query 4: Impossible travel
Two successful sign-ins from locations that are physically impossible to travel between in the time gap. This is one of the strongest indicators of credential compromise.
// Detection: impossible travel between successful sign-ins
// What it answers: are two sessions active from impossible locations?
SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType == "0"
| where UserPrincipalName =~ "user@contoso.com"
| project TimeGenerated, IPAddress, Location,
Lat = tostring(LocationDetails.geoCoordinates.latitude),
Lon = tostring(LocationDetails.geoCoordinates.longitude)
| sort by TimeGenerated asc
| extend PrevTime = prev(TimeGenerated), PrevLocation = prev(Location),
PrevLat = prev(Lat), PrevLon = prev(Lon)
| where isnotempty(PrevTime)
| extend TimeDiffMinutes = datetime_diff('minute', TimeGenerated, PrevTime)
| where Location != PrevLocation and TimeDiffMinutes < 120
| project TimeGenerated, Location, PrevTime, PrevLocation, TimeDiffMinutesTwo hours (TimeDiffMinutes < 120) between London and New York is physically impossible. The query surfaces these pairs. VPN usage can produce false positives here. If the user connects through a VPN that exits in a different country, the sign-in log records the VPN exit location, not the user's physical location. Check the IPAddress against known corporate VPN ranges before escalating.
Query 5: Sign-ins from risky or anonymous IPs
Tor exit nodes, known malicious infrastructure, and anonymous proxies. Entra ID flags these with the RiskLevelDuringSignIn field when Identity Protection is enabled.
// Investigation: sign-ins flagged as risky by Identity Protection
// What it answers: did the user authenticate through suspicious infrastructure?
SigninLogs
| where TimeGenerated > ago(7d)
| where UserPrincipalName =~ "user@contoso.com"
| where RiskLevelDuringSignIn in ("medium", "high")
| project TimeGenerated, IPAddress, Location, RiskLevelDuringSignIn,
RiskEventTypes_V2, AppDisplayName, ResultType,
ConditionalAccessStatusRiskLevelDuringSignIn is populated by Identity Protection detections: anonymous IP, atypical travel, malware-linked IP, leaked credentials. The RiskEventTypes_V2 field tells you which specific detection fired. A high risk sign-in that also succeeded (ResultType == "0") and wasn't blocked by CA (ConditionalAccessStatus != "failure") is a confirmed gap in your policy coverage.
Query 6: MFA registration changes
An attacker who compromises a password immediately tries to register their own MFA method. This query catches it.
// Detection: MFA method registration — was a new method added?
// What it answers: did someone register an authenticator the user didn't set up?
AuditLogs
| where TimeGenerated > ago(7d)
| where OperationName has "User registered security info"
or OperationName has "User registered all required security info"
| where tostring(InitiatedBy.user.userPrincipalName) =~ "user@contoso.com"
| project TimeGenerated, OperationName,
Method = tostring(TargetResources[0].displayName),
IP = tostring(InitiatedBy.user.ipAddress)This query uses AuditLogs, not SigninLogs, because MFA registration is an administrative action, not an authentication event. If you see a new authenticator app registered from an IP address that doesn't match the user's normal sign-in pattern, that's an attacker establishing persistence. They now have their own MFA method and can authenticate even after you reset the password.
Query 7: Token replay detection
AiTM phishing steals the session token after the user completes legitimate MFA. The attacker replays the stolen token from their own infrastructure. This query finds concurrent sessions from different IPs.
// Detection: concurrent sessions suggesting token theft
// What it answers: is someone using a stolen session token?
SigninLogs
| where TimeGenerated > ago(24h)
| where UserPrincipalName =~ "user@contoso.com"
| where ResultType == "0"
| summarize IPs = make_set(IPAddress),
Locations = make_set(Location),
IPCount = dcount(IPAddress)
by bin(TimeGenerated, 15m)
| where IPCount > 1
| project TimeGenerated, IPCount, IPs, LocationsIf a user is authenticated from two different IPs within the same 15-minute window, and those IPs are in different locations, someone is replaying a token. Legitimate scenarios exist (user switches from office Wi-Fi to mobile data), but different countries within 15 minutes is conclusive.
The Sigma rule equivalent:
title: Concurrent Sign-in Sessions from Multiple IPs
status: experimental
logsource:
product: azure
service: signinlogs
detection:
selection:
ResultType: "0"
timeframe: 15m
condition: selection | count(IPAddress) by UserPrincipalName > 1
level: mediumQuery 8: Conditional Access bypass
A successful sign-in where CA policies didn't apply or were bypassed. This catches scenarios where the attacker's session has a token from before a CA policy existed, or where the policy has an exclusion the attacker is exploiting.
// Investigation: successful sign-ins that bypassed Conditional Access
// What it answers: did authentication succeed without policy enforcement?
SigninLogs
| where TimeGenerated > ago(7d)
| where UserPrincipalName =~ "user@contoso.com"
| where ResultType == "0"
| where ConditionalAccessStatus == "notApplied"
or ConditionalAccessStatus == ""
| project TimeGenerated, IPAddress, Location, AppDisplayName,
DeviceDetail, ConditionalAccessStatus,
ConditionalAccessPoliciesConditionalAccessStatus == "notApplied" means no CA policy evaluated for this sign-in. If your tenant has CA policies and a successful sign-in bypassed all of them, either the user is excluded from all policies (check your exclusion groups) or the application isn't covered by any policy. Both are gaps.
Query 9: OAuth application consent grants
An attacker with a compromised session grants consent to a malicious application, giving it persistent access to the user's mailbox, files, or directory. This survives password resets.
// Detection: OAuth consent grants — did someone authorize an app?
// What it answers: was a malicious app granted access to this user's data?
AuditLogs
| where TimeGenerated > ago(7d)
| where OperationName == "Consent to application"
| extend AppName = tostring(TargetResources[0].displayName)
| extend ConsentUser = tostring(InitiatedBy.user.userPrincipalName)
| extend Permissions = tostring(TargetResources[0].modifiedProperties)
| where ConsentUser =~ "user@contoso.com"
| project TimeGenerated, AppName, ConsentUser, PermissionsCheck the Permissions field. Mail.Read, Files.ReadWrite, and User.ReadAll are the permissions attackers request most. A legitimate SaaS application typically requests a narrow set of permissions during onboarding. An attacker requests broad read access to mail, files, and directory. If you see a consent grant from the same timeframe as a suspicious sign-in, that application is part of the attack chain. Revoking the consent is a containment step, not just cleanup.
Query 10: Service principal sign-ins
Service principals don't use MFA. They authenticate with certificates or secrets. An attacker who compromises a service principal credential has persistent, MFA-free access to whatever that service principal can reach.
// Investigation: service principal authentication activity
// What it answers: are any service principals authenticating unexpectedly?
AADServicePrincipalSignInLogs
| where TimeGenerated > ago(7d)
| summarize SignInCount = count(),
IPs = make_set(IPAddress, 5),
Apps = make_set(ServicePrincipalName, 5)
by ServicePrincipalId, bin(TimeGenerated, 1d)
| sort by SignInCount descThis query uses AADServicePrincipalSignInLogs, not SigninLogs. Service principal sign-ins don't appear in the interactive sign-in logs. If you're only querying SigninLogs, you're blind to service principal abuse. A service principal signing in from a new IP or at unusual hours is the same indicator as a user doing the same thing. The difference is that service principals don't get MFA challenges, so there's one less barrier for the attacker.
What to do this week
- Run Query 2 (password spray) right now against your tenant for the last 24 hours. If you get results, check whether any of those IPs also produced a successful sign-in.
- Run Query 8 (CA bypass) for your entire tenant, not just one user. Remove the
UserPrincipalNamefilter and look for any successful sign-in where CA didn't apply. Each result is a gap in your policy coverage.
- Run Query 10 (service principal sign-ins) and compare the IPs against your known infrastructure. A service principal authenticating from an IP you don't recognize is a credential compromise.
- Save all 10 queries as a Sentinel hunting workbook. The next time an alert fires, you'll have the investigation sequence ready instead of writing queries from scratch.
- Check whether
AADServicePrincipalSignInLogsis flowing into your workspace. Many tenants haveSigninLogsconfigured but haven't enabled the service principal and non-interactive log tables. Go to Entra ID > Diagnostic settings and verify all four sign-in log categories are sending to your Log Analytics workspace.