Or: Monitoring a MySQL log table with Fail2ban
Cacti logs failed login attempts to a MySQL table, which Fail2ban can't read. By using MySQL's CSV storage engine we can write login attempts to an additional table, which on-disk format is ASCII or UTF-8, and thus readable by Fail2ban.
Note: This includes at least a trigger and and an optional scheduled event inside the database. I consider this "dirty", especially within a replication scenario.
$ mysql cacti
mysql> CREATE TABLE fail2ban ( -> `time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', -> ip varchar(40) NOT NULL DEFAULT '', -> username varchar(50) NOT NULL DEFAULT '') -> ENGINE=CSV;
mysql> delimiter | mysql> CREATE DEFINER=cacti@localhost TRIGGER failed_logins -> AFTER INSERT ON user_log FOR EACH ROW -> BEGIN -> IF NOT NEW.result = 1 -> THEN INSERT INTO fail2ban SET time=NEW.time, ip=NEW.ip, username=NEW.username; -> END IF; -> END; |
mysql> SET GLOBAL event_scheduler = 1 | mysql> CREATE DEFINER=cacti@localhost EVENT delete_old_logs -> ON SCHEDULE EVERY 1 hour COMMENT 'wipe old failed login attempts' -> DO DELETE FROM fail2ban WHERE time <= subtime(now(), '01:00:00') | mysql> quit|Do not forget to set event_scheduler = 1 in your MySQL server's my.cnf.
[Definition] failregex = ","Save it to a file called filter.d/cacti.local within your Fail2ban configuration directory, e.g. /etc/fail2ban, or /usr/local/etc/fail2ban on FreeBSD.
"," ignoreregex =
[cacti-ipfw] enabled = yes filter = cacti action = bsd-ipfw sendmail-whois-lines[name=cacti, logpath=/data/mysql/cacti/fail2ban.CSV] logpath = /data/mysql/cacti/fail2ban.CSV
# fail2ban-client add cacti-ipfw # fail2ban-client start cacti-ipfw