contrib/ossec2mysql.pl

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