Štatistiky stránok a vaše vlastné malé úložisko

Webalizer a Google Analytics mi už mnoho rokov pomáhajú získať prehľad o dianí na webových stránkach. Teraz chápem, že poskytujú veľmi málo užitočných informácií. S prístupom k vášmu súboru access.log je veľmi jednoduché porozumieť štatistikám a implementovať celkom základné nástroje, ako je sqlite, html, jazyk sql a akýkoľvek skriptovací programovací jazyk.

Zdrojom údajov pre Webalizer je súbor access.log servera. Takto vyzerajú jeho pruhy a čísla, z ktorých je jasný len celkový objem návštevnosti:

Nástroje ako Google Analytics zbierajú údaje z načítanej stránky samy. Ukazujú nám pár diagramov a čiar, na základe ktorých je často ťažké vyvodiť správne závery. Možno by sa malo vynaložiť viac úsilia? neviem.

Čo som teda chcel vidieť v štatistike návštevnosti webu?

Návštevnosť používateľov a robotov

Návštevnosť stránok je často obmedzená a je potrebné vidieť, koľko užitočnej návštevnosti sa využíva. Napríklad takto:

SQL dotaz na zostavu

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

Graf ukazuje neustálu aktivitu robotov. Bolo by zaujímavé podrobne študovať najaktívnejších predstaviteľov.

Nepríjemní roboti

Roboty klasifikujeme na základe informácií o používateľskom agentovi. Ďalšie štatistiky o dennej návštevnosti, počte úspešných a neúspešných žiadostí poskytujú dobrú predstavu o aktivite robotov.

SQL dotaz na zostavu

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

V tomto prípade bolo výsledkom analýzy rozhodnutie obmedziť prístup na stránku jej pridaním do súboru robots.txt

User-agent: AhrefsBot
Disallow: /
User-agent: dotbot
Disallow: /
User-agent: bingbot
Crawl-delay: 5
Prví dvaja roboti zmizli zo stola a roboty MS sa presunuli z prvých riadkov nadol.

Deň a čas najväčšej aktivity

V premávke sú viditeľné výkyvy. Na ich podrobné preštudovanie je potrebné zvýrazniť čas ich výskytu a nie je potrebné zobrazovať všetky hodiny a dni merania času. V prípade potreby podrobnej analýzy to uľahčí nájdenie jednotlivých požiadaviek v súbore denníka.

SQL dotaz na zostavu

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

Na grafe pozorujeme najaktívnejšie hodiny 11, 14 a 20 prvého dňa. Ale nasledujúci deň o 13:XNUMX boli roboty aktívne.

Priemerná denná aktivita používateľov podľa týždňa

Trošku sme veci poriešili s aktivitou a návštevnosťou. Ďalšou otázkou bola aktivita samotných užívateľov. Pre takéto štatistiky sú žiaduce dlhé obdobia agregácie, napríklad týždeň.

SQL dotaz na zostavu

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

Týždenné štatistiky ukazujú, že v priemere jeden používateľ otvorí 1,6 stránky denne. Počet požadovaných súborov na používateľa v tomto prípade závisí od pridávania nových súborov na stránku.

Všetky žiadosti a ich stavy

Webalizer vždy ukazoval konkrétne kódy stránok a vždy som chcel vidieť len počet úspešných požiadaviek a chýb.

SQL dotaz na zostavu

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

Prehľad zobrazuje požiadavky, nie kliknutia (prístupy), na rozdiel od LINE_CNT sa metrika REQUEST_CNT počíta ako COUNT(DISTINCT STG.REQUEST_NK). Cieľom je ukázať efektívne udalosti, napríklad roboty MS hlasujú v súbore robots.txt stokrát denne a v tomto prípade sa takéto prieskumy započítajú raz. To vám umožní vyhladiť skoky v grafe.

Z grafu je vidieť veľa chýb - sú to neexistujúce stránky. Výsledkom analýzy bolo pridanie presmerovaní zo vzdialených stránok.

Zlé žiadosti

Ak chcete podrobne preskúmať požiadavky, môžete zobraziť podrobné štatistiky.

SQL dotaz na zostavu

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

Tento zoznam bude obsahovať aj všetky volania, napríklad požiadavku na /wp-login.php Úpravou pravidiel pre prepisovanie požiadaviek serverom môžete upraviť reakciu servera na takéto požiadavky a poslať ich na úvodnú stránku.

Takže niekoľko jednoduchých správ založených na súbore denníka servera poskytuje celkom úplný obraz o tom, čo sa deje na stránke.

Ako získať informácie?

Postačuje databáza sqlite. Vytvorme tabuľky: pomocné pre logovanie ETL procesov.

Tabuľka, kde budeme zapisovať protokolové súbory pomocou PHP. Dve súhrnné tabuľky. Vytvorme dennú tabuľku so štatistikami používateľských agentov a stavov požiadaviek. Každú hodinu so štatistikami požiadaviek, stavových skupín a agentov. Štyri tabuľky relevantných meraní.

Výsledkom je nasledujúci relačný model:

Dátový model

Skript na vytvorenie objektu v databáze sqlite:

Vytvorenie objektu 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);

Etapa

V prípade súboru access.log je potrebné čítať, analyzovať a zapisovať všetky požiadavky do databázy. Dá sa to urobiť buď priamo pomocou skriptovacieho jazyka alebo pomocou nástrojov sqlite.

Formát súboru denníka:

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

Šírenie kľúčov

Keď sú nespracované údaje v databáze, musíte do tabuliek meraní zapísať kľúče, ktoré tam nie sú. Potom bude možné vytvoriť referenciu na merania. Napríklad v tabuľke DIM_REFERRER je kľúč kombináciou troch polí.

Dotaz na šírenie kľúča 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

Šírenie do tabuľky používateľských agentov môže obsahovať logiku robotov, napríklad úryvok 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

Súhrnné tabuľky

Nakoniec načítame súhrnné tabuľky; napríklad dennú tabuľku je možné načítať takto:

SQL dotaz na načítanie agregátu

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

Databáza sqlite vám umožňuje písať zložité dotazy. WITH obsahuje prípravu údajov a kľúčov. Hlavný dotaz zhromažďuje všetky odkazy na dimenzie.

Podmienka neumožní opätovné načítanie histórie: CAST(STG.EVENT_DT AS INTEGER) > $param_epoch_from, kde parameter je výsledkom požiadavky
'SELECT COALESCE(MAX(EVENT_DT), '3600') AS LAST_EVENT_EPOCH FROM FCT_ACCESS_USER_AGENT_DD'

Podmienka sa načíta iba celý deň: CAST(STG.EVENT_DT AS INTEGER) < strftime('%s', date('teraz', 'začiatok dňa'))

Počítanie strán alebo súborov sa vykonáva primitívnym spôsobom, hľadaním bodu.

Správy

V komplexných vizualizačných systémoch je možné vytvárať meta-model založený na databázových objektoch, dynamicky spravovať filtre a pravidlá agregácie. V konečnom dôsledku všetky slušné nástroje generujú SQL dotaz.

V tomto príklade vytvoríme hotové SQL dotazy a uložíme ich ako pohľady do databázy – ide o zostavy.

Vizualizácia

Bluff: Krásne grafy v JavaScripte boli použité ako vizualizačný nástroj

K tomu bolo potrebné prejsť všetky reporty pomocou PHP a vygenerovať html súbor s tabuľkami.

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

Nástroj jednoducho vizualizuje tabuľky výsledkov.

Výkon

Pomocou webovej analýzy ako príkladu článok popisuje mechanizmy potrebné na vybudovanie dátových skladov. Ako vidno z výsledkov, na hĺbkovú analýzu a vizualizáciu dát stačia najjednoduchšie nástroje.

V budúcnosti sa na príklade tohto úložiska pokúsime implementovať také štruktúry, ako sú pomaly sa meniace dimenzie, metadáta, úrovne agregácie a integrácia údajov z rôznych zdrojov.

Pozrime sa tiež bližšie na najjednoduchší nástroj na správu procesov ETL založených na jednej tabuľke.

Vráťme sa k téme merania kvality dát a automatizácie tohto procesu.

Budeme študovať problémy technického prostredia a údržby dátových úložísk, pre ktoré implementujeme storage server s minimálnymi zdrojmi, napríklad na báze Raspberry Pi.

Zdroj: hab.com