Access Fail2ban Database, /var/lib/fail2ban/fail2ban.sqlite3

vim ~/.sqliterc

.mode column
.headers on
.nullvalue NULL
attach "/var/lib/fail2ban/fail2ban.sqlite3" as fail2ban;

ls -lrth /var/lib/fail2ban/fail2ban.sqlite3

sudo chown :SystemAdmin /var/lib/fail2ban/fail2ban.sqlite3
sudo chmod g+r /var/lib/fail2ban/fail2ban.sqlite3

assign yourself into group: SystemAdmin


sqlite3















sqlite> .databases
seq  name             file
---  ---------------  ----------------------------------------------------------
0    main
2    fail2ban         /var/lib/fail2ban/fail2ban.sqlite3
sqlite> .tables
fail2ban.bans        fail2ban.jails
fail2ban.fail2banDb  fail2ban.logs

sqlite> SELECT jail, ip, strftime('%Y-%m-%d %H:%M:%S', timeofban, 'unixepoch' ) AS 'DateTime of ban', timeofban FROM fail2ban.bans ORDER BY timeofban DESC LIMIT 3;

jail|ip|DateTime of ban|timeofban
sshd|xxx.xx.xxx.xxx|2019-04-03 09:05:21|1554282321
sshd|xxx.xx.xxx.xxx|2019-04-03 09:04:01|1554282241
sshd|xx.xxx.xx.xx|2019-04-03 09:03:48|1554282228

Ban perform by month

sqlite> SELECT strftime('%Y-%m', timeofban, 'unixepoch' ) AS 'Date', COUNT( 1 ) FROM fail2ban.bans GROUP BY strftime('%Y-%m', timeofban, 'unixepoch' );

Date|COUNT( 1 )
2019-01|6078
2019-02|10906
2019-03|2428
2019-04|571

Ban performed by week of the year

sqlite> SELECT strftime('%W', timeofban, 'unixepoch' ) AS 'Week of Year', strftime('%Y-%m', timeofban, 'unixepoch' ) AS 'Date', COUNT( 1 ) FROM fail2ban.bans GROUP BY strftime('%Y-%m', timeofban, 'unixepoch' ), strftime('%W', timeofban, 'unixepoch' );
Week of Year  Date        COUNT( 1 )
------------  ----------  ----------
01            2019-01     1074
02            2019-01     1502
03            2019-01     2005
04            2019-01     1497
04            2019-02     1218
05            2019-02     1490
06            2019-02     3719
07            2019-02     3272
08            2019-02     1207
08            2019-03     229
09            2019-03     1258
10            2019-03     941
13            2019-04     577

Ban performed on current month of the year

2019-Apr-05_am105521
sqlite> SELECT strftime('%W', timeofban, 'unixepoch' ) AS 'Week of Year', strftime('%Y-%m-%d', timeofban, 'unixepoch' ) AS 'Date', COUNT( 1 ) FROM fail2ban.bans WHERE strftime('%Y-%m', timeofban, 'unixepoch' ) = strftime('%Y-%m', DATE('now') ) GROUP BY strftime('%Y-%m-%d', timeofban, 'unixepoch' ), strftime('%W', timeofban, 'unixepoch' );
Week of Year  Date        COUNT( 1 )
------------  ----------  ----------
13            2019-04-01  205
13            2019-04-02  290
13            2019-04-03  174
13            2019-04-04  13
13            2019-04-05  5

List of ip with count of banned.

SELECT ip, COUNT( 1 ) FROM fail2ban.bans GROUP BY ip  ORDER BY COUNT( 1 ) DESC;

List of ip with count of banned more than twice.

SELECT ip, COUNT( 1 ) FROM fail2ban.bans GROUP BY ip HAVING COUNT( 1 ) > 2 ORDER BY COUNT( 1 );

List of Top 50 ip with most count of banned.

SELECT ip, COUNT( 1 ) FROM fail2ban.bans GROUP BY ip HAVING COUNT( 1 ) > 2 ORDER BY COUNT( 1 ) DESC LIMIT 50;

Total count for distinct ip

SELECT COUNT( 1 ) FROM (SELECT DISTINCT( ip ) FROM fail2ban.bans );






.quit