Fail2ban: save your log into mysql and show it

Required:

• fail2ban 0.9
• mysql
• web server with php (apache with php and mysql library)

Create user and its database in mysql. Give all privilege to its database for its user. You can use any existing database, here is only sample of database structures:
database name : myf2b
table name: kci_logipv4

No	field	datatype
1	logdate	datetime
2	logipv4	int(11)
3	logmsg	varchar(1000)
4	kci_category	int(11)
5	id	(int11)
6	codecontinent	char(2)
7	codecontinent2	char(2)
8	codecontinent3	char(3)

table name: kci_category

No	field	datatype
1	id	int(11)
2	category	varchar(20)

Note:

1. We store IPv4 in long.
2. Field with underline is primary key 

Table kci_logipv4 will be used to store any log from trapped in fail2ban, and table kci_category will be used to categorize all log in type of attack. Populate kci_category with your wish, this is my category for example: 

id	category
10	SSH
20	FTP
30	HTTP/HTTPS
40	SMTP/POP/IMAP/POP3/S

We need a small application to store any log trapped in fail2ban. I use PHP to do that. Here is kci_log.php source code https://github.com/dedetok/fail2ban-to-mysql/blob/gh-pages/kci_log.php
That's all. Now you create a custom action mlocaldb.conf for fail2ban to call kci_log.php. Put mlocaldb.conf in /etc/fail2ban/action.d/, here is mlocaldb.conf https://github.com/dedetok/fail2ban-to-mysql/blob/gh-pages/mlocaldb%2Cconf
Note: You need to change this part 'http://[your_domain]/kci_log.php' >> /home/[user]/logs/curlfail2ban.log

• 'http://[your_domain]/kci_log.php' where kci_log.php reside
• /home/[user]/logs/curlfail2ban.log where the log will be store. You can remove it after you confidence.

The final step, edit your /etc/fail2ban/jail.conf and add a line to use mlocaldb at the end of action, for example:
...
[sshd]

port    = ssh
logpath = %(sshd_log)s
backend = %(sshd_backend)s
enabled = true
filter = sshd
action = iptables-ipset-proto4[name=sshd]
        mlocaldb[category=10]
        abuseipdb[category=4,18,22] 
...
Note change category with id you inserted into table kci_category. For example 20 for proftpd.
Show it in your web. This is kci_logread.php source code to show the log, feel free to modify it . https://github.com/dedetok/fail2ban-to-mysql/blob/gh-pages/kci_logread.php

See on Github https://github.com/dedetok/fail2ban-to-mysql