Différences
Ci-dessous, les différences entre deux révisions de la page.
Les deux révisions précédentes Révision précédente | Prochaine révision Les deux révisions suivantes | ||
tuto:ipasserelle:web:squid_log_mysql [09/10/2012 10:46] dani [Script de journalisation] |
tuto:ipasserelle:web:squid_log_mysql [09/10/2012 14:21] dani [Schéma MySQL] |
||
---|---|---|---|
Ligne 13: | Ligne 13: | ||
===== Schéma MySQL ===== | ===== Schéma MySQL ===== | ||
+ | |||
Il faut tout d' | Il faut tout d' | ||
- | <file sql squid_log.sql> | ||
- | -- Table to store squid access logs | ||
+ | <file sql squid_log.sql> | ||
+ | |||
CREATE TABLE access_log ( | CREATE TABLE access_log ( | ||
id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, | id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, | ||
proxy_host | proxy_host | ||
- | | + | |
date_day | date_day | ||
date_time | date_time | ||
Ligne 37: | Ligne 38: | ||
mime_type | mime_type | ||
) ENGINE=MYISAM; | ) ENGINE=MYISAM; | ||
+ | |||
-- trigger that extracts the date value from the timestamp column | -- trigger that extracts the date value from the timestamp column | ||
-- and stores it in the date_day and date_time columns | -- and stores it in the date_day and date_time columns | ||
Ligne 44: | Ligne 45: | ||
CREATE TRIGGER extract_date_bi BEFORE INSERT ON access_log FOR EACH ROW | CREATE TRIGGER extract_date_bi BEFORE INSERT ON access_log FOR EACH ROW | ||
BEGIN | BEGIN | ||
- | SET NEW.date_day | + | SET NEW.date_day |
- | SET NEW.date_time = TIME(FROM_UNIXTIME(NEW.timestamp)); | + | SET NEW.date_time = TIME(FROM_UNIXTIME(NEW.TIMESTAMP)); |
END; // | END; // | ||
+ | |||
CREATE TABLE deny_log ( | CREATE TABLE deny_log ( | ||
id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, | id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, | ||
Ligne 59: | Ligne 60: | ||
) ENGINE=MYISAM; | ) ENGINE=MYISAM; | ||
</ | </ | ||
- | |||
===== Script de journalisation ===== | ===== Script de journalisation ===== |