Populating mySQL with fail2ban information and banhammer

Fail2SQL – An SQL logger for fail2ban can be found at http://fail2sql.sourceforge.net/

But i will do two things at the same time as i also found this http://www.byteme.org.uk/2014/04/21/ban-hammer-fail2ban-geo-ip-on-google-maps/ which will give me statistics on the web with google maps about banned IPS with geo location. So for the next to do this in correct order, Download fail2SQL and then follow RobinC howto along side when activating Fail2SQL.

First we download fail2sql from http://fail2sql.sourceforge.net/ and banhammer from http://static.byteme.org.uk/banhammer.tar.gz and untar them, i usually use /usr/src/ with my project files.

You should now get fail2sql and banhammer folder. The banhammer folder we move to webserver for me it would be

But just move it so that you can reach it with webbrowser. When we have moved banhammer folder to apache root we continue with fail2sql but keep an eye on RobinC howto as we are going to make changes in the fail2ban.sql file and fail2sql script.

In the fail2sql folder edit the fail2ban.sql file and add ‘timestamp’ DATETIME, before PRIMARY KEY row.

This is how our fail2ban.sql should look like now. And also we should have at this point an user and database for us to work with if not click here for a quick howto (ADD LINK AND HOWTO). Its time to import table structure to our database.

We should also move the fail2sql folder somwhere where we have scripts and program for me i moved fail2sql folder to /usr/local/bin/ . Now lets go to /usr/local/bin/fail2sql/ folder and there edit the fail2sql script. Modify line 58 so that it looks like this.

And line 61 should look like this

So the only addition is NOW() in the end of both queries.

Now to that fail2sql should be ok we move on to configuring fail2ban to call on the script when its banning an IP.

in action.d folder you find all the conf files where we are going to add the call to script fail2sql. The command we want to use is:

Under every conf file there is a line stating

we add command to call fail2sql when fail2ban blocks an IP. I have added my script call to all configuration files belonging to iptables. For more configuration and usage of fail2sql you find an README in your fail2sql folder.

Now in your fail2sql folder run command to update the geolocation database.

Now fail2sql should be working and we should restart the fail2ban service

fail2ban should now operate as normal and call fail2sql on everyban you can check fail2sql table either my loggin into mysql and check the table or run command:

Should give you something like this.

ssh(22/tcp): 123.123.123.123 | Count: 1 | Geo: , India
ssh(22/tcp):123.123.123.123 | Count: 1 | Geo: Tsuen Wan, Hong Kong
ssh(22/tcp):123.123.123.123 | Count: 1 | Geo: Tokyo, Japan

Ok fail2ban now operates with fail2sql and populates the database with information so we can continue with the banhammer web staistics, for the google maps you need API key from google to make it work. Check here for information https://developers.google.com/maps/documentation/javascript/tutorial.

So we have extracted the banhammer.tar.gz and moved the banhammer folder under apache root. now go to that folder which is usually /var/www/banhammer or /var/www/html/banhammer. There we want to first edit the file dbinfo.php. Edit the rows to match your configuration towards your MySQL setup. Also notice first row is faulty, missing <?php

and index.html the line

Here you need to obtain your own API key from google for the google map to work. Now if you point your browser to http://server/banhammer/ you should see something similar altho the screenshot below is few hours after running fail2ban on my server.

banhammer_screenshot

Noticed an error in maps.js file after a while Top 5 countries shows all countries so the loop has no limit but that can be fixed by editing maps.js under banhammer folder and changing the line 38 to look like this

 

 

Now we have gotten fail2ban and fail2sql populating SQL for banhammer so that we can have statistics on the network, Next project for me will be to have fail2ban provide me the option to permanently ban an repeat attacker and also have rsylslog to gather logs from all my Mikrotik switchen. so that i can have fail2ban working on all my devices. After that change that mikrotik will get information from fail2ban and block the IP’s directly in my main switch rather than only blocking them locally on my server behind the gateway.

2 thoughts to “Populating mySQL with fail2ban information and banhammer”

  1. please help
    I’ve configured jail.local on [ssh] and I’ve got the ip blocked and the logs in /var/log/auth.log have been read, but the fail2sql database is still empty. .
    what is less than my configuration?
    thank you

    1. Sorry for really late reply, have been moving and new job on the other side of the country so haven’t been able to check in here (strangely enough, WP didnt notify me about an reply by email) incase you still need help.

      can you run “/usr/local/fail2sql/fail2sql ” manually to add and ip to the SQL database?

Leave a Reply

Your email address will not be published. Required fields are marked *