Izibalo zesayithi kanye nesitoreji sakho esincane

I-Weblizer ne-Google Analytics zingisizile ukuba ngithole ukuqonda ngokwenzeka kumawebhusayithi iminyaka eminingi. Manje sengiyaqonda ukuthi banikeza ulwazi oluwusizo oluncane kakhulu. Ukufinyelela ifayela lakho le-access.log, kulula kakhulu ukuqonda izibalo nokusebenzisa amathuluzi ayisisekelo, njenge-sqlite, i-html, ulimi lwe-sql nanoma yiluphi ulimi lohlelo lokubhala.

Umthombo wedatha we-Webalizer yifayela leseva le-access.log. Yile ndlela amabha nezinombolo zayo ezibukeka ngayo, lapho kuphela inani eliphelele lethrafikhi elicacile:

Amathuluzi afana ne-Google Analytics iqoqa idatha kusuka ekhasini elilayishiwe ngokwalo. Basibonisa imidwebo embalwa nemigqa, okusekelwe kuyo okuvame ukuba nzima ukwenza iziphetho ezifanele. Mhlawumbe kwakufanele kwenziwe umzamo owengeziwe? Angazi.

Ngakho-ke, yini engangifuna ukuyibona kwizibalo zabavakashi bewebhusayithi?

Ithrafikhi yomsebenzisi ne-bot

Ngokuvamile ithrafikhi yesayithi inomkhawulo futhi kuyadingeka ukubona ukuthi ingakanani ithrafikhi ewusizo esetshenziswayo. Ngokwesibonelo, kanje:

Umbuzo wombiko we-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_DT

Igrafu ibonisa umsebenzi oqhubekayo we-bots. Kungaba okuthakazelisayo ukufunda ngokuningiliziwe abameleli abakhuthele kakhulu.

Amabhothi acasulayo

Sihlukanisa ama-bots ngokusekelwe kulwazi lwe-ejenti yomsebenzisi. Izibalo ezengeziwe kuthrafikhi yansuku zonke, inombolo yezicelo eziphumelele nezingaphumeleli zinikeza umbono omuhle womsebenzi we-bot.

Umbuzo wombiko we-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 10

Kulesi simo, umphumela wokuhlaziya kube yisinqumo sokukhawulela ukufinyelela kusayithi ngokulingeza kufayela le-robots.txt.

User-agent: AhrefsBot
Disallow: /
User-agent: dotbot
Disallow: /
User-agent: bingbot
Crawl-delay: 5
Amabhothi amabili okuqala anyamalala etafuleni, futhi amarobhothi e-MS asuka emigqeni yokuqala.

Usuku nesikhathi somsebenzi omkhulu kakhulu

Ama-upwings ayabonakala kuthrafikhi. Ukuze uzifunde ngokuningiliziwe, kuyadingeka ukugqamisa isikhathi sokwenzeka kwazo, futhi akudingekile ukubonisa wonke amahora nezinsuku zokulinganisa isikhathi. Lokhu kuzokwenza kube lula ukuthola izicelo ngazinye kufayela lokungena uma kudingeka ukuhlaziya okuningiliziwe.

Umbuzo wombiko we-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_DT

Sibheka amahora asebenza kakhulu u-11, 14 no-20 osuku lokuqala eshadini. Kodwa ngakusasa ngo-13:XNUMX ama-bots ayesebenza.

Isilinganiso somsebenzisi wansuku zonke ngeviki

Silungise izinto kancane ngomsebenzi kanye nethrafikhi. Umbuzo olandelayo bekungumsebenzi wabasebenzisi ngokwabo. Ezibalweni ezinjalo, izikhathi ezinde zokuhlanganisa, njengeviki, ziyafiseleka.

Umbuzo wombiko we-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_DT

Izibalo zamasonto onke zibonisa ukuthi ngokwesilinganiso umsebenzisi oyedwa uvula amakhasi angu-1,6 ngosuku. Inani lamafayela aceliwe ngomsebenzisi ngamunye kuleli cala lincike ekwengezweni kwamafayela amasha kusayithi.

Zonke izicelo kanye nezimo zazo

I-Webalizer yayihlala ikhombisa amakhodi ekhasi athile futhi ngangihlale ngifuna ukubona inani lezicelo eziyimpumelelo namaphutha.

Umbuzo wombiko we-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_DT

Umbiko ubonisa izicelo, hhayi ukuchofoza (okuphambili), ngokungafani ne-LINE_CNT, i-REQUEST_CNT metric ibalwa njenge-COUNT(DISTINCT STG.REQUEST_NK). Umgomo ukukhombisa imicimbi ephumelelayo, isibonelo, i-MS bots ivotela ifayela le-robots.txt izikhathi ezingamakhulu ngosuku futhi, kulokhu, ukuvota okunjalo kuzobalwa kanye. Lokhu kukuvumela ukuthi ufinyelele kugxume kugrafu.

Kusuka kugrafu ungabona amaphutha amaningi - lawa amakhasi angekho. Umphumela wokuhlaziywa kwaba ukungezwa kokuqondisa kabusha okuvela emakhasini akude.

Izicelo ezimbi

Ukuze uhlole izicelo ngokuningiliziwe, ungabonisa izibalo ezinemininingwane.

Umbuzo wombiko we-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 20

Lolu hlu luzoqukatha zonke izingcingo, isibonelo, isicelo ku-/wp-login.php Ngokulungisa imithetho yokubhala kabusha izicelo yiseva, ungakwazi ukulungisa ukusabela kweseva kuzicelo ezinjalo futhi uzithumele ekhasini lokuqala.

Ngakho-ke, imibiko embalwa elula esekelwe kufayela lokungena leseva inikeza isithombe esiphelele salokho okwenzekayo kusayithi.

Indlela yokuthola ulwazi?

I-database ye-sqlite yanele. Ake sakhe amathebula: asizayo ekugawulweni kwezinqubo ze-ETL.

Isiteji sethebula lapho sizobhala khona amafayela welogi sisebenzisa i-PHP. Amatafula amabili ahlanganisiwe. Masidale ithebula lansuku zonke elinezibalo zama-ejenti abasebenzisi kanye nezimo zesicelo. Njalo ngehora ngezibalo zezicelo, amaqembu ezimo nama-ejenti. Amathebula amane ezilinganiso ezifanele.

Umphumela uba imodeli yobudlelwano elandelayo:

Imodeli yedatha

Iskripthi sokudala into kusizindalwazi se-sqlite:

Ukwakhiwa kwento ye-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);

Isiteji

Endabeni yefayela le-access.log, kuyadingeka ukufunda, ukuhlaziya nokubhala zonke izicelo kusizindalwazi. Lokhu kungenziwa ngokuqondile usebenzisa ulimi lokubhala noma ngokusebenzisa amathuluzi we-sqlite.

Ifomethi yefayela lokungena:

//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-]+) "(.*)" "(.*)"$/';

Ukusabalalisa okuyinhloko

Uma idatha eluhlaza ikusizindalwazi, udinga ukubhala okhiye abangekho kumathebula okulinganisa. Khona-ke kuzokwazi ukwakha ireferensi yezilinganiso. Isibonelo, kuthebula le-DIM_REFERRER, ukhiye uyinhlanganisela yezinkambu ezintathu.

Umbuzo wokusabalalisa ukhiye we-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

Ukusatshalaliswa kuthebula le-ejenti yomsebenzisi kungase kuqukathe i-bot logic, isibonelo amazwibela we-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_BOT

Amathebula ahlanganisiwe

Okokugcina, sizolayisha amatafula ahlanganisiwe; isibonelo, ithebula lansuku zonke lingalayishwa kanje:

Umbuzo we-SQL wokulayisha ukuhlanganisa

/* 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_ID

I-database ye-sqlite ikuvumela ukuthi ubhale imibuzo eyinkimbinkimbi. I-ITH iqukethe ukulungiswa kwedatha nokhiye. Umbuzo oyinhloko uqoqa zonke izinkomba zobukhulu.

Umbandela ngeke uvumele ukulayisha umlando futhi: CAST(STG.EVENT_DT AS INTEGER) > $param_epoch_from, lapho ipharamitha ingumphumela wesicelo
‘KHETHA I-COALESCE(MAX(EVENT_DT), ‘3600’) NJENGOBA LAST_EVENT_EPOCH FROM FROM FCT_ACCESS_USER_AGENT_DD’

Isimo sizolayisha kuphela usuku olugcwele: CAST(STG.EVENT_DT AS INTEGER) < strftime(‘%s’, idethi(‘manje’, ‘ukuqala kosuku’))

Ukubala amakhasi noma amafayela kwenziwa ngendlela yakudala, ngokucinga iphuzu.

Imibiko

Ezinhlelweni eziyinkimbinkimbi zokubuka, kungenzeka ukudala imodeli ye-meta esekelwe ezintweni zesizindalwazi, ukuphatha ngokuguquguqukayo izihlungi nemithetho yokuhlanganisa. Ekugcineni, wonke amathuluzi ahloniphekile akhiqiza umbuzo we-SQL.

Kulesi sibonelo, sizodala imibuzo ye-SQL eseyenziwe ngomumo futhi siyilondoloze njengokubukwa kusizindalwazi - lena imibiko.

Ukubonakala

I-Bluff: Amagrafu amahle ku-JavaScript asetshenziswe njengethuluzi lokubuka

Ukwenza lokhu, bekudingeka ukuthi udlule kuyo yonke imibiko usebenzisa i-PHP futhi ukhiqize ifayela le-html elinamathebula.

$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'
);

Ithuluzi livele libone ngeso lengqondo amathebula emiphumela.

isiphetho

Isebenzisa ukuhlaziywa kwewebhu njengesibonelo, i-athikili ichaza izindlela ezidingekayo zokwakha izindawo zokugcina idatha. Njengoba kungabonakala emiphumeleni, amathuluzi alula anele ukuhlaziya okujulile nokuboniswa kwedatha.

Ngokuzayo, sisebenzisa le khosombe njengesibonelo, sizozama ukusebenzisa izakhiwo ezishintsha kancane ubukhulu, imethadatha, amaleveli okuhlanganisa kanye nokuhlanganiswa kwedatha evela emithonjeni ehlukene.

Futhi, ake sibhekisise ithuluzi elilula kakhulu lokuphatha izinqubo ze-ETL ngokusekelwe etafuleni elilodwa.

Ake sibuyele esihlokweni sokulinganisa ikhwalithi yedatha nokwenza le nqubo ngokuzenzakalelayo.

Sizofunda izinkinga zendawo yobuchwepheshe kanye nokugcinwa kokugcinwa kwedatha, esizosebenzisa iseva yokugcina ngezinsiza ezincane, isibonelo, ngokusekelwe ku-Raspberry Pi.

Source: www.habr.com