Webalizer ir Google Analytics jau daugelį metų man padėjo suprasti, kas vyksta svetainėse. Dabar suprantu, kad jie suteikia labai mažai naudingos informacijos. Turint prieigą prie failo access.log, labai lengva suprasti statistiką ir įdiegti gana paprastas priemones, tokias kaip sqlite, html, sql kalba ir bet kokia skriptų programavimo kalba.
Webalizer duomenų šaltinis yra serverio failas access.log. Taip atrodo jos juostos ir skaičiai, iš kurių aišku tik bendras srautas:
Tokie įrankiai kaip „Google Analytics“ renka duomenis iš pačių įkelto puslapio. Jie mums parodo keletą diagramų ir linijų, pagal kurias dažnai sunku padaryti teisingas išvadas. Gal reikėjo labiau pasistengti? Nežinau.
Taigi, ką aš norėjau matyti svetainės lankytojų statistikoje?
Vartotojų ir robotų srautas
Dažnai svetainės srautas yra ribotas ir reikia žiūrėti, kiek naudingo srauto išnaudojama. Pavyzdžiui, taip:
SQL ataskaitos užklausa
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_DTGrafike rodomas nuolatinis botų aktyvumas. Būtų įdomu išsamiai ištirti aktyviausius atstovus.
Erzinantys robotai
Mes klasifikuojame robotus pagal vartotojo agento informaciją. Papildoma kasdienio srauto statistika, sėkmingų ir nesėkmingų užklausų skaičius leidžia gerai suprasti botų veiklą.
SQL ataskaitos užklausa
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 10Šiuo atveju analizės rezultatas buvo sprendimas apriboti prieigą prie svetainės įtraukiant ją į failą robots.txt
User-agent: AhrefsBot
Disallow: /
User-agent: dotbot
Disallow: /
User-agent: bingbot
Crawl-delay: 5
Pirmieji du robotai dingo nuo stalo, o MS robotai pajudėjo žemyn iš pirmųjų eilučių.
Didžiausios veiklos diena ir laikas
Eisme matomi pakilimai. Norint juos išsamiai išnagrinėti, būtina išryškinti jų atsiradimo laiką ir nebūtina rodyti visų laiko matavimo valandų ir dienų. Tai leis lengviau rasti atskiras užklausas žurnalo faile, jei reikės išsamios analizės.
SQL ataskaitos užklausa
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_DTGrafike stebime pirmos dienos 11, 14 ir 20 aktyviausias valandas. Tačiau kitą dieną 13:XNUMX robotai buvo aktyvūs.
Vidutinis naudotojų aktyvumas per dieną pagal savaitę
Šiek tiek sutvarkėme reikalus pagal veiklą ir eismą. Kitas klausimas buvo pačių vartotojų aktyvumas. Tokiai statistikai pageidautinas ilgas apibendrinimo laikotarpis, pavyzdžiui, savaitė.
SQL ataskaitos užklausa
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_DTSavaitės statistika rodo, kad vidutiniškai vienas vartotojas per dieną atveria 1,6 puslapio. Vieno vartotojo prašomų failų skaičius šiuo atveju priklauso nuo naujų failų įtraukimo į svetainę.
Visos užklausos ir jų būsenos
Webalizer visada rodydavo konkrečius puslapių kodus ir visada norėjau matyti tik sėkmingų užklausų ir klaidų skaičių.
SQL ataskaitos užklausa
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_DTAtaskaitoje pateikiamos užklausos, o ne paspaudimai (įvykiai), skirtingai nei LINE_CNT, REQUEST_CNT metrika apskaičiuojama kaip COUNT (DISTINCT STG.REQUEST_NK). Tikslas yra parodyti efektyvius įvykius, pavyzdžiui, MS robotai apklausia robots.txt failą šimtus kartų per dieną ir tokiu atveju tokios apklausos bus skaičiuojamos vieną kartą. Tai leidžia išlyginti šuolius diagramoje.
Iš grafiko matosi daug klaidų – tai neegzistuojantys puslapiai. Analizės rezultatas buvo peradresavimų iš nuotolinių puslapių pridėjimas.
Blogi prašymai
Norėdami išsamiai išnagrinėti užklausas, galite rodyti išsamią statistiką.
SQL ataskaitos užklausa
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 20Šiame sąraše taip pat bus visi iškvietimai, pvz., užklausa /wp-login.php Pakoreguodami serverio užklausų perrašymo taisykles, galite pakoreguoti serverio reakciją į tokias užklausas ir išsiųsti jas į pradinį puslapį.
Taigi, kelios paprastos ataskaitos, pagrįstos serverio žurnalo failu, suteikia gana išsamų vaizdą apie tai, kas vyksta svetainėje.
Kaip gauti informaciją?
Pakanka sqlite duomenų bazės. Sukurkime lenteles: pagalbines ETL procesų registravimui.
Lentelės etapas, kuriame rašysime žurnalo failus naudodami PHP. Dvi suvestinės lentelės. Sukurkime kasdienę lentelę su vartotojų agentų ir užklausų būsenų statistika. Kas valandą su užklausų, būsenų grupių ir agentų statistika. Keturios atitinkamų išmatavimų lentelės.
Rezultatas yra toks reliacinis modelis:
Duomenų modelis
Scenarijus, skirtas sukurti objektą sqlite duomenų bazėje:
DDL objektų kūrimas
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);Scena
Access.log failo atveju būtina perskaityti, analizuoti ir įrašyti visas užklausas į duomenų bazę. Tai galima padaryti tiesiogiai naudojant scenarijų kalbą arba naudojant sqlite įrankius.
Žurnalo failo formatas:
//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-]+) "(.*)" "(.*)"$/';
Raktų platinimas
Kai neapdoroti duomenys yra duomenų bazėje, į matavimo lenteles turite įrašyti raktus, kurių ten nėra. Tada bus galima sukurti nuorodą į matavimus. Pavyzdžiui, lentelėje DIM_REFERRER raktas yra trijų laukų derinys.
SQL rakto platinimo užklausa
/* 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 NULLPerkeliant į vartotojo agento lentelę gali būti roboto logikos, pavyzdžiui, sql fragmentas:
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_BOTSuvestinės lentelės
Galiausiai įkelsime suvestines lenteles; pavyzdžiui, dienos lentelę galima įkelti taip:
SQL užklausa, skirta įkelti agregatą
/* 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_IDSqlite duomenų bazė leidžia rašyti sudėtingas užklausas. WITH yra duomenų ir raktų paruošimas. Pagrindinė užklausa renka visas nuorodas į matmenis.
Sąlyga neleis vėl įkelti istorijos: CAST(STG.EVENT_DT AS INTEGER) > $param_epoch_from, kur parametras yra užklausos rezultatas
'SELECT COALESCE(MAX(EVENT_DT), '3600') AS LAST_EVENT_EPOCH FROM FCT_ACCESS_USER_AGENT_DD'
Sąlyga bus įkelta tik visą dieną: CAST(STG.EVENT_DT AS INTEGER) < strftime('%s', date('dabar', 'dienos pradžia'))
Puslapių ar failų skaičiavimas atliekamas primityviai, ieškant taško.
Ataskaitos
Sudėtingose vizualizacijos sistemose galima sukurti metamodelį remiantis duomenų bazės objektais, dinamiškai valdyti filtrus ir agregavimo taisykles. Galiausiai visi tinkami įrankiai generuoja SQL užklausą.
Šiame pavyzdyje mes sukursime paruoštas SQL užklausas ir išsaugosime jas kaip rodinius duomenų bazėje – tai ataskaitos.
Vizualizacija
Blefas: gražūs „JavaScript“ grafikai buvo naudojami kaip vizualizacijos įrankis
Norėdami tai padaryti, reikėjo pereiti visas ataskaitas naudojant PHP ir sugeneruoti html failą su lentelėmis.
$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'
);Įrankis tiesiog vizualizuoja rezultatų lenteles.
Produkcija
Kaip pavyzdį naudojant žiniatinklio analizę, straipsnyje aprašomi mechanizmai, reikalingi duomenų saugykloms kurti. Kaip matyti iš rezultatų, giliai duomenų analizei ir vizualizavimui pakanka paprasčiausių įrankių.
Ateityje, naudodami šią saugyklą kaip pavyzdį, bandysime įdiegti tokias struktūras kaip lėtai besikeičiančios dimensijos, metaduomenys, agregavimo lygiai ir duomenų iš skirtingų šaltinių integravimas.
Be to, atidžiau pažvelkime į paprasčiausią ETL procesų valdymo įrankį, pagrįstą viena lentele.
Grįžkime prie duomenų kokybės matavimo ir šio proceso automatizavimo temos.
Tirsime duomenų saugyklų techninės aplinkos ir priežiūros problemas, kurioms diegsime saugyklos serverį su minimaliais resursais, pavyzdžiui, Raspberry Pi pagrindu.
Šaltinis: www.habr.com