A Repeatable Threat-Hunting Exercise for Small Security Teams

Several failed sign-ins, then a success, then a role grant: that sequence is worth a closer look. It could also be an administrator mistyping a password before doing legitimate work.

I want a hunt like this to return a question an analyst can investigate. Before adapting it to an identity platform, I also want to see exactly what it matches when timestamps, duplicates, and tenant IDs change.

The example below uses invented events in SQLite. You can run the whole exercise locally with Python's standard library.

Define the sequence

For the same tenant, actor, and source IP, the query looks for at least three distinct failed sign-ins within the ten minutes before a success. It then looks for a role grant by the same actor in that tenant, from the moment of the success through five minutes afterward.

The role-grant actor is the person who made the change. The recipient of the new role is a different field in a real audit record. Joining a sign-in to the recipient by mistake would produce a plausible account of the wrong person's activity.

The fixture uses six fields:

Microsoft records identity activity in separate sign-in and audit logs. Adapting the exercise means mapping those sources into the normalized fields above, including their operation meanings and success codes. The SQLite table is deliberately small enough to inspect without that adapter.

Run the query

Download identity_hunt.py and run:

python3 identity_hunt.py

The script creates an in-memory database, runs eight cases individually, then runs the combined records. It prints JSON and fails an assertion if a result differs from the expected count. It needs no account or network connection, and the database disappears when the process exits.

This is the query:

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;

The first CTE removes identical redeliveries. The second joins failures to each successful sign-in and applies the threshold. The final join looks for the role grant without dropping the tenant boundary.

SELECT DISTINCT only removes rows whose selected fields agree. If two payloads use the same event ID but disagree on their contents, normalization needs a conflict rule. Quietly treating them as separate events could raise the failure count and create a match that the source never supported.

Look at the near misses

The saved September 9, 2026 run, using Python 3.13.12 and SQLite 3.50.4 on macOS arm64, passed all eight cases. The combined 35-row fixture returned two matches.

The missing-success case passes its assertion and still represents a coverage problem. The query behaves as written, but the event needed to connect the failures and grant isn't available. A separate source-health check is needed to distinguish that gap from a day without matching activity.

The 300- and 301-second cases are less dramatic but useful when maintaining the query. They make the inclusive endpoint explicit. If someone changes the timing rule later, the resulting difference appears in a small case they can read.

Review the match before choosing a response

For a matched sequence, find the role that was granted and its recipient. Check whether an approved change explains it, then inspect the actor's session, device, and other activity around the event.

The fixture has none of that context. Its output is a lead for review, so I wouldn't wire it directly to account disablement. A legitimate administrator can produce the same timing, and disrupting that account may interrupt the work needed to respond to an incident.

Collect those legitimate sequences as additional cases. Password resets, stale clients, and normal administrative work can help explain noisy matches. Avoid suppressing the account wholesale just because one match was expected; a narrower exception is easier to revisit.

Timing in a real pipeline

The same-IP condition misses a sequence that moves between networks. The short windows miss slower activity, and a stolen session may produce no failures at all. Workload identities may require different sources and a different hypothesis.

Arrival time creates another problem. The SQL compares source timestamps, but a scheduled rule can only query records that have arrived. A late role-grant event may miss one run, while overlapping lookbacks can return the same sequence several times.

My next adaptation would retain both source time and arrival time, replay delayed records, and measure missed and duplicate alerts under the intended schedule. Keep the existing fixtures with that work. They provide a quick way to see whether a fix for delayed ingestion accidentally changes tenant isolation or the failure threshold.