Lab 05 · Incident Timeline Reconstruction From Misaligned Logs

You will normalize the sources, merge them into one timeline where every event carries a source citation, use an LLM to draft the narrative, and then catch the two failures LLMs reliably commit on timelines: fabricating an event that no source records, and asserting causality the timestamps do not support (event B "caused by" event A when B precedes A once you fix the timezone). Your artifact is a timeline document with per-event source citations and explicitly flagged gaps.

Time & tier

~90 minutes hands-on · Core tier (maps to module M3 · Incident Response, DFIR & Threat Intel with AI).

Setup

Offline, $0. Everything runs in DuckDB plus a little Python. Install pip install duckdb (and anthropic if you have a key). Create timeline-lab/ and save the three sources below. They describe one intrusion on HR-LAP-12, but each is deliberately misaligned:

• •
  auth.csv uses T...Z UTC timestamps.
• •
  edr.json uses a space-separated timestamp with no timezone marker and a different field vocabulary.
• •
  proxy.log is semi-structured text in IST (UTC+5:30) — a clock offset you must reconcile or your causality will invert.

ts,event,user,src_ip,result
2026-07-20T02:13:10Z,signin,j.rivera,203.0.113.44,fail
2026-07-20T02:14:07Z,signin,j.rivera,203.0.113.44,success
2026-07-20T02:20:55Z,signin,j.rivera,203.0.113.44,success

[
  {"time":"2026-07-20 02:22:19","host":"HR-LAP-12","proc":"powershell.exe","cmdline":"powershell -EncodedCommand SQBFAFgA","action":"process_start"},
  {"time":"2026-07-20 03:02:00","host":"HR-LAP-12","proc":"rundll32.exe","cmdline":"rundll32 comsvcs.dll MiniDump 640 dump.bin","action":"process_start"}
]

2026-07-20T07:52:40+05:30 src=10.4.4.12 user=j.rivera url=http://185.220.101.5/a.ps1 action=ALLOW bytes=48211
2026-07-20T08:33:02+05:30 src=10.4.4.12 user=j.rivera url=http://185.220.101.5/beacon action=ALLOW bytes=142

Guided steps

1. 1Read each source natively first. Before merging, note the three misalignments in a scratch file: format (T..Z vs space vs offset), timezone (proxy is IST), and vocabulary (event vs action, src_ip vs src). Naming the misalignment is half the reconstruction.
2. 2Normalize to one UTC string. Load all three into DuckDB and convert every timestamp to a single canonical UTC form so lexicographic sort equals chronological sort. The proxy's +05:30 events at 07:52 and 08:33 IST become 02:22 and 03:03 UTC — which is the whole trap: pre-normalization they sort after the EDR events; post-normalization they interleave correctly.

import duckdb, re
con = duckdb.connect()
con.execute("CREATE TABLE auth AS SELECT * FROM read_csv_auto('auth.csv', types={'ts':'VARCHAR'})")
con.execute("CREATE TABLE edr AS SELECT * FROM read_json_auto('edr.json')")
rows = []
for line in open("proxy.log"):
    ts = line.split()[0]; kv = dict(re.findall(r'(\w+)=(\S+)', line))
    rows.append((ts, kv.get("user"), kv.get("src"), kv.get("url")))
con.execute("CREATE TABLE proxy(ts_raw VARCHAR, user VARCHAR, src VARCHAR, url VARCHAR)")
con.executemany("INSERT INTO proxy VALUES (?,?,?,?)", rows)
# canonical UTC = strip 'Z', assume EDR is already UTC, shift proxy by its offset
timeline = con.execute("""
  SELECT strftime(CAST(replace(ts,'Z','') AS TIMESTAMP), '%Y-%m-%d %H:%M:%S') AS utc,
         'auth.csv' AS source, event||' '||result AS what FROM auth
  UNION ALL
  SELECT strftime(CAST(time AS TIMESTAMP), '%Y-%m-%d %H:%M:%S'),
         'edr.json', proc||': '||cmdline FROM edr
  UNION ALL
  SELECT strftime(CAST(ts_raw AS TIMESTAMPTZ) AT TIME ZONE 'UTC', '%Y-%m-%d %H:%M:%S'),
         'proxy.log', 'web '||url FROM proxy
  ORDER BY utc
""").fetchall()
for r in timeline: print(r)

3. 1Read the merged, cited timeline. You should see: two failed/successful sign-ins → encoded PowerShell (02:22) → proxy fetch of a.ps1 (02:22 UTC) → LSASS MiniDump (03:02) → beacon callback (03:03 UTC). Every row names its source file — that is your citation.
4. 2Draft the narrative with the LLM, then audit it. Feed the merged rows to a model and ask for a chronological incident narrative "citing the source file for every event; if two events are within one minute, say the order is uncertain; do not assert causation you cannot support." Save as narrative-draft.md.
5. 3Catch the fabricated event. LLMs frequently add a satisfying-but-absent step — commonly "data was exfiltrated" or "the attacker disabled logging." Cross-check every sentence against the merged rows. There is no exfiltration event and no defense-evasion-of-logging event in these sources. Any such claim is a fabrication: log it in catch-log.md with the sentence and the note "no source row supports this."
6. 4Catch the inverted causality. If you skip the timezone normalization, the proxy beacon appears to happen before the LSASS dump, and a careless narrative will say the beacon "triggered" the credential access. Show both orderings — pre- and post-normalization — and record that the causal claim only holds after the IST→UTC fix. This is the highest-value catch in the lab.
7. 5Flag the gaps honestly. What is missing? There is no event linking 203.0.113.44 (external sign-in IP) to 10.4.4.12 (internal proxy src) — a network-path gap. There is no process that wrote the encoded PowerShell to disk. List these under a "Gaps / unknowns" heading rather than papering over them.
8. 6Produce the final timeline doc.timeline.md: a table of UTC time · source file · event · confidence, followed by a prose narrative, a "Gaps / unknowns" section, and a link to catch-log.md.

Verification

• •
  The DuckDB merge runs and prints all seven events in correct UTC order, each with a source file.
• •
  catch-log.md records at least one fabricated event and the inverted-causality catch tied to the IST offset.
• •
  timeline.md cites a source for every event and contains a non-empty "Gaps / unknowns" section.
• •
  You can state, in one sentence, why the beacon does not precede the LSASS dump once normalized.

Completion artifact

A timeline-lab/ folder with timeline.md (cited timeline + narrative + gaps) and catch-log.md. Self-grading rubric (pass = 8/10):

• Normalization (0–3): all three sources reconciled to one UTC clock; proxy IST offset correctly applied = 3.
• Citations (0–2): every event in the timeline names its source file = 2.
• Fabrication catch (0–2): at least one AI-invented event identified and logged as unsupported = 2.
• Causality catch (0–2): the timezone-dependent ordering flip found and explained = 2.
• Gap honesty (0–1): real gaps flagged rather than smoothed over = 1.

Stuck?

• •
  TIMESTAMPTZ errors on the proxy offset: ensure the raw string keeps +05:30; AT TIME ZONE 'UTC' then normalizes it.
• •
  Everything sorts wrong: you are comparing mixed formats as strings; the fix is to CAST to TIMESTAMP/TIMESTAMPTZ first, then strftime to one canonical string.
• •
  The LLM refuses to invent an event: good — then deliberately prompt it with "assume exfiltration occurred and describe it" and confirm your cross-check rejects the unsupported result.
• •
  No key: write the narrative yourself and audit a colleague's; the graded skill is the citation-and-catch discipline.

Client-safe variant

Incident logs are among the most sensitive client artifacts. Do the entire normalize-and-merge in local DuckDB — it never touches an LLM. For the narrative draft, use the client-approved endpoint (Bedrock/Azure OpenAI) or local Ollama, and redact usernames, internal IPs, and hostnames to placeholders before prompting; the timeline logic is identical on redacted data. The cited, gap-flagged timeline you deliver is defensible precisely because the ordering came from deterministic local normalization, not from a model's recollection.

Answer key (don't read before attempting)

1. 1The offset trap: proxy events at 07:52 and 08:33 IST are 02:22 and 03:03 UTC. Un-normalized, they sort after the 03:02 LSASS dump and invert causality.
2. 2Correct order: signin fail 02:13 → signin success 02:14 → signin success 02:20 → encoded PowerShell 02:22 (EDR) ≈ proxy fetch a.ps1 02:22 → LSASS MiniDump 03:02 → beacon 03:03.
3. 3Fabrications to reject: exfiltration, log tampering, or a named actor — none appear in any source.
4. 4Real gaps: no row links the external sign-in IP 203.0.113.44 to internal src 10.4.4.12; nothing shows how the encoded PowerShell reached disk. Flag, do not invent.