Hunt Identity Attacks in Sign-in Logs with KQL

Why this lab matters

The Entra admin centre is fine for looking up one sign-in. It is not built for questions such as "which IP address failed against forty users in an hour?". Kusto Query Language (KQL) in Log Analytics answers that in seconds, and the same query can become an alert in Microsoft Sentinel or Azure Monitor.

What you need

• A test tenant with Microsoft Entra ID P1 or P2, which is required to export sign-in logs.
• An Azure subscription and a Log Analytics workspace.
• The Security Administrator role in Entra, and Log Analytics Contributor on the workspace.

Step 1: Export the logs

1. In the Entra admin centre go to Identity, Monitoring and health, Diagnostic settings, Add diagnostic setting.
2. Tick SignInLogs, NonInteractiveUserSignInLogs and AuditLogs.
3. Choose Send to Log Analytics workspace, pick your workspace and save.
4. Generate some data: sign in successfully a few times, and enter a wrong password for two or three test users.

Step 2: Build a baseline

Open the workspace, select Logs and run:

SigninLogs
| where TimeGenerated > ago(24h)
| summarize Attempts = count(), Users = dcount(UserPrincipalName) by ResultType, ResultDescription
| order by Attempts desc

Learn the common codes in your tenant. The ones you will meet most:

ResultType	Meaning
0	Success
50126	Invalid username or password
50053	Account locked by smart lockout
50074	Strong authentication required
500121	MFA failed or was denied
53003	Blocked by Conditional Access

Step 3: Hunt for four patterns

Password spray

SigninLogs
| where TimeGenerated > ago(24h)
| where ResultType in ("50126", "50053")
| summarize Failures = count(), TargetUsers = dcount(UserPrincipalName),
            Sample = make_set(UserPrincipalName, 5)
    by IPAddress, bin(TimeGenerated, 1h)
| where TargetUsers >= 10
| order by TargetUsers desc

One address failing against many accounts is the signature. Lower the threshold to 3 in a small test tenant so you can see your own test data.

Legacy authentication

union SigninLogs, AADNonInteractiveUserSignInLogs
| where TimeGenerated > ago(7d)
| where ClientAppUsed !in ("Browser", "Mobile Apps and Desktop clients", "")
| summarize Attempts = count(), Successes = countif(ResultType == "0")
    by UserPrincipalName, ClientAppUsed
| order by Successes desc

Repeated MFA denials

SigninLogs
| where TimeGenerated > ago(24h)
| where ResultType == "500121"
| summarize Denials = count(), IPs = make_set(IPAddress, 5)
    by UserPrincipalName, bin(TimeGenerated, 30m)
| where Denials >= 5

Many denied prompts in a short window can mean an attacker has the password and is hoping the user approves by mistake.

First sign-in from a new country

let known = SigninLogs
    | where TimeGenerated between (ago(30d) .. ago(1d))
    | where ResultType == "0"
    | distinct UserPrincipalName, Location;
SigninLogs
| where TimeGenerated > ago(1d)
| where ResultType == "0" and isnotempty(Location)
| join kind = leftanti known on UserPrincipalName, Location
| project TimeGenerated, UserPrincipalName, Location, IPAddress, AppDisplayName

Step 4: Tune and schedule

1. Pick the spray query. Add a line to exclude addresses you trust, for example your office or VPN egress: | where IPAddress !in ("203.0.113.10").
2. Select New alert rule. Measure table rows, threshold greater than 0, evaluate every hour over the last hour.
3. Attach an action group that emails your test mailbox.
4. Trigger it with wrong passwords against several test users and confirm the alert arrives.

Evidence to keep

• A screenshot of the diagnostic setting.
• The saved queries, exported or stored in a query pack.
• The alert rule and one fired alert.

Clean up

Delete the alert rule and action group. Remove the diagnostic setting if the workspace was created only for this lab, because stored logs are billed by volume.