🥇Statistiques du site et votre petit entrepôt

L'outil Webalizer et Google Analytics m'ont aidé pendant de nombreuses années à comprendre ce qui se passe sur les sites Web. Je réalise maintenant qu'ils fournissent très peu d'informations utiles. Avoir accès à mon fichier access.log rend l'analyse des statistiques très simple, et il suffit d'outils élémentaires comme sqlite, html, le langage sql et n'importe quel langage de script.

Le fichier access.log est la source de données pour Webalizer. de serveurs. Voici comment se présentent ses colonnes et chiffres, dont seule la quantité globale de trafic est compréhensible :

Des outils comme Google Analytics collectent eux-mêmes des données à partir de la page chargée. Ils nous affichent quelques graphiques et lignes, sur la base desquels il est souvent difficile de tirer des conclusions correctes. Peut-être aurais-je dû faire plus d'efforts ? Je ne sais pas.

Alors, que voulais-je voir dans les statistiques de fréquentation du site ?

Le trafic des utilisateurs et des bots

Souvent, le trafic des sites a des limites et il est nécessaire de voir combien de trafic utile est utilisé. Par exemple, comme ceci :

Requête SQL de rapport

SELECT
1 as 'StackedArea: Trafic généré par les utilisateurs et les bots',
strftime('%d.%m', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Jour',
SUM(CASE WHEN USG.AGENT_BOT!='n.a.' THEN FCT.BYTES ELSE 0 END)/1000 AS 'Bots, Ko',
SUM(CASE WHEN USG.AGENT_BOT='n.a.' THEN FCT.BYTES ELSE 0 END)/1000 AS 'Utilisateurs, Ko'
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_DT

Le graphique montre une activité constante des bots. Il serait intéressant d'étudier de plus près les représentants les plus actifs.

Bots ennuyeux

Nous classifions les bots sur la base des informations de l'agent utilisateur. Des statistiques supplémentaires sur le trafic quotidien, ainsi que le nombre de requêtes réussies et échouées, fournissent une bonne vue d'ensemble de l'activité des bots.

Requête SQL de rapport

SELECT 
1 AS 'Tableau: Bots Ennuyeux',
MAX(USG.AGENT_BOT) AS 'Bot',
ROUND(SUM(FCT.BYTES)/1000 / 14.0, 1) AS 'Ko par jour',
ROUND(SUM(FCT.IP_CNT) / 14.0, 1) AS 'IPs par jour',
ROUND(SUM(CASE WHEN STS.STATUS_GROUP IN ('Erreur Client', 'Erreur Serveur') THEN FCT.REQUEST_CNT / 14.0 ELSE 0 END), 1) AS 'Requêtes d'erreur par jour',
ROUND(SUM(CASE WHEN STS.STATUS_GROUP IN ('Réussi', 'Redirection') THEN FCT.REQUEST_CNT / 14.0 ELSE 0 END), 1) AS 'Requêtes réussies par jour',
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 10

Dans ce cas, le résultat de l'analyse a été une décision de restreindre l'accès au site en 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 du tableau, tandis que les robots de MS ont été relégués vers le bas.

Jour et heure de la plus grande activité

On observe des pics dans le trafic. Pour les étudier en détail, il est nécessaire de définir le moment de leur apparition, sans avoir à afficher toutes les heures et jours de mesure. Cela facilitera la recherche de requêtes spécifiques dans le fichier journal en cas d'analyse approfondie.

Requête SQL de rapport

SELECT
1 AS 'Ligne : Jour et Heure des Visites des Utilisateurs et Bots',
strftime('%d.%m-%H', datetime(EVENT_DT, 'unixepoch')) AS 'Date Heure',
HIB AS 'Bots, Visites',
HIU AS 'Utilisateurs, Visites'
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_DT

Nous observons les heures les plus actives à 11, 14 et 20 heures le premier jour sur le graphique. En revanche, le lendemain, à 13 heures, les bots étaient actifs.

Activité quotidienne moyenne des utilisateurs par semaine

Nous avons un peu éclairci la question de l'activité et du trafic. La question suivante concernait l'activité même des utilisateurs. Pour une telle statistique, des périodes d'agrégation plus longues sont souhaitables, par exemple, une semaine.

Requête SQL de rapport

SELECT
1 AS 'Ligne : Activité Moyenne Quotidienne des Utilisateurs par Semaine',
strftime('%W semaine', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Semaine',
ROUND(1.0*SUM(FCT.PAGE_CNT)/SUM(FCT.IP_CNT),1) AS 'Pages par IP par Jour',
ROUND(1.0*SUM(FCT.FILE_CNT)/SUM(FCT.IP_CNT),1) AS 'Fichiers par IP par Jour'
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.' /* utilisateurs seulement */
  AND HST.STATUS_GROUP IN ('Successful') /* bonnes pages */
  AND datetime(FCT.EVENT_DT, 'unixepoch') >= date('now', '-3 month')
GROUP BY strftime('%W semaine', datetime(FCT.EVENT_DT, 'unixepoch'))
ORDER BY FCT.EVENT_DT

Les statistiques hebdomadaires montrent qu'en moyenne, un utilisateur ouvre 1,6 pages par jour. Le nombre de fichiers demandés par utilisateur dépend de l'ajout de nouveaux fichiers sur le site.

Toutes les requêtes et leurs statuts

Webalizer a toujours affiché des codes de pages spécifiques et on souhaitait toujours voir simplement le nombre de requêtes réussies et d'erreurs.

Requête SQL de rapport

SELECT
1 as 'Ligne : Toutes les demandes par statut',
strftime('%d.%m', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Jour',
SUM(CASE WHEN STS.STATUS_GROUP='Réussi' THEN FCT.REQUEST_CNT ELSE 0 END) AS 'Succès',
SUM(CASE WHEN STS.STATUS_GROUP='Redirection' THEN FCT.REQUEST_CNT ELSE 0 END) AS 'Redirection',
SUM(CASE WHEN STS.STATUS_GROUP='Erreur Client' THEN FCT.REQUEST_CNT ELSE 0 END) AS 'Erreur Client',
SUM(CASE WHEN STS.STATUS_GROUP='Erreur Serveur' THEN FCT.REQUEST_CNT ELSE 0 END) AS 'Erreur Serveur'
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_DT

Le rapport affiche les requêtes, et non les clics (hits). Contrairement à la métrique LINE_CNT, REQUEST_CNT est considéré comme COUNT(DISTINCT STG.REQUEST_NK). L'objectif est de montrer les événements effectifs, par exemple, les bots MS interrogent le fichier robots.txt des centaines de fois par jour, et dans ce cas, ces interrogations seront comptées une seule fois. Cela permet d'atténuer les pics sur le graphique.

Le graphique révèle de nombreuses erreurs — ce sont des pages inexistantes. L'analyse a abouti à l'ajout de redirections pour les pages supprimées.

Demandes erronées

Pour une analyse détaillée des requêtes, une statistique détaillée peut être générée.

Requête SQL de rapport

SELECT
  1 AS 'Table : Requêtes Erronées Principales',
  REQ.REQUEST_NK AS 'Demande',
  'Erreur' AS 'Statut de la Demande',
  ROUND(SUM(FCT.LINE_CNT) / 14.0, 1) AS 'Hits par Jour',
  ROUND(SUM(FCT.IP_CNT) / 14.0, 1) AS 'IPs par Jour',
  ROUND(SUM(FCT.BYTES)/1000 / 14.0, 1) AS 'Ko par Jour'
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 ('Erreur Client', 'Erreur Serveur')
  AND datetime(FCT.EVENT_DT, 'unixepoch') >= date('now', '-14 day')
GROUP BY REQ.REQUEST_NK
ORDER BY 4 DESC
LIMIT 20

Cette liste comprendra également tous les appels, par exemple, une requête à /wp-login.php. En ajustant les règles de réécriture des requêtes, serveur il est possible de corriger la réaction du serveur à de telles requêtes et de les rediriger vers la page d'accueil.

Ainsi, quelques rapports simples basés sur le fichier journal du serveur fournissent un aperçu assez complet de ce qui se passe sur le site.

Comment obtenir des informations ?

Les bases de données sqlite suffisent largement. Créons des tables : une auxiliaire pour enregistrer les processus ETL.

Table de staging, où nous allons écrire les fichiers journaux avec PHP. Deux tables d'agrégats. Créons une table quotidienne avec des statistiques sur les agents utilisateurs et les statuts des requêtes. Une horaire avec des statistiques sur les requêtes, les groupes de statuts et les agents. Quatre tables pour les dimensions correspondantes.

Le résultat est le modèle relationnel suivant :

Modèle de données.

Script pour créer un objet dans la base de données sqlite :

DDL création de l'objet

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énario

Dans le cas du fichier access.log, il est nécessaire de lire, d'analyser et d'enregistrer toutes les requêtes dans la base de données. Cela peut être fait soit directement à l'aide d'un langage de script, soit en utilisant des moyens sqlite.

Format du fichier log :

//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 se trouvent dans la base, il est nécessaire d'enregistrer dans les tables de dimensions les clés qui n'y sont pas présentes. Cela permettra de construire un lien vers les dimensions. Par exemple, dans la table DIM_REFERRER, la clé est une combinaison de trois champs.

Requête SQL de propagation des clés

/* 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 NULL

La propagation dans la table des agents utilisateurs peut contenir la logique des bots, par exemple, un 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 'autre'
ELSE 'n.a.' END AS AGENT_BOT

Tables d'agrégats

Enfin, nous allons charger les tables d'agrégats, par exemple, la table quotidienne peut être chargée de la manière suivante :

Requête SQL de chargement d'agrégats

/* 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_ID

La base de données sqlite permet d'écrire des requêtes complexes. WITH contient la préparation des données et des clés. La requête principale collecte tous les liens vers les dimensions.

La condition n'autorisera pas le rechargement de 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 qu'une journée complète : CAST(STG.EVENT_DT AS INTEGER) < strftime('%s', date('now', 'start of day'))

Le décompte des pages ou des fichiers est effectué de manière primitive, en recherchant 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 de manière dynamique les filtres et les règles d'agrégation. En fin de compte, tous les outils convenables génèrent des requêtes 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

L'outil de visualisation utilisé était Bluff : des graphiques magnifiques en JavaScript.

Pour cela, il a fallu parcourir tous les rapports avec 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 les tableaux de résultats.

Sortie

Cet article illustre, à travers l'analyse web, les mécanismes nécessaires à la construction d'entrepôts de données. Comme on peut le voir dans les résultats, les outils les plus simples suffisent pour une analyse et une visualisation approfondies des données.

Par la suite, en prenant cet entrepôt comme exemple, nous essaierons de réaliser des structures telles que des dimensions à évolutions lentes, des métadonnées, des niveaux d'agrégation et l'intégration de données provenant de différentes sources.

Nous examinerons également un outil de gestion ETL simple basé sur une seule table.

Retournons à la mesure de la qualité des données et à l'automatisation de ce processus.

Nous étudierons les problèmes de l'environnement technique et de la maintenance des entrepôts de données, pour cela nous allons mettre en place un serveur d'entrepôt avec des ressources minimales, par exemple basé sur un Raspberry Pi.

Source : habr.com

Statistiques du site et votre petit espace de stockage

Le trafic des utilisateurs et des bots

Bots ennuyeux

Jour et heure de la plus grande activité

Activité quotidienne moyenne des utilisateurs par semaine

Toutes les requêtes et leurs statuts

Demandes erronées

Comment obtenir des informations ?

Scénario

Propagation des clés

Tables d'agrégats

Rapports

Visualisation

Sortie