Parsing Bro’s connection logs and writing it into a database
- Create a database table with the following fields:
- sip address
- sport
- dip address
- dport -sbytes -rbytes -conn length
create table conn(sip inet, sport int, dip inet, dport int, rbytes int, sbytes int, length float);
- Use perl one liner: perl -ne ‘ @a = split; print “INSERT INTO conn VALUES(‘”‘”‘$a[2]‘”‘”‘, $a[5], ‘”‘”‘$a[3]‘”‘”‘, $a[6], $a[8], $a[9],$a[1]);\n”;’
BTW need also to replace ? with NULLs — do it with =~ s/// command.
- Combine all of them into a horrible thing: cat conn.log | perl -ne ‘@a = split; $a[8] =~ s/\?/NULL/; $a[9] =~ s/\?/NULL/; $a[1] =~ s/\?/NULL/; print “INSERT INTO conn VALUES(‘”‘”‘$a[2]‘”‘”‘, $a[5], ‘”‘”‘$a[3]‘”‘”‘, $a[6], $a[8], $a[9], $a[1]);\n”;’ | psql test test -h 127.0.0.1