Webalizer og Google Analytics hafa hjálpað mér að fá innsýn í hvað er að gerast á vefsíðum í mörg ár. Nú skil ég að þeir veita mjög litlar gagnlegar upplýsingar. Með aðgang að access.log skránni þinni er mjög auðvelt að skilja tölfræðina og innleiða einföld verkfæri eins og sqlite, html, sql tungumálið og hvaða forskriftarforritunarmál sem er.
Gagnagjafinn fyrir Webalizer er access.log skrá þjónsins. Svona líta súlur og tölur út, þar sem aðeins heildarmagn umferðarinnar er ljóst:
Verkfæri eins og Google Analytics safna sjálfum gögnum frá hlaðnu síðunni. Þeir sýna okkur nokkrar skýringarmyndir og línur, út frá þeim er oft erfitt að draga réttar ályktanir. Hefði kannski átt að gera meira átak? Veit ekki.
Svo, hvað vildi ég sjá í tölfræði vefsíðu gesta?
Umferð notenda og botna
Oft er umferð á síðuna takmörkuð og það er nauðsynlegt að sjá hversu mikið af gagnlegum umferð er notuð. Til dæmis, svona:
SQL skýrslufyrirspurn
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_DTGrafið sýnir stöðuga virkni vélmenna. Fróðlegt væri að kynna sér ítarlega virkasta fulltrúana.
Pirrandi vélmenni
Við flokkum vélmenni út frá upplýsingum um umboðsmann notenda. Viðbótarupplýsingar um daglega umferð, fjölda árangursríkra og misheppnaðra beiðna gefa góða hugmynd um virkni botna.
SQL skýrslufyrirspurn
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Í þessu tilviki var niðurstaða greiningarinnar sú ákvörðun að takmarka aðgang að síðunni með því að bæta henni við robots.txt skrána
User-agent: AhrefsBot
Disallow: /
User-agent: dotbot
Disallow: /
User-agent: bingbot
Crawl-delay: 5
Fyrstu tveir vélmennin hurfu af borðinu og MS vélmennin færðust niður úr fyrstu línum.
Dagur og tími mestu athafna
Uppsveiflur sjást í umferðinni. Til að rannsaka þær í smáatriðum er nauðsynlegt að varpa ljósi á tímann þegar þær gerast og það er ekki nauðsynlegt að sýna allar klukkustundir og daga tímamælinga. Þetta mun gera það auðveldara að finna einstakar beiðnir í skránni ef þörf er á nákvæmri greiningu.
SQL skýrslufyrirspurn
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_DTVið fylgjumst með virkustu klukkustundunum 11, 14 og 20 fyrsta dags á töflunni. En daginn eftir klukkan 13:XNUMX voru bottarnir virkir.
Dagleg meðalvirkni notenda eftir viku
Við redduðum hlutunum aðeins með virkni og umferð. Næsta spurning var virkni notendanna sjálfra. Fyrir slíka tölfræði eru langir samansafnunartímar, svo sem viku, æskilegir.
SQL skýrslufyrirspurn
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_DTVikulegar tölur sýna að að meðaltali opnar einn notandi 1,6 síður á dag. Fjöldi umbeðna skráa á hvern notanda í þessu tilfelli fer eftir því að nýjum skrám er bætt við síðuna.
Allar beiðnir og staða þeirra
Webalizer sýndi alltaf sérstaka síðukóða og ég vildi alltaf sjá bara fjölda vel heppnaðra beiðna og villna.
SQL skýrslufyrirspurn
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_DTSkýrslan sýnir beiðnir, ekki smelli (hits), ólíkt LINE_CNT er mæligildið REQUEST_CNT reiknað sem COUNT(DISTINCT STG.REQUEST_NK). Markmiðið er að sýna árangursríka atburði, til dæmis, MS vélmenni skoða robots.txt skrána hundruð sinnum á dag og í þessu tilviki verða slíkar kannanir taldar einu sinni. Þetta gerir þér kleift að jafna út stökk í línuritinu.
Af grafinu má sjá margar villur - þetta eru engar síður. Niðurstaða greiningarinnar var að bæta við tilvísunum frá ytri síðum.
Slæmar beiðnir
Til að skoða beiðnir ítarlega geturðu birt nákvæma tölfræði.
SQL skýrslufyrirspurn
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Þessi listi mun einnig innihalda öll símtöl, til dæmis beiðni til /wp-login.php Með því að breyta reglum um endurskrifa beiðnir af þjóninum geturðu stillt viðbrögð þjónsins við slíkum beiðnum og sent þær á upphafssíðuna.
Svo, nokkrar einfaldar skýrslur byggðar á notendaskrá netþjónsins gefa nokkuð heila mynd af því sem er að gerast á síðunni.
Hvernig á að fá upplýsingar?
Sqlite gagnagrunnur er nóg. Búum til töflur: hjálpartæki til að skrá ETL ferla.
Tafla stig þar sem við munum skrifa log skrár með PHP. Tvær samanlagðar töflur. Við skulum búa til daglega töflu með tölfræði um umboðsmenn notenda og biðja um stöður. Á klukkutíma fresti með tölfræði um beiðnir, stöðuhópa og umboðsmenn. Fjórar töflur yfir viðeigandi mælingar.
Niðurstaðan er eftirfarandi venslalíkan:
Gagnalíkan
Forskrift til að búa til hlut í sqlite gagnagrunni:
DDL hlut sköpun
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);Sviði
Þegar um er að ræða access.log skrána er nauðsynlegt að lesa, flokka og skrifa allar beiðnir í gagnagrunninn. Þetta er hægt að gera annað hvort beint með því að nota forskriftarmál eða með því að nota sqlite verkfæri.
Skráarsnið:
//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-]+) "(.*)" "(.*)"$/';
Lykilútbreiðsla
Þegar hrá gögnin eru í gagnagrunninum þarf að skrifa lykla sem eru ekki til í mælitöflurnar. Þá verður hægt að byggja tilvísun í mælingarnar. Til dæmis, í DIM_REFERRER töflunni, er lykillinn samsetning þriggja reita.
Fyrirspurn um útbreiðslu SQL lykils
/* 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Útbreiðsla til notendamiðlaratöflunnar getur innihaldið botnökfræði, til dæmis sql bútinn:
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_BOTSamanlagt töflur
Að lokum munum við hlaða heildartöflunum; til dæmis er hægt að hlaða daglegu töflunni sem hér segir:
SQL fyrirspurn til að hlaða samanlagðri
/* 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 gagnagrunnurinn gerir þér kleift að skrifa flóknar fyrirspurnir. WITH inniheldur gerð gagna og lykla. Aðalfyrirspurnin safnar öllum tilvísunum í víddir.
Skilyrðið leyfir ekki að hlaða sögunni aftur: CAST(STG.EVENT_DT AS INTEGER) > $param_epoch_from, þar sem færibreytan er afleiðing beiðninnar
'VELDU COALESCE(MAX(EVENT_DT), '3600') AS LAST_EVENT_EPOCH FRÁ FCT_ACCESS_USER_AGENT_DD'
Skilyrðið hleður aðeins allan daginn: CAST(STG.EVENT_DT AS INTEGER) < strftime('%s', date('now', 'start of day'))
Talning síðna eða skráa fer fram á frumstæðan hátt, með því að leita að punkti.
Skýrslur
Í flóknum sjónkerfiskerfum er hægt að búa til meta-líkan sem byggir á hlutum í gagnagrunni, stjórna síum og samsöfnunarreglum á kraftmikinn hátt. Að lokum búa öll almennileg verkfæri til SQL fyrirspurn.
Í þessu dæmi munum við búa til tilbúnar SQL fyrirspurnir og vista þær sem skoðanir í gagnagrunninum - þetta eru skýrslur.
Sjónræn
Bluff: Falleg línurit í JavaScript voru notuð sem sjónmyndartæki
Til að gera þetta var nauðsynlegt að fara í gegnum allar skýrslur með PHP og búa til html skrá með töflum.
$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ólið sýnir einfaldlega töflur yfir niðurstöður.
Output
Með því að nota vefgreiningu sem dæmi lýsir greinin þeim aðferðum sem nauðsynlegar eru til að byggja upp gagnageymslur. Eins og sést á niðurstöðunum duga einföldustu verkfærin til djúprar greiningar og sjónrænnar gagna.
Í framtíðinni, með því að nota þessa geymslu sem dæmi, munum við reyna að innleiða slíka uppbyggingu eins og hægt breytast víddir, lýsigögn, samsöfnunarstig og samþætting gagna frá mismunandi aðilum.
Við skulum líka skoða einfaldasta tólið til að stjórna ETL ferlum sem byggjast á einni töflu nánar.
Snúum okkur aftur að efninu að mæla gæði gagna og gera þetta ferli sjálfvirkt.
Við munum kynna okkur vandamálin í tæknilegu umhverfi og viðhaldi gagnageymslum, fyrir það munum við innleiða geymsluþjón með lágmarks fjármagni, til dæmis byggðan á Raspberry Pi.
Heimild: www.habr.com