From: Roland Häder <roland@mxchange.org>
Date: Thu, 28 Jul 2016 08:21:46 +0000 (+0200)
Subject: Updated database
X-Git-Url: https://git.mxchange.org/?p=ctracker.git;a=commitdiff_plain;h=5980f641cb37e2dea9bc30d6cfa96d66c09cec33

Updated database

Signed-off-by: Roland Häder <roland@mxchange.org>
---

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;