02 / AUTHENTICATION AND ACCESS · LESSON 07

Add account context

Join successful logins with an account export while preserving unknown identities.

16 min · learn at your pace

A join should not erase uncertainty

A login event tells you what an authentication service recorded. An account inventory supplies a different kind of context: whether an account is active or disabled and which team owns it. These sources can disagree because collection times differ or inventories are incomplete.

Build a lookup keyed by the exact account identifier. Emit successful logins for disabled accounts and separately label identities that are absent from the inventory. Do not silently treat an unknown account as active or call it malicious. Missing context is a limitation worth investigating.

Keep source-specific meaning

The supplied account table has one row per user and a documented state. That uniqueness contract matters: when duplicate keys are possible, define a policy before picking the last row encountered. The later capstone requires unique reference keys and fails clearly when its configuration is ambiguous.

An empty lookup table must still leave every success visible as unknown. Use filename-based input selection rather than relying on both record counters being equal. Keep the event ID in the report so a reviewer can inspect the source event, and retain the original owner text, including spaces and Unicode. Never execute owner names as shell code.

Try an example.sh
awk -F '\t' 'NR>1 {printf "%s: %s\n",$1,$2}' accounts.tsv
YOUR TURN

Put it into practice.

Join auth.tsv (id<TAB>user<TAB>ip<TAB>result) with unique accounts.tsv rows (user<TAB>state<TAB>owner; state active or disabled). For OK events, emit disabled or unknown accounts only. Print event_id<TAB>user<TAB>account_state<TAB>owner in authentication input order. Use unknown and - for missing accounts; preserve the owner for disabled accounts. FAIL events and active-account successes produce no row.

See an explained solution
One way to solve it.sh
printf 'event_id\tuser\taccount_state\towner\n'
awk -F '\t' 'FILENAME==ARGV[1] {if(FNR>1){state[$1]=$2;owner[$1]=$3}next}
FNR>1 && $4=="OK" {if(!($2 in state)) printf "%s\t%s\tunknown\t-\n",$1,$2;else if(state[$2]=="disabled") printf "%s\t%s\tdisabled\t%s\n",$1,$2,owner[$2]}
' accounts.tsv auth.tsv

The join preserves successful events whose account is missing from the inventory. It labels that uncertainty explicitly while keeping disabled-account context and original event IDs for review.