A scratch pad of Bro+PostgreSQL. PostgreSQL has native network-ish types, which is nice for Bro stuff. This code probably shouldn't be used anywhere live but makes for good notes. If you want Bro + Database, check out Brownian by Vlad Grigorescu https://github.com/grigorescu/Brownian Currently only conn.log and dns.log are supported.

gistfile1.txt

su postgres
createuser bro -l -P -E -S -d -R
createdb --owner=bro bro;

psql -h 127.0.0.1 -U bro bro -c "

create table conn(
ts              numeric(20,8) NOT NULL,
uid             VARCHAR(255) PRIMARY KEY,
id_orig_h       CIDR NOT NULL,
id_orig_p       integer NOT NULL,
id_resp_h       CIDR NOT NULL,
id_resp_p       integer NOT NULL,
proto           VARCHAR(255),
service         VARCHAR(255),
duration        float,
orig_bytes      integer,
resp_bytes      integer,
conn_state      VARCHAR(255),
local_orig      VARCHAR(255),
missed_bytes    integer,
history         VARCHAR(255),
orig_pkts       integer,
orig_ip_bytes   integer,
resp_pkts       integer,
resp_ip_bytes   integer,
tunnel_parents  VARCHAR(255)
);

create table dns(
ts              numeric(20,8) NOT NULL,
uid             VARCHAR(255) PRIMARY KEY,
id_orig_h       CIDR NOT NULL,
id_orig_p       integer NOT NULL,
id_resp_h       CIDR NOT NULL,
id_resp_p       integer NOT NULL,
proto           VARCHAR(255) NOT NULL,
trans_id        integer NOT NULL,
query           VARCHAR(255) NOT NULL,
qclass          integer NOT NULL,
qclass_name     VARCHAR(255) NOT NULL,
qtype           integer NOT NULL,
qtype_name      VARCHAR(10) NOT NULL,
rcode           integer,
rcode_name      VARCHAR(255),
AA              boolean,
TC              boolean,
RD              boolean,
RA              boolean,
Z               integer,
answers         VARCHAR(255) ARRAY,
TTLs            integer ARRAY,
rejected        boolean
);

#create table http(
#ts             numeric(20,8) NOT NULL,
#uid             VARCHAR(255) PRIMARY KEY,
#id_orig_h       CIDR NOT NULL,
#id_orig_p       integer NOT NULL,
#id_resp_h       CIDR NOT NULL,
#id_resp_p       integer NOT NULL,
#
#ts      uid     id.orig_h       id.orig_p       id.resp_h       id.resp_p       trans_depth    method  host    uri     referrer        user_agent      request_body_len        response_body_len      status_code     status_msg      info_code       info_msg        filename      tags     username        password        proxied mime_type       md5     extraction_file
#
);

GRANT ALL ON conn TO bro;
GRANT ALL ON dns TO bro;
";

grep -vF '#' conn.log  | psql -h 127.0.0.1 -U bro bro -c "COPY conn from STDIN with delimiter as E'\t' NULL as '-'";
grep -vF '#' dns.log  | psql -h 127.0.0.1 -U bro bro -c "COPY dns from STDIN with delimiter as E'\t' NULL as '-'";