1 # SQL script for converting existing agent and alert tables to the new
2 # schema for handling longer IPv6 addresses. This creates two tables
3 # agent_ipv6 and alert_ipv6 which will replace agent and alert.
7 CREATE TABLE IF NOT EXISTS agent_ipv6
9 id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
10 server_id SMALLINT UNSIGNED NOT NULL,
11 last_contact INT UNSIGNED NOT NULL,
12 ip_address VARCHAR(46) NOT NULL,
13 version VARCHAR(32) NOT NULL,
14 name VARCHAR(64) NOT NULL,
15 information VARCHAR(128) NOT NULL,
16 PRIMARY KEY (id, server_id)
19 CREATE TABLE IF NOT EXISTS alert_ipv6
21 id INT UNSIGNED NOT NULL AUTO_INCREMENT,
22 server_id SMALLINT UNSIGNED NOT NULL,
23 rule_id MEDIUMINT UNSIGNED NOT NULL,
24 level TINYINT UNSIGNED,
25 timestamp INT UNSIGNED NOT NULL,
26 location_id SMALLINT UNSIGNED NOT NULL,
29 src_port SMALLINT UNSIGNED,
30 dst_port SMALLINT UNSIGNED,
31 alertid VARCHAR(30) DEFAULT NULL,
33 full_log TEXT NOT NULL,
34 is_hidden TINYINT NOT NULL DEFAULT '0',
35 tld VARCHAR(5) NOT NULL DEFAULT '',
36 PRIMARY KEY (id, server_id),
39 INDEX time (timestamp),
46 select id, server_id, last_contact, inet_ntoa(ip_address),
47 version, name, information
51 select id, server_id, rule_id, level, timestamp, location_id,
52 inet_ntoa(src_ip), inet_ntoa(dst_ip), src_port, dst_port,
53 alertid, user, full_log, is_hidden, tld
56 # Move the converted tables into place:
58 # rename table agent to agent_old;
59 # rename table agent_ipv6 to agent;
61 # rename table alert to alert_old;
62 # rename table alert_ipv6 to alert;
64 # If everything looks ok, the old tables can deleted using:
66 # drop table agent_old;
67 # drop table alert_old;