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