A Webalizer és a Google Analytics évek óta segített abban, hogy betekintést nyerjek a webhelyeken zajló eseményekbe. Most már értem, hogy nagyon kevés hasznos információval szolgálnak. Az access.log fájlhoz való hozzáféréssel nagyon könnyű megérteni a statisztikákat, és olyan alapvető eszközöket implementálni, mint az sqlite, html, az sql nyelv és bármilyen szkript programozási nyelv.
A Webalizer adatforrása a szerver access.log fájlja. Így néznek ki annak oszlopai és számai, amelyekből csak a teljes forgalom látható:
Az olyan eszközök, mint a Google Analytics, magukról a betöltött oldalról gyűjtenek adatokat. Mutatnak nekünk pár ábrát, vonalat, amelyek alapján sokszor nehéz helyes következtetéseket levonni. Talán több erőfeszítést kellett volna tenni? Nem tudom.
Szóval mit szerettem volna látni a weboldal látogatottsági statisztikájában?
Felhasználói és botforgalom
A webhely forgalma gyakran korlátozott, és látni kell, hogy mennyi hasznos forgalmat használnak fel. Például így:
SQL jelentés lekérdezés
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_DTA grafikon a botok állandó tevékenységét mutatja. Érdekes lenne részletesen tanulmányozni a legaktívabb képviselőket.
Idegesítő robotok
A botokat a felhasználói ügynök információi alapján osztályozzuk. A napi forgalomra vonatkozó további statisztikák, a sikeres és sikertelen kérések száma jó képet ad a bot tevékenységéről.
SQL jelentés lekérdezés
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 10Ebben az esetben az elemzés eredménye az volt a döntés, hogy korlátozzák a webhelyhez való hozzáférést a robots.txt fájlhoz való hozzáadásával.
User-agent: AhrefsBot
Disallow: /
User-agent: dotbot
Disallow: /
User-agent: bingbot
Crawl-delay: 5
Az első két bot eltűnt az asztalról, az MS robotok pedig lefelé haladtak az első sorokból.
A legnagyobb tevékenység napja és ideje
Fellendülés látható a forgalomban. Részletes tanulmányozásukhoz szükséges kiemelni előfordulásuk idejét, és nem szükséges az időmérés összes óráját és napját megjeleníteni. Ez megkönnyíti az egyes kérések megtalálását a naplófájlban, ha részletes elemzésre van szükség.
SQL jelentés lekérdezés
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_DTA diagramon az első nap legaktívabb 11., 14. és 20. óráit figyeljük. De másnap 13:XNUMX-kor a botok aktívak voltak.
Átlagos napi felhasználói aktivitás heti bontásban
Kicsit rendeztük a dolgokat az aktivitással és a forgalommal. A következő kérdés maguknak a felhasználóknak az aktivitása volt. Az ilyen statisztikákhoz hosszú összesítési időszakok, például egy hét kívánatosak.
SQL jelentés lekérdezés
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_DTA heti statisztikák azt mutatják, hogy egy felhasználó átlagosan 1,6 oldalt nyit meg naponta. A felhasználónként kért fájlok száma ebben az esetben attól függ, hogy új fájlokat adtak hozzá a webhelyhez.
Minden kérés és állapotuk
A Webalizer mindig konkrét oldalkódokat mutatott, és mindig csak a sikeres kérések és hibák számát szerettem volna látni.
SQL jelentés lekérdezés
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_DTA jelentés a kéréseket jeleníti meg, nem a kattintásokat (lekéréseket), a LINE_CNT-től eltérően a REQUEST_CNT mutató kiszámítása COUNT(DISTINCT STG.REQUEST_NK). A cél az, hogy hatékony eseményeket mutassanak, például az MS botok naponta több százszor lekérdezik a robots.txt fájlt, és ebben az esetben az ilyen szavazásokat egyszer számolják. Ez lehetővé teszi az ugrások kisimítását a grafikonon.
A grafikonon sok hiba látható – ezek nem létező oldalak. Az elemzés eredménye a távoli oldalakról történő átirányítások hozzáadása volt.
Rossz kérések
A kérelmek részletes vizsgálatához részletes statisztikákat jeleníthet meg.
SQL jelentés lekérdezés
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 20Ez a lista tartalmazza az összes hívást is, például egy kérést a /wp-login.php címre. A kérések kiszolgáló általi átírási szabályainak módosításával módosíthatja a szerver reakcióját az ilyen kérésekre, és elküldheti azokat a kezdőoldalra.
Tehát néhány egyszerű jelentés a szerver naplófájlja alapján meglehetősen teljes képet ad arról, hogy mi történik az oldalon.
Hogyan lehet információt szerezni?
Elegendő egy sqlite adatbázis. Készítsünk táblákat: segédeszköz az ETL folyamatok naplózásához.
Táblázat szakasz, ahol naplófájlokat fogunk írni PHP használatával. Két összesített táblázat. Hozzon létre egy napi táblázatot a felhasználói ügynökök és a kérések állapotának statisztikáival. Óránként a kérésekre, állapotcsoportokra és ügynökökre vonatkozó statisztikákkal. Négy táblázat a vonatkozó mérésekről.
Az eredmény a következő relációs modell:
Adatmodell
Parancsfájl objektum létrehozásához sqlite adatbázisban:
DDL objektum létrehozása
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);Színpad
Az access.log fájl esetében minden kérést be kell olvasni, elemezni és be kell írni az adatbázisba. Ez megtehető közvetlenül egy szkriptnyelv használatával vagy sqlite eszközök használatával.
Naplófájl formátum:
//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-]+) "(.*)" "(.*)"$/';
Kulcsterjedés
Amikor a nyers adatok az adatbázisban vannak, akkor a mérési táblázatokba be kell írni azokat a kulcsokat, amelyek nincsenek ott. Ekkor lehet majd referenciát építeni a mérésekre. Például a DIM_REFERRER táblában a kulcs három mező kombinációja.
SQL kulcs terjesztési lekérdezés
/* 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 táblába történő terjesztés tartalmazhat bot logikát, például az sql kódrészletet:
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Összesített táblázatok
Végül betöltjük az összesített táblákat, például a napi táblázatot a következőképpen tölthetjük be:
SQL lekérdezés 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 összetett 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 a dimenziókra vonatkozó összes hivatkozást.
A feltétel nem teszi lehetővé az előzmények újbóli betöltését: CAST(STG.EVENT_DT AS INTEGER) > $param_epoch_from, ahol a paraméter a kérés eredménye
'SELECT COALESCE(MAX(EVENT_DT), '3600') AS LAST_EVENT_EPOCH FROM FCT_ACCESS_USER_AGENT_DD'
A feltétel csak a teljes napot tölti be: CAST(STG.EVENT_DT AS INTEGER) < strftime('%s', date('most', 'nap kezdete'))
Az oldalak vagy fájlok számlálása primitív módon, egy pont keresésével történik.
Jelentések
Összetett vizualizációs rendszerekben lehetőség nyílik adatbázis-objektumok alapján metamodell létrehozására, szűrők és aggregációs szabályok dinamikus kezelésére. Végső soron minden megfelelő eszköz SQL-lekérdezést generál.
Ebben a példában kész SQL lekérdezéseket készítünk, és nézetként mentjük el az adatbázisban – ezek jelentések.
Megjelenítés
Blöff: A JavaScript gyönyörű grafikonjait vizualizációs eszközként használták
Ehhez PHP-vel át kellett menni az összes jelentésen, és elő kellett készíteni 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'
);Az eszköz egyszerűen megjeleníti az eredmények táblázatait.
Teljesítmény
A cikk a webelemzés példaként való felhasználásával leírja az adattárházak felépítéséhez szükséges mechanizmusokat. Amint az az eredményekből látható, a legegyszerűbb eszközök is elegendőek az adatok mély elemzéséhez és megjelenítéséhez.
A jövőben ennek a repository-nak a felhasználásával megpróbálunk olyan struktúrákat megvalósítani, mint a lassan változó dimenziók, metaadatok, aggregációs szintek és a különböző forrásokból származó adatok integrálása.
Ezenkívül nézzük meg közelebbről az ETL-folyamatok egyetlen táblán alapuló kezelésének legegyszerűbb eszközét.
Térjünk vissza az adatminőség mérésének és ennek a folyamatnak az automatizálásának témájához.
Tanulmányozzuk az adattárolók műszaki környezetének és karbantartásának problémáit, melyhez minimális erőforrással, például Raspberry Pi-re épülő tárolószervert valósítunk meg.
Forrás: will.com