tuto:ipasserelle:web:squid_log_mysql

Différences

Ci-dessous, les différences entre deux révisions de la page.

Lien vers cette vue comparative

Prochaine révision 		Révision précédente
tuto:ipasserelle:web:squid_log_mysql [09/10/2012 10:35]
dani créée 		tuto:ipasserelle:web:squid_log_mysql [18/10/2012 09:44] (Version actuelle)
dani [Script de journalisation]
Ligne 13: Ligne 13:
  
 ===== Schéma MySQL ===== ===== Schéma MySQL =====
 +
  
 Il faut tout d'abord créer une base de donnée et lui injecter ce schéma: Il faut tout d'abord créer une base de donnée et lui injecter ce schéma:
  
-<file sql squid_log.sql> + 
--- Table to store squid access logs+<file sql squid_log.sql>-- Table to store squid access logs
  
 CREATE TABLE access_log ( CREATE TABLE access_log (
Ligne 32: Ligne 33:
     request_method       VARCHAR(15),     request_method       VARCHAR(15),
     url                  VARCHAR(200),     url                  VARCHAR(200),
 +    domain               VARCHAR(50),
     username             VARCHAR(30),     username             VARCHAR(30),
     squid_connect        VARCHAR(20),     squid_connect        VARCHAR(20),
Ligne 56: Ligne 58:
     client_ip            CHAR(15),     client_ip            CHAR(15),
     url                  VARCHAR(200),     url                  VARCHAR(200),
 +    domain               VARCHAR(50),
     username             VARCHAR(30)     username             VARCHAR(30)
 ) ENGINE=MYISAM; ) ENGINE=MYISAM;
-</file> 
  
 +</file>
  
 ===== Script de journalisation ===== ===== Script de journalisation =====
Ligne 70: Ligne 73:
 use File::Tail; use File::Tail;
 use DBI; use DBI;
 +use URI;
 use Getopt::Long; use Getopt::Long;
 use threads; use threads;
Ligne 131: Ligne 135:
     die "Couldn't connect to database\n" unless ($dbh);     die "Couldn't connect to database\n" unless ($dbh);
     return $dbh;     return $dbh;
 +}
 +
 +# escape chars for MySQL queries
 +sub mysql_escape {
 +    my $string = shift;
 +    $string =~ s|'|\\'|g;
 +    return $string;
 } }
  
Ligne 150: Ligne 161:
         my ($squid_status, $http_status) = split /\//, $status;         my ($squid_status, $http_status) = split /\//, $status;
         my ($squid_connect, $server_ip) = split /\//, $server;         my ($squid_connect, $server_ip) = split /\//, $server;
 +
 +        # Skip stats requested by squidclient
 +        next if ($url =~ m/^cache_object:/);
 +
 +        my $domain;
 +        if ($request_method eq 'CONNECT'){
 +            ($domain,undef) = split /:/, $url;
 +        }
 +        else{
 +            my $uri = URI->new($url);
 +            $domain = mysql_escape($uri->host);
 +        }
  
         # MySQL escape         # MySQL escape
         # Shouldn't be needed, but just in case logs contains junk         # Shouldn't be needed, but just in case logs contains junk
-        $timestamp =~ s|'|\\'|g; +        $timestamp      = mysql_escape($timestamp); 
-        $response_time =~ s|'|\\'|g; +        $response_time  = mysql_escape($response_time); 
-        $client_ip =~ s|'|\\'|g; +        $client_ip      = mysql_escape($client_ip); 
-        $squid_status  =~ s|'|\\'|g; +        $squid_status   = mysql_escape($squid_status); 
-        $http_status =~ s|'|\\'|g; +        $http_status    = mysql_escape($http_status); 
-        $reply_size =~ s|'|\\'|g; +        $reply_size     = mysql_escape($reply_size); 
-        $request_method =~ s|'|\\'|g; +        $request_method = mysql_escape($request_method); 
-        $url =~ s|'|\\'|g; +        $url            = mysql_escape($url); 
-        $username =~ s|'|\\'|g; +        $username       = mysql_escape($username); 
-        $squid_connect =~ s|'|\\'|g; +        $squid_connect  = mysql_escape($squid_connect); 
-        $server_ip =~ s|'|\\'|g; +        $server_ip      = mysql_escape($server_ip); 
-        $mime_type =~ s|'|\\'|g;+        $mime_type      = mysql_escape($mime_type);
  
         printlog("New access_log entry:\ntimestamp: $timestamp\nresponse_time: $response_time\n".         printlog("New access_log entry:\ntimestamp: $timestamp\nresponse_time: $response_time\n".
Ligne 174: Ligne 197:
         my $q = "INSERT INTO access_log ".         my $q = "INSERT INTO access_log ".
                 "(proxy_host, timestamp, response_time, client_ip, squid_status, http_status, ".                 "(proxy_host, timestamp, response_time, client_ip, squid_status, http_status, ".
-                "reply_size, request_method, url, username, squid_connect, server_ip, mime_type)".+                "reply_size, request_method, url, domain, username, squid_connect, server_ip, mime_type)".
                 " VALUES('$host', '$timestamp','$response_time','$client_ip','$squid_status','$http_status',".                 " VALUES('$host', '$timestamp','$response_time','$client_ip','$squid_status','$http_status',".
-                "'$reply_size','$request_method','$url','$username','$squid_connect','$server_ip','$mime_type')";+                "'$reply_size','$request_method','$url','$domain','$username','$squid_connect','$server_ip','$mime_type')";
  
         my $qh = $dbh->prepare($q);         my $qh = $dbh->prepare($q);
Ligne 200: Ligne 223:
         $category = $1;         $category = $1;
         $client_ip =~ s/\/\-$//;         $client_ip =~ s/\/\-$//;
 +
 +        my $uri = URI->new($url);
 +        my $domain = mysql_escape($uri->host);
  
         # MySQL escape         # MySQL escape
-        $date_day =~ s|'|\\'|g; +        $date_day  = mysql_escape($date_day); 
-        $date_time =~ s|'|\\'|g; +        $date_time = mysql_escape($date_time); 
-        $category =~ s|'|\\'|g; +        $category  = mysql_escape($category); 
-        $url  =~ s|'|\\'|g; +        $url       = mysql_escape($url); 
-        $client_ip =~ s|'|\\'|g; +        $client_ip = mysql_escape($client_ip); 
-        $username =~ s|'|\\'|g;+        $username  = mysql_escape($username);
  
         printlog("New deny_log entry:\ndate: $date_day\ntime: $date_time\ncategory: $category\n".         printlog("New deny_log entry:\ndate: $date_day\ntime: $date_time\ncategory: $category\n".
Ligne 213: Ligne 239:
  
         my $q = "INSERT INTO deny_log ".         my $q = "INSERT INTO deny_log ".
-                "(proxy_host, date_day, date_time, category, client_ip, url, username)". +                "(proxy_host, date_day, date_time, category, client_ip, url, domain, username)". 
-                " VALUES('$host', '$date_day','$date_time','$category','$client_ip','$url','$username')";+                " VALUES('$host', '$date_day','$date_time','$category','$client_ip','$url','$domain','$username')";
  
         my $qh = $dbh->prepare($q);         my $qh = $dbh->prepare($q);
Ligne 241: Ligne 267:
 ./squid-db-logd.pl --dbhost=127.0.0.1 --dbname=squid_log --dbuser=squid --dbpass=secret \ ./squid-db-logd.pl --dbhost=127.0.0.1 --dbname=squid_log --dbuser=squid --dbpass=secret \
     --squidguard --squidlogfile=/var/log/squid/access.log --squidguardlogfile=/var/log/squidGuard/deny.log     --squidguard --squidlogfile=/var/log/squid/access.log --squidguardlogfile=/var/log/squidGuard/deny.log
 +</code>
 +
 +===== Rotation et compression des tables =====
 +
 +La journalisation dans une base de donnée peut consommer **beaucoup** d'espace disque (en fonction du nombre d'utilisateur derrière ce proxy). L'idée est donc de faire une rotation des tables access_log et deny_log une fois par mois. Les anciennes tables sont compressées (toujours consultables directement par MySQL, mais en lecture seule) et de supprimer les vieilles tables (par exemple, on conserve 1 an de journaux). Voilà un petit script cron à mettre en place:
 +
 +<file bash squid-rotate.sh>
 +#!/bin/bash
 +
 +DB_HOST=localhost
 +# COnservation des journaux en nombre de jours
 +RETENTION=365
 +SQL_DB=squid_log
 +TABNAME="access_log deny_log"
 +SQLCMD="mysql ${SQL_DB} --batch -u squid -psecret";
 +MONTH=$(date +%m)
 +YEAR=$(date +%Y)
 +
 +# We rotate on the first day of a new month
 +if [ "$MONTH" == "1" ]; then
 +    MONTH=12
 +else
 +    MONTH=$(($MONTH-1))
 +fi
 +
 +# Pad with 0
 +MONTH=$(printf "%02d" $MONTH)
 +
 +DATE=$MONTH"_"$YEAR
 +
 +for T in ${TABNAME}; do
 +    # create table 0
 +    echo "CREATE TABLE IF NOT EXISTS ${T}_0 LIKE ${T};" | $SQLCMD;
 +
 +    # Rotate table
 +    echo "FLUSH TABLES ${T}; RENAME TABLE ${T} TO ${T}_$DATE; RENAME TABLE ${T}_0 TO ${T}" | ${SQLCMD}  >/dev/null 2>&1
 +
 +    # Drop _0 table if we rotate more than two times a month
 +    if echo "DESCRIBE ${T}_0;" | ${SQLCMD} >/dev/null 2>&1; then
 +        echo "DROP TABLE ${T}_0;" | $SQLCMD
 +    fi
 +
 +    #compress 2
 +    cd /var/lib/mysql/${SQL_DB}/
 +    echo "FLUSH TABLE ${T}_${DATE};" | $SQLCMD
 +    myisampack -s "${T}_${DATE}.MYI"
 +    myisamchk -s -rq --sort-index --analyze "${T}_${DATE}.MYI"
 +    echo "FLUSH TABLE ${T}_${DATE}" | $SQLCMD
 +done
 +
 +# Now check existing table to drop olds ones
 +for T in $(echo "show tables" | $SQLCMD | grep -v -P "^Tables_in_"$SQL_DB | grep -v -P "^(access|deny)_log$"); do
 +    TMONTH=$(echo $T | perl -pe 'm/^(access|deny)_log_(\d+)_(\d+)/; print $2;exit')
 +    TYEAR=$(echo $T | perl -pe 'm/^(access|deny)_log_(\d+)_(\d+)/; print $3;exit')
 +    # Drop table if older than configured retention
 +    if [ "$(($(date -d "01/$MONTH/$YEAR" +%s)-$(date -d "01/$TMONTH/$TYEAR" +%s)))" -gt "$((24*3600*$RETENTION))" ]; then
 +        echo "DROP TABLE $T;" | $SQLCMD
 +    fi
 +done
 +
 +</file>
 +
 +===== Quelques requêtes utiles =====
 +
 +  * Les 30 domaines les plus visités:
 +<code sql>
 +select domain,count(domain) as occurances from access_log group by domain order by occurances DESC limit 30;
 +</code>
 +
 +  * Les 10 catégories qui génèrent le plus de blocage par squidGuard
 +<code sql>
 +select category,count(category) as occurances from deny_log group by category order by occurances DESC limit 10;
 +</code>
 +
 +  * Afficher toutes les pages consultées (et le login de l'utilisateur) depuis la machine 192.168.7.50 le 8 Octobre 2012 entre 22h00 et 23h00
 +<code sql>
 +select date_day,date_time,url,username from access_log where client_ip='192.168.7.50' and date_day='2012-10-08' and date_time>'22:00:00' and date_time<'23:00:00';
 +</code>
 +
 +  * Même requête que précédemment, mais en exportant le résultat au format CSV dans /tmp/result.csv
 +<code sql>
 +select date_day,date_time,url,username INTO OUTFILE '/tmp/result.csv' FIELDS TERMINATED BY ','
 +OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n'
 +from access_log where client_ip='192.168.7.50' and date_day='2012-10-08' and date_time>'22:00:00' and date_time<'23:00:00';
 </code> </code>
  • tuto/ipasserelle/web/squid_log_mysql.1349771723.txt.gz
  • Dernière modification: 09/10/2012 10:35
  • de dani