Kippo statistic extraction script

estadisticas.sh

#!/bin/bash

TOPIP=15
TOPUP=30
TOPU=30
TOPP=30
TOPLU=15
TOPLP=15
GEOLITEDB="/home/<username>/maxmind/GeoIPCountryWhois.db" # Use the sqlite3 db created with http://pastebin.com/9WxCy5ks

#================================

ip2dec () {
    local a b c d ip=$@
    IFS=. read -r a b c d <<< "$ip"
    printf '%d\n' "$(((a * 256 ** 3) + (b * 256 ** 2) + (c * 256) + d))"
}

#================================

# saca el ranking de IPs con mas intentos de fuerza bruta
echo "===================="
echo "= Top $TOPIP source IP ="
echo "====================" 
iplist=$(grep "login attempt" kippo.log* kippo.log | cut -f 6,9 -d' ' | cut -f3 -d',' | sed s/\\]\\s\\[/:/g | tr -d ']' | cut -f1 -d: | gawk '{for(i=1;i<=NF;i++)a[$i]++}END{ for(o in a) printf "%s:%s\n ",o,a[o]}' | sort -t: -k2 -nr | tr -d ' ' | head -n $TOPIP)
for ip_and_count in $iplist
do
	country="<UNKNOWN COUNTRY>"
	if [[ -f $GEOLITEDB ]]; then
		ip=$(echo $ip_and_count | cut -d':' -f1)
		ipdec=$(ip2dec $ip)
		query="select cty_name from GeoIPCountryWhois where $ipdec between CAST(initipdec AS INTEGER) and CAST(endipdec AS INTEGER) limit 1;"
		country=$(sqlite3 $GEOLITEDB "$query")
	else 
		echo "Can't find GeoIP database $GEOLITEDB"
	fi
	echo "$ip_and_count ($country)"
done

echo "=========================="
echo "= Top $TOPUP user/passwords ="
echo "=========================="

grep  "login attempt" kippo.log* kippo.log  | awk '{print $9}' | tr -d ']' | tr -d '[' | sort | uniq -c | sort -nr  | head -n $TOPUP

echo "==================="
echo "= Top $TOPU users ="
echo "==================="

grep  "login attempt" kippo.log* kippo.log  | awk '{print $9}' | awk '{FS="/"}{print $1}' | tr -d ']' | tr -d '[' | sort | uniq -c | sort -nr  | head -n $TOPU

echo "======================="
echo "= Top $TOPP passwords ="
echo "======================="

grep  "login attempt" kippo.log*  kippo.log | awk '{print $9}' | awk '{FS="/"}{print $2}' | tr -d ']' | tr -d '[' | sort | uniq -c | sort -nr  | head -n $TOPP

echo "======================="
echo "= Last $TOPLU users ="
echo "======================="

# grep  "login attempt" kippo.log*  kippo.log | awk '{print $9}' | awk '{FS="/"}{print $1}' | tr -d ']' | tr -d '[' | tail -n $TOPLU
grep -h  "login attempt" kippo.log*  kippo.log | tail -n $TOPLU | awk '{print $9" ("$1" "$2")"}' | sed -r 's/\[(.*)\/(.*)\]( \(.*\))/\1\3/g'

echo "======================="
echo "= Last $TOPLP passwords ="
echo "======================="

# grep  "login attempt" kippo.log*  kippo.log | awk '{print $9}' | awk '{FS="/"}{print $2}' | tr -d ']' | tr -d '[' | tail -n $TOPLP
grep  -h "login attempt" kippo.log*  kippo.log | tail -n $TOPLU | awk '{print $9" ("$1" "$2")"}' | sed -r 's/\[(.*)\/(.*)\]( \(.*\))/\2\3/g'

echo "==================="
echo "= Fails / Success ="
echo "==================="
success=$(grep -E "login attempt \[.*/.*\] succeeded" kippo.log* kippo.log | wc -l)
fails=$(grep -E "login attempt \[.*/.*\] failed" kippo.log* kippo.log | wc -l)
echo "Nº Authentication Success: $success"
echo "Nº Authentication Fails: $fails"
#percentage=$((($success / $fails)*100))
#echo "Percentage of success: $percentage"

output.example.log

====================
= Top 15 source IP =
====================
103.41.124.12:6480 (Hong Kong)
103.41.124.53:6363 (Hong Kong)
103.41.124.19:5809 (Hong Kong)
117.27.249.4:1470 (China)
122.225.109.126:987 (China)
122.225.109.112:847 (China)
122.225.109.210:783 (China)
61.147.103.135:700 (China)
54.165.178.152:633 (United States)
122.225.109.207:581 (China)
218.244.130.250:567 (China)
222.186.34.244:552 (China)
61.174.51.197:537 (China)
61.174.51.232:522 (China)
122.225.97.89:512 (China)
==========================
= Top 30 user/passwords =
==========================
    374 root/admin
    178 admin/
    111 root/123456
    105 admin/admin
     86 root/root
     84 root/abcd1234
     84 root/12qwaszx
     82 root/1q2w3e4r5t6y
     81 root/Qwer1234
     81 root/PassWord
     80 root/1a2s3d4f
     78 root/abcd@123
     77 root/1qazXSW@
     74 root/ans#150
     73 root/xiaozhe
     72 root/password
     72 root/1122334455
     71 root/qwerty123456
     71 root/password321
     71 root/Password!
     71 root/danny
     71 root/1q2w3e
     71 admin/password
     70 root/start123
     70 root/rootpass
     70 root/changeme
     69 root/zxm10
     69 root/poiuyt
     69 root/pass123
     69 root/~!@#$%^&
===================
= Top 30 users =
===================
  41965 root
   2392 admin
    122 test
     83 oracle
     78 ubnt
     65 guest
     58 user
     46 www
     45 mysql
     44 linux
     44 apache
     41 toor
     41 tomcat
     32 pi
     32 nagios
     29 testing
     28 ftp
     25 support
     25 administrator
     23 PlcmSpIp
     22 default
     21 alex
     19 teamspeak
     19 postgres
     18 info
     18 aaron
     17 backup
     17 adm
     16 xbian
     16 vyatta
=======================
= Top 30 passwords =
=======================
    501 admin
    316
    213 123456
    165 password
    159 root
    123 12345
    108 1234
    101 abcd1234
     97 test
     97 default
     95 toor
     92 1qaz2wsx
     88 1q2w3e
     88 123
     86 oracle
     84 admin123
     84 12qwaszx
     83 12345678
     82 1q2w3e4r5t6y
     81 Qwer1234
     81 PassWord
     80 1a2s3d4f
     80 123123
     78 qwe123
     78 abcd@123
     78 1234567890
     77 1qazXSW@
     76 root123
     76 linux
     76 cisco
=======================
= Last 15 users =
=======================
admin (2015-01-16 04:21:23+0100)
admin (2015-01-16 04:21:25+0100)
admin (2015-01-16 04:21:26+0100)
admin (2015-01-16 05:16:33+0100)
admin (2015-01-16 05:16:34+0100)
admin (2015-01-16 05:16:36+0100)
admin (2015-01-16 06:12:30+0100)
admin (2015-01-16 06:12:32+0100)
admin (2015-01-16 06:12:33+0100)
admin (2015-01-16 06:54:17+0100)
administrator (2015-01-16 07:45:56+0100)
administrator (2015-01-16 07:45:57+0100)
administrator (2015-01-16 07:45:59+0100)
administrator (2015-01-16 08:34:33+0100)
administrator (2015-01-16 08:34:34+0100)
=======================
= Last 15 passwords =
=======================
abc123 (2015-01-16 04:21:23+0100)
abcd1234 (2015-01-16 04:21:25+0100)
qwerty (2015-01-16 04:21:26+0100)
1234 (2015-01-16 05:16:33+0100)
1234qwer (2015-01-16 05:16:34+0100)
1234qwerty (2015-01-16 05:16:36+0100)
1q2w3e (2015-01-16 06:12:30+0100)
admin1234 (2015-01-16 06:12:32+0100)
sysadmin (2015-01-16 06:12:33+0100)
sysadm (2015-01-16 06:54:17+0100)
administrator (2015-01-16 07:45:56+0100)
administrator123 (2015-01-16 07:45:57+0100)
adm (2015-01-16 07:45:59+0100)
sysadmin (2015-01-16 08:34:33+0100)
sysadm (2015-01-16 08:34:34+0100)
===================
= Fails / Success =
===================
Nº Authentication Success: 389
Nº Authentication Fails: 45792