tools/prads2db.pl

   1 #!/usr/bin/perl -w
   2 # ----------------------------------------------------------------------
   3 # prads2db.pl
   4 # Copyright (C) 2011-2012, Edward Fjellskål <edwardfjellskaal@gmail.com>
   5 #
   6 # This program is free software; you can redistribute it and/or modify
   7 # it under the terms of the GNU General Public License as published by
   8 # the Free Software Foundation; either version 2 of the License, or
   9 # (at your option) any later version.
  10 #
  11 # This program is distributed in the hope that it will be useful,
  12 # but WITHOUT ANY WARRANTY; without even the implied warranty of
  13 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  14 # GNU General Public License for more details.
  15 #
  16 # You should have received a copy of the GNU General Public License
  17 # along with this program; if not, write to the Free Software
  18 # Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
  19 # ----------------------------------------------------------------------
  20
  21 use strict;
  22 use warnings;
  23 use POSIX qw(setsid);
  24 use DateTime;
  25 use Getopt::Long qw/:config auto_version auto_help/;
  26 use DBI;
  27
  28 =head1 NAME
  29
  30  prads2db.pl - Load prads assets into DB
  31
  32 =head1 VERSION
  33
  34 0.2.0
  35
  36 =head1 SYNOPSIS
  37
  38  $ prads2db.pl [options]
  39
  40   OPTIONS:
  41
  42    --file         : set the prads-asset.log file to read from
  43    --daemon       : enables daemon mode
  44    --debug        : enable debug messages (default: 0 - disabled)
  45    --help         : this help message
  46    --version      : show prads2db.pl version
  47
  48 =cut
  49
  50 our $VERSION       = 0.1;
  51 our $DEBUG         = 0;
  52 our $DAEMON        = 0;
  53 our $TIMEOUT       = 1;
  54 my  $PFILE         = "/var/log/prads-asset.log";
  55 my  $LOGFILE       = q(/var/log/prads2db.log);
  56 my  $PIDFILE       = q(/var/run/prads2db.pid);
  57 our $DB_NAME       = "prads";
  58 our $DB_HOST       = "127.0.0.1";
  59 our $DB_PORT       = "3306";
  60 our $DB_USERNAME   = "prads";
  61 our $DB_PASSWORD   = "prads";
  62 our $DBI           = "DBI:mysql:$DB_NAME:$DB_HOST:$DB_PORT";
  63 our $TABLE_NAME    = "prads";
  64 our $AUTOCOMMIT    = 0;
  65
  66 GetOptions(
  67    'file=s'        => \$PFILE,
  68    'debug'         => \$DEBUG,
  69    'daemon'        => \$DAEMON,
  70 );
  71
  72 # Signal handlers
  73 use vars qw(%sources);
  74 #$SIG{"HUP"}   = \&recreate_merge_table;
  75 $SIG{"INT"}   = sub { game_over() };
  76 $SIG{"TERM"}  = sub { game_over() };
  77 $SIG{"QUIT"}  = sub { game_over() };
  78 $SIG{"KILL"}  = sub { game_over() };
  79 #$SIG{"ALRM"}  = sub { dir_watch(); alarm $TIMEOUT; };
  80
  81 warn "[*] Starting prads2db.pl\n";
  82
  83 # Prepare to meet the world of Daemons
  84 if ( $DAEMON ) {
  85    print "[*] Daemonizing...\n";
  86    chdir ("/") or die "chdir /: $!\n";
  87    open (STDIN, "/dev/null") or die "open /dev/null: $!\n";
  88    open (STDOUT, "> $LOGFILE") or die "open > $LOGFILE: $!\n";
  89    defined (my $dpid = fork) or die "fork: $!\n";
  90    if ($dpid) {
  91       # Write PID file
  92       open (PID, "> $PIDFILE") or die "open($PIDFILE): $!\n";
  93       print PID $dpid, "\n";
  94       close (PID);
  95       exit 0;
  96    }
  97    setsid ();
  98    open (STDERR, ">&STDOUT");
  99 }
 100
 101 our $dbh;
 102 # Connect to the DB
 103 connect_db();
 104 # Setup the prads table, if not exist
 105 setup_db();
 106
 107 # Start file_watch() which looks for new dns data and puts them into db
 108 warn "[*] Looking for passive DNS data in file: $PFILE\n";
 109 file_watch($PFILE);
 110 exit;
 111
 112 =head1 FUNCTIONS
 113
 114 =head2 setup_db
 115
 116  Checks if the pdns table exists, if not make it.
 117
 118 =cut
 119
 120 sub setup_db {
 121
 122    if (checkif_table_exist($TABLE_NAME)) {
 123       return;
 124    } else {
 125       if (new_table($TABLE_NAME)) {
 126          die "[E] Table $TABLE_NAME does not exist, and we could not create it! Sorry!\n";
 127       }
 128    }
 129 }
 130
 131 =head2 file_watch
 132
 133  This sub looks for new DNS data in a file.
 134  Takes $filename to watch as input.
 135
 136 =cut
 137
 138 sub file_watch {
 139    my $logfile = shift;
 140    my $startsize = 0;
 141    my $pos = 0;
 142    #infinite loop
 143    while (1) {
 144       $startsize = (stat $logfile)[7];
 145
 146       if (defined $startsize) {
 147           if (!defined $pos) {
 148              # Initial run.
 149              $pos = $startsize;
 150           }
 151
 152           if ($startsize < $pos) {
 153              # Log rotated
 154              #parseLogfile ($rotlogfile, $pos, (stat $rotlogfile)[7]);
 155              $pos = 0;
 156           }
 157
 158           parseLogfile ($logfile, $pos, $startsize);
 159           $pos = $startsize;
 160       }
 161       sleep $TIMEOUT;
 162    }
 163 }
 164
 165 sub parseLogfile {
 166     my ($fname, $start, $stop) = @_;
 167     open (LOGFILE, $fname) or return;
 168     seek (LOGFILE, $start, 0) or exit 2;
 169
 170     LINE:
 171     while (tell (LOGFILE) < $stop) {
 172        my $line =<LOGFILE>;
 173        chomp ($line);
 174        next if ( not defined $line || $line =~ /^asset,vlan,port,proto/);
 175        #my @elements = split/\|\|/,$line;
 176
 177        # 211.211.211.1,0,44163,6,SYN,[S4:56:1:60:M1460,S,T,N,W7:.:Linux:2.6 (newer, 7):link:ethernet/modem:uptime:1597hrs],8,1320426783
 178        # 211.211.211.1,0,0,0,ARP,[00:90:7F:3E:AF:94,(Watchguard)],0,1300470771
 179        if ($line =~ /^([\w\.:]+),([\d]{1,4}),([\d]{1,5}),([\d]{1,3}),(\S+?),\[(.*)\],([\d]{1,3}),(\d{10})$/) {
 180           my ($ip, $vlan, $port, $proto, $service, $meta, $dist, $ts) = ($1, $2, $3, $4, $5, $6, $7, $8);
 181           put_asset_to_db($ip, $vlan, $port, $proto, $service, $meta, $dist, $ts);
 182        } else {
 183             warn "[*] Error: Not valid prads format encountered: '$fname'";
 184             next LINE;
 185        }
 186     }
 187     close(LOGFILE);
 188 }
 189
 190 sub put_asset_to_db {
 191     # 208.115.111.68,0,44163,6,SYN,[S4:56:1:60:M1460,S,T,N,W7:.:Linux:2.6 (newer, 7):link:ethernet/modem:uptime:1597hrs],8,1320426783
 192     my ($ip, $vlan, $port, $proto, $service, $meta, $dist, $ts) = @_;
 193     #my $quoted_meta = $dbh->quote($meta);
 194     $meta =~ s/(')/\\$1/g;
 195     $meta =~ s/:uptime:\d+hrs//; # removes the uptime which changes
 196     my ($sql, $sth);
 197
 198     eval{
 199       $sql = qq[
 200              INSERT INTO $TABLE_NAME (
 201                IP,VLAN,PORT,PROTO,SERVICE,META,DIST,FIRST_SEEN,LAST_SEEN
 202              ) VALUES (
 203                '$ip','$vlan','$port','$proto','$service','$meta','$dist',FROM_UNIXTIME($ts),FROM_UNIXTIME($ts)
 204              ) ON DUPLICATE KEY UPDATE LAST_SEEN=FROM_UNIXTIME($ts)
 205              ];
 206       warn "$sql\n" if $DEBUG;
 207
 208       connect_db();
 209
 210       $sth = $dbh->prepare($sql);
 211       $sth->execute;
 212       $sth->finish;
 213    };
 214    if ($@) {
 215       # Failed
 216       warn "$sql\n";
 217       return 1;
 218    }
 219    return 0;
 220 }
 221
 222 sub connect_db {
 223     while (not defined $dbh) {
 224       print "[*] Connecting to database...\n";
 225       if ($dbh = DBI->connect($DBI,$DB_USERNAME,$DB_PASSWORD, {RaiseError => 0})) {
 226           print "[*] Re-connection to DB OK...\n";
 227       } else {
 228           print "[E] $DBI::errstr\n";
 229           print "[*] Sleeping for 60sec\n";
 230           sleep 60;
 231       }
 232     }
 233 }
 234
 235 sub new_table {
 236    my ($tablename) = shift;
 237    my ($sql, $sth);
 238    warn "[*] Creating $TABLE_NAME...\n";
 239    eval{
 240       $sql = "                                                      \
 241         CREATE TABLE IF NOT EXISTS $tablename                       \
 242         (                                                           \
 243         ID            BIGINT(20) UNSIGNED  NOT NULL AUTO_INCREMENT, \
 244         IP            varchar(39)          NOT NULL DEFAULT   '',   \
 245         VLAN          int(4)               NOT NULL DEFAULT  '0',   \
 246         PORT          int(5)               NOT NULL DEFAULT  '0',   \
 247         PROTO         int(3)               NOT NULL DEFAULT  '0',   \
 248         SERVICE       varchar(20)          NOT NULL DEFAULT   '',   \
 249         META          varchar(255)         NOT NULL DEFAULT   '',   \
 250         DIST          int(3)               NOT NULL DEFAULT  '0',   \
 251         FIRST_SEEN    DATETIME             NOT NULL,                \
 252         LAST_SEEN     DATETIME             NOT NULL,                \
 253         PRIMARY KEY (ID),                                           \
 254         UNIQUE KEY MARQ (IP,VLAN,PROTO,SERVICE,META),               \
 255         KEY ip_idx (IP),                                            \
 256         KEY service_idx (SERVICE),                                  \
 257         KEY meta_idx (META)                                         \
 258         )                                                           \
 259       ";
 260       $sth = $dbh->prepare($sql);
 261       $sth->execute;
 262       $sth->finish;
 263    };
 264    if ($@) {
 265       # Failed
 266       return 1;
 267    }
 268    return 0;
 269 }
 270
 271 =head2 checkif_table_exist
 272
 273  Checks if a table exists. Takes $tablename as input and
 274  returns 1 if $tablename exists, and 0 if not.
 275
 276 =cut
 277
 278 sub checkif_table_exist {
 279     my $tablename = shift;
 280     my ($sql, $sth);
 281     eval {
 282        $sql = "select count(*) from $TABLE_NAME where 1=0";
 283        $dbh->do($sql);
 284     };
 285     if ($dbh->err) {
 286        warn "[W] Table $TABLE_NAME does not exist.\n" if $DEBUG;
 287        return 0;
 288     }
 289     else{
 290        return 1;
 291     }
 292 }
 293
 294 =head2 game_over
 295
 296  Terminates the program in a sainfull way.
 297
 298 =cut
 299
 300 sub game_over {
 301     warn "[*] Terminating...\n";
 302     $dbh->disconnect;
 303     unlink ($PIDFILE);
 304     exit 0;
 305 }
 306
 307 =head1 AUTHOR
 308
 309  Edward Fjellskaal <edwardfjellskaal@gmail.com>
 310
 311 =head1 COPYRIGHT
 312
 313  This library is free software, you can redistribute it and/or modify
 314  it under the terms of GPL; either version 2 of the License, or
 315  (at your option) any later version.
 316
 317 =cut