Webalizer et Google Analytics m'ont aidé à mieux comprendre ce qui se passe sur les sites Web depuis de nombreuses années. Maintenant, je comprends qu'ils fournissent très peu d'informations utiles. Ayant accès à votre fichier access.log, il est très simple de comprendre les statistiques et de mettre en œuvre des outils assez basiques, comme sqlite, html, le langage sql et tout langage de programmation de script.
La source de données de Webalizer est le fichier access.log du serveur. Voici à quoi ressemblent ses barres et ses chiffres, d'où seul le volume total du trafic ressort :
Des outils tels que Google Analytics collectent eux-mêmes les données de la page chargée. Ils nous montrent quelques diagrammes et lignes sur la base desquels il est souvent difficile de tirer des conclusions correctes. Peut-être aurait-on dû faire plus d'efforts ? Je ne sais pas.
Alors, qu’est-ce que je voulais voir dans les statistiques de visites du site Web ?
Trafic des utilisateurs et des robots
Souvent, le trafic du site est limité et il est nécessaire de voir quelle quantité de trafic utile est utilisée. Par exemple, comme ceci :
Requête de rapport SQL
SELECT
1 as 'StackedArea: Traffic generated by Users and Bots',
strftime('%d.%m', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Day',
SUM(CASE WHEN USG.AGENT_BOT!='n.a.' THEN FCT.BYTES ELSE 0 END)/1000 AS 'Bots, KB',
SUM(CASE WHEN USG.AGENT_BOT='n.a.' THEN FCT.BYTES ELSE 0 END)/1000 AS 'Users, KB'
FROM
FCT_ACCESS_USER_AGENT_DD FCT,
DIM_USER_AGENT USG
WHERE FCT.DIM_USER_AGENT_ID=USG.DIM_USER_AGENT_ID
AND datetime(FCT.EVENT_DT, 'unixepoch') >= date('now', '-14 day')
GROUP BY strftime('%d.%m', datetime(FCT.EVENT_DT, 'unixepoch'))
ORDER BY FCT.EVENT_DTLe graphique montre l'activité constante des robots. Il serait intéressant d'étudier en détail les représentants les plus actifs.
Des robots ennuyeux
Nous classons les robots en fonction des informations de l'agent utilisateur. Des statistiques supplémentaires sur le trafic quotidien, le nombre de requêtes réussies et infructueuses donnent une bonne idée de l'activité des robots.
Requête de rapport SQL
SELECT
1 AS 'Table: Annoying Bots',
MAX(USG.AGENT_BOT) AS 'Bot',
ROUND(SUM(FCT.BYTES)/1000 / 14.0, 1) AS 'KB per Day',
ROUND(SUM(FCT.IP_CNT) / 14.0, 1) AS 'IPs per Day',
ROUND(SUM(CASE WHEN STS.STATUS_GROUP IN ('Client Error', 'Server Error') THEN FCT.REQUEST_CNT / 14.0 ELSE 0 END), 1) AS 'Error Requests per Day',
ROUND(SUM(CASE WHEN STS.STATUS_GROUP IN ('Successful', 'Redirection') THEN FCT.REQUEST_CNT / 14.0 ELSE 0 END), 1) AS 'Success Requests per Day',
USG.USER_AGENT_NK AS 'Agent'
FROM FCT_ACCESS_USER_AGENT_DD FCT,
DIM_USER_AGENT USG,
DIM_HTTP_STATUS STS
WHERE FCT.DIM_USER_AGENT_ID = USG.DIM_USER_AGENT_ID
AND FCT.DIM_HTTP_STATUS_ID = STS.DIM_HTTP_STATUS_ID
AND USG.AGENT_BOT != 'n.a.'
AND datetime(FCT.EVENT_DT, 'unixepoch') >= date('now', '-14 day')
GROUP BY USG.USER_AGENT_NK
ORDER BY 3 DESC
LIMIT 10Dans ce cas, le résultat de l'analyse a été la décision de restreindre l'accès au site en l'ajoutant au fichier robots.txt
User-agent: AhrefsBot
Disallow: /
User-agent: dotbot
Disallow: /
User-agent: bingbot
Crawl-delay: 5
Les deux premiers robots ont disparu de la table et les robots MS sont descendus des premières lignes.
Jour et heure de la plus grande activité
Des améliorations sont visibles dans le trafic. Pour les étudier en détail, il est nécessaire de mettre en évidence l'heure de leur apparition, et il n'est pas nécessaire d'afficher toutes les heures et tous les jours de mesure du temps. Cela facilitera la recherche de demandes individuelles dans le fichier journal si une analyse détaillée est nécessaire.
Requête de rapport SQL
SELECT
1 AS 'Line: Day and Hour of Hits from Users and Bots',
strftime('%d.%m-%H', datetime(EVENT_DT, 'unixepoch')) AS 'Date Time',
HIB AS 'Bots, Hits',
HIU AS 'Users, Hits'
FROM (
SELECT
EVENT_DT,
SUM(CASE WHEN AGENT_BOT!='n.a.' THEN LINE_CNT ELSE 0 END) AS HIB,
SUM(CASE WHEN AGENT_BOT='n.a.' THEN LINE_CNT ELSE 0 END) AS HIU
FROM FCT_ACCESS_REQUEST_REF_HH
WHERE datetime(EVENT_DT, 'unixepoch') >= date('now', '-14 day')
GROUP BY EVENT_DT
ORDER BY SUM(LINE_CNT) DESC
LIMIT 10
) ORDER BY EVENT_DTOn observe les heures les plus actives 11, 14 et 20 du premier jour sur le graphique. Mais le lendemain à 13hXNUMX les robots étaient actifs.
Activité quotidienne moyenne des utilisateurs par semaine
Nous avons un peu réglé les choses avec l'activité et le trafic. La question suivante concernait l'activité des utilisateurs eux-mêmes. Pour de telles statistiques, de longues périodes d’agrégation, par exemple une semaine, sont souhaitables.
Requête de rapport SQL
SELECT
1 as 'Line: Average Daily User Activity by Week',
strftime('%W week', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Week',
ROUND(1.0*SUM(FCT.PAGE_CNT)/SUM(FCT.IP_CNT),1) AS 'Pages per IP per Day',
ROUND(1.0*SUM(FCT.FILE_CNT)/SUM(FCT.IP_CNT),1) AS 'Files per IP per Day'
FROM
FCT_ACCESS_USER_AGENT_DD FCT,
DIM_USER_AGENT USG,
DIM_HTTP_STATUS HST
WHERE FCT.DIM_USER_AGENT_ID=USG.DIM_USER_AGENT_ID
AND FCT.DIM_HTTP_STATUS_ID = HST.DIM_HTTP_STATUS_ID
AND USG.AGENT_BOT='n.a.' /* users only */
AND HST.STATUS_GROUP IN ('Successful') /* good pages */
AND datetime(FCT.EVENT_DT, 'unixepoch') > date('now', '-3 month')
GROUP BY strftime('%W week', datetime(FCT.EVENT_DT, 'unixepoch'))
ORDER BY FCT.EVENT_DTLes statistiques hebdomadaires montrent qu'en moyenne un utilisateur ouvre 1,6 page par jour. Le nombre de fichiers demandés par utilisateur dépend dans ce cas de l'ajout de nouveaux fichiers sur le site.
Toutes les demandes et leurs statuts
Webalizer affichait toujours des codes de page spécifiques et je voulais toujours voir uniquement le nombre de demandes réussies et d'erreurs.
Requête de rapport SQL
SELECT
1 as 'Line: All Requests by Status',
strftime('%d.%m', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Day',
SUM(CASE WHEN STS.STATUS_GROUP='Successful' THEN FCT.REQUEST_CNT ELSE 0 END) AS 'Success',
SUM(CASE WHEN STS.STATUS_GROUP='Redirection' THEN FCT.REQUEST_CNT ELSE 0 END) AS 'Redirect',
SUM(CASE WHEN STS.STATUS_GROUP='Client Error' THEN FCT.REQUEST_CNT ELSE 0 END) AS 'Customer Error',
SUM(CASE WHEN STS.STATUS_GROUP='Server Error' THEN FCT.REQUEST_CNT ELSE 0 END) AS 'Server Error'
FROM
FCT_ACCESS_USER_AGENT_DD FCT,
DIM_HTTP_STATUS STS
WHERE FCT.DIM_HTTP_STATUS_ID=STS.DIM_HTTP_STATUS_ID
AND datetime(FCT.EVENT_DT, 'unixepoch') >= date('now', '-14 day')
GROUP BY strftime('%d.%m', datetime(FCT.EVENT_DT, 'unixepoch'))
ORDER BY FCT.EVENT_DTLe rapport affiche les demandes, pas les clics (hits), contrairement à LINE_CNT, la métrique REQUEST_CNT est calculée comme COUNT(DISTINCT STG.REQUEST_NK). L'objectif est d'afficher des événements efficaces, par exemple, les robots MS interrogent le fichier robots.txt des centaines de fois par jour et, dans ce cas, ces sondages seront comptés une fois. Cela vous permet de lisser les sauts dans le graphique.
Sur le graphique, vous pouvez voir de nombreuses erreurs - ce sont des pages inexistantes. Le résultat de l'analyse a été l'ajout de redirections depuis des pages distantes.
Mauvaises demandes
Pour examiner les demandes en détail, vous pouvez afficher des statistiques détaillées.
Requête de rapport SQL
SELECT
1 AS 'Table: Top Error Requests',
REQ.REQUEST_NK AS 'Request',
'Error' AS 'Request Status',
ROUND(SUM(FCT.LINE_CNT) / 14.0, 1) AS 'Hits per Day',
ROUND(SUM(FCT.IP_CNT) / 14.0, 1) AS 'IPs per Day',
ROUND(SUM(FCT.BYTES)/1000 / 14.0, 1) AS 'KB per Day'
FROM
FCT_ACCESS_REQUEST_REF_HH FCT,
DIM_REQUEST_V_ACT REQ
WHERE FCT.DIM_REQUEST_ID = REQ.DIM_REQUEST_ID
AND FCT.STATUS_GROUP IN ('Client Error', 'Server Error')
AND datetime(FCT.EVENT_DT, 'unixepoch') >= date('now', '-14 day')
GROUP BY REQ.REQUEST_NK
ORDER BY 4 DESC
LIMIT 20Cette liste contiendra également tous les appels, par exemple une requête vers /wp-login.php. En ajustant les règles de réécriture des requêtes par le serveur, vous pouvez ajuster la réaction du serveur à ces requêtes et les envoyer à la page de démarrage.
Ainsi, quelques rapports simples basés sur le fichier journal du serveur donnent une image assez complète de ce qui se passe sur le site.
Comment obtenir des informations ?
Une base de données SQLite est suffisante. Créons des tables : auxiliaires pour la journalisation des processus ETL.
Étape de table où nous écrirons les fichiers journaux en utilisant PHP. Deux tableaux agrégés. Créons un tableau quotidien avec des statistiques sur les agents utilisateurs et les statuts des demandes. Toutes les heures avec des statistiques sur les demandes, les groupes de statut et les agents. Quatre tableaux de mesures pertinentes.
Le résultat est le modèle relationnel suivant :
Modèle de données
Script pour créer un objet dans une base de données sqlite :
Création d'objets DDL
DROP TABLE IF EXISTS DIM_USER_AGENT;
CREATE TABLE DIM_USER_AGENT (
DIM_USER_AGENT_ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
USER_AGENT_NK TEXT NOT NULL DEFAULT 'n.a.',
AGENT_OS TEXT NOT NULL DEFAULT 'n.a.',
AGENT_ENGINE TEXT NOT NULL DEFAULT 'n.a.',
AGENT_DEVICE TEXT NOT NULL DEFAULT 'n.a.',
AGENT_BOT TEXT NOT NULL DEFAULT 'n.a.',
UPDATE_DT INTEGER NOT NULL DEFAULT 0,
UNIQUE (USER_AGENT_NK)
);
INSERT INTO DIM_USER_AGENT (DIM_USER_AGENT_ID) VALUES (-1);Scène
Dans le cas du fichier access.log, il est nécessaire de lire, analyser et écrire toutes les requêtes dans la base de données. Cela peut être fait soit directement à l'aide d'un langage de script, soit à l'aide des outils SQLite.
Format du fichier journal :
//67.221.59.195 - - [28/Dec/2012:01:47:47 +0100] "GET /files/default.css HTTP/1.1" 200 1512 "https://project.edu/" "Mozilla/4.0"
//host ident auth time method request_nk protocol status bytes ref browser
$log_pattern = '/^([^ ]+) ([^ ]+) ([^ ]+) ([[^]]+]) "(.*) (.*) (.*)" ([0-9-]+) ([0-9-]+) "(.*)" "(.*)"$/';
Propagation des clés
Lorsque les données brutes sont dans la base de données, vous devez écrire les clés qui n'y sont pas dans les tables de mesure. Il sera alors possible de construire une référence aux mesures. Par exemple, dans la table DIM_REFERRER, la clé est une combinaison de trois champs.
Requête de propagation de clé SQL
/* Propagate the referrer from access log */
INSERT INTO DIM_REFERRER (HOST_NK, PATH_NK, QUERY_NK, UPDATE_DT)
SELECT
CLS.HOST_NK,
CLS.PATH_NK,
CLS.QUERY_NK,
STRFTIME('%s','now') AS UPDATE_DT
FROM (
SELECT DISTINCT
REFERRER_HOST AS HOST_NK,
REFERRER_PATH AS PATH_NK,
CASE WHEN INSTR(REFERRER_QUERY,'&sid')>0 THEN SUBSTR(REFERRER_QUERY, 1, INSTR(REFERRER_QUERY,'&sid')-1) /* отрезаем sid - специфика цмс */
ELSE REFERRER_QUERY END AS QUERY_NK
FROM STG_ACCESS_LOG
) CLS
LEFT OUTER JOIN DIM_REFERRER TRG
ON (CLS.HOST_NK = TRG.HOST_NK AND CLS.PATH_NK = TRG.PATH_NK AND CLS.QUERY_NK = TRG.QUERY_NK)
WHERE TRG.DIM_REFERRER_ID IS NULLLa propagation vers la table de l'agent utilisateur peut contenir une logique de robot, par exemple l'extrait SQL :
CASE
WHEN INSTR(LOWER(CLS.BROWSER),'yandex.com')>0
THEN 'yandex'
WHEN INSTR(LOWER(CLS.BROWSER),'googlebot')>0
THEN 'google'
WHEN INSTR(LOWER(CLS.BROWSER),'bingbot')>0
THEN 'microsoft'
WHEN INSTR(LOWER(CLS.BROWSER),'ahrefsbot')>0
THEN 'ahrefs'
WHEN INSTR(LOWER(CLS.BROWSER),'mj12bot')>0
THEN 'majestic-12'
WHEN INSTR(LOWER(CLS.BROWSER),'compatible')>0 OR INSTR(LOWER(CLS.BROWSER),'http')>0
OR INSTR(LOWER(CLS.BROWSER),'libwww')>0 OR INSTR(LOWER(CLS.BROWSER),'spider')>0
OR INSTR(LOWER(CLS.BROWSER),'java')>0 OR INSTR(LOWER(CLS.BROWSER),'python')>0
OR INSTR(LOWER(CLS.BROWSER),'robot')>0 OR INSTR(LOWER(CLS.BROWSER),'curl')>0
OR INSTR(LOWER(CLS.BROWSER),'wget')>0
THEN 'other'
ELSE 'n.a.' END AS AGENT_BOTTableaux agrégés
Enfin, nous allons charger les tables agrégées ; par exemple, la table journalière peut être chargée comme suit :
Requête SQL pour charger l'agrégat
/* Load fact from access log */
INSERT INTO FCT_ACCESS_USER_AGENT_DD (EVENT_DT, DIM_USER_AGENT_ID, DIM_HTTP_STATUS_ID, PAGE_CNT, FILE_CNT, REQUEST_CNT, LINE_CNT, IP_CNT, BYTES)
WITH STG AS (
SELECT
STRFTIME( '%s', SUBSTR(TIME_NK,9,4) || '-' ||
CASE SUBSTR(TIME_NK,5,3)
WHEN 'Jan' THEN '01' WHEN 'Feb' THEN '02' WHEN 'Mar' THEN '03' WHEN 'Apr' THEN '04' WHEN 'May' THEN '05' WHEN 'Jun' THEN '06'
WHEN 'Jul' THEN '07' WHEN 'Aug' THEN '08' WHEN 'Sep' THEN '09' WHEN 'Oct' THEN '10' WHEN 'Nov' THEN '11'
ELSE '12' END || '-' || SUBSTR(TIME_NK,2,2) || ' 00:00:00' ) AS EVENT_DT,
BROWSER AS USER_AGENT_NK,
REQUEST_NK,
IP_NR,
STATUS,
LINE_NK,
BYTES
FROM STG_ACCESS_LOG
)
SELECT
CAST(STG.EVENT_DT AS INTEGER) AS EVENT_DT,
USG.DIM_USER_AGENT_ID,
HST.DIM_HTTP_STATUS_ID,
COUNT(DISTINCT (CASE WHEN INSTR(STG.REQUEST_NK,'.')=0 THEN STG.REQUEST_NK END) ) AS PAGE_CNT,
COUNT(DISTINCT (CASE WHEN INSTR(STG.REQUEST_NK,'.')>0 THEN STG.REQUEST_NK END) ) AS FILE_CNT,
COUNT(DISTINCT STG.REQUEST_NK) AS REQUEST_CNT,
COUNT(DISTINCT STG.LINE_NK) AS LINE_CNT,
COUNT(DISTINCT STG.IP_NR) AS IP_CNT,
SUM(BYTES) AS BYTES
FROM STG,
DIM_HTTP_STATUS HST,
DIM_USER_AGENT USG
WHERE STG.STATUS = HST.STATUS_NK
AND STG.USER_AGENT_NK = USG.USER_AGENT_NK
AND CAST(STG.EVENT_DT AS INTEGER) > $param_epoch_from /* load epoch date */
AND CAST(STG.EVENT_DT AS INTEGER) < strftime('%s', date('now', 'start of day'))
GROUP BY STG.EVENT_DT, HST.DIM_HTTP_STATUS_ID, USG.DIM_USER_AGENT_IDLa base de données SQLite vous permet d'écrire des requêtes complexes. AVEC contient la préparation des données et des clés. La requête principale collecte toutes les références aux dimensions.
La condition ne permettra pas de recharger l'historique : CAST(STG.EVENT_DT AS INTEGER) > $param_epoch_from, où le paramètre est le résultat de la requête
'SELECT COALESCE(MAX(EVENT_DT), '3600') AS LAST_EVENT_EPOCH FROM FCT_ACCESS_USER_AGENT_DD'
La condition ne chargera que la journée complète : CAST(STG.EVENT_DT AS INTEGER) < strftime('%s', date('now', 'start of day'))
Le comptage de pages ou de fichiers s'effectue de manière primitive, par recherche d'un point.
Rapports
Dans les systèmes de visualisation complexes, il est possible de créer un méta-modèle basé sur des objets de base de données, de gérer dynamiquement les filtres et les règles d'agrégation. En fin de compte, tous les outils décents génèrent une requête SQL.
Dans cet exemple, nous allons créer des requêtes SQL prêtes à l'emploi et les enregistrer sous forme de vues dans la base de données - ce sont des rapports.
Visualisation
Bluff : de superbes graphiques en JavaScript ont été utilisés comme outil de visualisation
Pour ce faire, il a fallu parcourir tous les rapports en PHP et générer un fichier html avec des tableaux.
$sqls = array(
'SELECT * FROM RPT_ACCESS_USER_VS_BOT',
'SELECT * FROM RPT_ACCESS_ANNOYING_BOT',
'SELECT * FROM RPT_ACCESS_TOP_HOUR_HIT',
'SELECT * FROM RPT_ACCESS_USER_ACTIVE',
'SELECT * FROM RPT_ACCESS_REQUEST_STATUS',
'SELECT * FROM RPT_ACCESS_TOP_REQUEST_PAGE',
'SELECT * FROM RPT_ACCESS_TOP_REQUEST_REFERRER',
'SELECT * FROM RPT_ACCESS_NEW_REQUEST',
'SELECT * FROM RPT_ACCESS_TOP_REQUEST_SUCCESS',
'SELECT * FROM RPT_ACCESS_TOP_REQUEST_ERROR'
);L'outil visualise simplement des tableaux de résultats.
conclusion
En utilisant l'analyse Web comme exemple, l'article décrit les mécanismes nécessaires à la création d'entrepôts de données. Comme le montrent les résultats, les outils les plus simples suffisent pour une analyse approfondie et une visualisation des données.
À l'avenir, en utilisant ce référentiel comme exemple, nous essaierons de mettre en œuvre des structures telles que des dimensions, des métadonnées, des niveaux d'agrégation et l'intégration de données à évolution lente provenant de différentes sources.
Examinons également de plus près l'outil le plus simple pour gérer les processus ETL basés sur une seule table.
Revenons au sujet de la mesure de la qualité des données et de l'automatisation de ce processus.
Nous étudierons les problématiques d'environnement technique et de maintenance des stockages de données, pour lesquels nous implémenterons un serveur de stockage avec un minimum de ressources, par exemple basé sur un Raspberry Pi.
Source: habr.com