wp_login_fails table has every failed login ever

I wrote a bash script to handle this. It have cron run the script once a week.
– It assumes ~/.mylogin.cnf is defined for authentication.
– It creates a list of busy hackers.
– It deletes entries older than 2 weeks.

cd ${DST_DIR}
#
# Get a list of the most prolific offenders,
# more than 20 attempts in the last 2 weeks.
#
filo=”$DST_DIR/lockdown-ip-count-“date +%FT%H.%M.%S“.csv”
sqlcmd=’select count(t.login_attempt_id) as ip_count,t.login_attempt_ip’
sqlcmd=”$sqlcmd from dbname.wp_login_fails t”
sqlcmd=”$sqlcmd where datediff(now(), t.login_attempt_date) < 15″
sqlcmd=”$sqlcmd group by t.login_attempt_ip having (20 < ip_count)”
sqlcmd=”$sqlcmd order by ip_count desc;”
# echo $sqlcmd
# exit 0

mysql –login-path=sohne_admin -e “$sqlcmd” > $filo
rtn=$?

# echo “mysql return value [$rtn]”

#
# Clean the database tables.
#
if [ 0 -eq $rtn ]; then
sqlcmd=’DELETE FROM dbname.wp_lockdowns WHERE 14 < datediff(now(), dbname.wp_lockdowns.lockdown_date);’
mysql –login-path=sohne_admin -e “$sqlcmd”
rtn=$?
# echo “mysql return value [$rtn]”

sqlcmd=’DELETE FROM dbname.wp_login_fails WHERE 14 < datediff(now(), dbname.wp_login_fails.login_attempt_date);’
mysql –login-path=sohne_admin -e “$sqlcmd”
rtn=$?
# echo “mysql return value [$rtn]”
else
echo “Login Lockdown data collection failed.”
echo ” Login tables not cleaned.”
fi
exit 0