contrib/ossec2mysql.pl

   1 #!/usr/bin/perl -w
   2 use Socket;
   3 use POSIX 'setsid';
   4 use strict;
   5 use Regexp::IPv6 qw($IPv6_re);
   6 # ---------------------------------------------------------------------------
   7 # Author: Meir Michanie (meirm@riunx.com)
   8 # Co-Author: J.A.Senger (jorge@br10.com.br)
   9 # $Id$
  10 # ---------------------------------------------------------------------------
  11 # http://www.riunx.com/
  12 # ---------------------------------------------------------------------------
  13 #
  14 # ---------------------------------------------------------------------------
  15 # About this script
  16 # ---------------------------------------------------------------------------
  17 #
  18 # "Ossec to Mysql" records the OSSEC HIDS alert logs in MySQL database.
  19 # It can run as a daemon (ossec2mysqld.pl), recording in real-time the logs in database or
  20 # as a simple script (ossec2mysql.pl).
  21 #
  22 # ---------------------------------------------------------------------------
  23 # Prerequisites
  24 # ---------------------------------------------------------------------------
  25 #
  26 # MySQL Server
  27 # Perl DBD::mysql module
  28 # Perl DBI module
  29 #
  30 # ---------------------------------------------------------------------------
  31 # Installation steps
  32 # ---------------------------------------------------------------------------
  33 #
  34 # 1) Create new database
  35 # 2a) Run ossec2mysql.sql to create MySQL tables in your database
  36 # 2b) Create BASE tables with snort tables extention
  37 # 3) Create a user to access the database;
  38 # 4) Copy ossec2mysql.conf to /etc/ossec2mysql.conf with 0600 permissions
  39 # 3) Edit /etc/ossec2mysql.conf according to your configuration:
  40 #       dbhost=localhost
  41 #       database=ossecbase
  42 #       debug=5
  43 #       dbport=3306
  44 #       dbpasswd=mypassword
  45 #       dbuser=ossecuser
  46 #       daemonize=0
  47 #       resolve=1
  48 #
  49 #
  50 # ---------------------------------------------------------------------------
  51 # License
  52 # ---------------------------------------------------------------------------
  53 #
  54 # This program is free software; you can redistribute it and/or
  55 # modify it under the terms of the GNU General Public License
  56 # as published by the Free Software Foundation; either version 2
  57 # of the License, or (at your option) any later version.
  58 #
  59 # This program is distributed in the hope that it will be useful,
  60 # but WITHOUT ANY WARRANTY; without even the implied warranty of
  61 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  62 # GNU General Public License for more details.
  63 #
  64 # You should have received a copy of the GNU General Public License
  65 # along with this program; if not, write to the Free Software
  66 # Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
  67 #
  68 # ---------------------------------------------------------------------------
  69 # About OSSEC HIDS
  70 # ---------------------------------------------------------------------------
  71 #
  72 # OSSEC HIDS is an Open Source Host-based Intrusion Detection System.
  73 # It performs log analysis and correlation, integrity checking,
  74 # rootkit detection, time-based alerting and active response.
  75 # http://www.ossec.net
  76 #
  77 # ---------------------------------------------------------------------------
  78
  79 # ---------------------------------------------------------------------------
  80 # Parameters
  81 # ---------------------------------------------------------------------------
  82 $SIG{TERM} = sub { &gracefulend('TERM')};
  83 $SIG{INT} = sub { &gracefulend('INT')};
  84 my ($RUNASDAEMON)=0;
  85 my ($DAEMONLOGFILE)='/var/log/ossec2mysql.log';
  86 my ($DAEMONLOGERRORFILE) = '/var/log/ossec2mysql.err';
  87 my ($LOGGER)='ossec2mysql';
  88 use ossecmysql;
  89
  90 my %conf;
  91 $conf{dbhost}='localhost';
  92 $conf{database}='snort';
  93 $conf{debug}=5;
  94 $conf{dbport}='3306';
  95 $conf{dbpasswd}='password';
  96 $conf{dbuser}='user';
  97 $conf{daemonize}=0;
  98 $conf{sensor}='sensor';
  99 $conf{hids_interface}='ossec';
 100 $conf{resolve}=1;
 101
 102
 103 my($OCT) = '(?:25[012345]|2[0-4]\d|1?\d\d?)';
 104
 105 my($IP) = $OCT . '\.' . $OCT . '\.' . $OCT . '\.' . $OCT . '\|' . $IPv6_re;
 106
 107 my $VERSION="0.4";
 108 my $sig_class_id=1;
 109 &help() unless @ARGV;
 110 my $dump=0;
 111 my ($hids_id,$hids,$hids_interface,$last_cid)=(undef, 'localhost', 'ossec',0);
 112 my ($tempvar,$VERBOSE)=(0,0);
 113 # ---------------------------------------------------------------------------
 114 # Arguments parsing
 115 # ---------------------------------------------------------------------------
 116 while (@ARGV){
 117         $_= shift @ARGV;
 118         if (m/^-d$|^--daemon$/){
 119                 $conf{daemonize}=1;
 120         }elsif ( m/^-h$|^--help$/){
 121                 &help();
 122         }elsif ( m/^-n$|^--noname$/){
 123                 $conf{'resolve'}=0;
 124         }elsif ( m/^-v$|^--verbose$/){
 125                  $VERBOSE=1;
 126         }elsif ( m/^--interface$/){
 127                 $conf{hids_interface}= shift @ARGV if @ARGV; # ossec-rt/ossec-feed
 128         }elsif ( m/^--sensor$/){
 129                 $conf{sensor}= shift @ARGV if @ARGV; # monitor
 130         }elsif ( m/^--conf$/){
 131                 $conf{conf}= shift @ARGV if @ARGV; # localhost
 132                 &loadconf(\%conf);
 133         }elsif ( m/^--dbhost$/){
 134                 $conf{dbhost}= shift @ARGV if @ARGV; # localhost
 135         }elsif ( m/^--dbport$/){
 136                 $conf{dbport}= shift @ARGV if @ARGV; # localhost
 137         }elsif ( m/^--dbname$/){
 138                 $conf{database}= shift @ARGV if @ARGV; # snort
 139         }elsif ( m/^--dbuser$/){
 140                 $conf{dbuser}= shift @ARGV if @ARGV; # root
 141         }elsif ( m/^--dbpass$/){
 142                 $conf{dbpasswd}= shift @ARGV if @ARGV; # monitor
 143         }
 144
 145 }
 146 if ($conf{dbpasswd}=~ m/^--stdin$/){
 147         print "dbpassword:";
 148         $conf{dbpasswd}=<>;
 149         chomp $conf{dbpasswd};
 150 }
 151 $hids=$conf{sensor} if exists($conf{sensor});
 152 $hids_interface=$conf{hids_interface} if exists($conf{hids_interface});
 153
 154 &daemonize() if $conf{daemonize};
 155 my $dbi= ossecmysql->new(%conf) || die ("Could not connect to $conf{dbhost}:$conf{dbport}:$conf{database} as $conf{dbpasswd}\n");
 156 ####
 157 # SQL vars;
 158 my ($query,$numrows,$row_ref);
 159 ####
 160 #get sensor id
 161 $query= 'select sid,last_cid from sensor where hostname=? and interface=?';
 162 $numrows= $dbi->execute($query,$hids,$hids_interface);
 163 if (1==$numrows){
 164         $row_ref=$dbi->{sth}->fetchrow_hashref;
 165         $hids_id=$row_ref->{sid};
 166         $last_cid=$row_ref->{last_cid};
 167 }else{
 168         $query="INSERT INTO sensor ( sid , hostname , interface , filter , detail , encoding , last_cid )
 169 VALUES (
 170 NULL , ?, ? , NULL , ? , ?, ?
 171 )";
 172         $numrows= $dbi->execute($query,$hids,$hids_interface,1,2,0);
 173         $hids_id=$dbi->lastid();
 174 }
 175 $dbi->{sth}->finish;
 176 &forceprintlog ("SENSOR:$hids; feed:$hids_interface; id:$hids_id; last cid:$last_cid");
 177
 178 my $newrecord=0;
 179 my %stats;
 180 my %resolv;
 181 my ($timestamp,$sec,$mail,$date,$alerthost,$alerthostip,$datasource,$rule,$level,$description,
 182         $srcip,$dstip,$user,$text)=();
 183 my $lasttimestamp=0;
 184 my $delta=0;
 185 ########################################################
 186 my $datepath=`date "+%Y/%b/ossec-alerts-%d.log"`;
 187 my $LOG='/var/ossec/logs/alerts/'. $datepath;
 188 chomp $LOG;
 189 &taillog($last_cid,$LOG);
 190 ################################################################
 191 sub forceprintlog(){
 192         $tempvar=$VERBOSE;
 193         $VERBOSE=1;
 194         &printlog (@_);
 195         $VERBOSE=$tempvar;
 196 }
 197
 198
 199 sub taillog {
 200    my ($last_cid,$LOG)=@_;
 201    while (<>) {
 202         if (m/^$/){
 203                 $newrecord=1;
 204                 next unless $timestamp;
 205                 $alerthostip=$alerthost if $alerthost=~ m/^$IP$/;
 206                 if ($alerthostip){
 207                         $dstip=$alerthostip;
 208                         $resolv{$alerthost}=$dstip;
 209                 }else{
 210                         if (exists $resolv{$alerthost}){
 211                                 $dstip=$resolv{$alerthost};
 212                         }else{
 213                                 if ($conf{'resolve'}){
 214                                         $dstip=`host $alerthost 2>/dev/null | grep 'has address\|has IPv6 address' `;
 215                                         if ($dstip =~m/($IP)/ ){
 216                                                 $dstip=$1;
 217                                         }else{
 218                                                 $dstip=$srcip;
 219                                         }
 220                                 }else{
 221                                         $dstip=$alerthost;
 222                                 }
 223                                 $resolv{$alerthost}=$dstip;
 224
 225                         }
 226                 }
 227                 #
 228                 $last_cid= &prepair2basedata(
 229                         $hids_id,
 230                         $last_cid,
 231                         $timestamp,
 232                         $sec,
 233                         $mail,
 234                         $date,
 235                         $alerthost,
 236                         $datasource,
 237                         $rule,
 238                         $level,
 239                         $description,
 240                         $srcip,
 241                         $dstip,
 242                         $user,
 243                         $text
 244                 );
 245                 ($timestamp,$sec,$mail,$date,$alerthost,$alerthostip,$datasource,$rule,$level,$description,
 246                 $srcip,$dstip,$user,$text)=();
 247                 next ;
 248         }
 249         if (m/^\*\* Alert ([0-9]+).([0-9]+):(.*)$/){
 250                 $timestamp=$1;
 251                 if ( $timestamp == $lasttimestamp){
 252                         $delta++;
 253                 }else{
 254                         $delta=0;
 255                         $lasttimestamp=$timestamp;
 256                 }
 257                 $sec=$2;
 258                 $mail=$3;
 259                 $mail=$mail ? $mail : 'nomail';
 260 #2006 Aug 29 17:19:52 firewall -> /var/log/messages
 261 #2006 Aug 30 11:52:14 192.168.0.45->/var/log/secure
 262 #2006 Sep 12 11:12:16 92382-Snort1 -> 172.16.176.132
 263 #
 264         }elsif ( m/^([0-9]+\s\w+\s[0-9]+\s[0-9]+:[0-9]+:[0-9]+)\s+(\S+)\s*->(.*)$/){
 265                 $date=$1;
 266                 $alerthost=$2;
 267                 $datasource=$3;
 268                 if ($datasource=~ m/($IP)/){
 269                         $alerthost=$1;
 270                         $datasource="remoted";
 271                 }
 272
 273
 274 #2006 Aug 29 17:33:31 (recepcao) 10.0.3.154 -> syscheck
 275         }elsif ( m/^([0-9]+\s\w+\s[0-9]+\s[0-9]+:[0-9]+:[0-9]+)\s+\((.*?)\)\s+(\S+)\s*->(.*)$/){
 276                 $date=$1;
 277                 $alerthost=$2;
 278                 $alerthostip=$3;
 279                 $datasource=$4;
 280         }elsif ( m/^([0-9]+\s\w+\s[0-9]+\s[0-9]+:[0-9]+:[0-9]+)\s(.*?)$/){
 281                 $date=$1;
 282                 $alerthost='localhost';
 283                 $datasource=$2;
 284         }elsif ( m/Rule: ([0-9]+) \(level ([0-9]+)\) -> (.*)$/ ){
 285                 $rule=$1;
 286                 $level=$2;
 287                 $description= $3;
 288         }elsif ( m/Src IP:/){
 289                 if ( m/Src IP: (\S+)/){
 290                         $srcip=$1;
 291                 }else{
 292                         $srcip='';
 293                 }
 294         }elsif ( m/User: (.*)$/){
 295                 $user=$1;
 296         }elsif( m/(.*)$/){
 297                 $text .=$1;
 298         }
 299
 300
 301    } # End while read line
 302 }
 303
 304
 305 sub prepair2basedata(){
 306         my (
 307                 $hids_id,
 308                 $last_cid,
 309                 $timestamp,
 310                 $sec,
 311                 $mail,
 312                 $date,
 313                 $alerthost,
 314                 $datasource,
 315                 $rule,
 316                 $level,
 317                 $description,
 318                 $srcip,
 319                 $dstip,
 320                 $user,
 321                 $text
 322         )=@_;
 323         my ($count,$query,$row_ref,$sig_id);
 324 ###
 325 #
 326 # Get/Set signature id
 327         $query = "SELECT sig_id FROM signature where sig_name=? and sig_class_id=? and sig_priority=? and sig_rev=? and sig_sid=? and sig_gid is NULL";
 328         $dbi->execute($query,$description,1,$level,0,$rule);
 329         $count=$dbi->{sth}->rows;
 330         if ($count){
 331                 $row_ref=$dbi->{sth}->fetchrow_hashref;
 332                 $sig_id=$row_ref->{sig_id};
 333                 &printlog ("REUSING SIGNATURE\n");
 334         }else{
 335                 $query="INSERT INTO signature ( sig_id , sig_name , sig_class_id , sig_priority , sig_rev , sig_sid , sig_gid )
 336 VALUES (
 337 NULL ,?, ? , ? , ? , ?, NULL
 338 )";
 339                 $dbi->execute($query,$description,1,$level,0,$rule);
 340                 $sig_id = $dbi->lastid();
 341         }
 342 $dbi->{sth}->finish;
 343 &printlog ("SIGNATURE: $sig_id\n");
 344 #######
 345 #
 346 # Set event
 347         $query="INSERT INTO event ( sid , cid , signature , timestamp )
 348 VALUES (
 349 ? , ? , ? ,?
 350 )";
 351         $last_cid++;
 352         $dbi->execute($query,$hids_id,$last_cid,$sig_id,&fixdate2base($date));
 353
 354 &printlog ("EVENT: ($query,$hids_id,$last_cid,$sig_id,&fixdate2base($date)\n");
 355 $dbi->{sth}->finish;
 356 #########
 357 #
 358 # Set acid_event
 359         $query=" INSERT INTO acid_event ( sid , cid , signature , sig_name , sig_class_id , sig_priority , timestamp , ip_src , ip_dst , ip_proto , layer4_sport , layer4_dport )
 360 VALUES (
 361 ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?, ?
 362 ) ";
 363         $dbi->execute($query,$hids_id,$last_cid,$sig_id,$description,1,$level,&fixdate2base($date),$srcip,$dstip,undef,undef,undef);
 364 &printlog ("ACID_EVENT: ($query,$hids_id,$last_cid,$sig_id,$description,1,$level,&fixdate2base($date),$srcip,$dstip,undef,undef)\n");
 365 $dbi->{sth}->finish;
 366
 367 #########
 368 #
 369 #
 370 # Set data
 371         $text = "** Alert $timestamp.$sec:\t$mail\n$date $alerthost -> $datasource\nRule: $rule (level $level) -> $description\nSrc IP: ($srcip)\nUser: $user\n$text";
 372         $query=" INSERT INTO data ( sid , cid , data_payload )
 373 VALUES (
 374 ?,?,?)";
 375         $dbi->execute($query,$hids_id,$last_cid,$text);
 376 &printlog ("DATA: ($query,$hids_id,$last_cid,$text)\n");
 377 $dbi->{sth}->finish;
 378 ##########
 379 #
 380         $query="UPDATE sensor SET last_cid=? where sid=? limit 1";
 381         $numrows= $dbi->execute($query,$last_cid,$hids_id);
 382 # end sub
 383 $dbi->{sth}->finish;
 384 return $last_cid;
 385 }
 386
 387 sub fixdate2base(){
 388         my ($date)=@_;
 389         $date=~ s/ Jan /-01-/;
 390         $date=~ s/ Feb /-02-/;
 391         $date=~ s/ Mar /-03-/;
 392         $date=~ s/ Apr /-04-/;
 393         $date=~ s/ May /-05-/;
 394         $date=~ s/ Jun /-06-/;
 395         $date=~ s/ Jul /-07-/;
 396         $date=~ s/ Aug /-08-/;
 397         $date=~ s/ Sep /-09-/;
 398         $date=~ s/ Oct /-10-/;
 399         $date=~ s/ Nov /-11-/;
 400         $date=~ s/ Dec /-12-/;
 401         $date=~ s/\s$//g;
 402         return $date;
 403 }
 404 sub version(){
 405         print "OSSEC report tool $VERSION\n";
 406         print "Licensed under GPL\n";
 407         print "Contributor Meir Michanie\n";
 408 }
 409
 410 sub help(){
 411         &version();
 412         print "This tool helps you import into base the alerts generated by ossec."
 413         . " More info in the doc directory .\n";
 414         print "Usage:\n";
 415         print "$0 [-h|--help] # This text you read now\n";
 416         print "Options:\n";
 417         print "\t--dbhost <hostname>\n";
 418         print "\t--dbname <database>\n";
 419         print "\t--dbport <[0-9]+>\n";
 420         print "\t--dbpass <dbpasswd>\n";
 421         print "\t--dbuser <dbuser>\n";
 422         print "\t-d|--daemonize\n";
 423         print "\t-n|--noname\n";
 424         print "\t-v|--verbose\n";
 425         print "\t--conf <ossec2based-config>\n";
 426         print "\t--sensor <sensor-name>\n";
 427         print "\t--interface <ifname>\n";
 428
 429         exit 0;
 430 }
 431
 432
 433 sub daemonize {
 434         chdir '/'               or die "Can't chdir to /: $!";
 435         open STDIN, '/dev/null' or die "Can't read /dev/null: $!";
 436         open STDOUT, ">>$DAEMONLOGFILE"
 437                                or die "Can't write to $DAEMONLOGFILE: $!";
 438         defined(my $pid = fork) or die "Can't fork: $!";
 439         if ($pid){
 440                 open (PIDFILE , ">/var/run/ossec2base2.pid") ;
 441                 print PIDFILE "$pid\n";
 442                 close (PIDFILE);
 443                 exit 0;
 444         }
 445         setsid                  or die "Can't start a new session: $!";
 446         open STDERR, ">>$DAEMONLOGERRORFILE" or die "Can't write to $DAEMONLOGERRORFILE: $!";
 447 }
 448
 449 sub gracefulend(){
 450         my ($signal)=@_;
 451         &forceprintlog ("Terminating upon signal $signal");
 452         &forceprintlog ("Daemon halted");
 453         close STDOUT;
 454         close STDERR;
 455         exit 0;
 456 }
 457
 458 sub printlog(){
 459         return unless $VERBOSE;
 460         my (@lines)=@_;
 461         foreach my $line(@lines){
 462                 chomp $line;
 463                 my ($date)=scalar localtime;
 464                 $date=~ s/^\S+\s+(\S+.*\s[0-9]{1,2}:[0-9]{1,2}:[0-9]{1,2}).*$/$1/;
 465                 print "$date $LOGGER: $line\n";
 466         }
 467 }
 468
 469
 470 sub loadconf(){
 471         my ($hash_ref)=@_;
 472         my $conf=$hash_ref->{conf};
 473         unless (-f $conf) { &printlog ("ERROR: I can't find config file $conf"); exit 1;}
 474         unless (open ( CONF , "$conf")){ &printlog ("ERROR: I can't open file $conf");exit 1;}
 475         while (<CONF>){
 476                 next if m/^$|^#/;
 477                 if ( m/^(\S+)\s?=\s?(.*?)$/) {
 478                         $hash_ref->{$1} = $2;
 479                 }
 480         }
 481         close CONF;
 482 }
 483