ProHoster > Blog > Administrácia > Štatistika stránky a vlastný malý úložný priestor

Štatistika stránky a vlastný malý úložný priestor

Nástroj Webalizer a Google Analytics mi mnoho rokov pomáhali získať prehľad o tom, čo sa deje na webových stránkach. Teraz chápem, že poskytujú veľmi málo užitočných informácií. Mať prístup k svojmu súboru access.log, je veľmi jednoduché pochopiť štatistiku a na to stačia elementárne nástroje ako sqlite, html, jazyk sql a akýkoľvek skriptovací jazyk.

Zdrojom dát pre Webalizer je súbor access.log servera. Takto vyzerajú jeho stĺpce a čísla, z ktorých je zrejmý len celkový objem prevádzky:

Štatistika stránky a vlastný malý úložný priestor
Štatistika stránky a vlastný malý úložný priestor
Nástroje ako Google Analytics zbierajú údaje z načítanej stránky samostatne. Zobrazujú nám pár diagramov a línií, na základe ktorých je často ťažké urobiť správne závery. Možno som mal venovať viac úsilí? Neviem.

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

Prevádzka používateľov a robotov

Často má prevádzka stránok obmedzenia a je potrebné vidieť, koľko užitočnej prevádzky sa využíva. Napríklad takto:

Štatistika stránky a vlastný malý úložný priestor

SQL dotaz na report

SELECT
1 as 'StackedArea: Prevádzka generovaná používateľmi a robotmi',
strftime('%d.%m', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Deň',
SUM(CASE WHEN USG.AGENT_BOT!='n.a.' THEN FCT.BYTES ELSE 0 END)/1000 AS 'Roboty, KB',
SUM(CASE WHEN USG.AGENT_BOT='n.a.' THEN FCT.BYTES ELSE 0 END)/1000 AS 'Používatelia, 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

Z grafu je zrejmá stála aktivita robotov. Bolo by zaujímavé podrobne preskúmať najaktívnejších predstaviteľov.

Dotieraví roboti

Klasifikujeme roboty na základe informácií používateľského agenta. Dodatočná štatistika o dennej prevádzke, počte úspešných a neúspešných požiadaviek poskytuje dobrý prehľad o aktivite robotov.

Štatistika stránky a vlastný malý úložný priestor

SQL dotaz na report

SELECT 
1 AS 'Tabuľka: Dotieraví Roboti',
MAX(USG.AGENT_BOT) AS 'Robot',
ROUND(SUM(FCT.BYTES)/1000/14.0, 1) AS 'KB za Deň',
ROUND(SUM(FCT.IP_CNT)/14.0, 1) AS 'IP za Deň',
ROUND(SUM(CASE WHEN STS.STATUS_GROUP IN ('Chyba klienta', 'Chyba servera') THEN FCT.REQUEST_CNT/14.0 ELSE 0 END), 1) AS 'Požiadavky s Chybou za Deň',
ROUND(SUM(CASE WHEN STS.STATUS_GROUP IN ('Úspešné', 'Presmerovanie') THEN FCT.REQUEST_CNT/14.0 ELSE 0 END), 1) AS 'Úspešné Požiadavky za Deň',
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 analýza ukázala rozhodnutie obmedziť prístup na webovú stránku pridaním do súboru robots.txt

User-agent: AhrefsBot
Disallow: /
User-agent: dotbot
Disallow: /
User-agent: bingbot
Crawl-delay: 5
Prvé dva boty zmizli z tabuľky, zatiaľ čo roboty MS sa posunuli z prvých riadkov nižšie.

Deň a čas najväčšej aktivity

V prenosoch sú viditeľné vzostupy. Pre podrobné preskúmanie je potrebné určiť čas ich vzniku, pričom nie je nutné zobraziť všetky hodiny a dni merania času. Takto bude jednoduchšie nájsť jednotlivé požiadavky v logovom súbore, ak bude potrebná podrobná analýza.

Štatistika stránky a vlastný malý úložný priestor

SQL dotaz na report

SELECT
1 AS 'Riadok: Deň a hodina zásahov od používateľov a botov',
strftime('%d.%m-%H', datetime(EVENT_DT, 'unixepoch')) AS 'Dátum a čas',
HIB AS 'Boty, zásahy',
HIU AS 'Používatelia, zásahy'
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

Pozorujeme najaktívnejšie hodiny 11, 14 a 20 prvého dňa na grafe. Na druhý deň sa boty aktivovali o 13. hodine.

Priemerná denná aktivita používateľov za týždne

S aktivitou a prenosom sme si trošku porozumeli. Ďalšou otázkou bola aktivita samotných používateľov. Pre takúto štatistiku sú žiaduce dlhšie obdobia agregácie, napríklad týždeň.

Štatistika stránky a vlastný malý úložný priestor

SQL dotaz na report

SELECT
1 as 'Riadok: Priemerná denná aktivita používateľov podľa týždňa',
strftime('%W týždeň', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Týždeň',
ROUND(1.0*SUM(FCT.PAGE_CNT)/SUM(FCT.IP_CNT),1) AS 'Stránky na IP na deň',
ROUND(1.0*SUM(FCT.FILE_CNT)/SUM(FCT.IP_CNT),1) AS 'Súbory na IP na deň'
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.' /* len používatelia */
  AND HST.STATUS_GROUP IN ('Successful') /* dobré stránky */
  AND datetime(FCT.EVENT_DT, 'unixepoch') > date('now', '-3 month')
GROUP BY strftime('%W týždeň', datetime(FCT.EVENT_DT, 'unixepoch'))
ORDER BY FCT.EVENT_DT

Štatistika za týždeň ukazuje, že v priemere jeden používateľ otvorí 1,6 stránky za deň. Počet požadovaných súborov na jedného používateľa v tomto prípade závisí od pridávania nových súborov na web.

Všetky požiadavky a ich stavy

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

Štatistika stránky a vlastný malý úložný priestor

SQL dotaz na report

VYBER
1 ako 'Riadok: Všetky žiadosti podľa stavu',
strftime('%d.%m', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Deň',
SUM(CASE WHEN STS.STATUS_GROUP='Úspešné' THEN FCT.REQUEST_CNT ELSE 0 END) AS 'Úspech',
SUM(CASE WHEN STS.STATUS_GROUP='Presmerovanie' THEN FCT.REQUEST_CNT ELSE 0 END) AS 'Presmerovanie',
SUM(CASE WHEN STS.STATUS_GROUP='Chyba klienta' THEN FCT.REQUEST_CNT ELSE 0 END) AS 'Chyba zákazníka',
SUM(CASE WHEN STS.STATUS_GROUP='Chyba servera' THEN FCT.REQUEST_CNT ELSE 0 END) AS 'Chyba servera'
Z
  FCT_ACCESS_USER_AGENT_DD FCT,
  DIM_HTTP_STATUS STS
KDE FCT.DIM_HTTP_STATUS_ID=STS.DIM_HTTP_STATUS_ID
  A datetime(FCT.EVENT_DT, 'unixepoch') >= date('now', '-14 day')
GROUP BY strftime('%d.%m', datetime(FCT.EVENT_DT, 'unixepoch'))
ORDER BY FCT.EVENT_DT

Správa zobrazuje žiadosti, a nie kliknutia (hity), na rozdiel od metriky LINE_CNT sa REQUEST_CNT počíta ako COUNT(DISTINCT STG.REQUEST_NK). Cieľom je ukázať efektívne udalosti, napríklad boti MS opakovane kontrolujú súbor robots.txt dvakrát denne a v tomto prípade budú takéto kontroly počítané len raz. Týmto sa vyhladzujú skoky na grafe.

Z grafu je možné vidieť množstvo chýb — neexistujúce stránky. Výsledkom analýzy bolo pridanie presmerovaní zo zrušených stránok.

Chybné požiadavky

Pre podrobnejšie preskúmanie žiadostí je možné zobraziť podrobnú štatistiku.

Štatistika stránky a vlastný malý úložný priestor

SQL dotaz na report

VYBER
  1 AS 'Tabuľa: Najčastejšie chybné požiadavky',
  REQ.REQUEST_NK AS 'Žiadosť',
  'Chyba' AS 'Stav požiadavky',
  ROUND(SUM(FCT.LINE_CNT) / 14.0, 1) AS 'Hity za deň',
  ROUND(SUM(FCT.IP_CNT) / 14.0, 1) AS 'IP za deň',
  ROUND(SUM(FCT.BYTES)/1000 / 14.0, 1) AS 'KB za deň'
Z
  FCT_ACCESS_REQUEST_REF_HH FCT,
  DIM_REQUEST_V_ACT REQ
KDE FCT.DIM_REQUEST_ID = REQ.DIM_REQUEST_ID
  A FCT.STATUS_GROUP IN ('Chyba klienta', 'Chyba servera')
  A datetime(FCT.EVENT_DT, 'unixepoch') >= date('now', '-14 day')
GROUP BY REQ.REQUEST_NK
ORDER BY 4 DESC
LIMIT 20

V tomto zozname budú zahrnuté aj všetky dopyty, napríklad požiadavka na /wp-login.php. Úpravou pravidiel pre prepisovanie požiadaviek serverom je možné upraviť reakciu servera na takéto požiadavky a presmerovať ich na domovskú stránku.

Takže niekoľko jednoduchých správ na základe súboru logu servera poskytuje dostatočne komplexný obraz toho, čo sa na stránke deje.

Ako získať informácie?

Databázy sqlite sú úplne dostatočné. Vytvoríme tabuľky: pomocnú pre logovanie procesov ETL.

Štatistika stránky a vlastný malý úložný priestor

Stage tabuľka, kam budeme zapisovať log súbory pomocou PHP. Dve tabuľky agregátov. Vytvoríme dennú tabuľku so štatistikami podľa používateľských agentov a stavov požiadaviek. Hodinovú so štatistikami podľa požiadaviek, skupín stavov a agentov. Štyri tabuľky príslušných dimenzií.

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

Model dátŠtatistika stránky a vlastný malý úložný priestor

Skript na vytvorenie objektu v databáze sqlite:

DDL vytvorenie objektu

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

Stage

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

Formát log súboru:

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

Propagácia kľúčov

Keď sú surové dáta v databáze, je potrebné zapísať do tabuliek dimenzií kľúče, ktoré tam nie sú. Potom bude možné vytvoriť odkaz na dimenzie. Napríklad v tabuľke DIM_REFERRER je kľúčom kombinácia troch polí.

SQL dopyt na propagáciu kľúčov

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

Propagácia do tabuľky používateľských agentov môže obsahovať logiku botov, 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

Tabuľky agregátov

Na záver nahráme tabuľky agregátov, napríklad denná tabuľka sa môže nahrávať nasledujúcim spôsobom:

SQL dopyt 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 umožňuje písať zložité dopyty. WITH obsahuje prípravu dát a kľúčov. Hlavný dopyt zbiera všetky odkazy na dimenzie.

Podmienka nedovolí znova načítať históriu: CAST(STG.EVENT_DT AS INTEGER) > $param_epoch_from, kde parameter je výsledkom dotazu
SELECT COALESCE(MAX(EVENT_DT), '3600') AS LAST_EVENT_EPOCH FROM FCT_ACCESS_USER_AGENT_DD

Podmienka načíta iba celý deň: CAST(STG.EVENT_DT AS INTEGER) < strftime('%s', date('now', 'start of day'))

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

Správy

V zložitých vizualizačných systémoch je možné vytvárať meta-model na základe objektov databázy, dynamicky spravovať filtre a pravidlá agregácie. V konečnom dôsledku všetky slušné nástroje generujú SQL dopyt.

V tomto príklade vytvoríme už hotové SQL dotazy a uložíme ich vo forme view do databázy — to sú reporty.

Vizualizácia

Na vizualizáciu sa použil nástroj Bluff: Beautiful graphs in JavaScript

Na to bolo potrebné pomocou PHP prechádzať všetkými reportmi 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.

Záver

Na príklade webovej analýzy článok opisuje mechanizmy potrebné na vytvorenie dátových skladov. Ako je vidieť z výsledkov, na hlbokú analýzu a vizualizáciu dát sú potrebné iba najjednoduchšie nástroje.

V ďalšom kroku, na príklade tohto skladu, sa pokúsime implementovať štruktúry ako pomaly sa meniacich dimenzií, metadát, úrovne agregácie a integráciu dát z rôznych zdrojov.

Taktiež si podrobnejšie prejdeme najjednoduchší nástroj na riadenie ETL procesov založený na jednej tabuľke.

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

Preskúmame problémy technického prostredia a údržby datových skladov, na čo zrealizujeme server skladu s minimálnymi zdrojmi, napríklad na báze Raspberry Pi.

Zdroj: habr.com

Kúpte si spoľahlivý hosting pre webové stránky s ochranou proti DDoS, VPS VDS servery 🔥 Kúpte si spoľahlivý hosting pre webové stránky s ochranou proti DDoS, VPS VDS servery | ProHoster