Iinkcukacha-manani zesayithi kunye neyakho indawo yokugcina encinci

I-Webalizer kunye ne-Google Analytics zindincedile ukuba ndiqonde ukuba kwenzeka ntoni kwiiwebhusayithi iminyaka emininzi. Ngoku ndiyaqonda ukuba babonelela ngolwazi oluncinci kakhulu oluluncedo. Ukuba nofikelelo kwifayile yakho yofikelelo.log, kulula kakhulu ukuqonda izibalo kunye nokuphumeza izixhobo ezisisiseko, njenge sqlite, html, ulwimi lwesql kunye naluphi na ulwimi lodweliso lwenkqubo.

Umthombo wedatha ye-Webalizer yifayile yomncedisi wofikelelo.log. Yile ndlela imivalo kunye namanani akhangeleka ngayo, apho kuphela umthamo opheleleyo wezithuthi ezicacileyo:

Izixhobo ezifana ne-Google Analytics ziqokelela idatha kwiphepha elilayishiwe ngokwazo. Basibonisa imizobo eliqela kunye nemigca, esekwe kuyo kudla ngokuba nzima ukwenza izigqibo ezichanekileyo. Mhlawumbi kwakufanele kwenziwe umgudu ongakumbi? Ndazi.

Ke, bendifuna ukubona ntoni kwiwebhusayithi yezibalo zabatyeleli?

Umsebenzisi kunye ne-bot traffic

Ngokuqhelekileyo ukugcwala kwendawo kunqongophele kwaye kuyafuneka ukubona ukuba kungakanani na ukugcwala kwezithuthi ezisetyenziswayo. Umzekelo, njengale:

Umbuzo wengxelo yeSQL

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. Kuya kuba nomdla ukufunda ngokweenkcukacha abameli abasebenzayo kakhulu.

Iibhothi ezicaphukisayo

Sihlela i-bots ngokusekelwe kulwazi lwe-arhente yomsebenzisi. Izibalo ezongezelelweyo kwitrafikhi yemihla ngemihla, inani lezicelo eziphumeleleyo nezingaphumeleliyo zinika umbono olungileyo womsebenzi we-bot.

Umbuzo wengxelo yeSQL

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

Kule meko, umphumo wohlalutyo wawusisigqibo sokukhawulela ukufikelela kwisiza ngokuyongeza kwifayile ye-robots.txt.

User-agent: AhrefsBot
Disallow: /
User-agent: dotbot
Disallow: /
User-agent: bingbot
Crawl-delay: 5
Iibhothi ezimbini zokuqala zanyamalala etafileni, kwaye iirobhothi ze-MS zehla ukusuka kwimigca yokuqala.

Usuku kunye nexesha lomsebenzi omkhulu

Ukuphakama kuyabonakala kwitrafikhi. Ukuzifundela ngokweenkcukacha, kuyimfuneko ukugqamisa ixesha lokuvela kwazo, kwaye akuyimfuneko ukubonisa zonke iiyure kunye neentsuku zokulinganisa ixesha. Oku kuya kwenza kube lula ukufumana izicelo zomntu ngamnye kwifayile yelog ukuba uhlalutyo oluneenkcukacha luyafuneka.

Umbuzo wengxelo yeSQL

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

Sijonga ezona yure zisebenzayo nge-11, 14 kunye ne-20 yosuku lokuqala kwitshathi. Kodwa kusuku olulandelayo ngo-13:XNUMX ii-bots zazisebenza.

I-avareji yomsebenzisi wemihla ngemihla ngeveki

Salungisa izinto kancinci ngomsebenzi kunye netrafikhi. Umbuzo olandelayo yayingumsebenzi wabasebenzisi ngokwabo. Kumanani anjalo, amaxesha amade okudibanisa, njengeveki, ayanqweneleka.

Umbuzo wengxelo yeSQL

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 zeveki zibonisa ukuba ngokomndilili omnye umsebenzisi uvula amaphepha ayi-1,6 ngosuku. Inani leefayile eziceliweyo ngomsebenzisi ngamnye kule meko kuxhomekeke ekongezweni kweefayile ezintsha kwindawo.

Zonke izicelo kunye neemeko zazo

I-Webalizer yayisoloko ibonisa iikhowudi zephepha elithile kwaye bendihlala ndifuna ukubona inani lezicelo eziyimpumelelo kunye neempazamo.

Umbuzo wengxelo yeSQL

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

Ingxelo ibonisa izicelo, hayi ukucofa (ukubetha), ngokungafaniyo ne-LINE_CNT, i-REQUEST_CNT metric ibalwa njenge-COUNT(DISTINCT STG.REQUEST_NK). Injongo kukubonisa iziganeko ezisebenzayo, umzekelo, i-MS bots poll ifayile ye-robots.txt ngamakhulu amaxesha ngosuku kwaye, kulo mzekelo, ezo zonyulo ziya kubalwa kanye. Oku kukuvumela ukuba ugudise ukutsiba kwigrafu.

Ukusuka kwigrafu ungabona iimpazamo ezininzi - la maphepha angekhoyo. Isiphumo sokuhlalutya kwaba kokongezwa kokuqondisa kwakhona ukusuka kumaphepha akude.

Izicelo ezimbi

Ukuphonononga izicelo ngokweenkcukacha, ungabonisa iinkcukacha-manani.

Umbuzo wengxelo yeSQL

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

Olu luhlu luya kuphinda luqulathe zonke iifowuni, umzekelo, isicelo kwi-wp-login.php Ngokuhlengahlengisa imigaqo yokuphinda ubhale izicelo ngumncedisi, unokulungelelanisa ukusabela komncedisi kwizicelo ezinjalo kwaye uzithumele kwiphepha lokuqala.

Ke, iingxelo ezimbalwa ezilula ezisekwe kwifayile yelog yeseva zinika umfanekiso opheleleyo wento eyenzekayo kwisiza.

Indlela yokufumana ulwazi?

I-database ye-sqlite yanele. Masenze iitafile: ezincedisayo ekugawulweni kweenkqubo ze-ETL.

Inqanaba letheyibhile apho siya kubhala iifayile zelog sisebenzisa i-PHP. Iitafile ezimbini ezihlanganisiweyo. Masenze itheyibhile yemihla ngemihla enezibalo kwiiarhente zabasebenzisi kunye neemeko zokucela. Ngeyure ngezibalo zezicelo, amaqela esimo kunye neearhente. Iitheyibhile ezine zemilinganiselo efanelekileyo.

Isiphumo yile modeli yobudlelwane ilandelayo:

Imodeli yedatha

Okushicilelweyo ukwenza into kwisiseko sedata sesqlite:

Ukudala into 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);

Iqonga

Kwimeko yefayile ye-access.log, kuyimfuneko ukufunda, ukucazulula nokubhala zonke izicelo kwiziko ledatha. Oku kunokwenziwa ngokuthe ngqo usebenzisa ulwimi lokubhala okanye usebenzisa izixhobo ze-sqlite.

Ifomati yefayile yelog:

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

Ukusasazwa okuphambili

Xa idatha ekrwada ikwisiseko sedatha, kufuneka ubhale izitshixo ezingekhoyo kwiitafile zokulinganisa. Emva koko kuya kwenzeka ukuba kwakhiwe isalathiso kwimilinganiselo. Umzekelo, kwitheyibhile ye-DIM_REFERRER, isitshixo yindibaniselwano yemimandla emithathu.

Umbuzo ongundoqo we-SQL wokusasaza

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

Usasazo kwitheyibhile yearhente yomsebenzisi inokuqulatha i-bot logic, umzekelo i-snippet yesql:

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

Iitafile ezidityanisiweyo

Okokugqibela, siya kulayisha iitafile ezidityanisiweyo; umzekelo, itafile yemihla ngemihla inokulayishwa ngolu hlobo lulandelayo:

Umbuzo weSQL wokulayishwa kweaggregate

/* 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 ukuba ubhale imibuzo enzima. KUNYE iqulethe ukulungiswa kwedatha kunye nezitshixo. Umbuzo oyintloko uqokelela zonke iireferensi zemilinganiselo.

Umqathango awuzukuvumela ukulayisha imbali kwakhona: CAST(STG.EVENT_DT AS INTEGER) > $param_epoch_from, apho iparameter sisiphumo sesicelo.
‘KHETHA i-COALESCE(MAX(EVENT_DT), ‘3600’) NJENGOKO LAST_EVENT_EPOCH UKUSUKA FCT_ACCESS_USER_AGENT_DD’

Umqathango uya kulayishwa kuphela imini epheleleyo: CAST(STG.EVENT_DT AS INTEGER) < strftime(‘%s’, umhla(‘ngoku’, ‘ukuqala kosuku’))

Ukubala amaphepha okanye iifayile zenziwa ngendlela yakudala, ngokukhangela inqaku.

Iingxelo

Kwiinkqubo eziyinkimbinkimbi zokujonga, kunokwenzeka ukwenza imodeli ye-meta esekelwe kwizinto zesiseko sedatha, ukulawula ngokukhawuleza izihlungi kunye nemithetho yokudibanisa. Ekugqibeleni, zonke izixhobo ezifanelekileyo zivelisa umbuzo we-SQL.

Kulo mzekelo, siya kudala imibuzo ye-SQL esele yenziwe kwaye siyigcine njengeembono kwi-database - ezi ziingxelo.

Ukubonakala

Bluff: Iigrafu ezintle kwiJavaScript zisetyenziswe njengesixhobo sokubonwayo

Ukwenza oku, kwakuyimfuneko ukuhamba kuzo zonke iingxelo usebenzisa i-PHP kwaye uvelise ifayile ye-html kunye neetafile.

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

Isixhobo sibonisa ngokulula iitheyibhile zeziphumo.

isiphelo

Ukusebenzisa uhlalutyo lwewebhu njengomzekelo, inqaku lichaza iindlela eziyimfuneko zokwakha iindawo zokugcina idatha. Njengoko kunokubonwa kwiziphumo, izixhobo ezilula zanele ukuhlalutya okunzulu kunye nokubonwa kwedatha.

Kwixesha elizayo, sisebenzisa le ndawo yokugcina njengomzekelo, siya kuzama ukuphumeza izakhiwo ezinjengokutshintsha kancinane imilinganiselo, imethadatha, amanqanaba okudibanisa kunye nokudityaniswa kwedatha evela kwimithombo eyahlukeneyo.

Kwakhona, makhe sijonge ngakumbi kwisixhobo esilula sokulawula iinkqubo ze-ETL ezisekelwe kwitafile enye.

Masibuyele kwisihloko sokulinganisa umgangatho wedatha kunye nokwenza le nkqubo ngokuzenzekelayo.

Siza kufunda iingxaki zendawo yobugcisa kunye nokugcinwa kokugcinwa kwedatha, apho siya kuphumeza iseva yokugcina kunye nezixhobo ezincinci, umzekelo, ngokusekelwe kwi-Raspberry Pi.

umthombo: www.habr.com