Using grep and awk to pull per‑user SSH login failures from /var/log/auth.log into a CSV file -

A quick way to see who’s repeatedly failing to log in via SSH is to pull the relevant lines from /var/log/auth.log and turn them into a CSV. The CSV can then be fed into a spreadsheet, a Grafana dashboard, or a simple shell script that alerts you when a user crosses a threshold. Below is a step‑by‑step recipe that uses only grep and awk, two tools that are guaranteed to be present on any modern Linux distribution.

Auth.log format recap

The default Debian/Ubuntu format looks like this:

Oct 12 07:34:12 server sshd[1234]: Failed password for invalid user admin from 203.0.113.45 port 54321 ssh2

Fields:

Month day time
Hostname
Program and PID
Message text

The message always contains the keyword Failed password for SSH authentication failures. The username follows for, and the source IP follows from. This structure is stable across most distros, but if you’re on a custom PAM configuration, double‑check the pattern.

One‑liner that writes CSV

grep -E 'Failed password' /var/log/auth.log* |
awk '
  BEGIN { FS=" "; OFS=","; print "date,host,program,user,ip" }
  {
    # Re‑assemble the date (month day time)
    date = $1 " " $2 " " $3
    # Hostname is $4
    host = $4
    # Program is the first field of $5 (sshd[1234]:)
    program = $5
    # Username is the word after "for"
    for (i=1; i<=NF; i++) {
      if ($i ~ /for$/) { user = $(i+1); break }
    }
    # IP is the word after "from"
    for (i=1; i<=NF; i++) {
      if ($i ~ /from$/) { ip = $(i+1); break }
    }
    # Remove trailing colon from program
    sub(/:$/, "", program)
    print date, host, program, user, ip
  }
' > /var/log/ssh_failures.csv

Why this works

grep -E 'Failed password' pulls only the relevant lines, even from rotated logs (auth.log.1, auth.log.2.gz, etc.).
awk uses space as the field separator (FS=" ").
The BEGIN block prints a header row.
The loops search for the for and from tokens to capture the username and IP, which keeps the script resilient to minor format changes (e.g., invalid user vs. a real username).
The final print writes a comma‑separated line.

Rotated logs

If you want to include compressed rotated logs, replace the grep line with:

zgrep -E 'Failed password' /var/log/auth.log* 2>/dev/null

zgrep transparently reads .gz files. The 2>/dev/null silences “file not found” messages for non‑existent rotated files.

Permissions and safety

The CSV contains usernames and IP addresses, which can be sensitive. Store it in a directory that only root or a dedicated monitoring user can read:

sudo mkdir -p /var/log/monitor
sudo chown root:monitor /var/log/monitor
sudo chmod 750 /var/log/monitor
sudo mv /var/log/ssh_failures.csv /var/log/monitor/

If you’re running the script as a cron job, use the same ownership and permissions to avoid accidental exposure.

Alternatives and trade‑offs

Approach	Pros	Cons
`grep` + `awk` on `auth.log`	Zero dependencies, fast for small logs	Requires manual handling of rotated logs; limited to plain text
`journalctl -u sshd -p err`	Uses systemd’s journal, no log rotation worries	Needs systemd; output format is different
`fail2ban` or `auditd`	Built‑in failure tracking, can trigger bans	Adds complexity; may miss non‑SSH failures

If your system uses systemd-journald exclusively (e.g., on a minimal container), replace the grep step with:

journalctl -u sshd -p err --since "24h" --output short-iso |
awk '...same awk code...'

The --output short-iso gives you a consistent timestamp format that the awk script can parse.

Common pitfalls

Symptom	Likely cause	Fix
No output	No `Failed password` lines in the log	Check `/var/log/auth.log` or `/var/log/secure` on RHEL/CentOS
Wrong username	Username contains spaces (rare)	Adjust the `awk` field extraction to capture until `from`
IP missing	Log format changed (e.g., `ssh2` removed)	Update the `awk` loop that looks for `from`

If the script silently outputs nothing, add -v to grep to see which files it scans:

grep -v -E 'Failed password' /var/log/auth.log*

Security hygiene

Log rotation: Keep auth.log rotation at least daily to avoid huge files that slow the script. The default Debian logrotate config is fine.
File ownership: As shown above, restrict the CSV to a dedicated group.
Avoid exposing usernames: If you only care about IPs, drop the user field from the header and print statement.
Audit the script: Store the script in /usr/local/bin/ssh_failures_to_csv and set chmod 755. Use sudo to run it so that it can read the log files.

Automating with systemd

A systemd timer keeps the CSV fresh without a cron job:

# /etc/systemd/system/ssh-failures.service
[Unit]
Description=Generate SSH failure CSV

[Service]
Type=oneshot
ExecStart=/usr/local/bin/ssh_failures_to_csv

# /etc/systemd/system/ssh-failures.timer
[Unit]
Description=Run ssh-failures.service every hour

[Timer]
OnCalendar=*-*-* *:00:00
Persistent=true

[Install]
WantedBy=timers.target

Enable it:

sudo systemctl enable --now ssh-failures.timer

Now the CSV is refreshed every hour, ready for downstream consumption.

Downstream use

With a CSV you can:

Import into a spreadsheet and create a pivot table that shows the top 5 IPs per user.
Feed it into Grafana using the CSV plugin to build a real‑time dashboard.
Trigger a custom script that sends an email if a user exceeds a threshold of failures in the last 24 h.

A simple threshold script:

awk -F, '{ if ($4 != "") counts[$4]++ } END { for (u in counts) if (counts[u] > 10) print u, counts[u] }' /var/log/monitor/ssh_failures.csv

This prints any user that exceeded the threshold.