Meaningful plots from connection statistics (using conn2db2csv.pl)
Now that I have the infrastrucutre for asking meaningful queries, here’s what I could be interesting in. These can be nicely scripted and shown on the webpage using CGI (all these are sliding windows with no input parameters, thus no worries about SQL injections).
BTW: I know this SQL is ugly like hell, but the framwork is really flexible and these queries are very easy to write. Here they go:
Last 3 days’s connections on known ports (hourly):
./conn2db2csv.pl -q "select date_trunc('hour',time),
coalesce(sum(case when dport=80 or dport =443 then brecv else NULL end),0) as r80,
coalesce(sum(case when dport=80 or dport = 443 then bsent else NULL end),0) as s80,
coalesce(sum(case when dport=22 or dport=7322 then brecv else NULL end),0) as r22,
coalesce(sum(case when dport=22 or dport=7322 then bsent else NULL end),0) as s22,
coalesce(sum(case when dport=993 then brecv else NULL end),0) as r993,
coalesce(sum(case when dport=993 then bsent else NULL end),0) as s993,
coalesce(sum(case when dport=53 then brecv else NULL end),0) as r53,
coalesce(sum(case when dport=53 then bsent else NULL end),0) as s53,
coalesce(sum(case when dport=25 or dport = 587 then brecv else NULL end),0) as r25,
coalesce(sum(case when dport=25 or dport = 587 then bsent else NULL end),0) as s25
from conn where dip='85.10.194.212' and time > now()-'3 days'::interval group by 1 having(sum(brecv) >0) order by 1" -d "dbi:Pg:dbname=bro;host=localhost;port=54321" -u bro -p <password> -s ' '
Last half year’s connection on known ports (daily) :
./conn2db2csv.pl -q "select date_trunc('day',time),
coalesce(sum(case when dport=80 or dport =443 then brecv else NULL end),0) as r80,
coalesce(sum(case when dport=80 or dport = 443 then bsent else NULL end),0) as s80,
coalesce(sum(case when dport=22 or dport=7322 then brecv else NULL end),0) as r22,
coalesce(sum(case when dport=22 or dport=7322 then bsent else NULL end),0) as s22,
coalesce(sum(case when dport=993 then brecv else NULL end),0) as r993,
coalesce(sum(case when dport=993 then bsent else NULL end),0) as s993,
coalesce(sum(case when dport=53 then brecv else NULL end),0) as r53,
coalesce(sum(case when dport=53 then bsent else NULL end),0) as s53,
coalesce(sum(case when dport=25 or dport = 587 then brecv else NULL end),0) as r25,
coalesce(sum(case when dport=25 or dport = 587 then bsent else NULL end),0) as s25
from conn where dip='85.10.194.212' and time > now()-'6 months'::interval group by 1 having(sum(brecv) >0) order by 1" -d "dbi:Pg:dbname=bro;host=localhost;port=54321" -u bro -p <password> -s ' '
The number of incoming connections and total bytes sent and received on all server ports (in a week):
./conn2db2csv.pl -q "select dport, count(*), coalesce(sum(bsent),0), coalesce(sum(brecv),0)
from conn where dip='85.10.194.212' and time > now()-'1 week'::interval group by 1 having (sum(brecv)>0) order by 1" -d "dbi:Pg:dbname=bro;host=localhost;port=54321" -u bro -p <password> -s ' '
Similarly, might be interesting what the server contacted:
./conn2db2csv.pl -q "select dport, count(*), coalesce(sum(bsent),0), coalesce(sum(brecv),0)
from conn where sip='85.10.194.212' and time > now()-'1 week'::interval group by 1 having (sum(brecv)>0) order by 1" -d "dbi:Pg:dbname=bro;host=localhost;port=54321" -u bro -p <password> -s ' '
Finally, scanning attempts:
./conn2db2csv.pl -q "select dport, count(*), coalesce(sum(bsent),0), coalesce(sum(brecv),0)
from conn where dip='85.10.194.212' and time > now()-'1 month'::interval group by 1 having (sum(brecv)) is null order by 1" -d "dbi:Pg:dbname=bro;host=localhost;port=54321" -u bro -p <password> -s ' '
And top port scanners:
./conn2db2csv.pl -q "select sip, count(dport), count(*)
from conn where dip='85.10.194.212' and time > now()-'1 month'::interval group by 1 having (sum(brecv)) is null order by 2 desc" -d "dbi:Pg:dbname=bro;host=localhost;port=54321" -u bro -p <password> -s ' '
Finally how to print such a thing using gnuplot:
set timefmt "%Y-%m-%d %H:%M:%S"
set logscale y
plot '<file>' using 1:3 with l t 'P80', '' using 1:4 with l t 'P22', '' using 1:5 with l t 'P53', '' using 1:6 with l t 'P25'