Statisticile site-ului și propriul tău spațiu de stocare mic

Webalizer și Google Analytics m-au ajutat să obțin o perspectivă asupra a ceea ce se întâmplă pe site-uri web de mulți ani. Acum înțeleg că oferă foarte puține informații utile. Având acces la fișierul dvs. access.log, este foarte ușor să înțelegeți statisticile și să implementați instrumente destul de de bază, precum sqlite, html, limbajul sql și orice limbaj de programare de scripting.

Sursa de date pentru Webalizer este fișierul access.log al serverului. Iată cum arată barele și numerele sale, din care este clar doar volumul total de trafic:

Instrumente precum Google Analytics colectează ele însele date de pe pagina încărcată. Ele ne arată câteva diagrame și linii, pe baza cărora este adesea dificil să tragem concluzii corecte. Poate ar fi trebuit depus mai mult efort? Nu stiu.

Deci, ce am vrut să văd în statisticile vizitatorilor site-ului?

Trafic de utilizatori și bot

Adesea, traficul pe site este limitat și este necesar să vedem cât de mult trafic util este utilizat. De exemplu, așa:

Interogare de raport 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_DT

Graficul arată activitatea constantă a roboților. Ar fi interesant să studiem în detaliu cei mai activi reprezentanți.

Boti enervanti

Clasificăm boții pe baza informațiilor despre agentul utilizator. Statisticile suplimentare privind traficul zilnic, numărul de solicitări reușite și nereușite oferă o idee bună despre activitatea botului.

Interogare de raport 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 10

În acest caz, rezultatul analizei a fost decizia de a restricționa accesul la site prin adăugarea acestuia în fișierul robots.txt

User-agent: AhrefsBot
Disallow: /
User-agent: dotbot
Disallow: /
User-agent: bingbot
Crawl-delay: 5
Primii doi roboți au dispărut de pe masă, iar roboții MS s-au deplasat în jos de pe primele rânduri.

Ziua și ora celei mai mari activități

Creșterile sunt vizibile în trafic. Pentru a le studia în detaliu, este necesar să se evidențieze ora apariției lor și nu este necesar să se afișeze toate orele și zilele de măsurare a timpului. Acest lucru va face mai ușor să găsiți cereri individuale în fișierul jurnal dacă este necesară o analiză detaliată.

Interogare de raport 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_DT

Observăm cele mai active ore 11, 14 și 20 din prima zi pe grafic. Dar a doua zi la 13:XNUMX boții erau activi.

Activitatea zilnică medie a utilizatorilor pe săptămână

Am rezolvat puțin lucrurile cu activitate și trafic. Următoarea întrebare a fost activitatea utilizatorilor înșiși. Pentru astfel de statistici, sunt de dorit perioade lungi de agregare, cum ar fi o săptămână.

Interogare de raport 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_DT

Statisticile săptămânale arată că, în medie, un utilizator deschide 1,6 pagini pe zi. Numărul de fișiere solicitate per utilizator în acest caz depinde de adăugarea de fișiere noi pe site.

Toate cererile și starea acestora

Webalizer a arătat întotdeauna anumite coduri de pagină și am vrut întotdeauna să văd doar numărul de solicitări și erori reușite.

Interogare de raport 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_DT

Raportul afișează solicitări, nu clicuri (accesări), spre deosebire de LINE_CNT, valoarea REQUEST_CNT este calculată ca COUNT(DISTINCT STG.REQUEST_NK). Scopul este de a arăta evenimente eficiente, de exemplu, boții MS interogează fișierul robots.txt de sute de ori pe zi și, în acest caz, astfel de sondaje vor fi numărate o dată. Acest lucru vă permite să neteziți salturile în grafic.

Din grafic puteți vedea multe erori - acestea sunt pagini inexistente. Rezultatul analizei a fost adăugarea de redirecționări din pagini la distanță.

Cereri proaste

Pentru a examina cererile în detaliu, puteți afișa statistici detaliate.

Interogare de raport 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 20

Această listă va conține, de asemenea, toate apelurile, de exemplu, o solicitare către /wp-login.php Prin ajustarea regulilor de rescriere a cererilor de către server, puteți ajusta reacția serverului la astfel de solicitări și le puteți trimite la pagina de pornire.

Așadar, câteva rapoarte simple bazate pe fișierul jurnal al serverului oferă o imagine destul de completă a ceea ce se întâmplă pe site.

Cum să obțineți informații?

O bază de date sqlite este suficientă. Să creăm tabele: auxiliar pentru înregistrarea proceselor ETL.

Etapa de tabel în care vom scrie fișiere jurnal folosind PHP. Două tabele agregate. Să creăm un tabel zilnic cu statistici despre agenții utilizatori și stările solicitărilor. Orar cu statistici despre cereri, grupuri de status și agenți. Patru tabele de măsurători relevante.

Rezultatul este următorul model relațional:

Model de date

Script pentru a crea un obiect într-o bază de date sqlite:

Crearea obiectelor 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);

Etapă

În cazul fișierului access.log, este necesar să citiți, să analizați și să scrieți toate cererile în baza de date. Acest lucru se poate face fie direct folosind un limbaj de scripting, fie folosind instrumente sqlite.

Format fișier jurnal:

//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-]+) "(.*)" "(.*)"$/';

Propagarea cheii

Când datele brute sunt în baza de date, trebuie să scrieți cheile care nu sunt acolo în tabelele de măsurare. Apoi va fi posibil să construiți o referință la măsurători. De exemplu, în tabelul DIM_REFERRER, cheia este o combinație de trei câmpuri.

Interogare de propagare a cheii 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 NULL

Propagarea către tabelul user agent poate conține logica bot, de exemplu fragmentul 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_BOT

Tabele agregate

În cele din urmă, vom încărca tabelele agregate; de ​​exemplu, tabelul zilnic poate fi încărcat după cum urmează:

Interogare SQL pentru încărcarea agregatului

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

Baza de date sqlite vă permite să scrieți interogări complexe. WITH conține pregătirea datelor și a cheilor. Interogarea principală colectează toate referințele la dimensiuni.

Condiția nu va permite încărcarea din nou a istoricului: CAST(STG.EVENT_DT AS INTEGER) > $param_epoch_from, unde parametrul este rezultatul solicitării
„SELECT COALESCE(MAX(EVENT_DT), „3600”) CA LAST_EVENT_EPOCH FROM FCT_ACCESS_USER_AGENT_DD”

Condiția va încărca numai ziua întreagă: CAST(STG.EVENT_DT AS INTEGER) < strftime('%s', date('acum', 'începutul zilei'))

Numărarea paginilor sau a fișierelor se realizează într-un mod primitiv, prin căutarea unui punct.

Rapoarte

În sistemele complexe de vizualizare, este posibil să se creeze un meta-model bazat pe obiecte de bază de date, să se gestioneze dinamic filtrele și regulile de agregare. În cele din urmă, toate instrumentele decente generează o interogare SQL.

În acest exemplu, vom crea interogări SQL gata făcute și le vom salva ca vizualizări în baza de date - acestea sunt rapoarte.

Vizualizare

Bluff: Grafice frumoase în JavaScript au fost folosite ca instrument de vizualizare

Pentru a face acest lucru, a fost necesar să parcurgeți toate rapoartele folosind PHP și să generați un fișier html cu tabele.

$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'
);

Instrumentul vizualizează pur și simplu tabele cu rezultate.

Producție

Folosind analiza web ca exemplu, articolul descrie mecanismele necesare pentru a construi depozite de date. După cum se poate observa din rezultate, cele mai simple instrumente sunt suficiente pentru analiza profundă și vizualizarea datelor.

În viitor, folosind acest depozit ca exemplu, vom încerca să implementăm structuri precum dimensiuni care se schimbă lent, metadate, niveluri de agregare și integrarea datelor din diferite surse.

De asemenea, să aruncăm o privire mai atentă la cel mai simplu instrument pentru gestionarea proceselor ETL bazate pe un singur tabel.

Să revenim la subiectul cu privire la măsurarea calității datelor și automatizarea acestui proces.

Vom studia problemele mediului tehnic și întreținerea stocărilor de date, pentru care vom implementa un server de stocare cu resurse minime, de exemplu, bazat pe un Raspberry Pi.

Sursa: www.habr.com