banip/ban2mysql.pl

#!/usr/bin/perl
use strict;
use warnings;
use DBI;
use DateTime;
use Data::Printer;
use File::Slurp;

use Mojo::UserAgent;

#my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime();
#$year = $year+1900;


# Connect to the database.
my $dbh = DBI->connect("DBI:MariaDB:database=kram;host=edna",
                       "steffen", "66WXRlvF0UUV",
                       {'RaiseError' => 1});

my @file = read_file('/var/log/HWR/kernel.log.1');

my $ua = Mojo::UserAgent->new();

my %nolandforip;
my %nolandips;
my $hundred = 0;
my $first = 0;

my $country = $dbh->selectcol_arrayref('SELECT country, cid FROM banip_country', {Columns=>[1, 2]});
my %coun = @$country;

$nolandips{$first} = ();

for my $l ( @file ) {
    my ( $year, $month, $day, $h, $m, $s, $kat, $src, $dst, $port ) = $l =~ /(\d{4})-(\d{2})-(\d{2})T(..):(..):(..)\+.* HWR kernel:.*banIP\/inbound\/drop\/(.*?):.*SRC=(\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}) DST=(\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}).*DPT=(\d+)/;

    my $dt = '';
    if ( $month ) {
        $day = sprintf "%02d", $day;
        my $mysqldt = "$year-$month-$day $h:$m:$s";
        my $dt1 = DateTime->new(
            year       => $year,
            month      => $month,
            day        => $day,
            hour       => $h,
            minute     => $m,
            second     => $s
        );

        my $id;

        my $da = $dbh->selectrow_hashref('SELECT * FROM banip_attacker WHERE src = ? and port = ?', undef, $src, $port );
        if ( $da ) {
            $id = $da->{id};
            my ( $year2, $month2, $day2, $h2, $m2, $s2) = $da->{updated} =~ /(....)-(..)-(..) (..):(..):(..)/;
            my $dt2 = DateTime->new(
                year       => $year2,
                month      => $month2,
                day        => $day2,
                hour       => $h2,
                minute     => $m2,
                second     => $s2
            );

            my $cmp = DateTime->compare( $dt1, $dt2 );
            if ( $cmp == 1 ) {
                $dbh->do('UPDATE banip_attacker set count = count + 1, updated = ? WHERE src = ? and port = ?', undef, $mysqldt, $src, $port );
            }

            if ( ! $nolandforip{$src} ) {
                if ( !$da->{cid} ) {
                    $nolandforip{$src} = 1;

                    if ( $hundred == 100 ) {
                        $first ++;
                        $nolandips{$first} = ();
                        $hundred = 0;
                    }

                    push @{$nolandips{$first}}, {"query"=> $src, "fields"=> "country,countryCode,query", "lang"=> "de"};
                    $hundred ++;
                }
            }

        } else {

            $dbh->do(
                'INSERT INTO banip_attacker (
                    created,
                    updated,
                    src,
                    dst,
                    port,
                    kat)
                VALUES (
                    ?,
                    ?,
                    ?,
                    ?,
                    ?,
                    ?)', undef,
                    $mysqldt,
                    $mysqldt,
                    $src,
                    $dst,
                    $port,
                    $kat
            );
            $id = $dbh->last_insert_id();

            $nolandforip{$src} = 1;

            if ( $hundred == 100 ) {
                $first ++;
                $nolandips{$first} = ();
                $hundred = 0;
            }

            push @{$nolandips{$first}}, {"query"=> $src, "fields"=> "country,countryCode,query", "lang"=> "de"};
            $hundred ++;
        }

        my $attack_days = $dbh->selectrow_hashref(
            'SELECT
                *
            FROM banip_attacker_days
            WHERE
                id = ? and
                datum = ?',
                undef,
                $id,
                "$year-$month-$day"
        );
        if ( $attack_days ) {
            my ( $h2, $m2, $s2) = $attack_days->{ende} =~ /(..):(..):(..)/;
            my $dt2 = DateTime->new(
                year       => $year,
                month      => $month,
                day        => $day,
                hour       => $h2,
                minute     => $m2,
                second     => $s2
            );

            my $cmp = DateTime->compare( $dt1, $dt2 );
            if ( $cmp == 1 ) {
                $dbh->do(
                    'UPDATE banip_attacker_days set
                        count = count + 1,
                        ende = ?
                    WHERE
                        aid = ?',
                        undef,
                        "$h:$m:$s",
                        $attack_days->{aid}
                );
            }
        } else {
            $dbh->do(
                'INSERT INTO banip_attacker_days (
                    id,
                    datum,
                    count,
                    beginn,
                    ende)
                VALUES (
                    ?,
                    ?,
                    ?,
                    ?,
                    ?
                )', undef,
                    $id,
                    "$year-$month-$day",
                    1,
                    "$h:$m:$s",
                    "$h:$m:$s"
            );
        }
    }
}

# Land über IP von ip-api.com holen batch mit jeweils 100 ips
for my $ar ( keys %nolandips ) {
    sleep(5);
    my $req = $ua->post("http://ip-api.com/batch" => {Accept => '*/*'} => json => $nolandips{$ar} )->result->json;
# p $nolandips{$ar};

    for my $query ( @$req ) {
        my $cid;
        # land in Datenbank bekannt cid zuordnen
        if ( $coun{$query->{country}} ) {
            $cid = $coun{$query->{country}};
        } else {
            # Land nicht bekannt eintragen und neue id zuordnen
            $dbh->do(
                'INSERT INTO banip_country (
                    country,
                    countryCode)
                VALUES (
                    ?,
                    ?)', undef,
                    $query->{country},
                    $query->{countryCode},
            );
            $cid = $dbh->last_insert_id();
            $coun{$query->{country}} = $cid;
        }

        $dbh->do('UPDATE banip_attacker set cid = ?, updated = updated WHERE src = ?', undef, $cid, $query->{query} );
    }
}