Webalizer ja Google Analytics on aidanud mul saada ülevaadet veebisaitidel toimuvast juba aastaid. Nüüd saan aru, et need annavad väga vähe kasulikku teavet. Juurdepääs failile access.log on väga lihtne statistikast aru saada ja üsna lihtsaid tööriistu, nagu sqlite, html, sql keel ja mis tahes skriptimiskeel, juurutada.
Webalizeri andmeallikaks on serveri access.log fail. Sellised näevad välja selle tulbad ja numbrid, millest selgub ainult liikluse kogumaht:
Sellised tööriistad nagu Google Analytics koguvad andmeid laaditud lehelt ise. Nad näitavad meile paari diagrammi ja joont, mille põhjal on sageli raske õigeid järeldusi teha. Võib-olla oleks pidanud rohkem pingutama? Ei tea.
Niisiis, mida ma tahtsin veebisaidi külastajate statistikas näha?
Kasutajate ja robotite liiklus
Sageli on saidi liiklus piiratud ja on vaja vaadata, kui palju kasulikku liiklust kasutatakse. Näiteks nii:
SQL aruande päring
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_DTGraafik näitab robotite pidevat aktiivsust. Huvitav oleks üksikasjalikult uurida kõige aktiivsemaid esindajaid.
Ärritavad robotid
Me liigitame robotid kasutajaagendi teabe põhjal. Täiendav statistika igapäevase liikluse, edukate ja ebaõnnestunud päringute arvu kohta annab hea ülevaate robotite tegevusest.
SQL aruande päring
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 10Antud juhul sai analüüsi tulemuseks otsus piirata juurdepääsu saidile, lisades selle faili robots.txt
User-agent: AhrefsBot
Disallow: /
User-agent: dotbot
Disallow: /
User-agent: bingbot
Crawl-delay: 5
Esimesed kaks robotit kadusid tabelist ja MS-i robotid liikusid esimestest ridadest allapoole.
Suurima tegevuse päev ja aeg
Liikluses on märgata tõuse. Nende üksikasjalikuks uurimiseks on vaja esile tuua nende toimumise aeg ning pole vaja kuvada kõiki ajamõõtmise tunde ja päevi. See hõlbustab üksikute päringute leidmist logifailist, kui on vaja üksikasjalikku analüüsi.
SQL aruande päring
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_DTJälgime graafikul esimese päeva aktiivsemaid tunde 11, 14 ja 20. Kuid järgmisel päeval kell 13:XNUMX olid robotid aktiivsed.
Keskmine päevane kasutajaaktiivsus nädala lõikes
Aktiivsuse ja liiklusega panime asjad natukene korda. Järgmine küsimus oli kasutajate endi aktiivsus. Sellise statistika jaoks on soovitav pikk koondamisperiood, näiteks nädal.
SQL aruande päring
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_DTIganädalane statistika näitab, et keskmiselt avab üks kasutaja 1,6 lehekülge päevas. Taotletavate failide arv kasutaja kohta sõltub sel juhul uute failide lisamisest saidile.
Kõik taotlused ja nende olekud
Webalizer näitas alati konkreetseid lehekoode ja ma tahtsin alati näha ainult edukate päringute ja vigade arvu.
SQL aruande päring
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_DTAruandes kuvatakse päringud, mitte klõpsud (löögid), erinevalt LINE_CNT-st arvutatakse REQUEST_CNT mõõdik COUNT(DISTINCT STG.REQUEST_NK). Eesmärk on näidata tõhusaid sündmusi, näiteks MS-i robotid küsitlevad faili robots.txt sadu kordi päevas ja sellisel juhul loetakse selliseid küsitlusi üks kord. See võimaldab teil graafikus hüppeid siluda.
Graafikult on näha palju vigu – need on olematud leheküljed. Analüüsi tulemuseks oli kauglehtede ümbersuunamiste lisamine.
Halvad taotlused
Taotluste üksikasjalikuks uurimiseks saate kuvada üksikasjalikku statistikat.
SQL aruande päring
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 20See loend sisaldab ka kõiki kõnesid, näiteks päringut aadressile /wp-login.php Kui kohandate serveri poolt päringute ümberkirjutamise reegleid, saate kohandada serveri reaktsiooni sellistele päringutele ja saata need avalehele.
Seega annavad mõned serveri logifailil põhinevad lihtsad aruanded saidil toimuvast üsna täieliku ülevaate.
Kuidas saada teavet?
Piisab sqlite andmebaasist. Koostame tabelid: abiseade ETL protsesside logimiseks.
Tabelietapp, kus kirjutame PHP-ga logifailid. Kaks koondtabelit. Loome igapäevase tabeli kasutajaagentide ja päringu olekute statistikaga. Iga tund koos statistikaga päringute, olekurühmade ja agentide kohta. Neli asjakohaste mõõtude tabelit.
Tulemuseks on järgmine relatsioonimudel:
Andmemudel
Skript objekti loomiseks sqlite'i andmebaasis:
DDL objekti loomine
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);Lava
Access.log faili puhul on vaja lugeda, sõeluda ja kirjutada andmebaasi kõik päringud. Seda saab teha kas otse, kasutades skriptikeelt või kasutades sqlite tööriistu.
Logifaili formaat:
//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-]+) "(.*)" "(.*)"$/';
Võtme levitamine
Kui algandmed on andmebaasis, tuleb mõõtmistabelitesse kirjutada võtmed, mida seal pole. Siis on võimalik mõõtmistele viide ehitada. Näiteks tabelis DIM_REFERRER on võtmeks kolme välja kombinatsioon.
SQL-võtme levitamise päring
/* 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 NULLKasutajaagendi tabelisse levitamine võib sisaldada bot-loogikat, näiteks sql-i fragmenti:
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_BOTKoondtabelid
Viimasena laadime koondtabelid, näiteks päevatabeli saab laadida järgmiselt:
SQL-päring agregaadi laadimiseks
/* 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 andmebaas võimaldab kirjutada keerulisi päringuid. WITH sisaldab andmete ja võtmete ettevalmistamist. Põhipäring kogub kõik viited dimensioonidele.
Tingimus ei luba ajalugu uuesti laadida: CAST(STG.EVENT_DT AS TÄISARV) > $param_epoch_from, kus parameeter on päringu tulemus
'SELECT COALESCE(MAX(EVENT_DT),'3600') AS LAST_EVENT_EPOCH FROM FCT_ACCESS_USER_AGENT_DD'
Tingimus laaditakse ainult terve päeva: CAST(STG.EVENT_DT AS TÄISARV) < strftime('%s', date('now', 'Start of day'))
Lehtede või failide loendamine toimub primitiivsel viisil, punkti otsides.
Aruanded
Keerulistes visualiseerimissüsteemides on võimalik luua andmebaasiobjektidel põhinevat metamudelit, hallata dünaamiliselt filtreid ja agregeerimisreegleid. Lõppkokkuvõttes genereerivad kõik korralikud tööriistad SQL-päringu.
Antud näites loome valmis SQL päringud ja salvestame need vaadetena andmebaasi – need on aruanded.
Visualiseerimine
Bluff: visualiseerimisvahendina kasutati ilusaid JavaScripti graafikuid
Selleks oli vaja PHP abil kõik aruanded läbi käia ja genereerida html fail tabelitega.
$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'
);Tööriist lihtsalt visualiseerib tulemuste tabeleid.
Väljund
Veebianalüüsi näitel kirjeldatakse artiklis andmeladude ehitamiseks vajalikke mehhanisme. Nagu tulemustest näha, piisab andmete süvaanalüüsiks ja visualiseerimiseks kõige lihtsamatest tööriistadest.
Edaspidi proovime selle hoidla näitel rakendada selliseid struktuure nagu aeglaselt muutuvad dimensioonid, metaandmed, koondamistasemed ja erinevatest allikatest pärit andmete integreerimine.
Vaatame lähemalt ka lihtsaimat tööriista ETL-protsesside haldamiseks ühe tabeli alusel.
Tuleme tagasi andmete kvaliteedi mõõtmise ja selle protsessi automatiseerimise teema juurde.
Uurime andmehoidlate tehnilise keskkonna ja hoolduse probleeme, mille jaoks rakendame minimaalse ressursiga salvestusserverit näiteks Raspberry Pi baasil.
Allikas: www.habr.com