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