02 / AUTHENTICATION AND ACCESS · LESSON 06

A success after repeated failures

Correlate a successful login with failures in an exact preceding window.

16 min · learn at your pace

Sequence adds context

A successful login following several failures can deserve review. It might also be a person recovering from a mistyped password. Define the pattern before looking at results: same account, same source address, at least three failures in the preceding five minutes.

The exporter supplies integer seconds since midnight for one known UTC day. This keeps the exercise about correlation rather than platform-specific date parsing. For a success at second S, count failures where S-300 <= failure < S. The start is included and simultaneous failures are excluded; events after the success cannot explain it.

Input order is not time order

Files may arrive out of order. Store events first and evaluate successes after reading the input. A simple nested loop is understandable for these tiny datasets. Its quadratic cost makes it unsuitable for large production exports; a sorted sliding window or database query would scale better.

Require both identity fields to match. Never correlate unrelated people merely because they share an IP, and do not join different addresses merely because the account matches. Emit the success event ID as provenance. Sorting those IDs makes reruns stable, but the output still represents a detection hypothesis rather than a confirmed incident.

Try an example.sh
awk -F '\t' 'NR>1 && $5=="OK" {printf "%s starts at second %d\n",$1,$2-300}' auth.tsv
YOUR TURN

Put it into practice.

auth.tsv contains unique validated id<TAB>second<TAB>user<TAB>ip<TAB>result records from one UTC day; users contain no whitespace. For each OK event at second S, count FAIL events for that same user and IP where S-300 <= second < S. Print success_id<TAB>prior_failures and only successes with at least 3 such failures, sorted by success ID. Input order is arbitrary.

See an explained solution
One way to solve it.sh
export LC_ALL=C
printf 'success_id\tprior_failures\n'
awk -F '\t' 'NR>1 {n++;id[n]=$1;t[n]=$2;u[n]=$3;ip[n]=$4;r[n]=$5}
END {for(i=1;i<=n;i++) if(r[i]=="OK") {count=0;for(j=1;j<=n;j++) if(r[j]=="FAIL" && u[j]==u[i] && ip[j]==ip[i] && t[j]>=t[i]-300 && t[j]<t[i]) count++;if(count>=3) printf "%s\t%d\n",id[i],count}}
' auth.tsv | sort

Loading all events first handles out-of-order evidence. Each success gets its own window and pair of identity keys, so simultaneous failures and another account’s activity cannot inflate its count.