This shows you the differences between two versions of the page.
Next revision | Previous revision | ||
quota [2018/05/16 18:34] moliver@uclv created |
quota [2018/05/21 20:50] moliver@uclv |
||
---|---|---|---|
Line 74: | Line 74: | ||
DROP PROCEDURE IF EXISTS `squid_log_main`$$ | DROP PROCEDURE IF EXISTS `squid_log_main`$$ | ||
CREATE DEFINER=`squid_log`@`%` PROCEDURE `squid_log_main`() | CREATE DEFINER=`squid_log`@`%` PROCEDURE `squid_log_main`() | ||
- | BEGIN | + | BEGIN |
- | + | ||
- | DECLARE fecha_actual decimal(15,3); | + | DECLARE fecha_actual decimal(15,3); |
- | DECLARE dias char(10); | + | DECLARE dias char(10); |
- | DECLARE ahora datetime; | + | DECLARE ahora datetime; |
- | DECLARE valor_dias char(11); | + | DECLARE ciclo TINYINT(1); |
- | DECLARE done TINYINT(1) DEFAULT 0; | + | DECLARE valor_dias char(11); |
- | DECLARE q_full_log_create text; | + | DECLARE done TINYINT(1) DEFAULT 0; |
- | DECLARE q_full_log_insert text; | + | DECLARE q_full_log_create text; |
- | + | DECLARE q_full_log_insert text; | |
- | DECLARE dias_en_el_log CURSOR FOR | + | |
- | SELECT from_unixtime(time_since_epoch,'%Y_%m_%d') as dias from full_access_log group by dias; | + | DECLARE dias_en_el_log CURSOR FOR SELECT from_unixtime(time_since_epoch,'%Y_%m_%d') as dias from full_access_log group by dias; |
- | + | ||
- | DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; | + | DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; |
- | + | ||
- | SET done = 0; | + | SET done = 0; |
- | SET ahora = now(); | + | SET ciclo = 0; |
- | SET fecha_actual = UNIX_TIMESTAMP(); | + | SET ahora = now(); |
- | + | SET fecha_actual = UNIX_TIMESTAMP(); | |
- | select now(), "00 - Version 2017-10-07.1"; | + | |
+ | SET max_heap_table_size = 1024*1024*1024*6; | ||
+ | |||
+ | |||
+ | select now(), "00 - Version 2017-10-10.1"; | ||
+ | |||
+ | select now(), "01 - Creando tablas necesarias si no existen"; | ||
+ | |||
+ | CREATE TABLE IF NOT EXISTS `totales_10` ( | ||
+ | `usuario` varchar(32) NOT NULL, | ||
+ | `tiempo` date NOT NULL, | ||
+ | `consumo` int(15) NOT NULL, | ||
+ | `tadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP | ||
+ | ) ENGINE=INNODB DEFAULT CHARSET=latin1; | ||
+ | |||
+ | |||
+ | CREATE TABLE IF NOT EXISTS `totales_full` ( | ||
+ | `usuario` varchar(32) NOT NULL, | ||
+ | `tiempo` date NOT NULL, | ||
+ | `consumo` int(15) NOT NULL, | ||
+ | `tadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP | ||
+ | ) ENGINE=INNODB DEFAULT CHARSET=latin1; | ||
- | SET max_heap_table_size = 1024*1024*1024*6; | + | CREATE TABLE IF NOT EXISTS `access_log` ( |
- | CREATE TABLE IF NOT EXISTS `access_log` ( | + | `time_since_epoch` decimal(15,3) DEFAULT NULL, |
- | `time_since_epoch` decimal(15,3) DEFAULT NULL, | + | `response_time` int(11) DEFAULT NULL, |
- | `response_time` int(11) DEFAULT NULL, | + | `client_src_ip_addr` char(15) DEFAULT NULL, |
- | `client_src_ip_addr` char(15) DEFAULT NULL, | + | `squid_request_status` varchar(40) DEFAULT NULL, |
- | `squid_request_status` varchar(40) DEFAULT NULL, | + | `http_status_code` varchar(10) DEFAULT NULL, |
- | `http_status_code` varchar(10) DEFAULT NULL, | + | `reply_size` int(11) DEFAULT NULL, |
- | `reply_size` int(11) DEFAULT NULL, | + | `request_method` varchar(20) DEFAULT NULL, |
- | `request_method` varchar(20) DEFAULT NULL, | + | `request_url` varchar(1000) DEFAULT NULL, |
- | `request_url` varchar(1000) DEFAULT NULL, | + | `username` varchar(20) DEFAULT NULL, |
- | `username` varchar(20) DEFAULT NULL, | + | `squid_hier_status` varchar(20) DEFAULT NULL, |
- | `squid_hier_status` varchar(20) DEFAULT NULL, | + | `server_ip_addr` char(15) DEFAULT NULL, |
- | `server_ip_addr` char(15) DEFAULT NULL, | + | `mime_type` varchar(50) DEFAULT NULL |
- | `mime_type` varchar(50) DEFAULT NULL | + | ) ENGINE=MEMORY DEFAULT CHARSET=latin1; |
+ | |||
+ | |||
+ | CREATE TABLE IF NOT EXISTS `usuarios` ( | ||
+ | `usuario` varchar(32) NOT NULL, | ||
+ | `cuota` int(20) NOT NULL DEFAULT '0', | ||
+ | `total` bigint(20) NOT NULL DEFAULT '0', | ||
+ | `ldap` varchar(256) NOT NULL, | ||
+ | `actualizado` datetime NOT NULL, | ||
+ | `total30` bigint(20) NOT NULL DEFAULT '0', | ||
+ | `cuota2` int(20) NOT NULL DEFAULT '0', | ||
+ | PRIMARY KEY (`usuario`) | ||
+ | ) ENGINE=MyISAM DEFAULT CHARSET=latin1; | ||
+ | |||
+ | CREATE TABLE IF NOT EXISTS full_log_10 ( | ||
+ | `response_time` int(11) DEFAULT NULL, | ||
+ | `client_src_ip_addr` char(15) DEFAULT NULL, | ||
+ | `squid_request_status` varchar(40) DEFAULT NULL, | ||
+ | `http_status_code` varchar(10) DEFAULT NULL, | ||
+ | `reply_size` int(11) DEFAULT NULL, | ||
+ | `request_method` varchar(20) DEFAULT NULL, | ||
+ | `request_url` varchar(1000) DEFAULT NULL, | ||
+ | `username` varchar(20) DEFAULT NULL, | ||
+ | `squid_hier_status` varchar(20) DEFAULT NULL, | ||
+ | `server_ip_addr` char(15) DEFAULT NULL, | ||
+ | `mime_type` varchar(50) DEFAULT NULL, | ||
+ | `network_id` int(11) DEFAULT NULL, | ||
+ | `domain_id` int(11) DEFAULT 0, | ||
+ | `request_date` DATETIME NOT NULL | ||
+ | ) ENGINE=INNODB DEFAULT CHARSET=latin1; | ||
+ | |||
+ | |||
+ | CREATE TABLE IF NOT EXISTS `sess` ( | ||
+ | `dir_ip` varchar(15) NOT NULL, | ||
+ | `usuario` varchar(32) NOT NULL, | ||
+ | `tiempo` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP | ||
) ENGINE=MEMORY DEFAULT CHARSET=latin1; | ) ENGINE=MEMORY DEFAULT CHARSET=latin1; | ||
- | + | ||
- | select now(), "001 - Creando full_access_log si no existe y limpiandola de datos anteriores"; | + | |
- | DROP TABLE IF EXISTS `full_access_log`; | + | select now(), "02 - Creando full_access_log en memoria si no existe y limpiandola de datos anteriores"; |
- | CREATE TABLE IF NOT EXISTS `full_access_log` ( | + | |
- | `time_since_epoch` decimal(15,3) DEFAULT NULL, | + | DROP TABLE IF EXISTS `full_access_log`; |
- | `response_time` int(11) DEFAULT NULL, | + | CREATE TABLE IF NOT EXISTS `full_access_log` ( |
- | `client_src_ip_addr` char(15) DEFAULT NULL, | + | `time_since_epoch` decimal(15,3) DEFAULT NULL, |
- | `squid_request_status` varchar(40) DEFAULT NULL, | + | `response_time` int(11) DEFAULT NULL, |
- | `http_status_code` varchar(10) DEFAULT NULL, | + | `client_src_ip_addr` char(15) DEFAULT NULL, |
- | `reply_size` int(11) DEFAULT NULL, | + | `squid_request_status` varchar(40) DEFAULT NULL, |
- | `request_method` varchar(20) DEFAULT NULL, | + | `http_status_code` varchar(10) DEFAULT NULL, |
- | `request_url` varchar(1000) DEFAULT NULL, | + | `reply_size` int(11) DEFAULT NULL, |
- | `username` varchar(20) DEFAULT NULL, | + | `request_method` varchar(20) DEFAULT NULL, |
- | `squid_hier_status` varchar(20) DEFAULT NULL, | + | `request_url` varchar(1000) DEFAULT NULL, |
- | `server_ip_addr` char(15) DEFAULT NULL, | + | `username` varchar(20) DEFAULT NULL, |
- | `mime_type` varchar(50) DEFAULT NULL, | + | `squid_hier_status` varchar(20) DEFAULT NULL, |
- | `network_id` int(11) DEFAULT NULL, | + | `server_ip_addr` char(15) DEFAULT NULL, |
- | `domain_id` int(11) DEFAULT 0, | + | `mime_type` varchar(50) DEFAULT NULL, |
- | `domain_name` varchar(255) DEFAULT NULL, | + | `network_id` int(11) DEFAULT NULL, |
- | `request_date` DATETIME NOT NULL, | + | `domain_id` int(11) DEFAULT 0, |
- | `network_name` char(15) DEFAULT NULL | + | `domain_name` varchar(255) DEFAULT NULL, |
- | ) ENGINE=MEMORY DEFAULT CHARSET=latin1; | + | `request_date` DATETIME NOT NULL, |
- | TRUNCATE full_access_log; | + | `network_name` char(15) DEFAULT NULL |
- | + | ) ENGINE=MEMORY DEFAULT CHARSET=latin1; | |
- | select now(), "01 - Pasando desde access_log a full_access_log para analisis posterior"; | + | |
- | INSERT INTO full_access_log(time_since_epoch,response_time,client_src_ip_addr,squid_request_status,http_status_code, | + | TRUNCATE full_access_log; |
- | reply_size,request_method,request_url,username,squid_hier_status,server_ip_addr,mime_type) | + | |
- | select * from access_log where time_since_epoch < fecha_actual; | + | |
- | + | ||
- | + | select now(), "03 - Pasando desde access_log a full_access_log para analisis posterior"; | |
- | select now(), "02 - Cantidad de records en access_log :", COUNT(*) FROM access_log; | + | |
- | + | INSERT INTO full_access_log(time_since_epoch,response_time,client_src_ip_addr,squid_request_status,http_status_code, | |
- | select now(), "03 - Cantidad de records en full_access_log :", COUNT(*) FROM full_access_log; | + | reply_size,request_method,request_url,username,squid_hier_status,server_ip_addr,mime_type) |
- | + | select * from access_log where time_since_epoch < fecha_actual; | |
- | select now(), "04 - Fecha actual", fecha_actual; | + | |
- | + | ||
- | select now(), "05 - Borrando lo que ya se paso desde access_log"; | + | |
- | delete from access_log where time_since_epoch < fecha_actual; | + | |
+ | select now(), "04 - Cantidad de records en access_log :", COUNT(*) FROM access_log; | ||
+ | |||
+ | |||
+ | |||
+ | select now(), "05 - Cantidad de records en full_access_log :", COUNT(*) FROM full_access_log; | ||
+ | |||
+ | |||
+ | |||
+ | select now(), "06 - Fecha actual", fecha_actual; | ||
+ | |||
+ | |||
+ | |||
+ | select now(), "07 - Borrando lo que ya se paso desde access_log"; | ||
+ | |||
+ | -- select count(*) from access_log where time_since_epoch < fecha_actual; | ||
+ | delete from access_log where time_since_epoch < fecha_actual; | ||
| | ||
- | select now(), "07 - actualizando full_access_log para el nombre de la red"; | ||
- | update full_access_log set network_name=SUBSTRING_INDEX(full_access_log.client_src_ip_addr, '.', 3); | ||
- | |||
- | select now(), "08 - actualizando networks base"; | ||
- | insert ignore into networks(base) select network_name as base from full_access_log; | ||
- | |||
- | select now(), "09 - actualizando full_access_log y networks donde los nombres y las bases coinciden"; | ||
- | update full_access_log,networks set full_access_log.network_id=networks.id where full_access_log.network_name = networks.base; | ||
- | |||
- | select now(), "10 - actualizando full_access_log con los nombres de dominios"; | ||
- | update full_access_log set domain_name=getDomainName(request_url); | ||
- | |||
- | select now(), "11 - actualizando la tabla domains"; | ||
- | insert ignore into domains(domain) select domain_name as domain from full_access_log; | ||
- | |||
- | select now(), "12 - actualizando los grupos de dominios"; | ||
- | update domains set domains.group_id=domains.id where domains.group_id=0; | ||
- | |||
- | select now(), "13 - actualizando full_access_log con los nuevos grupos de dominios"; | ||
- | update full_access_log, domains set domain_id = domains.group_id where full_access_log.domain_name = domains.domain ; | ||
- | |||
- | select now(), "14 - actualizando full_access_log campo request_date desde formato unix a normal"; | ||
- | update full_access_log set request_date=FROM_UNIXTIME(time_since_epoch); | ||
- | |||
- | select now(), "15 - creando tablas totales_10 y totales_full si no existen"; | ||
- | |||
- | CREATE TABLE IF NOT EXISTS `totales_10` ( | ||
- | `usuario` varchar(32) NOT NULL, | ||
- | `tiempo` date NOT NULL, | ||
- | `consumo` int(15) NOT NULL, | ||
- | `tadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP | ||
- | ) ENGINE=INNODB DEFAULT CHARSET=latin1; | ||
- | |||
- | CREATE TABLE IF NOT EXISTS `totales_full` ( | ||
- | `usuario` varchar(32) NOT NULL, | ||
- | `tiempo` date NOT NULL, | ||
- | `consumo` int(15) NOT NULL, | ||
- | `tadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP | ||
- | ) ENGINE=INNODB DEFAULT CHARSET=latin1; | ||
- | |||
- | select now(), "16 - insertando en totales_10 desde full_access_log"; | ||
- | insert into totales_10 | + | select now(), "08 - actualizando full_access_log para el nombre de la red"; |
- | select username as usuario, DATE_FORMAT(request_date, '%Y-%m-%d' ) as tiempo, sum(reply_size) as consumo , now() as tadded | + | |
- | from full_access_log | + | |
- | where | + | |
- | squid_request_status <> "TAG_NONE" and | + | |
- | squid_request_status <> "TAG_NONE_ABORTED" and | + | |
- | squid_request_status <> "TAG_NONE_TIMEDOUT" and | + | |
- | squid_request_status <> "TCP_DENIED" and | + | |
- | squid_request_status <> "TCP_DENIED_ABORTED" and | + | |
- | squid_request_status <> "TCP_DENIED_TIMEDOUT" and | + | |
- | instr(server_ip_addr,'10.') <> 1 | + | |
- | group by username,tiempo; | + | |
- | + | ||
- | select now(), "17 - creando tabla usuarios si no existe"; | + | |
- | + | ||
- | CREATE TABLE IF NOT EXISTS `usuarios` ( | + | |
- | `usuario` varchar(32) NOT NULL, | + | |
- | `cuota` int(20) NOT NULL DEFAULT '0', | + | |
- | `total` bigint(20) NOT NULL DEFAULT '0', | + | |
- | `ldap` varchar(256) NOT NULL, | + | |
- | `actualizado` datetime NOT NULL, | + | |
- | `total30` bigint(20) NOT NULL DEFAULT '0', | + | |
- | `cuota2` int(20) NOT NULL DEFAULT '0', | + | |
- | PRIMARY KEY (`usuario`) | + | |
- | ) ENGINE=MyISAM DEFAULT CHARSET=latin1; | + | |
+ | update full_access_log set network_name=SUBSTRING_INDEX(full_access_log.client_src_ip_addr, '.', 3); | ||
- | select now(), "18 - borrando de totales_full lo que este hace mas de 30 dias"; | ||
- | delete from totales_full where datediff(now(), tiempo) > 30; | ||
- | select now(), "19 - pasando a totales_full lo que este en totales_10 desde hace mas de 4 dias"; | ||
- | insert into totales_full | ||
- | (select * from totales_10 where datediff(ahora, tiempo) > 4); | ||
- | |||
- | select now(), "20 - borrar de totales_10 lo que se paso anteriormente"; | ||
- | delete from totales_10 where datediff(ahora, tiempo) > 4; | ||
- | |||
- | select now(), "21 - borrando de usuarios los que no se han actualizado en 30 dias"; | + | select now(), "09 - actualizando networks base"; |
- | delete from usuarios where datediff(now(), actualizado) > 30; | + | insert ignore into networks(base) select network_name as base from full_access_log; |
- | select now(), "22 - limpiando total y total30 en tabla usuarios"; | ||
- | update usuarios set total=0; | ||
- | |||
- | select now(), "23 - insertar en usuarios total el consumo desde totales_10"; | ||
- | insert into usuarios(usuario,total) | ||
- | (select usuario, sum(consumo) as total from totales_10 group by usuario) | ||
- | on duplicate key update total=values(total); | ||
- | select now(), "24 - insertar en usuarios total30 el consumo desde totales_full"; | + | select now(), "10 - actualizando full_access_log y networks donde los nombres y las bases coinciden"; |
- | update usuarios set total30=0; | + | |
- | insert into usuarios(usuario,total30) | + | |
- | (select usuario, sum(consumo) as total30 from totales_full | + | |
- | where datediff(ahora, tiempo) < 4*4 group by usuario) | + | |
- | on duplicate key update total30=values(total30); | + | |
- | + | ||
- | select now(), "25 - actualizar consumo grande con el valor de los ultimos dias"; | + | |
- | update usuarios set total30=total30+total; | + | |
- | update usuarios set cuota2=cuota*5; | + | |
+ | update full_access_log,networks set full_access_log.network_id=networks.id where full_access_log.network_name = networks.base; | ||
- | select now(), "26 - crear full_log_10 si no existe"; | ||
- | CREATE TABLE IF NOT EXISTS full_log_10 ( | ||
- | `response_time` int(11) DEFAULT NULL, | ||
- | `client_src_ip_addr` char(15) DEFAULT NULL, | ||
- | `squid_request_status` varchar(40) DEFAULT NULL, | ||
- | `http_status_code` varchar(10) DEFAULT NULL, | ||
- | `reply_size` int(11) DEFAULT NULL, | ||
- | `request_method` varchar(20) DEFAULT NULL, | ||
- | `request_url` varchar(1000) DEFAULT NULL, | ||
- | `username` varchar(20) DEFAULT NULL, | ||
- | `squid_hier_status` varchar(20) DEFAULT NULL, | ||
- | `server_ip_addr` char(15) DEFAULT NULL, | ||
- | `mime_type` varchar(50) DEFAULT NULL, | ||
- | `network_id` int(11) DEFAULT NULL, | ||
- | `domain_id` int(11) DEFAULT 0, | ||
- | `request_date` DATETIME NOT NULL | ||
- | ) ENGINE=INNODB DEFAULT CHARSET=latin1; | ||
- | |||
- | select now(), "27 - pasando datos desde full_access_log para full_log_10 DISABLE"; | ||
- | |||
- | #INSERT INTO full_log_10 | ||
- | # (response_time,client_src_ip_addr, | ||
- | # `squid_request_status`,`http_status_code`, | ||
- | # `reply_size`,`request_method`,`request_url`, | ||
- | # `username`,`squid_hier_status`, | ||
- | # `server_ip_addr`,`mime_type`, | ||
- | # `network_id`,`domain_id`,`request_date`) | ||
- | # SELECT | ||
- | # response_time,client_src_ip_addr,`squid_request_status`, | ||
- | # `http_status_code`,`reply_size`,`request_method`,`request_url`, | ||
- | # `username`,`squid_hier_status`, | ||
- | # `server_ip_addr`,`mime_type`,`network_id`,`domain_id`,`request_date` | ||
- | # FROM full_access_log; | ||
- | |||
- | |||
- | DELETE FROM full_log_10 WHERE datediff(now(), request_date) > 10; | ||
- | |||
- | select now(), "28 - setting done 0"; | ||
- | SET done = 0; | ||
- | select now(), "29 - buscando dias en el log"; | + | select now(), "11 - actualizando full_access_log con los nombres de dominios"; |
+ | |||
+ | update full_access_log set domain_name=getDomainName(request_url); | ||
+ | |||
+ | |||
+ | |||
+ | select now(), "12 - actualizando la tabla domains"; | ||
+ | insert ignore into domains(domain) select domain_name as domain from full_access_log; | ||
+ | |||
+ | select now(), "13 - actualizando los grupos de dominios"; | ||
- | OPEN dias_en_el_log; | + | update domains set domains.group_id=domains.id where domains.group_id=0; |
- | REPEAT | + | |
- | FETCH dias_en_el_log INTO valor_dias; | + | |
- | select now(), "29.1"; | + | |
- | IF NOT done THEN | + | select now(), "14 - actualizando full_access_log con los nuevos grupos de dominios"; |
- | select now(), "29.1"; | + | |
- | set @q_full_log_create = CONCAT(" | + | update full_access_log, domains set domain_id = domains.group_id where full_access_log.domain_name = domains.domain ; |
- | CREATE TABLE IF NOT EXISTS full_log_",valor_dias," ( | + | |
- | `response_time` int(11) DEFAULT NULL, | + | |
- | `client_src_ip_addr` char(15) DEFAULT NULL, | + | |
- | `squid_request_status` varchar(40) DEFAULT NULL, | + | select now(), "15 - actualizando full_access_log campo request_date desde formato unix a normal"; |
- | `http_status_code` varchar(10) DEFAULT NULL, | + | |
- | `reply_size` int(11) DEFAULT NULL, | + | update full_access_log set request_date=FROM_UNIXTIME(time_since_epoch); |
- | `request_method` varchar(20) DEFAULT NULL, | + | |
- | `request_url` varchar(1000) DEFAULT NULL, | + | |
- | `username` varchar(20) DEFAULT NULL, | + | |
- | `squid_hier_status` varchar(20) DEFAULT NULL, | + | select now(), "16 - Buscando dias en el log"; |
- | `server_ip_addr` char(15) DEFAULT NULL, | + | |
- | `mime_type` varchar(50) DEFAULT NULL, | + | OPEN dias_en_el_log; |
- | `network_id` int(11) DEFAULT NULL, | + | REPEAT |
- | `domain_id` int(11) DEFAULT 0, | + | FETCH dias_en_el_log INTO valor_dias; |
- | `request_date` DATETIME NOT NULL | + | IF NOT done THEN |
- | ) ENGINE=INNODB DEFAULT CHARSET=latin1;"); | + | set @q_full_log_create = CONCAT(" |
- | + | CREATE TABLE IF NOT EXISTS full_log_",valor_dias," ( | |
- | select now(), "29.2"; | + | `response_time` int(11) DEFAULT NULL, |
- | PREPARE stmt1 FROM @q_full_log_create; | + | `client_src_ip_addr` char(15) DEFAULT NULL, |
- | EXECUTE stmt1 ; | + | `squid_request_status` varchar(40) DEFAULT NULL, |
- | DEALLOCATE PREPARE stmt1; | + | `http_status_code` varchar(10) DEFAULT NULL, |
- | + | `reply_size` int(11) DEFAULT NULL, | |
- | + | `request_method` varchar(20) DEFAULT NULL, | |
- | select now(), "29.3"; | + | `request_url` varchar(1000) DEFAULT NULL, |
- | + | `username` varchar(20) DEFAULT NULL, | |
- | set @q_full_log_insert = CONCAT(" | + | `squid_hier_status` varchar(20) DEFAULT NULL, |
- | INSERT INTO full_log_",valor_dias,"(response_time,client_src_ip_addr,`squid_request_status`,`http_status_code`,`reply_size`,`request_method`,`request_url`, | + | `server_ip_addr` char(15) DEFAULT NULL, |
- | `username`,`squid_hier_status`,`server_ip_addr`,`mime_type`,`network_id`,`domain_id`,`request_date`) | + | `mime_type` varchar(50) DEFAULT NULL, |
- | SELECT response_time,client_src_ip_addr,`squid_request_status`,`http_status_code`,`reply_size`,`request_method`,`request_url`, | + | `network_id` int(11) DEFAULT NULL, |
- | `username`,`squid_hier_status`,`server_ip_addr`,`mime_type`,`network_id`,`domain_id`,`request_date` | + | `domain_id` int(11) DEFAULT 0, |
- | FROM full_access_log; | + | `request_date` DATETIME NOT NULL |
- | "); | + | ) ENGINE=INNODB DEFAULT CHARSET=latin1;"); |
- | + | PREPARE stmt1 FROM @q_full_log_create; | |
- | PREPARE stmt2 FROM @q_full_log_insert; | + | EXECUTE stmt1 ; |
- | EXECUTE stmt2; | + | DEALLOCATE PREPARE stmt1; |
- | select now(), "29.4"; | + | set @q_full_log_insert = CONCAT(" |
- | DEALLOCATE PREPARE stmt2; | + | INSERT INTO full_log_",valor_dias,"(response_time,client_src_ip_addr,`squid_request_status`,`http_status_code`,`reply_size`,`request_method`,`request_url`, |
- | + | `username`,`squid_hier_status`,`server_ip_addr`,`mime_type`,`network_id`,`domain_id`,`request_date`) | |
- | END IF; | + | SELECT response_time,client_src_ip_addr,`squid_request_status`,`http_status_code`,`reply_size`,`request_method`,`request_url`, |
- | + | `username`,`squid_hier_status`,`server_ip_addr`,`mime_type`,`network_id`,`domain_id`,`request_date` | |
- | select now(), "29.5"; | + | FROM full_access_log; |
- | + | "); | |
+ | PREPARE stmt2 FROM @q_full_log_insert; | ||
+ | EXECUTE stmt2; | ||
+ | DEALLOCATE PREPARE stmt2; | ||
+ | END IF; | ||
UNTIL done END REPEAT ; | UNTIL done END REPEAT ; | ||
- | |||
- | select now(), "29.6"; | ||
- | |||
CLOSE dias_en_el_log; | CLOSE dias_en_el_log; | ||
- | |||
- | select now(), "30 - cantidad de pasados semanales", count(*) from usuarios where total > cuota ; | ||
- | select now(), "31 - cantidad de pasados mensuales", count(*) from usuarios where total30 > cuota*5 ; | ||
- | SET @bytes := (SELECT SUM(consumo) FROM totales_10 where tadded > curdate()); | ||
- | SELECT now(), "32 - cantidad de bytes contados hoy",curdate(), | + | -- empezar limpieza de full_access_log para quitar todo lo que no se desea contar |
- | CASE | + | select now(), "17 - Limpiando full_access_log", count(*) from full_access_log; |
- | WHEN ABS(@bytes) < 1024 THEN CONCAT( ROUND( @bytes, 3 ), ' Bytes') | + | delete from full_access_log where squid_request_status = "TAG_NONE"; |
- | WHEN ABS(@bytes) < 1048576 THEN CONCAT( ROUND( (@bytes/1024), 3 ), ' KB') | + | delete from full_access_log where squid_request_status = "TAG_NONE_ABORTED"; |
- | WHEN ABS(@bytes) < 1073741824 THEN CONCAT( ROUND( (@bytes/1048576), 3 ), ' MB') | + | delete from full_access_log where squid_request_status = "TAG_NONE_TIMEDOUT"; |
- | WHEN ABS(@bytes) < 1099511627776 THEN CONCAT( ROUND( (@bytes/1073741824), 3 ), ' GB' ) | + | delete from full_access_log where squid_request_status = "TCP_DENIED"; |
- | WHEN ABS(@bytes) < 1125899906842624 THEN CONCAT( ROUND( (@bytes/1099511627776), 3 ), ' TB') | + | delete from full_access_log where squid_request_status = "TCP_DENIED_ABORTED"; |
- | WHEN ABS(@bytes) < 1152921504606846976 THEN CONCAT( ROUND( (@bytes/1125899906842624), 3 ), ' PB' ) | + | delete from full_access_log where squid_request_status = "TCP_DENIED_TIMEDOUT"; |
- | WHEN ABS(@bytes) < 1180591620717411303424 THEN CONCAT( ROUND( (@bytes/1152921504606846976) ,2), ' EB' ) | + | delete from full_access_log where squid_request_status = "TCP_DENIED_TIMEDOUT"; |
- | WHEN ABS(@bytes) < 1208925819614629174706176 THEN CONCAT( ROUND( (@bytes/1180591620717411303424), 3), ' ZB' ) | + | -- no contrar lo que se pide desde las casas |
- | WHEN ABS(@bytes) < 1237940039285380274899124224 THEN CONCAT( ROUND( (@bytes/1208925819614629174706176), 3), ' YB' ) | + | delete from full_access_log where instr(client_src_ip_addr,'10.71.54.') = 1; |
- | WHEN ABS(@bytes) < 1267650600228229401496703205376 THEN CONCAT( ROUND( (@bytes/1237940039285380274899124224), 3), ' BB' ) | + | -- no contar lo que se pide local |
- | END | + | delete from full_access_log where instr(server_ip_addr,'10.') = 1; |
- | as totales; | + | -- no contar lo que se pida antes de las 8 y depsues de las 5 |
- | + | -- delete from full_access_log where hour(request_date) < 8 ; | |
+ | -- delete from full_access_log where hour(request_date) >= 17 ; | ||
+ | select now(), "17a - Limpiando full_access_log", count(*) from full_access_log; | ||
+ | |||
+ | select now(), "18 - Calculando los totales desde full_access_log y almacenando en totales_10"; | ||
+ | |||
+ | insert into totales_10 | ||
+ | select username as usuario, DATE_FORMAT(request_date, '%Y-%m-%d' ) as tiempo, sum(reply_size) as consumo , now() as tadded | ||
+ | from full_access_log group by username, tiempo; | ||
+ | |||
+ | |||
+ | -- select now(), "19 - borrando de totales_full los datos con mas de 10 dias"; | ||
+ | -- se mantienen 10 dias para estudios de traficos pero en realidad no hace falta tanto | ||
+ | -- delete from totales_full where datediff(now(), tiempo) > 10; | ||
+ | |||
+ | -- no voy a guardar mas de 10 dias | ||
+ | -- select now(), "20 - pasando a totales_full lo que este en totales_10 desde hace mas de ",ciclo,"dias"; | ||
+ | -- insert into totales_full | ||
+ | -- (select * from totales_10 where datediff(ahora, tiempo) > ciclo); | ||
+ | |||
+ | select now(), "21 - borrar de totales_10 lo que tenga mas de 10 dias"; | ||
+ | delete from totales_10 where datediff(ahora, tiempo) > 10; | ||
+ | |||
+ | select now(), "22 - borrando de usuarios los que no se han actualizado en 30 dias:", count(*) from usuarios where datediff(now(), actualizado) > 30; | ||
+ | delete from usuarios where datediff(ahora, actualizado) > 30; | ||
+ | |||
+ | select now(), "23 - limpiando total y total30 en tabla usuarios"; | ||
+ | update usuarios set total=0; | ||
+ | |||
+ | select now(), "24 - insertar en usuarios total el consumo desde totales_10"; | ||
+ | insert into usuarios(usuario,total) | ||
+ | (select usuario, sum(consumo) as total from totales_10 where datediff(ahora, tiempo) <= ciclo group by usuario) | ||
+ | on duplicate key update total=values(total); | ||
+ | |||
+ | select now(), "25 - insertar en usuarios total30 el consumo desde totales_full"; | ||
+ | update usuarios set total30=0; | ||
+ | insert into usuarios(usuario,total30) | ||
+ | (select usuario, sum(consumo) as total30 from totales_10 where datediff(ahora, tiempo) <= (ciclo+1)*4 group by usuario) | ||
+ | on duplicate key update total30=values(total30); | ||
+ | |||
+ | select now(), "26 - actualizar consumo grande con el valor de los ultimos dias"; | ||
+ | -- update usuarios set total30=total30+total; | ||
+ | update usuarios set cuota2=cuota*5; | ||
+ | |||
+ | -- select now(), "27 - pasando datos desde full_access_log para full_log_10 DISABLE"; | ||
+ | |||
+ | -- INSERT INTO full_log_10 | ||
+ | -- (response_time,client_src_ip_addr, | ||
+ | -- `squid_request_status`,`http_status_code`, | ||
+ | -- `reply_size`,`request_method`,`request_url`, | ||
+ | -- `username`,`squid_hier_status`, | ||
+ | -- `server_ip_addr`,`mime_type`, | ||
+ | -- `network_id`,`domain_id`,`request_date`) | ||
+ | -- SELECT | ||
+ | -- response_time,client_src_ip_addr,`squid_request_status`, | ||
+ | -- `http_status_code`,`reply_size`,`request_method`,`request_url`, | ||
+ | -- `username`,`squid_hier_status`, | ||
+ | -- `server_ip_addr`,`mime_type`,`network_id`,`domain_id`,`request_date` | ||
+ | -- FROM full_access_log; | ||
+ | |||
+ | |||
+ | -- delete FROM full_log_10 WHERE datediff(now(), request_date) > (ciclo*4)+1; | ||
+ | |||
+ | -- select now(), "28 - setting done 0"; | ||
+ | -- SET done = 0; | ||
+ | |||
+ | select now(), "29 - limpiando sesiones mayores de 2 horas"; | ||
+ | delete from sess where now() - tiempo > 20000; | ||
+ | |||
+ | |||
+ | select now(), "29 - cantidad de pasados semanales", count(*) from usuarios where total > cuota ; | ||
+ | select now(), "30 - cantidad de pasados mensuales", count(*) from usuarios where total30 > cuota2 ; | ||
+ | |||
+ | SET @bytes := (SELECT SUM(consumo) FROM totales_10 where tadded > curdate()); | ||
+ | |||
+ | SELECT now(), "31 - cantidad de bytes contados hoy",curdate(), | ||
+ | CASE | ||
+ | WHEN ABS(@bytes) < 1024 THEN CONCAT( ROUND( @bytes, 3 ), ' Bytes') | ||
+ | WHEN ABS(@bytes) < 1048576 THEN CONCAT( ROUND( (@bytes/1024), 3 ), ' KB') | ||
+ | WHEN ABS(@bytes) < 1073741824 THEN CONCAT( ROUND( (@bytes/1048576), 3 ), ' MB') | ||
+ | WHEN ABS(@bytes) < 1099511627776 THEN CONCAT( ROUND( (@bytes/1073741824), 3 ), ' GB' ) | ||
+ | WHEN ABS(@bytes) < 1125899906842624 THEN CONCAT( ROUND( (@bytes/1099511627776), 3 ), ' TB') | ||
+ | WHEN ABS(@bytes) < 1152921504606846976 THEN CONCAT( ROUND( (@bytes/1125899906842624), 3 ), ' PB' ) | ||
+ | WHEN ABS(@bytes) < 1180591620717411303424 THEN CONCAT( ROUND( (@bytes/1152921504606846976) ,2), ' EB' ) | ||
+ | WHEN ABS(@bytes) < 1208925819614629174706176 THEN CONCAT( ROUND( (@bytes/1180591620717411303424), 3), ' ZB' ) | ||
+ | WHEN ABS(@bytes) < 1237940039285380274899124224 THEN CONCAT( ROUND( (@bytes/1208925819614629174706176), 3), ' YB' ) | ||
+ | WHEN ABS(@bytes) < 1267650600228229401496703205376 THEN CONCAT( ROUND( (@bytes/1237940039285380274899124224), 3), ' BB' ) | ||
+ | END | ||
+ | as totales; | ||
+ | | ||
- | |||
END$$ | END$$ | ||
Line 865: | Line 906: | ||
GRANT SELECT, INSERT ON `squid_log_db`.`access_log` | GRANT SELECT, INSERT ON `squid_log_db`.`access_log` | ||
TO 'squid_process'@'10.12.1.77' | TO 'squid_process'@'10.12.1.77' | ||
- | IDENTIFIED BY PASSWORD '*0FA27A617EFEAA7FB28C1829C581BC7902B306A2'; | + | IDENTIFIED BY PASSWORD 'XXXXX'; |
Line 872: | Line 913: | ||
GRANT ALL PRIVILEGES ON `squid_log_db`.* | GRANT ALL PRIVILEGES ON `squid_log_db`.* | ||
TO 'squid_log'@'%' | TO 'squid_log'@'%' | ||
- | IDENTIFIED BY PASSWORD '*3619196BECCB2DE880F591CC8D552269E30DE687'; | + | IDENTIFIED BY PASSWORD 'XXXXX'; |