Webalizer in Google Analytics mi že vrsto let pomagata pri vpogledu v dogajanje na spletnih straneh. Zdaj razumem, da ponujajo zelo malo koristnih informacij. Če imate dostop do vaše datoteke access.log, je zelo enostavno razumeti statistiko in implementirati povsem osnovna orodja, kot so sqlite, html, jezik sql in kateri koli skriptni programski jezik.
Vir podatkov za Webalizer je datoteka access.log strežnika. Takole izgledajo njegove vrstice in številke, iz katerih je razviden le skupni obseg prometa:
Orodja, kot je Google Analytics, sama zbirajo podatke z naložene strani. Pokažejo nam nekaj diagramov in črt, na podlagi katerih je pogosto težko pravilno sklepati. Mogoče bi se bilo treba bolj potruditi? ne vem
Torej, kaj sem želel videti v statistiki obiskanosti spletnega mesta?
Promet uporabnikov in botov
Pogosto je promet na spletnem mestu omejen in treba je videti, koliko koristnega prometa se uporablja. Na primer takole:
Poizvedba poročila SQL
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_DTGraf prikazuje konstantno aktivnost botov. Zanimivo bi bilo podrobno preučiti najbolj aktivne predstavnike.
Nadležni boti
Bote razvrščamo na podlagi informacij uporabniškega agenta. Dodatni statistični podatki o dnevnem prometu, številu uspešnih in neuspešnih zahtevkov dajejo dobro predstavo o aktivnosti bota.
Poizvedba poročila SQL
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 10V tem primeru je bila rezultat analize odločitev o omejitvi dostopa do spletnega mesta z dodajanjem v datoteko robots.txt
User-agent: AhrefsBot
Disallow: /
User-agent: dotbot
Disallow: /
User-agent: bingbot
Crawl-delay: 5
Prva dva robota sta izginila iz mize, roboti MS pa so se premaknili s prvih vrstic.
Dan in čas največje aktivnosti
V prometu so vidni dvigi. Za njihovo podrobno preučitev je treba izpostaviti čas njihovega nastanka in ni treba prikazati vseh ur in dni merjenja časa. To bo olajšalo iskanje posameznih zahtev v dnevniški datoteki, če je potrebna podrobna analiza.
Poizvedba poročila SQL
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_DTNa grafikonu opazimo najbolj aktivne ure 11, 14 in 20 prvega dne. Toda naslednji dan ob 13:XNUMX so bili roboti aktivni.
Povprečna dnevna aktivnost uporabnikov po tednih
Malo smo uredili zadeve z dejavnostjo in prometom. Naslednje vprašanje je bila aktivnost samih uporabnikov. Za takšno statistiko so zaželena dolga obdobja združevanja, na primer en teden.
Poizvedba poročila SQL
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_DTTedenska statistika kaže, da v povprečju en uporabnik odpre 1,6 strani na dan. Število zahtevanih datotek na uporabnika je v tem primeru odvisno od dodajanja novih datotek na spletno mesto.
Vse zahteve in njihovi statusi
Webalizer je vedno prikazoval specifične kode strani in vedno sem želel videti samo število uspešnih zahtev in napak.
Poizvedba poročila SQL
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_DTPoročilo prikazuje zahteve, ne klikov (zadetkov), za razliko od LINE_CNT je metrika REQUEST_CNT izračunana kot COUNT(DISTINCT STG.REQUEST_NK). Cilj je prikazati učinkovite dogodke, na primer roboti MS anketirajo datoteko robots.txt več stokrat na dan in v tem primeru se bodo takšne ankete štele enkrat. To vam omogoča, da zgladite skoke v grafu.
Iz grafa lahko vidite veliko napak - to so neobstoječe strani. Rezultat analize je bil dodatek preusmeritev z oddaljenih strani.
Slabe zahteve
Če želite podrobno pregledati zahteve, lahko prikažete podrobno statistiko.
Poizvedba poročila SQL
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 20Ta seznam bo vseboval tudi vse klice, na primer zahtevo za /wp-login.php S prilagoditvijo pravil za prepisovanje zahtev s strani strežnika, lahko prilagodite odziv strežnika na takšne zahteve in jih pošljete na začetno stran.
Nekaj preprostih poročil, ki temeljijo na dnevniški datoteki strežnika, torej daje dokaj popolno sliko o tem, kaj se dogaja na spletnem mestu.
Kako do informacij?
Zadostuje baza podatkov sqlite. Ustvarimo tabele: pomožne za beleženje ETL procesov.
Faza tabele, kjer bomo pisali dnevniške datoteke s PHP. Dve zbirni tabeli. Ustvarimo dnevno tabelo s statistiko uporabniških agentov in statusov zahtev. Vsako uro s statistiko zahtevkov, statusnih skupin in agentov. Štiri tabele ustreznih meritev.
Rezultat je naslednji relacijski model:
Podatkovni model
Skript za ustvarjanje predmeta v bazi podatkov sqlite:
Ustvarjanje predmeta 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);Stopnja
V primeru datoteke access.log je potrebno prebrati, razčleniti in zapisati vse zahteve v bazo podatkov. To je mogoče storiti neposredno z uporabo skriptnega jezika ali z orodji sqlite.
Oblika datoteke dnevnika:
//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-]+) "(.*)" "(.*)"$/';
Širjenje ključa
Ko so neobdelani podatki v zbirki podatkov, morate v merske tabele vpisati ključe, ki jih tam ni. Potem bo mogoče zgraditi referenco na meritve. Na primer, v tabeli DIM_REFERRER je ključ kombinacija treh polj.
Poizvedba za razmnoževanje ključ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Širjenje v tabelo uporabniškega agenta lahko vsebuje logiko robota, na primer izrezek 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_BOTZbirne tabele
Nazadnje bomo naložili zbirne tabele; dnevno tabelo lahko na primer naložimo takole:
SQL poizvedba za nalaganje agregata
/* 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_IDBaza podatkov sqlite omogoča pisanje kompleksnih poizvedb. WITH vsebuje pripravo podatkov in ključev. Glavna poizvedba zbira vse reference na dimenzije.
Pogoj ne bo dovoljeval ponovnega nalaganja zgodovine: CAST(STG.EVENT_DT AS INTEGER) > $param_epoch_from, kjer je parameter rezultat zahteve
'SELECT COALESCE(MAX(EVENT_DT), '3600') AS LAST_EVENT_EPOCH FROM FCT_ACCESS_USER_AGENT_DD'
Pogoj bo naložil samo cel dan: CAST(STG.EVENT_DT AS INTEGER) < strftime('%s', date('now', 'start of day'))
Štetje strani ali datotek poteka na primitiven način, z iskanjem točke.
Poročila
V kompleksnih vizualizacijskih sistemih je mogoče ustvariti metamodel na podlagi objektov baze podatkov, dinamično upravljati filtre in pravila združevanja. Navsezadnje vsa spodobna orodja ustvarijo poizvedbo SQL.
V tem primeru bomo ustvarili že pripravljene SQL poizvedbe in jih shranili kot poglede v bazo podatkov – to so poročila.
Vizualizacija
Blef: čudoviti grafi v JavaScriptu so bili uporabljeni kot orodje za vizualizacijo
Za to je bilo potrebno pregledati vsa poročila z uporabo PHP-ja in ustvariti html datoteko s tabelami.
$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'
);Orodje preprosto vizualizira tabele rezultatov.
Izhod
Članek na primeru spletne analize opisuje mehanizme, potrebne za izgradnjo podatkovnih skladišč. Kot je razvidno iz rezultatov, za poglobljeno analizo in vizualizacijo podatkov zadostujejo najenostavnejša orodja.
V prihodnosti bomo na primeru tega repozitorija poskušali implementirati takšne strukture, kot so počasi spreminjajoče se dimenzije, metapodatki, ravni združevanja in integracija podatkov iz različnih virov.
Oglejmo si tudi pobližje najenostavnejše orodje za upravljanje procesov ETL, ki temelji na eni sami tabeli.
Vrnimo se k temi merjenja kakovosti podatkov in avtomatizacije tega procesa.
Preučevali bomo problematiko tehničnega okolja in vzdrževanja podatkovnih shranjevalnikov, za kar bomo implementirali skladiščni strežnik z minimalnimi viri, na primer na osnovi Raspberry Pi.
Vir: www.habr.com