A Repeatable Threat-Hunting Exercise for Small Security Teams

A useful hunt starts with a claim you can disprove. For this exercise, the claim is narrow: an account that succeeds after several failed sign-ins and then grants a role deserves review. The sequence may reflect misuse, but it may also reflect an administrator mistyping a password before doing legitimate work. The query should identify a sequence for analysis without assigning intent.

The exercise runs entirely on your machine. It generates invented identity activity, queries an in-memory SQLite database, and checks expected results. It does not perform sign-ins, alter roles, contact Microsoft, or simulate an attack against an application. That makes it suitable for reviewing correlation logic before arranging an authorized identity-platform lab.

Define the hypothesis precisely

For a single tenant, actor ID, and source IP, find at least three distinct failure records within the ten minutes preceding a successful sign-in. Then find a role-grant record attributed to the same actor in the same tenant within five minutes after that success, including the endpoint.

Here, user_id on a role-grant event means the actor who made the change, not the recipient of the new role. That distinction matters when adapting the query. Joining a sign-in to the wrong field can tell a convincing story about the wrong person.

The input fields are event_id, tenant, user_id, ip, time_s, and kind. Timestamps are integer seconds on a synthetic timeline, domains are unnecessary, and the IP address comes from the documentation range 192.0.2.0/24. Event kinds are normalized labels: failure, success, and role_grant.

Microsoft's monitoring overview describes separate sign-in and audit logs. Our single table is a teaching schema, not an export format or a drop-in Sentinel rule. A production adapter must establish the source fields, operation meanings, success codes, ingestion behavior, and stable identifiers first.

Run the experiment

Save identity_hunt.py in a working directory and run:

python3 identity_hunt.py

Python's standard library is sufficient. The script builds its records, runs eight independent cases, runs the combined dataset, and prints JSON. Assertions fail if expected results change. It opens no network connection and leaves no database behind.

This is the query used by the script:

WITH unique_events AS (
  SELECT DISTINCT event_id, tenant, user_id, ip, time_s, kind
  FROM events
), candidates AS (
  SELECT s.event_id AS sign_in_id, s.tenant, s.user_id, s.time_s,
         COUNT(f.event_id) AS failures
  FROM unique_events s JOIN unique_events f
    ON f.tenant = s.tenant AND f.user_id = s.user_id AND f.ip = s.ip
   AND f.kind = 'failure'
   AND f.time_s >= s.time_s - 600 AND f.time_s < s.time_s
  WHERE s.kind = 'success'
  GROUP BY s.event_id, s.tenant, s.user_id, s.time_s
  HAVING COUNT(f.event_id) >= 3
)
SELECT DISTINCT c.tenant, c.user_id, c.sign_in_id,
                p.event_id AS grant_id
FROM candidates c JOIN unique_events p
  ON p.tenant = c.tenant AND p.user_id = c.user_id
 AND p.kind = 'role_grant'
 AND p.time_s >= c.time_s AND p.time_s <= c.time_s + 300
ORDER BY c.tenant, c.user_id, c.sign_in_id, p.event_id;

Deduplication here removes identical redeliveries. It doesn't reconcile conflicting payloads carrying the same event ID. A real normalization layer should quarantine that conflict or apply a documented source-version rule; quietly counting both would undermine the threshold.

Expected cases and observed results

The September 9, 2026 run used Python 3.13.12 and SQLite 3.50.4 on macOS arm64. All eight case assertions passed, and the combined 35-row fixture produced two matches. The saved JSON preserves the outputs.

The last case is not successful detection. It demonstrates a blind spot. The script passes because the test expects that blind spot; an operational report must still call it a missed sequence when the underlying action is known to have happened.

Turn a match into an analyst question

For the positive case, inspect whether the role grant was expected, which role was granted, who received it, whether an approved change existed, and whether the actor's session and device context fit that work. The fixture intentionally omits those answers. Automatically disabling the account from this query would attach a strong response to a weak conclusion.

Before promoting it into an alert, collect legitimate examples. Password resets, stale clients, and administrative work can produce similar timing. Adjust the hypothesis using observed operational context, and preserve those cases in the test suite. Avoid suppressing an entire privileged account just because its first alert was benign.

Know what the exercise misses

The same-IP condition excludes activity that changes networks. The short window misses slower sequences. A stolen session might have no failed sign-ins at all. Workload identities may require different sources and fields. Delayed events can fall outside a scheduled lookback even when their source timestamps fit the query. Tenant-specific identifiers still need correct normalization before any of this logic runs.

A useful next iteration is to replay the same fixtures with delayed ingestion and overlapping scheduled windows, then check both missing and duplicated alerts. Keep source time separate from arrival time. The goal is to understand where the detector loses information before placing it in someone's incident queue.

Cleanup is automatic for the in-memory database. If you saved the script or redirected its JSON output, those ordinary local files are the only artifacts to remove. Keep the expected cases with the query when you adapt it; they make later threshold and schema changes reviewable.