Capstone: investigate suspicious account activity
Combine validated authentication evidence, account context, indicators and hashes into a reproducible case report.
Build an evidence trail another analyst can follow
The help desk reports unusual activity on a fictional account. You have an authentication export, account-state inventory, reviewed IP indicators and a small investigation policy. Your job is to produce a reproducible account of the evidence. You are not deciding that a compromise definitely occurred.
Start by validating the required inputs and reference tables. Missing evidence or ambiguous policy should stop the tool before it replaces an earlier report. Malformed individual authentication records are different: reject them visibly, retain their physical line numbers and continue analysing valid records.
Separate collection scope from detection
The case covers one UTC day and a policy-selected half-open time window. First validate and deduplicate the entire authentication export; then select the window. This ordering prevents a later duplicate inside the window from replacing a valid earlier observation outside it. Record counts distinguish accepted input from events actually in scope.
Each finding has a reason and a source reference. Indicator matches are exact. Account-state context distinguishes disabled and unknown users. A repeated-failure group anchors its finding to a deterministic source event so the report stays stable even when input is out of chronological order. Several independent observations may refer to one event.
Preserve, stage and explain
Hash original input bytes, including policy and reference data, before publishing the manifest. A hash identifies the bytes used; it does not establish that the collector or baseline was trustworthy. Keep the evidence unchanged and write reports to a separate directory.
Use small functions and a temporary workspace to keep parsing, detection and publication understandable. Test empty exports, boundaries, duplicates, Unicode names and missing inputs. Successful reruns replace prior reports. Explain limitations in the summary: these supplied exports support an investigation, not a claim about every event that happened on a real endpoint.
printf 'Case inputs:\n'
for name in accounts.tsv auth.tsv indicators.tsv policy.conf; do
printf '%s: ' "$name"
awk 'END {printf "%d records\n",NR}' "evidence/$name"
done
Put it into practice.
Build bash main.sh [input_directory] [output_directory], defaulting to evidence and reports. Use the four required files accounts.tsv, auth.tsv, indicators.tsv and policy.conf. Inputs use UTF-8, LF records, literal tabs, no embedded tabs/newlines in fields, and case-sensitive identifiers. Never modify evidence. The output directory is separate from the evidence directory.
Reference inputs: accounts.tsv has header user<TAB>state, unique nonempty users without ASCII whitespace/control characters, and state active or disabled. indicators.tsv has header ip and unique canonical addresses of the form 192.0.2.N, where N is 0–255 without leading zeros. These addresses are compared exactly; this is not a network scanner. Header-only reference files are valid. Missing required files must fail with Missing required evidence; malformed/duplicate reference rows or headers must fail with Invalid reference evidence, all on stderr with nonzero status.
Policy is exactly one of each window_start=N, window_end=N and failure_threshold=N, with no spaces around = and no unknown or duplicate keys. Allow blank lines and comments starting #. Values are canonical nonnegative decimal integers without leading zeros. Start is 0–86399, end is 1–86400, start < end, and threshold is 1–9. Times are seconds since midnight on 2026-11-01 UTC, with a half-open [start,end) window. Invalid policy must fail with Invalid policy. Do not source or execute it.
Authentication header is id<TAB>timestamp<TAB>user<TAB>ip<TAB>result; a different or missing header fails with Invalid authentication header. For each subsequent physical line, reject using the first applicable reason: fields (not exactly five); id (not ^[A-Za-z0-9_-]+$); timestamp (not exactly 2026-11-01THH:MM:SSZ with valid hour/minute/second); user (empty or containing ASCII whitespace/control characters); ip (not the canonical documentation address format above); result (not FAIL or OK); duplicate (ID already accepted). A rejected row never reserves its ID. First valid occurrence wins across the whole file, even outside the time window. Rejected rows become errors; they do not abort the investigation.
Create findings.tsv with header id<TAB>timestamp<TAB>user<TAB>ip<TAB>reason<TAB>detail<TAB>source<TAB>line. Use accepted events inside the window only. Every event from a listed IP gets indicator_ip with detail equal to the IP. Every OK for a disabled account gets disabled_account with detail disabled; an absent account gets unknown_account with detail unknown. For each user/IP pair reaching the FAIL threshold, add one repeated_failures finding with the count as detail, anchored to its latest failure by timestamp then greatest ID in LC_ALL=C lexical order. Multiple reasons may reference one event. Source is auth.tsv and line is its original physical line number. Sort by timestamp, ID, then reason in LC_ALL=C order.
Create timeline.tsv with header timestamp<TAB>id<TAB>user<TAB>ip<TAB>result<TAB>source<TAB>line, containing all accepted in-window events sorted by timestamp then ID. Create errors.tsv with header source<TAB>line<TAB>reason, in original rejection order. Create evidence_manifest.tsv with header path<TAB>sha256, hashing the original four files in accounts.tsv, auth.tsv, indicators.tsv, policy.conf order; path is the supplied input-directory argument plus / and the basename. Use lowercase SHA-256 and support macOS shasum as well as Linux sha256sum.
Create summary.txt with these exact keys on separate lines: accepted (all unique valid rows, including outside the window), in_window, rejected, findings, affected_users (distinct users with any finding), then limitation=Supplied exports only; findings require review. Use key=value and a final newline. Reports always include their headers, even with no data. Successful reruns replace all five reports; startup validation failures preserve any previous reports. No stdout is required, and successful runs produce no stderr.
See an explained solution
export LC_ALL=C
input=${1:-evidence}
out=${2:-reports}
for name in accounts.tsv auth.tsv indicators.tsv policy.conf; do
[ -f "$input/$name" ] || { printf 'Missing required evidence: %s\n' "$name" >&2; exit 1; }
done
values=$(awk -F '=' '
/^[[:space:]]*$/ || /^#/ {next}
{if(NF!=2 || ($1!="window_start" && $1!="window_end" && $1!="failure_threshold") || $2!~/^(0|[1-9][0-9]*)$/ || seen[$1]++) bad=1;v[$1]=$2}
END {if(bad || !seen["window_start"] || !seen["window_end"] || !seen["failure_threshold"] || v["window_start"]<0 || v["window_start"]>86399 || v["window_end"]<1 || v["window_end"]>86400 || v["window_start"]>=v["window_end"] || v["failure_threshold"]<1 || v["failure_threshold"]>9) exit 1;printf "%d %d %d\n",v["window_start"],v["window_end"],v["failure_threshold"]}
' "$input/policy.conf") || { printf 'Invalid policy\n' >&2; exit 1; }
read -r start end threshold <<< "$values"
awk -F '\t' '
function ip_ok(s, a,n){n=split(s,a,".");return n==4 && a[1]=="192" && a[2]=="0" && a[3]=="2" && a[4]~/^(0|[1-9][0-9]*)$/ && length(a[4])<=3 && a[4]<=255}
FILENAME==ARGV[1] {if(FNR==1){if($0!="user\tstate") bad=1;next}if(NF!=2 || $1=="" || $1~/[[:space:][:cntrl:]]/ || ($2!="active" && $2!="disabled") || users[$1]++) bad=1;next}
FNR==1 {if($0!="ip") bad=1;next}
{if(NF!=1 || !ip_ok($1) || ips[$1]++) bad=1}
END {exit bad?1:0}
' "$input/accounts.tsv" "$input/indicators.tsv" || { printf 'Invalid reference evidence\n' >&2; exit 1; }
[ "$(head -n 1 "$input/accounts.tsv")" = $'user\tstate' ] && [ "$(head -n 1 "$input/indicators.tsv")" = ip ] || { printf 'Invalid reference evidence\n' >&2; exit 1; }
[ "$(head -n 1 "$input/auth.tsv")" = $'id\ttimestamp\tuser\tip\tresult' ] || { printf 'Invalid authentication header\n' >&2; exit 1; }
tmp=$(mktemp -d "${TMPDIR:-/tmp}/rocks-case.XXXXXX") || exit 1
trap 'rm -rf "$tmp"' EXIT
: > "$tmp/findings.data"
: > "$tmp/timeline.data"
printf 'source\tline\treason\n' > "$tmp/errors.tsv"
awk -F '\t' -v start="$start" -v end="$end" -v threshold="$threshold" -v work="$tmp" '
function ip_ok(s, a,n){n=split(s,a,".");return n==4 && a[1]=="192" && a[2]=="0" && a[3]=="2" && a[4]~/^(0|[1-9][0-9]*)$/ && length(a[4])<=3 && a[4]<=255}
function time_ok(s){return length(s)==20 && s~/^2026-11-01T[0-9][0-9]:[0-9][0-9]:[0-9][0-9]Z$/ && substr(s,12,2)<24 && substr(s,15,2)<60 && substr(s,18,2)<60}
function emit(id,t,u,ip,reason,detail,line){printf "%s\t%s\t%s\t%s\t%s\t%s\tauth.tsv\t%d\n",id,t,u,ip,reason,detail,line >> (work "/findings.data");findings++;affected[u]=1}
FILENAME==ARGV[1] {if(FNR>1) state[$1]=$2;next}
FILENAME==ARGV[2] {if(FNR>1) indicator[$1]=1;next}
FNR==1 {next}
{
reason=""
if(NF!=5) reason="fields"
else if($1!~/^[A-Za-z0-9_-]+$/) reason="id"
else if(!time_ok($2)) reason="timestamp"
else if($3=="" || $3~/[[:space:][:cntrl:]]/) reason="user"
else if(!ip_ok($4)) reason="ip"
else if($5!="FAIL" && $5!="OK") reason="result"
else if($1 in seen) reason="duplicate"
if(reason!=""){printf "auth.tsv\t%d\t%s\n",FNR,reason >> (work "/errors.tsv");rejected++;next}
seen[$1]=1;accepted++
second=substr($2,12,2)*3600+substr($2,15,2)*60+substr($2,18,2)
if(second<start || second>=end) next
in_window++
printf "%s\t%s\t%s\t%s\t%s\tauth.tsv\t%d\n",$2,$1,$3,$4,$5,FNR >> (work "/timeline.data")
if($4 in indicator) emit($1,$2,$3,$4,"indicator_ip",$4,FNR)
if($5=="OK") {if(!($3 in state)) emit($1,$2,$3,$4,"unknown_account","unknown",FNR);else if(state[$3]=="disabled") emit($1,$2,$3,$4,"disabled_account","disabled",FNR)}
if($5=="FAIL") {key=$3 SUBSEP $4;count[key]++;if(!(key in last_t) || $2>last_t[key] || ($2==last_t[key] && ("x" $1)>("x" last_id[key]))) {last_t[key]=$2;last_id[key]=$1;last_line[key]=FNR;user[key]=$3;address[key]=$4}}
}
END {for(key in count) if(count[key]>=threshold) emit(last_id[key],last_t[key],user[key],address[key],"repeated_failures",count[key],last_line[key]);for(u in affected) users++
printf "accepted=%d\nin_window=%d\nrejected=%d\nfindings=%d\naffected_users=%d\nlimitation=Supplied exports only; findings require review.\n",accepted,in_window,rejected,findings,users > (work "/summary.txt")}
' "$input/accounts.tsv" "$input/indicators.tsv" "$input/auth.tsv" || exit 1
printf 'id\ttimestamp\tuser\tip\treason\tdetail\tsource\tline\n' > "$tmp/findings.tsv"
sort -t $'\t' -k2,2 -k1,1 -k5,5 "$tmp/findings.data" >> "$tmp/findings.tsv" || exit 1
printf 'timestamp\tid\tuser\tip\tresult\tsource\tline\n' > "$tmp/timeline.tsv"
sort -t $'\t' -k1,1 -k2,2 "$tmp/timeline.data" >> "$tmp/timeline.tsv" || exit 1
sha256_file() {
if command -v sha256sum >/dev/null 2>&1; then
value=$(sha256sum < "$1") || return 1
else
value=$(shasum -a 256 < "$1") || return 1
fi
printf '%s\n' "${value%% *}"
}
printf 'path\tsha256\n' > "$tmp/evidence_manifest.tsv"
for name in accounts.tsv auth.tsv indicators.tsv policy.conf; do
hash=$(sha256_file "$input/$name") || exit 1
printf '%s/%s\t%s\n' "$input" "$name" "$hash" >> "$tmp/evidence_manifest.tsv"
done
mkdir -p "$out" || exit 1
for name in findings.tsv timeline.tsv evidence_manifest.tsv errors.tsv summary.txt; do
mv "$tmp/$name" "$out/$name" || exit 1
done
The reference separates startup validation from row rejection, deduplicates before selecting the window, and attaches physical source lines to every finding. Its independent rules preserve uncertainty instead of declaring compromise. Temporary reports, stable byte-order sorting, original-file hashes and explicit limitations make the output reproducible and reviewable.