list_bans

#!/usr/bin/perl

# -----------------------------------------------------------------------------
#
# RBL Updater Suite - a suite to update realtime blocklists for rspamd
# Copyright (C) 2022 - John Bradley (userjack6880)
#
# list_bans 
#   used to list all current bans by the system
#
# Available at: https://github.com/userjack6880/rbl_updater
#
# -----------------------------------------------------------------------------
#
# This file is part of the RBL Updater Suite for use with rspamd
#
# The RBL Updater Suite is free software: you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by the Free 
# Software Foundation, either version 3 of the License, or (at your option) any
# later version.
#
# This program is distributed in the hope that it will be useful, but WITHOUT 
# ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
# FOR A PARTICULAR PURPOSE.  See the GNU General Public License for more 
# details.
#
# You should have received a copy of the GNU General Public License along with 
# this program.  If not, see <https://www.gnu.org/licenses/>.
#
# -----------------------------------------------------------------------------

use strict;
use warnings;

use Getopt::Std;
use File::Tail;
use File::Basename;
use DBI;
use Text::Table;

# version number
my $version = '1 Feature Complete';

# basic settings --------------------------------------------------------------
my %opts = ();
getopts('c:l:h', \%opts);

my $limit     = ($opts{'l'} || 1000);
my $conffile = ($opts{'c'} || 'config.conf');

# usage sub -------------------------------------------------------------------
sub usage {
	print "\n".
	      "\tUsage:\n".
	      "\t\t./list_bans [opts]\n".
	      "\n".
	      "\tThis script shows all current bans and infractions.\n".
          "\n".
	      "\tOptions:\n".
          "\t\t-c\t[confpath]\tLoad Config File\n".
	      "\t\t-l\t[number]\tlimit output of banned IPs\n".
	      "\n";
}

if (defined $opts{'h'}) {
    usage();
    exit;
}

# load conf file --------------------------------------------------------------
our ($asnlist,$iplist,$dbname,$dbhost,$dbport,$dbuser,$dbpass,$log);

if (substr($conffile,0,1) ne '/' and substr($conffile,0,1) ne '.') { $conffile = "./$conffile"; }

if (-e File::Basename::dirname($0)."/$conffile") { $conffile = File::Basename::dirname($0)."/$conffile"; }
unless (-e $conffile) { usage(); die "Error! Could not read $conffile\n"; }


my $conftest = do $conffile;
die "$conffile could not be parsed: $@" if $@;
die "could not do $conffile: $!" if !defined $conftest;

# declare "global" variables --------------------------------------------------
my $dbh;

# Begin Main Code -------------------------------------------------------------

print "\nRBL Updater Suite v.$version\n".
      "\tInfraction and Ban Inspector\n\n".
      "Getting current infractions and bans...\n";

# Open DB connection
$dbh = DBI->connect(
            "DBI:mysql:database=$dbname;host=$dbhost;port=$dbport",
            $dbuser,
            $dbpass
          ) or die "Could not connect to database!\n";

# pull out all IPs that are pemabanned *or* have a ban expiration *after* today
my $sth = $dbh->prepare(q{
  SELECT INET_NTOA(ip4), ip4_net, provider, infractions, ban_expiration, permaban
  FROM ip_blocklist
  WHERE ip4_net NOT IN (SELECT ip4_net FROM ipnet_blocklist WHERE ban_expiration > NOW() OR permaban = 1)
    AND (   ban_expiration > NOW()
         OR permaban = 1 )
  ORDER BY infractions DESC,
           permaban DESC,
           ban_expiration DESC
  LIMIT ?;
});
$sth->execute($limit);

print "\nIP Bans\n";

my $ip_table = Text::Table->new("| IP", "| Network", "| Provider", "| Infractions", "| Ban Expiration", "|");      

while (my @row = $sth->fetchrow_array()) {
	my $ban = $row[4];
	my $provider;
	$ban = 'Permanent' if $row[5] == 1;
	if (defined $row[2]) {
		$provider = $row[2];
	}
	else {
		$provider = 'Unknown';
	}

	$ip_table->load(["| ".$row[0], "| ".$row[1], "| ".$provider, "| ".$row[3], "| ".$ban, "|"]);
}

print $ip_table;

# pull out all the Perfixes that are permabanned *or* have a ban expiration *after* today
$sth = $dbh->prepare(q{
  SELECT ip4_net, asn, provider, infractions, ban_expiration, permaban
  FROM ipnet_blocklist
  WHERE asn NOT IN (SELECT asn FROM asn_blocklist WHERE permaban = 1)
    AND (   ban_expiration > NOW()
         OR permaban = 1 )
  ORDER BY infractions DESC,
           permaban DESC,
           ban_expiration DESC;
});
$sth->execute();

print "\nNetwork Bans\n";

my $net_table = Text::Table->new("| Network", "| ASN", "| Provider", "| Infractions", "| Ban Expiration", "|");

while (my @row = $sth->fetchrow_array()) {
	my $ban = $row[4];
	my $provider;
	$ban = 'Permanent' if $row[5] == 1;
	if (defined $row[2]) {
		$provider = $row[2];
	}
	else {
		$provider = 'Unknown';
	}

	$net_table->load(["| ".$row[0], "| ".$row[1], "| ".$provider, "| ".$row[3], "| ".$ban, "|"]);
}

print $net_table;

# pull out all the ASNs that are permabanned
$sth = $dbh->prepare(q{
  SELECT asn, provider, blocked_ranges, total_ranges, infractions, permaban
  FROM asn_blocklist;
});
$sth->execute();

print "\nASN Bans\n";

my $asn_table = Text::Table->new("| ASN", "| Provider", "| Blocked Ranges", "| Total Ranges", "| Infractions", "| Permaban", "|");

while (my @row = $sth->fetchrow_array()) {
	my $perma = 'No';
	my $provider;
	$perma = 'Yes' if $row[5] == 1;
	if (defined $row[1]) {
		$provider = $row[1];
	}
	else {
		$provider = 'Unknown';
	}

	$asn_table->load(["| ".$row[0], "| ".$provider, "| ".$row[2], "| ".$row[3], "| ".$row[4], "| ".$perma, "|"]);
}

print $asn_table;

# Close DB connection
$dbh->disconnect;