Nástroj Webalizer a Google Analytics mi mnoho let pomáhaly získat představu o tom, co se děje na webových stránkách. Nyní chápu, že poskytují velmi málo užitečných informací. S přístupem k mému souboru access.log je analýza statistik velmi jednoduchá a k realizaci postačí základní nástroje, jako jsou sqlite, html, jazyk sql a jakýkoli skriptovací jazyk.
Zdroj dat pro Webalizer je soubor access.log serveru. To jsou jeho sloupce a čísla, z nichž je patrný pouze celkový objem provozu:
Nástroje jako Google Analytics sbírají data ze serveru samostatně. Zobrazují nám pár diagramů a linií, na jejichž základě je často obtížné činit správné závěry. Možná by bylo potřeba vynaložit větší úsilí? Nevím.
Takže co bych chtěl vidět ve statistikách návštěvnosti webu?
Provoz uživatelů a botů
Často je provoz na webových stránkách omezen a je nutné vidět, kolik užitečného provozu je využito. Například takto:
SQL dotaz zprávy
SELECT
1 as 'StackedArea: Provoz generovaný uživateli a boty',
strftime('%d.%m', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Den',
SUM(CASE WHEN USG.AGENT_BOT!='n.a.' THEN FCT.BYTES ELSE 0 END)/1000 AS 'Boti, KB',
SUM(CASE WHEN USG.AGENT_BOT='n.a.' THEN FCT.BYTES ELSE 0 END)/1000 AS 'Uživatelé, 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_DTZ grafu je patrná stálá aktivita botů. Bylo by zajímavé podrobně prozkoumat nejaktivnější zástupce.
Otravní boti
Klasifikujeme boty na základě informací o uživatelském agentovi. Další statistika o denním provozu, počtu úspěšných a neúspěšných požadavků poskytuje dobrý přehled o aktivitě botů.
SQL dotaz zprávy
SELECT
1 AS 'Tabulka: Otravní boti',
MAX(USG.AGENT_BOT) AS 'Bot',
ROUND(SUM(FCT.BYTES)/1000/14.0, 1) AS 'KB za den',
ROUND(SUM(FCT.IP_CNT)/14.0, 1) AS 'IP za den',
ROUND(SUM(CASE WHEN STS.STATUS_GROUP IN ('Chyba klienta', 'Chyba serveru') THEN FCT.REQUEST_CNT/14.0 ELSE 0 END), 1) AS 'Chybné požadavky za den',
ROUND(SUM(CASE WHEN STS.STATUS_GROUP IN ('Úspěšné', 'Přesměrování') THEN FCT.REQUEST_CNT/14.0 ELSE 0 END), 1) AS 'Úspěšné požadavky za den',
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 10V tomto případě výsledkem analýzy bylo rozhodnutí o omezení přístupu na web prostřednictvím přidání do souboru robots.txt
User-agent: AhrefsBot
Disallow: /
User-agent: dotbot
Disallow: /
User-agent: bingbot
Crawl-delay: 5
První dva boti zmizeli z tabulky a roboti MS se posunuli dolů.
Den a čas nejvyšší aktivity
V provozu jsou viditelné výkyvy. Abyste je detailně prozkoumali, je nutné vymezit čas jejich vzniku, přičemž není nutné zobrazovat všechny hodiny a dny měření času. Tak bude snazší najít jednotlivé dotazy v protokolu při potřebě podrobné analýzy.
SQL dotaz zprávy
SELECT
1 AS 'Řádek: Den a hodina zásahů od uživatelů a botů',
strftime('%d.%m-%H', datetime(EVENT_DT, 'unixepoch')) AS 'Datum a čas',
HIB AS 'Boti, zásahy',
HIU AS 'Uživatelé, 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_DTNa grafu pozorujeme nejaktivnější hodiny 11, 14 a 20 prvního dne. Na následující den se boti aktivovali ve 13 hodin.
Průměrná denní aktivita uživatelů podle týdnů
S aktivitou a provozem jsme si již udělali určité povědomí. Další otázkou byla aktivita samotných uživatelů. Pro takovou statistiku jsou žádoucí delší období agregace, například týden.
SQL dotaz zprávy
SELECT
1 as 'Řádek: Průměrná denní aktivita uživatelů podle týdne',
strftime('%W week', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Týden',
ROUND(1.0*SUM(FCT.PAGE_CNT)/SUM(FCT.IP_CNT),1) AS 'Stránky na IP za den',
ROUND(1.0*SUM(FCT.FILE_CNT)/SUM(FCT.IP_CNT),1) AS 'Soubory na IP za den'
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.' /* pouze uživatelé */
AND HST.STATUS_GROUP IN ('Úspěšné') /* dobré stránky */
AND datetime(FCT.EVENT_DT, 'unixepoch') > date('now', '-3 month')
GROUP BY strftime('%W week', datetime(FCT.EVENT_DT, 'unixepoch'))
ORDER BY FCT.EVENT_DTStatistika za týden ukazuje, že průměrný uživatel otevře 1,6 stránky denně. Počet požadovaných souborů na jednoho uživatele v tomto případě závisí na přidání nových souborů na web.
Všechny dotazy a jejich stavy
Webalizer vždy ukazoval konkrétní kódy stránek a chtěl jsem vždy vidět pouze počet úspěšných požadavků a chyb.
SQL dotaz zprávy
SELECT
1 as 'Řádek: Všechny požadavky podle stavu',
strftime('%d.%m', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Den',
SUM(CASE WHEN STS.STATUS_GROUP='Úspěšné' THEN FCT.REQUEST_CNT ELSE 0 END) AS 'Úspěch',
SUM(CASE WHEN STS.STATUS_GROUP='Přesměrování' THEN FCT.REQUEST_CNT ELSE 0 END) AS 'Přesměrování',
SUM(CASE WHEN STS.STATUS_GROUP='Klientská chyba' THEN FCT.REQUEST_CNT ELSE 0 END) AS 'Chyba zákazníka',
SUM(CASE WHEN STS.STATUS_GROUP='Chyba serveru' THEN FCT.REQUEST_CNT ELSE 0 END) AS 'Chyba serveru'
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_DTReport zobrazuje požadavky, nikoli kliknutí (návštěvy); na rozdíl od metriky LINE_CNT se REQUEST_CNT počítá jako COUNT(DISTINCT STG.REQUEST_NK). Cílem je ukázat efektivní události, například roboty MS, které stokrát denně dotazují soubor robots.txt, a v tomto případě budou takové dotazy započítány pouze jednou. To umožňuje vyhladit skoky na grafu.
Z grafu lze vidět mnoho chyb — neexistující stránky. Výsledkem analýzy bylo přidání přesměrování ze smazaných stránek.
Chybné požadavky
Pro podrobné prozkoumání požadavků lze vypsat detailní statistiky.
SQL dotaz zprávy
SELECT
1 AS 'Tabulka: Nejčastější chybné požadavky',
REQ.REQUEST_NK AS 'Požadavek',
'Chyba' AS 'Stav požadavku',
ROUND(SUM(FCT.LINE_CNT) / 14.0, 1) AS 'Návštěvy za den',
ROUND(SUM(FCT.IP_CNT) / 14.0, 1) AS 'IP za den',
ROUND(SUM(FCT.BYTES)/1000 / 14.0, 1) AS 'KB za den'
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 ('Chyba klienta', 'Chyba serveru')
AND datetime(FCT.EVENT_DT, 'unixepoch') >= date('now', '-14 day')
GROUP BY REQ.REQUEST_NK
ORDER BY 4 DESC
LIMIT 20V tomto seznamu budou také všechny pokusy, například požadavek na /wp-login.php. Upravováním pravidel pro přepisování požadavků serverem můžeme upravit reakci serveru na podobné požadavky a poslat je na domovskou stránku.
Takže několik jednoduchých zpráv na základě souboru logu serveru poskytuje dostatečně úplný obrázek o tom, co se na webu děje.
Jak získat informace?
Databáze sqlite je zcela dostatečná. Vytvoříme tabulky: pomocnou pro logování ETL procesů.
Staging tabulka, kam budeme zapisovat log soubory pomocí PHP. Dvě tabulky agregátů. Vytvoříme denní tabulku se statistikou podle uživatelských agentů a stavů požadavků. Hodinovou se statistikou podle požadavků, skupin stavů a agentů. Čtyři tabulky odpovídajících měření.
Výsledkem byla následující relační model:
Model dat
Skript pro vytvoření objektu v databázi sqlite:
DDL vytvoření 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);Staging
V případě souboru access.log je nutné přečíst, rozparsovat a zapsat do databáze všechny požadavky. To lze provést buď přímo pomocí skriptovacího jazyka, nebo využitím prostředků sqlite.
Formát log souboru:
//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-]+) "(.*)" "(.*)"$/';
Propagace klíčů
Když jsou surová data v databázi, je třeba zapsat do tabulek měření klíče, které tam chybí. Pak bude možné vybudovat vazbu na měření. Například v tabulce DIM_REFERRER je klíčem kombinace tří polí.
SQL dotaz na propagaci klíčů
/* 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 NULLPropagace do tabulky uživatelských agentů může obsahovat logiku robotů, například úryvek 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_BOTTabulky agregátů
Nakonec budeme načítat tabulky agregátů, například denní tabulka může být načítána následujícím způsobem:
SQL dotaz na načtení 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_IDDatabáze SQLite umožňuje psát složité dotazy. WITH obsahuje přípravu dat a klíčů. Hlavní dotaz sbírá všechny vazby na měření.
Podmínka nezaplní historii znovu: CAST(STG.EVENT_DT AS INTEGER) > $param_epoch_from, kde je parametr výsledkem dotazu
'SELECT COALESCE(MAX(EVENT_DT), '3600') AS LAST_EVENT_EPOCH FROM FCT_ACCESS_USER_AGENT_DD'
Podmínka načte pouze celý den: CAST(STG.EVENT_DT AS INTEGER) < strftime('%s', date('now', 'start of day'))
Počítání stránek nebo souborů se provádí primitivním způsobem, vyhledáváním tečky.
Zprávy
Ve složitých vizualizačních systémech je možné vytvářet meta-modely na základě objektů databáze, dynamicky řídit filtry a pravidla agregace. Konečně, všechny slušné nástroje generují SQL dotazy.
V tomto příkladu vytvoříme již hotové SQL dotazy a uložíme je ve formě pohledů v databázi — to jsou zprávy.
Vizualizace
Jako nástroj vizualizace byl použit Bluff: Beautiful graphs in JavaScript
K tomu bylo potřeba pomocí PHP projít všechny reporty a vygenerovat HTML soubor s tabulkami.
$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 pouze vizualizuje tabulky výsledků.
Výstup
Na příkladu webové analýzy článek popisuje mechanismy potřebné pro budování datových skladů. Jak lze vidět z výsledků, pro hlubokou analýzu a vizualizaci dat stačí ty nejjednodušší nástroje.
V budoucnu, na příkladu tohoto skladu, se pokusíme realizovat takové struktury, jako jsou pomalu se měnící dimenze, metadata, úrovně agregace a integraci dat z různých zdrojů.
Také se podrobněji podíváme na nejjednodušší nástroj pro řízení ETL procesů založeného na jedné tabulce.
Vrátíme se k tématu měření kvality dat a automatizaci tohoto procesu.
Prozkoumáme problémy technického prostředí a údržby datových skladů, na což si vytvoříme server skladu s minimálními zdroji, například na bázi Raspberry Pi.
Zdroj: habr.com
