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