-- use innodb database -- this is just a rough draft of how to set the tables up. -- I'm sure it could be impreved a little. -- create database logwatch; use logwatch; -- #DROP TABLE IF EXISTS header; CREATE TABLE header ( source_ip VARCHAR(20) NOT NULL, PRIMARY KEY (source_ip), ip_name VARCHAR(50) NOT NULL, note VARCHAR(40) NOT NULL, host VARCHAR(20) NOT NULL, output_level TINYINT UNSIGNED, city VARCHAR(20), state CHAR(2), cc CHAR(4), country VARCHAR(20), address1 VARCHAR(50), address2 VARCHAR(50), mnt_by VARCHAR(120), t_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE = InnoDB; CREATE TABLE ports ( source_ip VARCHAR(20) NOT NULL, protocol CHAR(4) NOT NULL, packet_count INT UNSIGNED NOT NULL, port INT UNSIGNED NOT NULL, process_date DATE NULL DEFAULT '0000-00-00', FOREIGN KEY (source_ip) REFERENCES header(source_ip) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE = InnoDB; -- this is just a dump or /etc/services into the database CREATE TABLE services ( port_protocol VARCHAR(20) NOT NULL, PRIMARY KEY (port_protocol), name VARCHAR(20) NOT NULL, port SMALLINT UNSIGNED NOT NULL, protocol CHAR(4) NOT NULL, open_freq DOUBLE(7,6) UNSIGNED, aliases VARCHAR(40) NULL, note VARCHAR(40) NULL, t_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE = InnoDB; CREATE TABLE mod_evasive ( source_ip VARCHAR(20) NOT NULL, ip_name VARCHAR(50) NOT NULL, date DATETIME NULL DEFAULT '0000-00-00 00-00-00', protocol CHAR(4) NOT NULL, message VARCHAR(20) NOT NULL ) ENGINE = InnoDB; -- # 'agent' => 'Mozilla/5.0 (X11; Linux x86_64; rv:20.0) Gecko/20100101 Firefox/20.0', -- # 'cmd' => 'POST', -- # 'date' => '2013-05-18', -- # 'gmt' => '-0500', -- # 'length' => '-', -- # 'protocol' => 'HTTP/1.1', -- # 'rcode' => '302', -- # 'source_ip' => '69.128.193.177', -- # 'time' => '14:01:07', -- # 'url' => 'https://www.apriorifarm.com/wp-login.php' CREATE TABLE http_log ( agent VARCHAR(80) NOT NULL, cmd VARCHAR(4) NOT NULL, date DATETIME NULL DEFAULT '0000-00-00 00-00-00', gmt CHAR(5) NOT NULL, length VARCHAR(20) NOT NULL, request VARCHAR(50) NOT NULL, protocol VARCHAR(10) NOT NULL, rcode SMALLINT UNSIGNED NOT NULL, rmessage VARCHAR(40) NOT NULL, source_ip VARCHAR(20) NOT NULL, ip_name VARCHAR(50) NOT NULL, url VARCHAR(80) NOT NULL ) ENGINE = InnoDB;