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
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
|
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;
-- New data that should not be processed in case there is more that should be aggregated together with it.
-- Should give wrong number of rows if it is 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.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.124", "73.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.124", "12.12.12.12", 123, 319, 77, 78, 3, 301, "0-200", NOW());
-- Old data that should be processed
-- First set of data
INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
VALUES ("1.1.1.1", "2.2.2.2", 1, 2, 5, 10, 5, 3, "0-199", "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 ("1.1.1.1", "2.2.2.2", 1, 2, 5, 10, 6, 3, "0-199", "2008-12-01 12:23:33");
INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
VALUES ("1.1.1.1", "2.2.2.2", 1, 2, 5, 10, 7, 3, "0-199", "2008-12-01 12:23:34");
INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
VALUES ("1.1.1.1", "2.2.2.2", 1, 2, 5, 10, 9, 3, "200-299", "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 ("1.1.1.1", "2.2.2.2", 1, 2, 5, 10, 1, 3, "200-299", "2008-12-01 12:23:33");
INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
VALUES ("1.1.1.1", "2.2.2.2", 2, 3, 5, 10, 7, 3, "200-299", "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 ("2.2.2.2", "1.1.1.1", 2, 3, 5, 10, 6, 3, "200-299", "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 ("2.2.2.2", "1.1.1.1", 2, 3, 6, 10, 1, 3, "200-299", "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 ("2.2.2.2", "1.1.1.1", 2, 3, 6, 11, 0, 3, "200-299", "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 ("2.2.2.2", "1.1.1.1", 2, 4, 6, 11, 4, 3, "200-299", "2008-12-01 12:23:32");
-- Second set of data
INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
VALUES ("1.1.1.1", "2.2.2.2", 1, 2, 5, 10, 5, 3, "0-199", "2008-12-01 12:28:13");
INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
VALUES ("1.1.1.1", "2.2.2.2", 1, 2, 5, 10, 6, 3, "0-199", "2008-12-01 12:28:14");
INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
VALUES ("1.1.1.1", "2.2.2.2", 1, 2, 5, 10, 7, 3, "0-199", "2008-12-01 12:28:15");
INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
VALUES ("1.1.1.1", "2.2.2.2", 1, 2, 5, 10, 9, 3, "200-299", "2008-12-01 12:28:13");
INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
VALUES ("1.1.1.1", "2.2.2.2", 1, 2, 5, 10, 1, 3, "200-299", "2008-12-01 12:28:14");
INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
VALUES ("1.1.1.1", "2.2.2.2", 2, 3, 5, 10, 7, 3, "200-299", "2008-12-01 12:28:13");
INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
VALUES ("2.2.2.2", "1.1.1.1", 2, 3, 5, 10, 6, 3, "200-299", "2008-12-01 12:28:13");
INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
VALUES ("2.2.2.2", "1.1.1.1", 2, 3, 6, 10, 1, 3, "200-299", "2008-12-01 12:28:13");
INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
VALUES ("2.2.2.2", "1.1.1.1", 2, 3, 6, 11, 0, 3, "200-299", "2008-12-01 12:28:13");
INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
VALUES ("2.2.2.2", "1.1.1.1", 2, 4, 6, 11, 4, 3, "200-299", "2008-12-01 12:28:13");
-- Third set of data
INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
VALUES ("1.1.1.1", "2.2.2.2", 1, 2, 5, 10, 5, 3, "0-199", "2008-12-01 12:29:59");
INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
VALUES ("1.1.1.1", "2.2.2.2", 1, 2, 5, 10, 6, 3, "0-199", "2008-12-01 12:29:58");
INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
VALUES ("1.1.1.1", "2.2.2.2", 1, 2, 5, 10, 7, 3, "0-199", "2008-12-01 12:29:57");
INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
VALUES ("1.1.1.1", "2.2.2.2", 1, 2, 5, 10, 9, 3, "200-299", "2008-12-01 12:29:59");
INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
VALUES ("1.1.1.1", "2.2.2.2", 1, 2, 5, 10, 1, 3, "200-299", "2008-12-01 12:29:58");
INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
VALUES ("1.1.1.1", "2.2.2.2", 2, 3, 5, 10, 7, 3, "200-299", "2008-12-01 12:29:59");
INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
VALUES ("2.2.2.2", "1.1.1.1", 2, 3, 5, 10, 6, 3, "200-299", "2008-12-01 12:29:59");
INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
VALUES ("2.2.2.2", "1.1.1.1", 2, 3, 6, 10, 1, 3, "200-299", "2008-12-01 12:29:59");
INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
VALUES ("2.2.2.2", "1.1.1.1", 2, 3, 6, 11, 0, 3, "200-299", "2008-12-01 12:29:59");
INSERT INTO test_raw (ip_src, ip_dst, as_src, as_dst, port_src, port_dst, packets, bytes, pkt_len_distrib, stamp_inserted)
VALUES ("2.2.2.2", "1.1.1.1", 2, 4, 6, 11, 4, 3, "200-299", "2008-12-01 12:29:59");
|
