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 10:46] dani [Script de journalisation] |
tuto:ipasserelle:web:squid_log_mysql [09/10/2012 14:34] dani |
||
---|---|---|---|
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 ( | ||
Ligne 32: | Ligne 33: | ||
request_method | request_method | ||
url VARCHAR(200), | url VARCHAR(200), | ||
+ | domain | ||
username | username | ||
squid_connect | squid_connect | ||
Ligne 56: | Ligne 58: | ||
client_ip | client_ip | ||
url VARCHAR(200), | url VARCHAR(200), | ||
+ | domain | ||
username | username | ||
) ENGINE=MYISAM; | ) ENGINE=MYISAM; | ||
- | </ | ||
+ | </ | ||
===== Script de journalisation ===== | ===== Script de journalisation ===== | ||
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-> | ||
Ligne 302: | Ligne 321: | ||
</ | </ | ||
+ | |||
+ | ===== Quelques requêtes utiles ===== | ||
+ | |||
+ | * Les 30 domaines les plus visités: | ||
+ | <code sql> | ||
+ | select domain, | ||
+ | </ | ||
+ | |||
+ | * Les 10 catégories les plus bloquées par squidGuard | ||
+ | <code sql> | ||
+ | select category, | ||
+ | </ |