A Webalizer és a Google Analytics segédeszközök évek óta segítenek abban, hogy képet kapjak arról, mi történik a weboldalakon. Ma már értem, hogy nagyon kevés hasznos információt nyújtanak. Az access.log fájlomhoz való hozzáféréssel a statisztikai elemzés nagyon egyszerű, és csupán alapvető eszközökre, például sqlite-ra, html-re, sql-re és bármilyen programozási nyelvre van szükség.
A Webalizer adatforrása az access.log fájl. a szerver. Így néznek ki az oszlopai és számai, amelyekből csupán az összes forgalom mennyisége érthető:
Az olyan eszközök, mint a Google Analytics, önállóan gyűjtik az adatokat a betöltött oldalról. Két diagramos és vonalábrát mutatnak, amely alapján gyakran nehéz helyes következtetéseket levonni. Talán több erőfeszítést kellett volna tennem? Nem tudom.
Nos, mit szerettem volna látni a weboldal látogatottsági statisztikáiban?
Felhasználói és botforgalom
Gyakran a weboldalak forgalma korlátozott, és fontos látni, hogy mennyi hasznos forgalmat használnak. Például így:
SQL jelentés lekérdezés
SELECT
1 AS 'StackedArea: A felhasználók és botok által generált forgalom',
strftime('%d.%m', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Nap',
SUM(CASE WHEN USG.AGENT_BOT!='n.a.' THEN FCT.BYTES ELSE 0 END)/1000 AS 'Botok, KB',
SUM(CASE WHEN USG.AGENT_BOT='n.a.' THEN FCT.BYTES ELSE 0 END)/1000 AS 'Felhasználók, 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_DTA grafikonból folyamatos botaktivitás látható. Érdekes lenne részletesen megvizsgálni a legaktívabb képviselőket.
Zavaró botok
A botokat a felhasználói ügynök információja alapján osztályozzuk. A napi forgalom, a sikeres és a sikertelen kérések számának további statisztikája jó képet ad a botok aktivitásáról.
SQL jelentés lekérdezés
SELECT
1 AS 'Tábla: Zavaró Botok',
MAX(USG.AGENT_BOT) AS 'Bot',
ROUND(SUM(FCT.BYTES)/1000/14.0, 1) AS 'KB naponta',
ROUND(SUM(FCT.IP_CNT)/14.0, 1) AS 'IP naponta',
ROUND(SUM(CASE WHEN STS.STATUS_GROUP IN ('Client Error', 'Server Error') THEN FCT.REQUEST_CNT/14.0 ELSE 0 END), 1) AS 'Hiba kérések naponta',
ROUND(SUM(CASE WHEN STS.STATUS_GROUP IN ('Successful', 'Redirection') THEN FCT.REQUEST_CNT/14.0 ELSE 0 END), 1) AS 'Sikeres kérések naponta',
USG.USER_AGENT_NK AS 'Ügynök'
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 10Ebben az esetben az elemzés eredménye az volt, hogy korlátozni kell a webhelyhez való hozzáférést a robots.txt fájlba való bejegyzéssel.
Felhasználói ügynök: AhrefsBot
Tiltás: /
Felhasználói ügynök: dotbot
Tiltás: /
Felhasználói ügynök: bingbot
Kúszási késleltetés: 5
Az első két bot eltűnt a táblázatból, míg a MS robotok a felső sorokból hátrébb kerültek.
A legnagyobb aktivitás napja és időpontja
A forgalomban emelkedések láthatók. A részletes vizsgálathoz szükséges kiemelni az azok előfordulásának időpontját, nem szükséges megjeleníteni az összes órát és napot az időmérés során. Így könnyebben megtalálhatók az egyes kérések a naplófájlban, ha részletes elemzés szükséges.
SQL jelentés lekérdezés
SELECT
1 AS 'Sor: Felhasználók és botok találatainak napja és időpontja',
strftime('%d.%m-%H', datetime(EVENT_DT, 'unixepoch')) AS 'Dátum Idő',
HIB AS 'Botok, Találatok',
HIU AS 'Felhasználók, Találatok'
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_DTA grafikonon a legaktívabb órák 11, 14 és 20 az első napon. Másnap 13 órakor a robotok aktívak voltak.
A felhasználók napi átlagos aktivitása hetente
Már kicsit jobban átlátjuk az aktivitást és a forgalmat. A következő kérdés a felhasználók aktivitása volt. Az ilyen statisztikához ajánlott hosszú aggregációs időszakok, például egy hét.
SQL jelentés lekérdezés
SELECT
1 as 'Sor: Napi Felhasználói Aktivitás Hetente',
strftime('%W week', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Hét',
ROUND(1.0*SUM(FCT.PAGE_CNT)/SUM(FCT.IP_CNT),1) AS 'Oldalak IP-nként naponta',
ROUND(1.0*SUM(FCT.FILE_CNT)/SUM(FCT.IP_CNT),1) AS 'Fájlok IP-nként naponta'
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.' /* felhasználók csak */
AND HST.STATUS_GROUP IN ('Successful') /* jó oldalak */
AND datetime(FCT.EVENT_DT, 'unixepoch') > date('now', '-3 month')
GROUP BY strftime('%W week', datetime(FCT.EVENT_DT, 'unixepoch'))
ORDER BY FCT.EVENT_DTA heti statisztika azt mutatja, hogy átlagosan egy felhasználó napi 1,6 oldalt nyit meg. Az egy felhasználóra jutó kért fájlok száma ebben az esetben a weboldalra feltöltött új fájlok számától függ.
Minden kérés és azok státusza
A Webalizer mindig megmutatta a konkrét oldalkódokat, és szerettem volna, ha csak a sikeres kérések és hibák számát látom.
SQL jelentés lekérdezés
SELECT
1 as 'Sor: Minden kérelem státusza',
strftime('%d.%m', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Nap',
SUM(CASE WHEN STS.STATUS_GROUP='Sikeres' THEN FCT.REQUEST_CNT ELSE 0 END) AS 'Siker',
SUM(CASE WHEN STS.STATUS_GROUP='Átirányítás' THEN FCT.REQUEST_CNT ELSE 0 END) AS 'Átirányítás',
SUM(CASE WHEN STS.STATUS_GROUP='Kliens hiba' THEN FCT.REQUEST_CNT ELSE 0 END) AS 'Ügyfél hiba',
SUM(CASE WHEN STS.STATUS_GROUP='Szerver hiba' THEN FCT.REQUEST_CNT ELSE 0 END) AS 'Szerver hiba'
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_DTA jelentés a kéréseket tükrözi, nem a kattintásokat (látogatások), mivel a LINE_CNT metrikát a REQUEST_CNT COUNT(DISTINCT STG.REQUEST_NK) értéke számítja. A cél az, hogy hatékony eseményeket mutasson, például a MS botjai naponta többször is lekérdezik a robots.txt fájlt, és ebben az esetben ezek a lekérdezések egyszer fognak számítani. Ez lehetővé teszi a grafikonon a kiugrások simítását.
A grafikonból sok hibát láthatunk - ezek nem létező oldalak. Az elemzés eredményeként átirányításokat adtunk hozzá az eltávolított oldalakról.
Hibás kérések
A kérések részletes vizsgálatához részletes statisztikát lehet megjeleníteni.
SQL jelentés lekérdezés
SELECT
1 AS 'Táblázat: Legnagyobb hibás kérések',
REQ.REQUEST_NK AS 'Kérelem',
'Hiba' AS 'Kérelem státusza',
ROUND(SUM(FCT.LINE_CNT) / 14.0, 1) AS 'Látogatások naponta',
ROUND(SUM(FCT.IP_CNT) / 14.0, 1) AS 'IP-k naponta',
ROUND(SUM(FCT.BYTES)/1000 / 14.0, 1) AS 'KB naponta'
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 ('Kliens hiba', 'Szerver hiba')
AND datetime(FCT.EVENT_DT, 'unixepoch') >= date('now', '-14 day')
GROUP BY REQ.REQUEST_NK
ORDER BY 4 DESC
LIMIT 20Ebben a listában minden hívás szerepelni fog, például a kérés a /wp-login.php, a kérések átírási szabályainak kiigazításával. szerverrel ki lehet igazítani a szerver válaszát az ilyen kérésekre, és átirányíthatjuk őket a kezdőoldalra.
Tehát néhány egyszerű jelentés a szerver naplófájlja alapján teljes képet ad arról, mi történik az oldalon.
Hogyan szerezhetünk információt?
Az sqlite adatbázisok elegendőek. Létrehozunk táblázatokat: egy segédtáblát az ETL folyamatok naplózásához.
Staging táblázat, ahová a log fájlokat írjuk PHP eszközökkel. Két aggregáló táblázat. Létrehozunk egy napi táblázatot a felhasználói ügynökökről és a kérések státuszairól, valamint egy óránkénti táblázatot a kérésekről, a státusz csoportokról és az ügynökökről. Négy megfelelő dimenziós táblázatot.
Ennek eredményeként a következő relációs modell jött létre:
Adatmodell
Script az objektum létrehozásához az sqlite adatbázisban:
DDL objektum létrehozása
TÖRLÉS TÁBLÁZAT HA NINCS 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);Stádium
Az access.log fájl esetében olvasni, parse-elni és beírni kell az adatbázisba az összes lekérdezést. Ezt meg lehet tenni közvetlenül a szkriptnyelv eszközeivel, vagy sqlite eszközeivel.
A log fájl formátuma:
//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-]+) "(.*)" "(.*)"$/';
Kulcsok propagálása
Amikor a nyers adatok az adatbázisban vannak, be kell írni a dimenziós táblákba azokat a kulcsokat, amelyek ott nincsenek. Ekkor lehetséges a hivatkozás kialakítása a dimenziókra. Például a DIM_REFERRER táblában a kulcs a három mező kombinációja.
SQL lekérdezés a kulcsok propagálásához
/* 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 NULLA felhasználói ügynökök táblába való propagálás tartalmazhat botlogikát, például egy sql részlet:
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_BOTAggregáló táblák
Az utolsó lépésben az aggregáló táblákat töltjük be, például a napi táblát a következőképpen lehet betölteni:
SQL lekérdezés az aggregátum betöltéséhez
/* 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_IDAz sqlite adatbázis lehetővé teszi bonyolult lekérdezések írását. A WITH tartalmazza az adatok és kulcsok előkészítését. A fő lekérdezés összegyűjti az összes hivatkozást a dimenziókra.
A feltétel nem engedi újra betölteni a történetet: CAST(STG.EVENT_DT AS INTEGER) > $param_epoch_from, ahol a paraméter a lekérdezés eredménye
'SELECT COALESCE(MAX(EVENT_DT), '3600') AS LAST_EVENT_EPOCH FROM FCT_ACCESS_USER_AGENT_DD'
A feltétel csak teljes napot tölt be: CAST(STG.EVENT_DT AS INTEGER) < strftime('%s', date('now', 'start of day'))
Oldalak vagy fájlok számlálása primitív módon történik, pont keresésével.
Jelentések
Bonyolult vizualizációs rendszerekben lehetőség van meta-modell létrehozására az adatbázis-objektumok alapján, dinamikusan kezelni a szűrőket és az aggregálási szabályokat. Végső soron minden tisztességes eszköz SQL lekérdezéseket generál.
Ebben a példában előre elkészített SQL lekérdezéseket hozunk létre, és ezeket nézetek formájában elmentjük az adatbázisban - ez lesz a jelentések.
Vizualizáció.
A vizualizációs eszközként a Bluff-ot használtuk: Szép grafikonok JavaScriptben
Ehhez PHP segítségével végigmentünk az összes jelentésen, és generáltunk egy html fájlt táblázatokkal.
$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'
);Ez az eszköz egyszerűen vizualizálja az eredmények táblázatait.
Következtetés
A webanalízis példáján a cikk leírja az adathalmozók felépítéséhez szükséges mechanizmusokat. A végeredmények alapján a mélyreható adatelemzéshez és vizualizációhoz elegendő a legegyszerűbb eszközök használata.
Továbbiakban, ezen tároló példáján megpróbáljuk megvalósítani az olyan struktúrákat, mint a lassan változó dimenziók, metaadatok, aggregációs szintek és az adatok integrálása különböző forrásokból.
Részletesebben megvizsgáljuk az ETL folyamatok kezelésére szolgáló legegyszerűbb eszközt egyetlen táblázat alapján.
Térjünk vissza az adatok minőségének mérésére és ennek a folyamatnak az automatizálására.
Megvizsgáljuk a technikai környezet és az adathalmozók karbantartásának problémáit, ezért egy minimális erőforrással rendelkező tárolószervert valósítunk meg, például Raspberry Pi-alapján.
Forrás: habr.com
