AWK, cut or ced...double parsing mail log to get authentication failures

Question

I would like to parse mail log files, which originally look like this:

2018-10-23 23:27:51,026 INFO  [ImapServer-4] [ip=10.10.11.50;oip=168.232.24.2;via=10.10.11.50(nginx/1.7.1);ua=Zimbra/8.8.7_GA_1964;cid=127325;] imap - authentication failed for [user@domain.com] (invalid password)

for keywords, either: "invalid password" or "authentication failed"

Goal is to sort them by either "OIP" (original IP) or by user MAIL accoount, to see in first case the attacking IP, and in second case, which user account is under attack.

Those should be 2 command lines (will incorporate them into my bash script for easier administration of mail servers).

What I came to is this:

cat /opt/zimbra/log/mailbox.log | grep "invalid password" | awk -F " " '{print $1 $2 $5 $11 }'

...but I am stuck there. I do not know how to double-parse attacker IP from "oid=" and make some "uniq" and "sort" against results. I am trying to get results like this:

Case 1 - display attacking IPs, sorted by number of invalid logins:

37    1.2.3.4
16    3.4.5.6
 8    6.7.8.9

Case 2 - display attacked MAIL accounts, sorted by number of invalid logins:

128   info@domain.com
 37   user@domain.com
  6   user2@domain.com

I will then run manually my (above) one-liner to analyze deeper, but for overview can you help me with AWK or cut or sed commands, please?

Had a quick look: `awk 'BEGIN{FS=OFS=";"}/invalid password/{ oips=substr($2,index($2," - ")+5);oip[oips]++} END { for (ip in oip) printf("%s\t%s\n", oip[ip],ip)}' logfile`. Give it a try. The email part is easy as well: `awk -v RS="[][]" '/@/{email[$1]++} END { for (e in email) print e,email[e]}' logfile` — Valentin Bajrami, Oct 23 '18 at 22:22

score 2 · Answer 1 · answered Oct 23 '18 at 22:23

Using space or semicolon as the field separator, you can do

$ awk -F '[ ;]' '/authentication failed/ {print $7, $17}' file
oip=168.232.24.2 [user@domain.com]

Or perl

$ perl -lne '/authentication failed|invalid password/ 
         and /oip=(.+?);.*for \[(.+?@.+?)\]/ 
         and print "$1 $2"' file
168.232.24.2 user@domain.com

Use either one of those, then sort | uniq -c the output

score 1 · Accepted Answer · answered Oct 23 '18 at 21:57

$ cat $$ 
2018-10-23 23:27:51,026 INFO  [ImapServer-4] [ip=10.10.11.50;oip=168.232.24.2;via=10.10.11.50(nginx/1.7.1);ua=Zimbra/8.8.7_GA_1964;cid=127325;] imap - authentication failed for [user@domain.com] (invalid password)
$ cat $$ | egrep '(authentication failed|invalid password)' | egrep -o "[[a-z]*@[a-z]*\.[a-z]*]" | sort | uniq -c
      1 [user@domain.com]
$ cat $$ | egrep '(authentication failed|invalid password)' | egrep -o "oip=[0-9]*\.[0-9]*\.[0-9]*\.[0-9]*" | sort | uniq -c
      1 oip=168.232.24.2
$

This one is almost perfect! I just modified `sort` to `sort -nr` so to have descending order. Thank you! — Labsy, Oct 24 '18 at 01:00

AWK, cut or ced...double parsing mail log to get authentication failures

2 Answers2