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

Les deux révisions précédentes Révision précédente
Prochaine révision
Révision précédente
tuto:ipasserelle:web:squid_log_mysql [09/10/2012 14:22]
dani [Schéma MySQL]
tuto:ipasserelle:web:squid_log_mysql [18/10/2012 09:44] (Version actuelle)
dani [Script de journalisation]
Ligne 73: 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 134: 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 153: 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 177: 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 203: 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 216: 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 305: Ligne 328:
  
 </file> </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>
  • tuto/ipasserelle/web/squid_log_mysql.1349785324.txt.gz
  • Dernière modification: 09/10/2012 14:22
  • de dani