From f6f632c58580b123bdc5c8bea75a532de5f0142e Mon Sep 17 00:00:00 2001 From: =?utf8?q?Roland=20H=C3=A4der?= Date: Wed, 31 Aug 2016 09:11:18 +0200 Subject: [PATCH] Added view for request methods MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Signed-off-by: Roland Häder --- install/install.sql | 21 +++++++++++---------- libs/lib_updates.php | 8 ++++++++ 2 files changed, 19 insertions(+), 10 deletions(-) diff --git a/install/install.sql b/install/install.sql index 82a0bad..e14a8fe 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, 8, 10, 30, 'Y', 'en'); +(1, 9, 10, 30, 'Y', 'en'); DROP TABLE IF EXISTS `ctracker_data`; CREATE TABLE IF NOT EXISTS `ctracker_data` ( @@ -118,32 +118,33 @@ CREATE TABLE IF NOT EXISTS `view_total_attacks` ( ,`max_attack_repeat` bigint(20) unsigned );DROP TABLE IF EXISTS `view_attack_distribution`; -CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_attack_distribution` AS select count(0) AS `Rows`,`ctracker_data`.`remote_addr` AS `remote_addr` from `ctracker_data` group by `ctracker_data`.`remote_addr` order by count(0) desc; +CREATE VIEW `view_attack_distribution` AS select count(0) AS `Rows`,`ctracker_data`.`remote_addr` AS `remote_addr` from `ctracker_data` group by `ctracker_data`.`remote_addr` order by count(0) desc; DROP TABLE IF EXISTS `view_most_attacked_domains`; -CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_most_attacked_domains` AS select count(0) AS `Rows`,`ctracker_data`.`server_name` AS `server_name` from `ctracker_data` group by `ctracker_data`.`server_name` order by count(0) desc; +CREATE VIEW `view_most_attacked_domains` AS select count(0) AS `Rows`,`ctracker_data`.`server_name` AS `server_name` from `ctracker_data` group by `ctracker_data`.`server_name` order by count(0) desc; DROP TABLE IF EXISTS `view_most_attacked_scripts`; -CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_most_attacked_scripts` AS select count(0) AS `Rows`,`ctracker_data`.`script_name` AS `script_name` from `ctracker_data` group by `ctracker_data`.`script_name` order by count(0) desc; +CREATE VIEW `view_most_attacked_scripts` AS select count(0) AS `Rows`,`ctracker_data`.`script_name` AS `script_name` from `ctracker_data` group by `ctracker_data`.`script_name` order by count(0) desc; DROP TABLE IF EXISTS `view_most_attacking_agents`; -CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_most_attacking_agents` AS select count(0) AS `Rows`,`ctracker_data`.`user_agent` AS `user_agent` from `ctracker_data` group by `ctracker_data`.`user_agent` order by count(0) desc; +CREATE VIEW `view_most_attacking_agents` AS select count(0) AS `Rows`,`ctracker_data`.`user_agent` AS `user_agent` from `ctracker_data` group by `ctracker_data`.`user_agent` order by count(0) desc; DROP TABLE IF EXISTS `view_proxy_addresses`; -CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_proxy_addresses` AS select distinct `ctracker_data`.`remote_addr` AS `remote_addr`,`ctracker_data`.`proxy_addr` AS `proxy_addr` from `ctracker_data` where (`ctracker_data`.`remote_addr` <> `ctracker_data`.`proxy_addr`) order by `ctracker_data`.`remote_addr` limit 1; +CREATE VIEW `view_proxy_addresses` AS select distinct `ctracker_data`.`remote_addr` AS `remote_addr`,`ctracker_data`.`proxy_addr` AS `proxy_addr` from `ctracker_data` where (`ctracker_data`.`remote_addr` <> `ctracker_data`.`proxy_addr`) order by `ctracker_data`.`remote_addr` limit 1; DROP TABLE IF EXISTS `view_proxy_attackers`; -CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_proxy_attackers` AS select count(0) AS `Rows`,`ctracker_data`.`proxy_used` AS `has_used_proxy` from `ctracker_data` group by `ctracker_data`.`proxy_used` order by `ctracker_data`.`proxy_used`; +CREATE VIEW `view_proxy_attackers` AS select count(0) AS `Rows`,`ctracker_data`.`proxy_used` AS `has_used_proxy` from `ctracker_data` group by `ctracker_data`.`proxy_used` order by `ctracker_data`.`proxy_used`; 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`); +CREATE 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_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; +CREATE 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; +CREATE 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; +CREATE VIEW `view_most_request_methods` AS SELECT SQL_CALC_FOUND_ROWS COUNT(`request_method`) AS `total_rows`, `request_method` FROM `ctracker_data` GROUP BY `request_method`; ALTER TABLE `ctracker_ticket_data` ADD CONSTRAINT `ctracker_ticket_data_ibfk_1` FOREIGN KEY (`ctracker_ticket_id`) REFERENCES `ctracker_ticket` (`ctracker_ticket`) ON DELETE CASCADE, diff --git a/libs/lib_updates.php b/libs/lib_updates.php index 72b8e23..94e4f4b 100644 --- a/libs/lib_updates.php +++ b/libs/lib_updates.php @@ -90,6 +90,14 @@ FOREIGN KEY ( `ctracker_data_id` ) REFERENCES `' . $GLOBALS['ctracker_dbname'] . CHANGE `check_worm` `check_get` tinytext COLLATE 'utf8_general_ci' NOT NULL COMMENT 'GET data after check' AFTER `check_ua`, CHANGE `check_post` `check_post` tinytext COLLATE 'utf8_general_ci' NOT NULL COMMENT 'POST data after check' AFTER `check_worm`", ), + + // View for request methods + 8 => array( + 'CREATE VIEW `view_most_request_methods` AS + SELECT SQL_CALC_FOUND_ROWS COUNT(`request_method`) AS `total_rows`, `request_method` + FROM `ctracker_data` + GROUP BY `request_method`' + ), ); } -- 2.39.5