From dc95a1e6bdd0032eb0646ef3e1463e0843519618 Mon Sep 17 00:00:00 2001 From: =?utf8?q?Roland=20H=C3=A4der?= Date: Thu, 28 Jul 2016 10:21:46 +0200 Subject: [PATCH] Updated database MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Signed-off-by: Roland Häder --- install/install.sql | 11 ++++++----- 1 file changed, 6 insertions(+), 5 deletions(-) diff --git a/install/install.sql b/install/install.sql index b3cd077..82a0bad 100644 --- a/install/install.sql +++ b/install/install.sql @@ -20,7 +20,7 @@ CREATE TABLE IF NOT EXISTS `ctracker_config` ( ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `ctracker_config` (`ctracker_config`, `ctracker_db_version`, `ctracker_min_sleep`, `ctracker_max_sleep`, `ctracker_alert_user`, `ctracker_language`) VALUES -(1, 7, 10, 30, 'Y', 'en'); +(1, 8, 10, 30, 'Y', 'en'); DROP TABLE IF EXISTS `ctracker_data`; CREATE TABLE IF NOT EXISTS `ctracker_data` ( @@ -30,8 +30,9 @@ CREATE TABLE IF NOT EXISTS `ctracker_data` ( `user_agent` tinytext NOT NULL COMMENT 'User agent string', `get_data` tinytext NOT NULL COMMENT 'Serialized GET data', `post_data` tinytext NOT NULL COMMENT 'Serialized POST data', - `check_worm` tinytext NOT NULL COMMENT 'Serialized GET data', - `check_post` tinytext NOT NULL COMMENT 'Serialized GET data', + `check_ua` tinytext NOT NULL COMMENT 'User-agent string after check', + `check_get` tinytext NOT NULL COMMENT 'GET data after check', + `check_post` tinytext NOT NULL COMMENT 'POST data after check', `server_name` tinytext COMMENT 'Server''s host name', `script_name` varchar(255) DEFAULT NULL COMMENT 'Full script name', `referer` varchar(255) NOT NULL COMMENT 'Referer', @@ -105,7 +106,7 @@ CREATE TABLE IF NOT EXISTS `view_spreaded_attacks` ( ,`user_agent` tinytext ,`server_name` tinytext ,`get_data` tinytext -,`check_worm` tinytext +,`check_get` tinytext ,`proxy_used` enum('Y','N') ,`first_attempt` timestamp ,`last_attempt` timestamp @@ -138,7 +139,7 @@ DROP TABLE IF EXISTS `view_proxy_ip_usage`; CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_proxy_ip_usage` AS select sum(`ctracker_data`.`count`) AS `total_proxy_ips`,count(`ctracker_data`.`id`) AS `distinct_proxy_ips` from `ctracker_data` where (`ctracker_data`.`remote_addr` <> `ctracker_data`.`proxy_addr`); DROP TABLE IF EXISTS `view_spreaded_attacks`; -CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_spreaded_attacks` AS select (unix_timestamp(`ctracker_data`.`last_attempt`) - unix_timestamp(`ctracker_data`.`first_attempt`)) AS `time_diff`,`ctracker_data`.`id` AS `id`,`ctracker_data`.`remote_addr` AS `remote_addr`,`ctracker_data`.`user_agent` AS `user_agent`,`ctracker_data`.`server_name` AS `server_name`,`ctracker_data`.`get_data` AS `get_data`,`ctracker_data`.`check_worm` AS `check_worm`,`ctracker_data`.`proxy_used` AS `proxy_used`,`ctracker_data`.`first_attempt` AS `first_attempt`,`ctracker_data`.`last_attempt` AS `last_attempt`,`ctracker_data`.`count` AS `count` from `ctracker_data` group by `ctracker_data`.`remote_addr` order by (unix_timestamp(`ctracker_data`.`last_attempt`) - unix_timestamp(`ctracker_data`.`first_attempt`)) desc; +CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_spreaded_attacks` AS select (unix_timestamp(`ctracker_data`.`last_attempt`) - unix_timestamp(`ctracker_data`.`first_attempt`)) AS `time_diff`,`ctracker_data`.`id` AS `id`,`ctracker_data`.`remote_addr` AS `remote_addr`,`ctracker_data`.`user_agent` AS `user_agent`,`ctracker_data`.`server_name` AS `server_name`,`ctracker_data`.`get_data` AS `get_data`,`ctracker_data`.`check_get` AS `check_get`,`ctracker_data`.`proxy_used` AS `proxy_used`,`ctracker_data`.`first_attempt` AS `first_attempt`,`ctracker_data`.`last_attempt` AS `last_attempt`,`ctracker_data`.`count` AS `count` from `ctracker_data` group by `ctracker_data`.`remote_addr` order by (unix_timestamp(`ctracker_data`.`last_attempt`) - unix_timestamp(`ctracker_data`.`first_attempt`)) desc; DROP TABLE IF EXISTS `view_total_attacks`; CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_total_attacks` AS select sum(`ctracker_data`.`count`) AS `total_attacks`,count(`ctracker_data`.`id`) AS `distinct_attacks`,max(`ctracker_data`.`count`) AS `max_attack_repeat` from `ctracker_data` limit 1; -- 2.39.5