Join two sources and discover your key does not match

short · 55 min · Objective 4.1

Task

Write a correlation across two log sources, find the rows that fail to join, and fix the normalisation rather than the rule.

Steps

  1. Write a correlation joining two sources on an identity and record the match rate in lab/join-rate.md as matched over attempted.
  2. Extract the UNMATCHED values from each side into lab/join-misses.csv as source,raw_value,canonical_value and work out why they failed -- domain prefix, case, qualified versus unqualified, a service account shared by many actors.
  3. Normalise to a canonical form at ingest, re-run, and record the new match rate.
  4. Repeat for a host join, and record what you used as the durable identifier rather than a hostname or an address.
  5. Write lab/join-note.md on why an address-based join needs a time-bounded lookup rather than a static mapping.

Verify

grep -Ec '[0-9]+' lab/join-rate.md
awk -F, 'NR>1 {n++} END {print n" unmatched value(s) analysed"}' lab/join-misses.csv
grep -Eci 'canonical|normalis|normaliz' lab/join-misses.csv lab/join-note.md 2>/dev/null
grep -Eci 'lease|time-bounded|nat|dynamic' lab/join-note.md

Match rates recorded BEFORE and AFTER, with the second higher, and the unmatched values analysed. Correlation capability is determined at ingest: a rule that cannot join is discovering a decision made when the sources were onboarded.

This is an independent study companion for CompTIA SecurityX CAS-005 and is not produced by or endorsed by CompTIA.