Différences
Ci-dessous, les différences entre deux révisions de la page.
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:35] dani créée |
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 241: | Ligne 260: | ||
./ | ./ | ||
--squidguard --squidlogfile=/ | --squidguard --squidlogfile=/ | ||
+ | </ | ||
+ | |||
+ | ===== Rotation et compression des tables ===== | ||
+ | |||
+ | La journalisation dans une base de donnée peut consommer **beaucoup** d' | ||
+ | |||
+ | <file bash squid-rotate.sh> | ||
+ | #!/bin/bash | ||
+ | |||
+ | DB_HOST=localhost | ||
+ | # COnservation des journaux en nombre de jours | ||
+ | RETENTION=365 | ||
+ | SQL_DB=squid_log | ||
+ | TABNAME=" | ||
+ | SQLCMD=" | ||
+ | MONTH=$(date +%m) | ||
+ | YEAR=$(date +%Y) | ||
+ | |||
+ | # We rotate on the first day of a new month | ||
+ | if [ " | ||
+ | MONTH=12 | ||
+ | else | ||
+ | MONTH=$(($MONTH-1)) | ||
+ | fi | ||
+ | |||
+ | # Pad with 0 | ||
+ | MONTH=$(printf " | ||
+ | |||
+ | DATE=$MONTH" | ||
+ | |||
+ | for T in ${TABNAME}; do | ||
+ | # create table 0 | ||
+ | echo " | ||
+ | |||
+ | # Rotate table | ||
+ | echo "FLUSH TABLES ${T}; RENAME TABLE ${T} TO ${T}_$DATE; RENAME TABLE ${T}_0 TO ${T}" | ${SQLCMD} | ||
+ | |||
+ | # Drop _0 table if we rotate more than two times a month | ||
+ | if echo " | ||
+ | echo "DROP TABLE ${T}_0;" | ||
+ | fi | ||
+ | |||
+ | #compress 2 | ||
+ | cd / | ||
+ | echo "FLUSH TABLE ${T}_${DATE};" | ||
+ | myisampack -s " | ||
+ | myisamchk -s -rq --sort-index --analyze " | ||
+ | echo "FLUSH TABLE ${T}_${DATE}" | ||
+ | done | ||
+ | |||
+ | # Now check existing table to drop olds ones | ||
+ | for T in $(echo "show tables" | ||
+ | TMONTH=$(echo $T | perl -pe ' | ||
+ | TYEAR=$(echo $T | perl -pe ' | ||
+ | # Drop table if older than configured retention | ||
+ | if [ " | ||
+ | echo "DROP TABLE $T;" | $SQLCMD | ||
+ | fi | ||
+ | done | ||
+ | |||
+ | </ | ||
+ | |||
+ | ===== 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, | ||
</ | </ |