Bro log into MySQL

Hongjie Xin hxin at anr.mcnc.org
Fri Nov 14 11:03:17 PST 2003


Manu,

Thanks for sharing the code.
Attached is my simple perl/DBI script(dbi-bro.pl) without change
original bro code.

Just pipe the log output to dbi-bro.pl, such as

# bro -r trace.1 ./mypolicy.bro | dbi-bro.pl

But when I use live traffic, I can't pipe or redirect the log output to
the script, even a file. I don't know why. :(



Hongjie

== dbi-bro.pl

#! /usr/bin/perl

use DBI;

# configuration variables
$dbhost='localhost';
$dbname='dbname';
$dbuser='username';
$dbpass='passwd';

$table='tablename';

$dsn = "DBI:mysql:dbname=$dbname;host=$dbhost";

$dbh = DBI->connect($dsn, $dbuser, $dbpass) ||
        die "database error: $DBI::errstr" ;

$sth = $dbh->prepare("INSERT INTO $table
                      VALUES (?,?,?,?,?,?,?,?,?,?)");

$PerlParsingFormat="([\\d|-]+)\.([\\d|-]+) ([^ ]+) ([^ ]+) ([\\d|-]+)
([\\d|-]+) ([^ ]+)
([^ ]+) ([^ ]+) ([^ ]+)";

while (<>) {
        @field=map(/^$PerlParsingFormat/,$_);
        $sth->execute(@field) ;
}

#
#create the table in mysql
#
#CREATE  TABLE log_bro (
#SECONDS int( 10)  NOT  NULL ,
#MICROS int( 6)  NOT  NULL ,
#DURATION NUMERIC( 20, 10  )  NOT  NULL ,
#PROTO VARCHAR( 10 )  NOT  NULL ,
#ORIGBYTES int( 20)  NOT  NULL ,
#RESPBYTES int( 20)  NOT  NULL ,
#LOCALHOST VARCHAR( 100  )  NOT  NULL ,
#REMOTEHOST VARCHAR( 100  )  NOT  NULL ,
#STATE VARCHAR( 5  )  NOT  NULL ,
#FLAGS VARCHAR( 10  )
#)


On Fri, 2003-11-14 at 11:58, manu wrote:
> You can find a patch for the bro "CURRENT" release which enables you to 
> log in a MySQL database at http://manux.rstack.org/bro_mysql.
> 
> It requires the library MySQL++.
> It uses a new bro bif called log_external which logs alerts according to 
> a method passed as an argument.
> 
> With this patch the only supported method is ALERT_LOG_EXTERNAL_SQL 
> which logs in a MySQL database.
> 
> A basic bro script that uses this method is given as an example 
> (policy/test.bro).
> 
> For instance:
> [paul at duncan bro-pub-0.8a48.dev-mysql]$ ./bro -r 
> example-attacks/ftp-site-exec.trace test
> 976284129.459304 0.91239 other-3914 431 0 38.33.11.127 131.243.169.116 SF X
> 976284140.775142 0.567636 other-3915 304 0 38.33.11.127 131.243.169.116 SF X
> ...
> 
> mysql> select * from conn_bro;
> +-----+------------------+----------+-----------------+-----------------+-----------+-----------+--------------+-----------+-----------+-----------+-------+------+------+
> | cid | start_time       | duration | orig_addr       | resp_addr       
> | orig_port | resp_port | addl         | conn_type | orig_size | 
> resp_size | state | type | code |
> +-----+------------------+----------+-----------------+-----------------+-----------+-----------+--------------+-----------+-----------+-----------+-------+------+------+
> |   1 | 976284129.459304 |        1 | 38.33.11.127    | 131.243.169.116 
> | 20/tcp    | 3914/tcp  | Hello world! |         0 |       431 |         
> 0 | SF    | 0 |    0 |
> |   2 | 976284140.775142 |        1 | 38.33.11.127    | 131.243.169.116 
> | 20/tcp    | 3915/tcp  | Hello world! |         0 |       304 |         
> 0 | SF    |
> ...
> 
> Other methods should come out : RRD and SPOs(Snort Plugin Output); If 
> anyone has already done that, I would like to share from you.
> 
> Feed back would be appreciated.
> 
> Manu
> 
> 
> "Anton Chuvakin, Ph.D." wrote:
>  >
>  > >I am wondering whether anyone has tried direct bro log into mysql 
> table or not.
>  > Well, what do you mean by "log"? All contents of all files or alert.log
>  > contents only?
>  >
>  > I was thinking of doing the same thing for MySQL logging, but there seems
>  > to be little value in that: the logs are pretty much free form text 
> and no
>  > sensible schema can be designed. RDBMS will be just as good as a plain
>  > text file...
>  >
>  > >Otherwise I will create simple perl/DBI interface by myself.
>  > Do share the code, if/when its created.
>  >
>  > --
>  > Anton Chuvakin, Ph.D., GCIA, GCIH
>  > Senior Security Analyst
>  > Product Management Group
>  > netForensics -  http://www.netForensics.com
>  > 732-393-6071
> 





More information about the Bro mailing list