1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
|
drop database if exists test;
create database test;
USE test;
drop table if exists test_raw;
create table test_raw (
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 test_clean;
CREATE TABLE test_clean (
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 test.* to flowcleaner@localhost identified by 'nil' with grant option;
grant all privileges on test.* to pmacct@localhost identified by 'arealsmartpwd' with grant option;
-- New data that should not be processed in case there is more that should be aggregated together with it.
INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
VALUES ("123.123.123.124", "12.12.12.12", 123, 321, 80, 80, 3, 300, "0-200", NOW());
INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
VALUES ("123.123.123.123", "12.12.12.12", 123, 321, 80, 80, 3, 300, "0-200", NOW());
INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
VALUES ("123.123.123.125", "12.12.12.12", 123, 321, 80, 80, 3, 300, "0-200", NOW());
INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
VALUES ("123.123.123.126", "12.12.12.12", 123, 321, 80, 80, 3, 300, "0-200", NOW());
INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
VALUES ("124.123.123.127", "13.12.12.12", 123, 321, 80, 80, 3, 300, "0-200", NOW());
INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
VALUES ("124.123.123.122", "13.12.12.12", 123, 321, 80, 80, 3, 300, "0-200", NOW());
-- Old data that should be processed
INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
VALUES ("123.123.123.122", "12.12.12.12", 123, 321, 80, 80, 3, 300, "0-200", "2008-12-01 12:23:32");
INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
VALUES ("123.123.123.124", "12.12.12.12", 123, 321, 80, 80, 3, 300, "0-200", "2008-12-01 12:23:32");
INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
VALUES ("123.123.123.125", "12.12.12.12", 123, 321, 80, 80, 3, 300, "0-200", "2008-12-01 12:23:32");
INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
VALUES ("123.123.123.121", "12.12.12.12", 123, 321, 80, 80, 3, 300, "0-200", "2008-12-01 12:23:32");
INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
VALUES ("124.123.123.126", "13.12.12.12", 123, 321, 80, 80, 3, 300, "0-200", "2008-12-01 12:23:32");
INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
VALUES ("124.123.123.123", "13.12.12.13", 123, 321, 80, 80, 3, 300, "0-200", "2008-12-01 12:23:32");
|
