Scenario 04 — The Living Investigation

Scenario

Thirty days ago, Phantom Feed noticed automated activity against the public profile endpoint /api/v1/profiles/user_<N>. Over the course of the month, the security team deployed a series of defenses. The attacker adapted. The team deployed again. The attacker adapted again. By day 30, the attack is still going but at a fraction of its peak.

Now the post-incident review is on your desk. You have 30 days of access logs covering everything: legitimate user traffic, the attacker at every phase, and every defensive response. Your job is to reconstruct the timeline from the data alone.

Your goal

Produce a phase-by-phase reconstruction of the campaign that answers:

• What did the attacker do first? When was the first defense deployed and what did it do?
• How did the attacker bypass each defense? How long did each bypass take to deploy?
• Which defenses were most disruptive (longest gap between defense and bypass)?
• Which defenses were least disruptive (attacker had a workaround almost immediately)?
• If Phantom Feed had to deploy one defense on day 1, which one would have been most effective long-term?

You should be able to name and describe every distinct phase, with the SQL that proves each transition.

What you may assume

• The target endpoint is /api/v1/profiles/user_<N>.
• Background traffic is real users browsing the platform normally for the full 30 days.
• The attacker is a single coordinated actor who adapts in response to defensive deployments.
• The dataset includes a phaseTag column. Don't filter on it until you are checking your work — solve the investigation from the substantive columns first.
• The base date is 2026-04-01. Day 0 in queries below means 2026-04-01.

The dataset

You are working with a single table named access_logs, ~127,000 rows over 30 days. Click the Schema tab in the sidebar to see all columns with column-level hints. Most relevant columns:

• timestamp — for daily bucketing with julianday()
• uri — the request path; filter to /api/v1/profiles/% for the target endpoint
• ja4, userAgent — for detecting tool switches
• requestCookieNames — for detecting cookie-presence defense and the bypass
• responseCode — for spotting when each defense fires (429 → 403 → 401)
• userID — '0' for unauthenticated; user_900000+ is the attacker's first batch of fake accounts; user_950000+ is the post-captcha batch

Tips

• Bucket by day first. Bucket by day for every interesting metric. The timeline is the answer.
• Reading the daily response-code distribution is the single highest-leverage query.
• The Sample queries tab gives you starter queries that walk the reconstruction.
• Once you have the timeline, write it up as a narrative. "On day X, defense Y was deployed. By day Z, the attacker had pivoted to Z'." That format is exactly what post-incident reviews look like.

Open the console for this scenario