User Tools

Site Tools


quota

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

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(baseselect 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';
  
  
quota.txt · Last modified: 2020/04/10 17:38 (external edit)