Cacti and Fail2ban

Or: Monitoring a MySQL log table with Fail2ban

Intro

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.

Setup

1. Connect to your Cacti database, e.g.:

   $ mysql cacti
   

2. Create the new table:

   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;
   

3. Create the trigger. For this, we have to alter the input delimiter of the MySQL client temporarily:

   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; |
   

4. Optionally, start the event scheduler and create an event which deletes any old records from that table:

   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.
5. As the trigger writes only failed login attempts (result=1) to the new table, we can get away with a fairly simple Fail2ban filter:

   [Definition]
   failregex = ","<HOST>","
   ignoreregex =
   

   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.
6. Finally, extend your jail.local, e.g.:

   [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
   

7. Either restart Fail2ban, or activate the new jail:

   # fail2ban-client add cacti-ipfw
   # fail2ban-client start cacti-ipfw