# Connection records in a database? **URL:** \ **Category:** Zeek\ **Created:** [October 2, 2008, 8:18pm UTC](https://community.zeek.org/t/connection-records-in-a-database/1390 "2008-10-02T20:18:30Z")\ **Posts on this page:** 14\ **Page:** 1
**Author:** ![Randolph\_Reitz](https://avatars.discourse-cdn.com/v4/letter/r/bb73d2/32.png) [@Randolph\_Reitz](https://community.zeek.org/u/Randolph_Reitz)\ **Post date:** [October 2, 2008, 8:18pm UTC](https://community.zeek.org/t/connection-records-in-a-database/1390/1 "2008-10-02T20:18:30Z")
I want to stuff connections records into a relational database (likely postgres). Has anyone done this? My first shot will be to write a simple python process that tails the conn.\* log file and inserts records. I'm wondering if there is a more elegant way to collect and insert connection records? As far as motivation, at FNAL we have a issue tracking system which includes email notification. I would like to use bro to find 'issues' and then create an event in the issue tracking system. The tracking system workflow will resolve a local IP address into a specific machine, find the registered user(s) and send a notification email (informational, warning, critical). It would be useful if this email contained a list of recent connections for the system. This would help the recipient understand what recent computer use caused the network activity that triggered the issue. Hence, having recent connections in a database would be helpful. I think time machine might be too much. Currently I'm thinking of saving a small time period - say a rolling week's worth of connections (or whatever fits). I've previously used splunk ([http://www.splunk.com](http://www.splunk.com)) to suck in connection records for later searches. This worked, however splunk introduced a delay in retrieval that caused problems formatting the notification email. Thanks, Randy Reitz Fermilab ---
**Author:** ![Seth\_Hall](https://avatars.discourse-cdn.com/v4/letter/s/50afbb/32.png) [@Seth\_Hall](https://community.zeek.org/u/Seth_Hall)\ **Post date:** [October 3, 2008, 3:00am UTC](https://community.zeek.org/t/connection-records-in-a-database/1390/2 "2008-10-03T03:00:44Z")
> I want to stuff connections records into a relational database (likely > postgres). Has anyone done this? I don't push my connection records, but I'm pushing a number of my other logs into postgres. > My first shot will be to write a simple python process that tails the > conn.\* log file and inserts records. I'm wondering if there is a more > elegant way to collect and insert connection records? I have a threaded ruby script that uses the "COPY FROM" technique to push blocks of rows into the database. It's still early and messy, but it does work fairly well and it keeps up with a brisk pace of INSERTs. 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. 🙂    .Seth ---
**Author:** ![Stephen\_Chan](https://avatars.discourse-cdn.com/v4/letter/s/a9adbd/32.png) [@Stephen\_Chan](https://community.zeek.org/u/Stephen_Chan)\ **Post date:** [October 3, 2008, 7:06am UTC](https://community.zeek.org/t/connection-records-in-a-database/1390/3 "2008-10-03T07:06:39Z")
Seth Hall wrote: > 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. Hi Seth,     I've got one written already, if you're interested I can send you the source.     Steve ---
**Author:** ![Seth\_Hall](https://avatars.discourse-cdn.com/v4/letter/s/50afbb/32.png) [@Seth\_Hall](https://community.zeek.org/u/Seth_Hall)\ **Post date:** [October 3, 2008, 11:20am UTC](https://community.zeek.org/t/connection-records-in-a-database/1390/4 "2008-10-03T11:20:40Z")
Please! I actually just wrote one which is getting close to working, but I'd be happy to see your implementation.    .Seth ---
**Author:** ![Christopher\_Jay\_Man1](https://avatars.discourse-cdn.com/v4/letter/c/b5e925/32.png) [@Christopher\_Jay\_Man1](https://community.zeek.org/u/Christopher_Jay_Man1)\ **Post date:** [October 3, 2008, 4:26pm UTC](https://community.zeek.org/t/connection-records-in-a-database/1390/5 "2008-10-03T16:26:21Z")
Hi, I have written a similar program in C. It imports over 2 Mill. connection log lines in just about 20 minutes. Other scripted methods, such as via Perl, appear to take a bit more time, CPU and RAM, which is why I chose C. It parses logs (conn.log only right now) from Bro and puts the contents into MySQL. The code is autoconf’ed, so you might want to give it a try. I also include the SQL Table layout I used. I have the code up here: [https://sourceforge.net/projects/bro-tools/](https://sourceforge.net/projects/bro-tools/) HTH Cheers! –Christopher ---
**Author:** ![Seth\_Hall](https://avatars.discourse-cdn.com/v4/letter/s/50afbb/32.png) [@Seth\_Hall](https://community.zeek.org/u/Seth_Hall)\ **Post date:** [October 3, 2008, 4:32pm UTC](https://community.zeek.org/t/connection-records-in-a-database/1390/6 "2008-10-03T16:32:45Z")
I'm not seeing any files there.    .Seth ---
**Author:** ![Christopher\_Jay\_Man1](https://avatars.discourse-cdn.com/v4/letter/c/b5e925/32.png) [@Christopher\_Jay\_Man1](https://community.zeek.org/u/Christopher_Jay_Man1)\ **Post date:** [October 3, 2008, 5:52pm UTC](https://community.zeek.org/t/connection-records-in-a-database/1390/7 "2008-10-03T17:52:58Z")
Hi Seth, My error. I have associated the file with the release at: [http://sourceforge.net/projects/bro-tools/](https://sourceforge.net/projects/bro-tools/). HTH Cheers! –Christopher ---
**Author:** ![mel](https://avatars.discourse-cdn.com/v4/letter/m/da6949/32.png) [@mel](https://community.zeek.org/u/mel)\ **Post date:** [October 4, 2008, 6:53am UTC](https://community.zeek.org/t/connection-records-in-a-database/1390/8 "2008-10-04T06:53:14Z")
Seth Hall wrote: > > My first shot will be to write a simple python process that tails the > > conn.\* log file and inserts records. I'm wondering if there is a more > > elegant way to collect and insert connection records? 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. I stopped working on it (due to work commitment, mainly) after realizing that the best way to do it is by using Broccoli - which up until now I haven't got around to do. > 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! > Seth Hall --mel [1] [http://security.org.my/brologs2db.rb](http://security.org.my/brologs2db.rb) [2] [http://security.org.my/brodb.sql.txt](http://security.org.my/brodb.sql.txt) ---
**Author:** ![Richard\_Bejtlich2](https://avatars.discourse-cdn.com/v4/letter/r/e480ec/32.png) [@Richard\_Bejtlich2](https://community.zeek.org/u/Richard_Bejtlich2)\ **Post date:** [October 4, 2008, 8:22pm UTC](https://community.zeek.org/t/connection-records-in-a-database/1390/9 "2008-10-04T20:22:13Z")
Randy, Can you or anyone else add details on your experiences using Bro with Splunk? I'm considering pairing the two. Thank you, Richard ---
**Author:** ![Vern](https://yyz1.discourse-cdn.com/flex011/user_avatar/community.zeek.org/vern/32/630_2.png) [@Vern](https://community.zeek.org/u/Vern)\ **Post date:** [October 4, 2008, 11:31pm UTC](https://community.zeek.org/t/connection-records-in-a-database/1390/10 "2008-10-04T23:31:41Z")
> I want to stuff connections records into a relational database (likely > postgres). Has anyone done this? Note, we have a significant research project underway for exporting Bro events into a high-performance database for purposes of both forensics and real-time detection of previously described activity. We describe the vision in our recent HotSecurity paper:   [Principles for Developing Comprehensive Network Visibility](http://www.icir.org/vern/papers/awareness-hotsec08/index.html) The underlying technology is partially implemented, but won't be ready for use by others for a good while.     Vern ---
**Author:** ![Seth\_Hall](https://avatars.discourse-cdn.com/v4/letter/s/50afbb/32.png) [@Seth\_Hall](https://community.zeek.org/u/Seth_Hall)\ **Post date:** [October 6, 2008, 3:04pm UTC](https://community.zeek.org/t/connection-records-in-a-database/1390/11 "2008-10-06T15:04:55Z")
> 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] [PostgreSQL: Documentation: 16: 34.10. Functions Associated with the COPY Command](http://www.postgresql.org/docs/current/static/libpq-copy.html#LIBPQ-COPY-SEND) ---
**Author:** ![robin](https://yyz1.discourse-cdn.com/flex011/user_avatar/community.zeek.org/robin/32/599_2.png) [@robin](https://community.zeek.org/u/robin)\ **Post date:** [October 6, 2008, 4:24pm UTC](https://community.zeek.org/t/connection-records-in-a-database/1390/12 "2008-10-06T16:24:37Z")
I like this approach! Robin ---
**Author:** ![Randolph\_Reitz](https://avatars.discourse-cdn.com/v4/letter/r/bb73d2/32.png) [@Randolph\_Reitz](https://community.zeek.org/u/Randolph_Reitz)\ **Post date:** [October 15, 2008, 6:48pm UTC](https://community.zeek.org/t/connection-records-in-a-database/1390/13 "2008-10-15T18:48:27Z")
Yes, individual inserts don't work! Here is the conn.log file on my BRO installation... [brother@dtmb ~]$ s=$(wc -l spool/bro/conn.log | awk '{print $1}'); while true; do sleep 10;s1=$(wc -l spool/bro/conn.log | awk '{print $1}');printf "%d\n" $((s1 - s));s=$s1;done 4750 4728 4565 4243 4926 4379 ^C Looks like conn.log is adding ~450 connections per second. Here is what happens with a python script that tails conn.log and inserts each record into a Postgres DB... [brother@dtmb ~]$ l=$(echo "select count(\*) from bro\_connections" | psql -h nimisrv nimi\_dev | awk '/^ [0-9]/ { print $1}');while true;do sleep 10;n=$(echo "select count(\*) from bro\_connections" | psql -h nimisrv nimi\_dev | awk '/^ [0-9]/ { print $1}');printf "%d\n" $((n-l));l=$n;done 1756 1625 1631 1667 1670 1838 ^C Maybe ~160 records per second. Not even close. It's always nice to know what not to do. Randy ---
**Author:** ![system](https://canada1.discourse-cdn.com/flex011/uploads/zeek/original/1X/f09d732bc2cc7c7cc7e35db67cf4e1d5233ce7a7.png) [@system](https://community.zeek.org/u/system)\ **Post date:** [May 6, 2022, 3:38pm UTC](https://community.zeek.org/t/connection-records-in-a-database/1390/14 "2022-05-06T15:38:40Z")