[Bro] Connection records in a database?

[Seth Hall]

On Oct 4, 2008, at 2:53 AM, mel wrote:
> I have something[1] similar written late last year, which parses Bro
> logs and inserts the data to PostgreSQL[2]. I also have an extremely
> alpha version of the web frontend, written in PHP with Symfony  
> framework.

Nice! I'd be interested to take a look at it.  I've been working on  
something similar recently.

I checked out your log importer too, but I noticed that you're doing  
individual inserts for each record.  In my testing, doing individual  
inserts doesn't scale for high data rates, the database can't insert  
data quickly enough.  I have been using the COPY [1] method for  
inserting data in batches and it turns out that even at high data  
rates the database can keep up just fine.

>> I'm going to get started on a C or C++ application soon that will use
>> Broccoli to listen to some event which would be intended for database
>> logging.  You would have to run a Bro script that would throw the
>> database logging event for each connection, but that should be fairly
>> easy to write.  We'll see how far I make it with that. :)
>
> Keep us updated!


On Friday, I got an initial version of my C++ database logger  
functioning. :)  Here's how it will work...

In your bro scripts, you'll call something like this (field names and  
values don't have to have the same name)...
   event db_log("http_logs", [$orig_h=orig_h, $resp_h=resp_h,  
$resp_p=resp_p, $method=method, $url=url]);

The database logger will listen for the db_log event and dynamically  
create the following SQL query...
   COPY http_logs (orig_h, resp_h, resp_p, method, url) FROM STDIN

Every time the db_log event is called for that table, it will send  
another row of data to the database.  Once a certain number of rows  
have been pushed to the database it will end the COPY query and all of  
the data you have already pushed to the database will be inserted.   
The COPY query will then be executed again and the cycle repeats.

For any data you want to insert to a database, all you have to do is  
make sure that your database has the necessary fields in it, then  
throw the proper db_log event.  I'll be releasing the code under the  
BSD license as soon as I get a few more features added to it.

   .Seth

[1] http://www.postgresql.org/docs/current/static/libpq-copy.html#LIBPQ-COPY-SEND

---
Seth Hall
Network Security - Office of the CIO
The Ohio State University
Phone: 614-292-9721