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 | Révision précédente Prochaine révision Les deux révisions suivantes | ||
tuto:ipasserelle:web:squid_log_mysql [09/10/2012 14:21] dani [Schéma MySQL] |
tuto:ipasserelle:web:squid_log_mysql [09/10/2012 14:22] dani [Script de journalisation] |
||
---|---|---|---|
Ligne 19: | Ligne 19: | ||
<file sql squid_log.sql> | <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 33: | Ligne 33: | ||
request_method | request_method | ||
url VARCHAR(200), | url VARCHAR(200), | ||
+ | domain | ||
username | username | ||
squid_connect | squid_connect | ||
Ligne 38: | Ligne 39: | ||
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 45: | Ligne 46: | ||
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 57: | Ligne 58: | ||
client_ip | client_ip | ||
url VARCHAR(200), | url VARCHAR(200), | ||
+ | domain | ||
username | username | ||
) ENGINE=MYISAM; | ) ENGINE=MYISAM; | ||
+ | |||
</ | </ | ||
Ligne 70: | Ligne 73: | ||
use File::Tail; | use File::Tail; | ||
use DBI; | use DBI; | ||
+ | use URI; | ||
use Getopt:: | use Getopt:: | ||
use threads; | use threads; | ||
Ligne 131: | Ligne 135: | ||
die " | die " | ||
return $dbh; | return $dbh; | ||
+ | } | ||
+ | |||
+ | # escape chars for MySQL queries | ||
+ | sub mysql_escape { | ||
+ | my $string = shift; | ||
+ | $string =~ s|' | ||
+ | return $string; | ||
} | } | ||
Ligne 150: | Ligne 161: | ||
my ($squid_status, | my ($squid_status, | ||
my ($squid_connect, | my ($squid_connect, | ||
+ | |||
+ | # Extract domain part | ||
+ | # Usefull to have in a separate column for statistics | ||
+ | my $uri = URI-> | ||
+ | my $domain = mysql_escape($uri-> | ||
# MySQL escape | # MySQL escape | ||
# Shouldn' | # Shouldn' | ||
- | $timestamp =~ s|' | + | $timestamp |
- | $response_time =~ s|' | + | $response_time |
- | $client_ip =~ s|' | + | $client_ip |
- | $squid_status | + | $squid_status |
- | $http_status =~ s|' | + | $http_status |
- | $reply_size =~ s|' | + | $reply_size |
- | $request_method =~ s|' | + | $request_method = mysql_escape($request_method); |
- | $url =~ s|' | + | $url = mysql_escape($url); |
- | $username =~ s|' | + | $username |
- | $squid_connect =~ s|' | + | $squid_connect |
- | $server_ip =~ s|' | + | $server_ip |
- | $mime_type =~ s|' | + | $mime_type |
printlog(" | printlog(" | ||
Ligne 174: | Ligne 190: | ||
my $q = " | my $q = " | ||
" | " | ||
- | " | + | " |
" VALUES(' | " VALUES(' | ||
- | "' | + | "' |
my $qh = $dbh-> | my $qh = $dbh-> | ||
Ligne 200: | Ligne 216: | ||
$category = $1; | $category = $1; | ||
$client_ip =~ s/\/\-$//; | $client_ip =~ s/\/\-$//; | ||
+ | |||
+ | my $uri = URI-> | ||
+ | my $domain = mysql_escape($uri-> | ||
# MySQL escape | # MySQL escape | ||
- | $date_day =~ s|' | + | $date_day |
- | $date_time =~ s|' | + | $date_time = mysql_escape($date_time); |
- | $category =~ s|' | + | $category |
- | $url =~ s|' | + | $url |
- | $client_ip =~ s|' | + | $client_ip = mysql_escape($client_ip); |
- | $username =~ s|' | + | $username |
printlog(" | printlog(" | ||
Ligne 213: | Ligne 232: | ||
my $q = " | my $q = " | ||
- | " | + | " |
- | " VALUES(' | + | " VALUES(' |
my $qh = $dbh-> | my $qh = $dbh-> |