Requêtes SQL sur les journaux de squid

Avec la contrib WebFiltering, tous les journaux de squid sont inscrit dans une base de données MySQL. Cette page recense quelques requêtes utiles pour l'analyse de ces données

• Les 60 noms de domaines les plus visités sur les 3 derniers mois

SELECT DOMAIN, COUNT( DOMAIN ) AS occurances
FROM access_log
WHERE date_day > DATE_SUB( CURDATE( ) , INTERVAL 2 MONTH ) 
AND DOMAIN NOT LIKE  '192.168.%'
GROUP BY DOMAIN
ORDER BY occurances DESC
LIMIT 60;

• La même chose, en essayant d'enlever des résultats les serveurs de pub et autres traceurs les plus courants

SELECT DOMAIN, COUNT( DOMAIN ) AS occurances
FROM access_log
WHERE date_day > DATE_SUB( CURDATE( ) , INTERVAL 2 MONTH ) 
AND DOMAIN NOT LIKE '192.168.%'
AND DOMAIN NOT LIKE '%adnxs.com'
AND DOMAIN NOT LIKE '%google-analytics.com'
AND DOMAIN NOT LIKE '%cedexis.com'
AND DOMAIN NOT LIKE '%cedexis-radar.net'
AND DOMAIN NOT LIKE '%xiti.com'
AND DOMAIN NOT LIKE 'ads.%'
AND DOMAIN NOT LIKE '%adnext.fr'
AND DOMAIN NOT LIKE '%.addthis.com'
AND DOMAIN NOT LIKE '%.googleadservices.com'
AND DOMAIN NOT LIKE '%.scorecardresearch.com'
AND DOMAIN NOT LIKE '%.estat.com'
AND DOMAIN NOT LIKE '%.doubleclick.net'
AND DOMAIN NOT LIKE 'pubs.lemonde.fr'
AND DOMAIN NOT LIKE '%.quantserve.com'
AND DOMAIN NOT LIKE '%.pubdirecte.com'
AND DOMAIN NOT LIKE '%.ligatus.com'
AND DOMAIN NOT LIKE '%.ezakus.net'
AND DOMAIN NOT LIKE 'adserver.adtech.de'
AND DOMAIN NOT LIKE '%.yieldmanager.com'
AND DOMAIN NOT LIKE 'stats.wordpress.com'
AND DOMAIN NOT LIKE '%.smartadserver.com'
GROUP BY DOMAIN
ORDER BY occurances DESC
LIMIT 60

• Les 30 plus gros consommateurs de web (en bande passante), sur les 3 derniers mois

SELECT client_ip AS  'Adresse IP', username AS Utilisateur, SUM( reply_size / ( 1024 *1024 ) ) AS Volume
FROM access_log WHERE client_ip!='127.0.0.1' 
AND date_day > DATE_SUB( CURDATE( ) , INTERVAL 2 MONTH ) 
GROUP BY client_ip
ORDER BY Volume DESC 
LIMIT 30;