diff options
Diffstat (limited to 'dbSetup')
| -rw-r--r-- | dbSetup/dbSetup.mysql | 41 | ||||
| -rw-r--r-- | dbSetup/dbtestdata.sql | 11 |
2 files changed, 52 insertions, 0 deletions
diff --git a/dbSetup/dbSetup.mysql b/dbSetup/dbSetup.mysql new file mode 100644 index 0000000..45ed9aa --- /dev/null +++ b/dbSetup/dbSetup.mysql @@ -0,0 +1,41 @@ +USE pmacct; + +drop database if exists pmacct; +create database pmacct; +use pmacct; + +drop table if exists acct; + create table acct ( + ip_src CHAR(39) NOT NULL, + ip_dst CHAR(39) NOT NULL, + as_src INT(4) UNSIGNED NOT NULL, + as_dst INT(4) UNSIGNED NOT NULL, + port_src INT(2) UNSIGNED NOT NULL, + port_dst INT(2) UNSIGNED NOT NULL, + packets INT UNSIGNED NOT NULL, + bytes INT UNSIGNED NOT NULL, + pkt_len_distrib CHAR(10) NOT NULL, + stamp_inserted DATETIME NOT NULL, + stamp_updated DATETIME, + stamp_processed DATETIME, + PRIMARY KEY (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, pkt_len_distrib, stamp_inserted) +); + +DROP TABLE IF EXISTS clean_data; + +CREATE TABLE clean_data ( + ipb_src CHAR(39) NOT NULL, + ipb_dst CHAR(39) NOT NULL, + as_src INT(4) UNSIGNED NOT NULL, + as_dst INT(4) UNSIGNED NOT NULL, + port_src INT(2) UNSIGNED NOT NULL, + port_dst INT(2) UNSIGNED NOT NULL, + occurences INT UNSIGNED NOT NULL, + volume CHAR(10) NOT NULL, + time_added DATETIME NOT NULL, + time_privatized DATETIME, + UNIQUE idx (ipb_src, ipb_dst, as_src, as_dst, port_src, port_dst, volume, time_added) +); + +grant all privileges on pmacct.* to flowcleaner@localhost identified by 'nil' with grant option; +grant all privileges on pmacct.* to pmacct@localhost identified by 'arealsmartpwd' with grant option; diff --git a/dbSetup/dbtestdata.sql b/dbSetup/dbtestdata.sql new file mode 100644 index 0000000..7f877f1 --- /dev/null +++ b/dbSetup/dbtestdata.sql @@ -0,0 +1,11 @@ +-- New data that should not be processed in case there is more that should be aggregated together with it. +INSERT INTO raw_data (ip_src, ip_dst, time, port, packet_size) VALUES ("123.123.123.123", "12.12.12.12", NOW(), 80, 255); +INSERT INTO raw_data (ip_src, ip_dst, time, port, packet_size) VALUES ("123.123.123.123", "12.12.12.12", NOW(), 80, 255); +INSERT INTO raw_data (ip_src, ip_dst, time, port, packet_size) VALUES ("45.45.45.45", "12.12.12.12", NOW(), 80, 255); + +-- Old data that should be processed +INSERT INTO raw_data (ip_src, ip_dst, time, port, packet_size) VALUES ("63.63.63.63", "73.73.73.73", '2008-12-01 12:00:00', 80, 200); +INSERT INTO raw_data (ip_src, ip_dst, time, port, packet_size) VALUES ("63.63.63.63", "73.73.73.73", '2008-12-01 12:00:00', 80, 200); +INSERT INTO raw_data (ip_src, ip_dst, time, port, packet_size) VALUES ("8.8.8.8", "11.73.73.73", '2008-12-01 12:00:00', 80, 200); +INSERT INTO raw_data (ip_src, ip_dst, time, port, packet_size) VALUES ("63.63.63.63", "73.73.73.73", '2008-12-01 12:00:00', 80, 200); +INSERT INTO raw_data (ip_src, ip_dst, time, port, packet_size) VALUES ("100.63.63.63", "8.8.8.8", '2008-12-01 12:00:00', 80, 200); |