File /etc/fail2ban/fail2ban.prune.sqlite.commands:
delete from bans where timeofban <= strftime('%s', date('now', '-90 days'));
vacuum;
.quit
From cli:
sqlite3 /var/lib/fail2ban/fail2ban.sqlite3 <
/etc/fail2ban/fail2ban.prune.sqlite.commands
Can schedule in cron too.
Bill
On 12/15/2017 7:08 AM, Bill Shirley wrote:
Don't forget the 'vacuum' command:
-rw-------. 1 root root 164M Dec 15 06:56 fail2ban.sqlite3
sqlite3 /var/lib/fail2ban/fail2ban.sqlite3
sqlite> delete from bans where timeofban <= strftime('%s', '2016-07-25');
sqlite> vacuum;
sqlite> .quit
-rw-------. 1 root root 76M Dec 15 07:02 fail2ban.sqlite3
Bill
On 12/15/2017 6:11 AM, Admin Beckspaced wrote:
Hello Mark,
thanks for your reply. Also going to top post here and close the topic ;)
I also found that link about sqlite db actually never gets purged by fail2ban.
https://github.com/fail2ban/fail2ban/issues/1316
Did play around a bit with the cron command they provide in that post:
python -c "import sys, logging; logging.basicConfig(stream=sys.stdout, level=logging.INFO); from fail2ban.server.database
import Fail2BanDb; db = Fail2BanDb('/var/lib/fail2ban/fail2ban.sqlite3'); db.purge()"
But be careful it purges the complete database so all data gets lost!
you could instead delete older entries, e.g. older than 90 days, only.
These are the command I ran in the sqlite cli.
perhaps this might help someone?
thanks & greetings
Becki
linux:~ # sqlite3
sqlite> .open /var/lib/fail2ban/fail2ban.sqlite3
sqlite> .database
seq name file
--- --------------- ----------------------------------------------------------
0 main /var/lib/fail2ban/fail2ban.sqlite3
sqlite> .tables
bans fail2banDb jails logs
sqlite> .schema bans
CREATE TABLE bans(jail TEXT NOT NULL, ip TEXT, timeofban INTEGER NOT NULL, data JSON, FOREIGN KEY(jail) REFERENCES
jails(name) );
CREATE INDEX bans_jail_timeofban_ip ON bans(jail, timeofban);
CREATE INDEX bans_jail_ip ON bans(jail, ip);
CREATE INDEX bans_ip ON bans(ip);
sqlite> SELECT count(*) from bans;
sqlite> SELECT count(ip) FROM bans WHERE timeofban <= strftime('%s',
date('now', '-90 days'));
sqlite> DELETE FROM bans WHERE timeofban <= strftime('%s', date('now', '-90
days'));
On 14.12.2017 20:33, Mark Costlow wrote:
I have been looking at dbpurgeage here recently as well. Unfortunately
I don't have an answer for you, just more questions.
We've never set it to a specific value, so it is at the default
of 86400. However, our sqlite data file does not seem to ever
have entries purged from the bans table. On one set of machines
where fail2ban was first set up in March 2015, the entries go back
to then. On another set initialized about 7 months ago, they
go back 7 months.
Both of these setups are using recidive jails, in addition to several
"normal" jails. They are all working fine. We were trying to troubleshoot
why they take a very long time to shut down and start up. The months/years
of cruft in the bans table seems to be the answer ... if we trim
that table shutdown/startup is much faster.
One set of these is running 0.9.3 on gentoo linux, the other set is running
0.9.6 on FreeBSD.
I just found this thread says stock fail2ban doesn't implement the
purge at all, and suggests you would need to add a cron job to do
so: https://github.com/fail2ban/fail2ban/issues/1316
I think we are going to just add a cron job to purge the table periodically.
Mark
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Fail2ban-users mailing list
Fail2ban-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/fail2ban-users
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Fail2ban-users mailing list
Fail2ban-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/fail2ban-users
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Fail2ban-users mailing list
Fail2ban-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/fail2ban-users
