4.2 Key Fields in the Sign-In Log

Key Fields in the Sign-In Log

The sign-in log tables contain over 50 columns each. You do not need all of them. In practice, eight fields answer 80% of your investigation questions. Learn these eight cold, and you can triage any sign-in event in under a minute.

Figure 4.2: The six fields shown above, plus MfaDetail and DeviceDetail, cover 80% of your investigation needs.

ResultType — Success or failure

ResultType is a numeric code. Zero means success. Everything else is a failure with a specific reason.

The full error code reference is at learn.microsoft.com/en-us/entra/identity-platform/reference-error-codes, but these five account for the vast majority of what you will encounter.

IPAddress and LocationDetails

The IP address and its geolocation tell you where the sign-in came from. Compare against the user’s normal patterns.

1
2
3
4
5
6
7
8

SigninLogs
| where TimeGenerated > ago(30d)
| where UserPrincipalName =~ "j.morrison@northgateeng.com"
| where ResultType == 0
| extend Country = tostring(LocationDetails.countryOrRegion)
| extend City = tostring(LocationDetails.city)
| summarize SignInCount = count(), LastSeen = max(TimeGenerated) by IPAddress, Country, City
| sort by SignInCount desc

This builds a baseline: which IPs and locations does this user normally sign in from? Any sign-in from an IP not in this list is worth investigating.

ClientAppUsed — The legacy authentication signal

This field identifies the authentication protocol. Modern authentication (browser, mobile app, desktop app) supports MFA. Legacy protocols do not.

ConditionalAccessStatus and ConditionalAccessPolicies

ConditionalAccessStatus gives you the overall verdict: success (allowed), failure (blocked), or notApplied (no policies evaluated). The ConditionalAccessPolicies field is a JSON array containing every policy that evaluated, with its individual result.

Parsing the policies array to find the specific blocking policy:

1
2
3
4
5
6
7
8
9

SigninLogs
| where TimeGenerated > ago(1d)
| where ConditionalAccessStatus == "failure"
| mv-expand CAPolicy = parse_json(ConditionalAccessPolicies)
| where tostring(CAPolicy.result) == "failure"
| extend PolicyName = tostring(CAPolicy.displayName)
| project TimeGenerated, UserPrincipalName, IPAddress, PolicyName
| summarize BlockCount = count() by PolicyName
| sort by BlockCount desc

This shows which conditional access policies are doing the most blocking — useful for understanding your security posture and identifying policies that may need tuning.

MfaDetail

Shows whether MFA was satisfied and which method was used (phone call, authenticator app push, FIDO2 key, OTP). Critical for AiTM investigation: if MFA was satisfied but the user says they were not involved, the token was likely captured after MFA completion on a proxy page.

DeviceDetail

Contains the device name, operating system, browser, compliance status, and whether the device is Intune-managed. An unregistered device from an unfamiliar IP is a stronger compromise indicator than an unregistered device from a known corporate network.