Menu
▾
▴
#1 add a time field
I made a change to packet2sql i thought you might be interested in. I added a field, called
packet_time, so i can store the date and time seperately. This means that you can do sql queries
which group by the date; to get a one week summary you could:
select count(*) as count, packet_date, interface, chain, sourcehost,destport
from logged_packets
where interface = "ppp0"
and chain = "input"
and packet_date = "2001-09-26"
group by sourcehost
order by packet_date DESC LIMIT 7;
The diffs are:
# diff packet2sql-2.1.0/src/PacketSieve.cpp PacketSieve.cpp
177,178c177,178
< << (nday<10?"0":"") << nday << "'," << endl
< << setw(8) << "'" << stime << "'," << endl
---
> << (nday<10?"0":"") << nday << " "
> << stime << "'," << endl
# diff packet2sql-2.1.0/sql/logged_packets-ddl.sql logged_packets-ddl.sql
8,9c8
< packet_date date not null,
< packet_time time not null,
---
> packet_date timestamp,
24c23
< PRIMARY KEY (packet_date, packet_time, id, sourcehost, desthost));
---
> PRIMARY KEY (packet_date, id, sourcehost, desthost));
Im not sure if it was possible to do this with the database in the previous form, but i found it easier
this way - and it made for some interesting queries, so i thought i would share :)
