SQLite and fail2ban
- April 30th, 2015
- Posted in Documentation
- Write comment
OS: CentOS7
I wanted to see exactly how and where the fail2ban IP addresses were stored. Looking in the fail2ban.conf file, I found the following:
dbfile = /var/lib/fail2ban/fail2ban.sqlite3
So, I did a little research to try to find out how access the database.
To open or connect to the database:
# sqlite3 /var/lib/fail2ban/fail2ban.sqlite3
To list all the tables in the database:
sqlite> .tables
bans fail2banDb jails logs
To query a table:
sqlite> SELECT * FROM logs;
Another table:
sqlite> SELECT * FROM bans;
To disconnect from the database:
sqlite> .quit
Very good. I wanted to do the same. I wanted to look at some of the rows in the ban table. There was a column “timeofban” which obviously held the unix time integer, but for some reason I’ve been unable to print out that column as a date. I was hoping to find some sample queries of that table which brought me here. I tried
select jail, ip, strftime(“%Y-%m-%d %H:%M:%S”, timeofban, “localtime”) , bancount from bans;
which worked except that the timeofban column was empty. I also tried it without the “localtime” argument. Still returned an empty field. Oh well, I’ll keep looking for some samples …
@Marnix A. van Ammers
This is what I was able use to get the result: select jail, ip, DATETIME(timeofban, ‘unixepoch’) , bancount from bans;
I think this will give you the result you are looking for.