Repeated authentication failures
Group failed logins by both account and source address before applying a threshold.
Choose the unit of detection
Three failures across a whole organisation mean something different from three failures against one account from one address. A detection needs an explicit grouping key. Here the key is the pair of user and source IP, and the threshold is inclusive: three or more failures.
Awk has associative arrays even though Bash 3.2 does not. counts[$2,$3]++ stores a composite key using awk’s internal separator. The input contract excludes control characters, so that separator cannot occur inside a field. Keep display values separately if you do not want to split the key later.
Stable reports make reviews easier
Array traversal order is unspecified. Sort extracted rows under LC_ALL=C before printing the final report. A header should stay outside that sort. Otherwise the column names can end up in the middle of the data.
Count only exact FAIL results. A successful login does not subtract a failure and does not count toward this threshold. These are already validated, distinct events within a collection period. Duplicate elimination and rolling time windows are separate policies; the next lesson adds a preceding-window rule. A threshold crossing remains a lead for an analyst, not an automatic account-compromise verdict.
awk -F '\t' 'NR>1 && $4=="FAIL" {n++} END {printf "failures=%d\n",n}' auth.tsv
Put it into practice.
auth.tsv has validated, unique id<TAB>user<TAB>ip<TAB>result records, with nonempty users containing no whitespace. Print user<TAB>ip<TAB>failures and groups with at least 3 FAIL events, sorted by user then IP under LC_ALL=C. Group by both fields; OK events do not contribute. Empty results still print the header.
See an explained solution
export LC_ALL=C
printf 'user\tip\tfailures\n'
awk -F '\t' 'NR>1 && $4=="FAIL" {k=$2 SUBSEP $3;n[k]++;u[k]=$2;ip[k]=$3} END {for(k in n) if(n[k]>=3) printf "%s\t%s\t%d\n",u[k],ip[k],n[k]}' auth.tsv | sort
The composite key prevents unrelated users or addresses from being pooled together. Filtering before counting keeps successful events out, and sorting removes dependence on awk’s array traversal order.