Changes

=== Logs MySQL ===

=== Logs MySQL ===

MySQL loging of clients requests is handled by a independent daemon called squid-db-logd. It monitors squid access log and squidGuard deny log in real time, parse it and put everything in the database called squid_log. In this database, the table access_log list all the access while the deny_log only list denied pages. This feature may need a lot of disk space. On a busy server, you can easily reach 3GB / month only for the database (and more for the dump when you backup your server). To limit the needed space, a cron job remove the oldest entries. The default config keeps one year of log. You can change this setting with (value is in day and default is 365)

Le requêtage des logs MYSQL est assurée par un démon indépendant appelé squid-db-logd. Il surveille le journal des accès Squid et Squidguard deny log en temps réel, l'analyse et met tout dans le squid_log.

Dans cette base de données, la table access_log liste tout les accès tandi que le deny_log ne list que les accès interdit. Cette fonction peut avoir besoin de beaucoup d'espace disque. Sur un serveur occupé, vous pouvez facilement atteindre 3 GB/mois seulement pour la base de données (et plus pour les dumps quand vous sauvegardez votre serveur). Pour limiter l'espace nécessaire, une tâche cron permet de supprimer les entrées les plus anciennes. La configuration par défaut conserve un an de logs (soit environ 40 GB de logs). Vous pouvez modifier ce réglage avec cette commande (la valeur par defaut est de 365 jours) :

 

  db configuration setprop squid-db-logd Retention 180

  db configuration setprop squid-db-logd Retention 180

If you want to completely disable this feature, you can stop this daemon:

Si vous souhaitez desactiver complétement cette fonction, vous pouvez stoper ce service :

 

  db configuration setprop squid-db-logd status disabled

  db configuration setprop squid-db-logd status disabled

  sv d /service/squid-db-logd

  sv d /service/squid-db-logd

Here are some example of queries you can run:

 

* Obtenez les 30 sites les plus visités :

  echo "SELECT DOMAIN,COUNT(DOMAIN) AS occurances FROM access_log GROUP BY DOMAIN ORDER BY occurances DESC LIMIT 30;" | mysql squid_log

  echo "SELECT DOMAIN,COUNT(DOMAIN) AS occurances FROM access_log GROUP BY DOMAIN ORDER BY occurances DESC LIMIT 30;" | mysql squid_log

*Get the top 10 most used blocked categories

* Obtenez les 10 categories les plus blockés :

 

  echo "SELECT category,COUNT(category) AS occurances FROM deny_log GROUP BY category ORDER BY occurances DESC LIMIT 10;" | mysql squid_log

  echo "SELECT category,COUNT(category) AS occurances FROM deny_log GROUP BY category ORDER BY occurances DESC LIMIT 10;" | mysql squid_log

*get all the pages requested by the client 192.168.7.50 on Oct 12 2012 between 10pm and 11 pm, and export the result in /tmp/result.csv

* Obtenez toutes les pages demandés par le client 192.168.7.50 le 12 Oct 2012 entre 22h00 et 23h00 et exporté le resultat dans /tmp/result.csv

  echo "SELECT date_day,date_time,url,username INTO OUTFILE '/tmp/result.csv' FIELDS TERMINATED BY ','

  echo "SELECT date_day,date_time,url,username INTO OUTFILE '/tmp/result.csv' FIELDS TERMINATED BY ','

  OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n'

  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';" mysql squid_log

  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';" mysql squid_log

=== Désintalation ===

=== Désintalation ===