Tirakoobka goobta iyo kaydintaada yar ee gaarka ah

Webalizer iyo Google Analytics ayaa iga caawiyay inaan fahmo waxa ka dhacaya mareegaha sanado badan. Hadda waxaan fahamsanahay inay bixiyaan macluumaad aad u yar oo faa'iido leh. Helitaanka galkaaga access.log, aad bay u fududahay in la fahmo tirakoobka iyo in la hirgeliyo aaladaha aasaasiga ah, sida sqlite, html, luqadda sql iyo luuqad kasta oo qoraal ah.

Isha xogta ee Webalizer waa faylka access.log ee server-ka. Tani waa waxa ay baararka iyo tirooyinka ay u eg yihiin, kuwaas oo kaliya wadarta guud ee taraafikada ay caddahay:

Qalabka sida Google Analytics waxay ururiyaan xogta bogga la raray laftooda. Waxay ina tusinayaan jaantusyo iyo xariiqyo kala duwan, kuwaas oo ku salaysan inta badan ay adagtahay in la sameeyo gabagabo sax ah. Ma laga yaabaa in dadaal dheeraad ah la sameeyo? Ma garanayo.

Haddaba, maxaan rabay inaan ku arko tirakoobka booqdayaasha mareegta?

Isticmaalaha iyo taraafikada bot

Inta badan gaadiidka goobta waa xaddidan yahay waxaana lagama maarmaan ah in la arko inta faa'iido leh ee taraafikada loo isticmaalo. Tusaale ahaan, sida tan:

Su'aasha warbixinta 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

Garaafku wuxuu muujinayaa dhaqdhaqaaqa joogtada ah ee bots Waxay noqon lahayd mid xiiso leh in si faahfaahsan loo barto wakiilada ugu firfircoon.

Bots dhibsado

Waxaan u kala saareynaa bots ku saleysan macluumaadka wakiilka isticmaalaha. Tirokoobyo dheeri ah oo ku saabsan taraafikada maalinlaha ah, tirada codsiyada lagu guuleystay iyo kuwa aan lagu guulaysan waxay siinayaan fikrad wanaagsan oo ah dhaqdhaqaaqa bot.

Su'aasha warbixinta 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

Xaaladdan oo kale, natiijada falanqaynta ayaa ahayd go'aanka lagu xaddiday gelitaanka goobta iyadoo lagu darayo faylka robots.txt

User-agent: AhrefsBot
Disallow: /
User-agent: dotbot
Disallow: /
User-agent: bingbot
Crawl-delay: 5
Labadii bots ee hore ayaa miiska ka lumay, robots-yadii MS-na waxay ka soo degeen safkii hore.

Maalinta iyo wakhtiga waxqabadka ugu weyn

Upswings ayaa ka dhex muuqda taraafikada Si aad si faahfaahsan u barato iyaga, waxaa lagama maarmaan ah in la muujiyo wakhtiga ay dhacayaan, mana aha lagama maarmaan in la muujiyo dhammaan saacadaha iyo maalmaha qiyaasta wakhtiga. Tani waxay sahlaysaa in la helo su'aalo gaar ah oo ku jira faylka log haddii loo baahdo falanqayn faahfaahsan.

Su'aasha warbixinta 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

Waxaan ilaalinaa saacadaha ugu firfircoon 11, 14 iyo 20 ee maalinta ugu horeysa shaxda. Laakin maalintii xigtay saacadu markay ahayd 13:XNUMX bots-ku way firfircoonaayeen.

Celceliska dhaqdhaqaaqa maalinlaha ah ee isticmaalaha toddobaad kasta

Waxoogaa waxaanu ku xalinay dhaqdhaqaaq iyo socod. Su'aasha xigta waxay ahayd waxqabadka isticmaalayaasha laftooda. Tirakoobyada noocaas ah, muddooyinka isku-darka dheer, sida toddobaadka, ayaa la jecel yahay.

Su'aasha warbixinta 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

Tirakoobka toddobaadlaha ah ayaa muujinaya in celcelis ahaan hal isticmaale uu furo 1,6 bog maalintii. Tirada faylasha la codsaday isticmaale kasta kiiskan wuxuu ku xiran yahay ku darida faylasha cusub ee goobta.

Dhammaan codsiyada iyo xaaladahooda

Webalizer had iyo jeer wuxuu muujiyaa koodka bogag gaar ah waxaana mar walba doonayay inaan arko kaliya tirada codsiyada iyo khaladaadka guulaystay.

Su'aasha warbixinta 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

Warbixintu waxay soo bandhigaysaa codsiyada, ma aha dhagsi (kudhisyo), oo ka duwan LINE_CNT, mitirka REQUEST_CNT waxaa loo xisaabiyaa sida COUNT(DISTINCT STG.REQUEST_NK). Hadafka ayaa ah in la muujiyo dhacdooyin wax ku ool ah, tusaale ahaan, MS bots poll the robots.txt faylka boqollaal jeer maalintii iyo, kiiskan, codbixintan oo kale waxaa la tirin doonaa hal mar. Tani waxay kuu ogolaaneysaa inaad sifududeyso boodada garaafka.

Garaafyada waxaad ka arki kartaa khaladaad badan - kuwani waa bogag aan jirin. Natiijadii falanqayntu waxay ahayd ku-darka dib-u-hagidda bogag fogfog.

Codsiyada xun

Si aad u baarto codsiyada si faahfaahsan, waxaad soo bandhigi kartaa tirakoob faahfaahsan.

Su'aasha warbixinta 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

Liiskani wuxuu sidoo kale ka koobnaan doonaa dhammaan wicitaanada, tusaale ahaan, codsi ku socda /wp-login.php Adigoo hagaajinaya sharciyada dib u qorista codsiyada server-ka, waxaad hagaajin kartaa falcelinta server-ka ee codsiyadaas oo aad u dirto bogga bilowga.

Markaa, warbixinno yar oo fudud oo ku salaysan faylka log server-ka ayaa siinaya sawir dhammaystiran oo ku saabsan waxa ka dhacaya goobta.

Sidee lagu helaa macluumaadka?

Xogta sqlite ayaa ku filan. Aan abuurno jadwalyo: caawiyayaasha gelitaanka hababka ETL.

Marxaladda miiska halkaas oo aan ku qori doono galalka log anagoo adeegsanayna PHP. Laba miis wadar ahaan. Aan abuurno miis maalinle ah oo leh tirokoob ku saabsan wakiilada isticmaalaha oo aan codsano xaaladaha. Saacadle leh tirakoobyada codsiyada, kooxaha heerka iyo wakiilada. Afar miis oo cabbirro khuseeya.

Natiijadu waa qaabkan xidhiidhka ah:

Qaabka xogta

Qoraal si loo abuuro shay ku jira kaydka xogta sqlite:

Abuuritaanka shayga 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);

Marxalad

Xaaladda galitaanka access.log, waa lagama maarmaan in la akhriyo, lafo-gureeyo oo la qoro dhammaan codsiyada database-ka. Tan waxaa lagu samayn karaa si toos ah iyadoo la isticmaalayo luqadda qoraalka ama iyadoo la isticmaalayo qalabka sqlite.

Gal qaabka faylka:

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

Faafinta muhiimka ah

Marka xogta cayriin ay ku jirto keydka xogta, waxaad u baahan tahay inaad ku qorto furayaasha aan meesha ku jirin miisaska cabbirka. Kadibna waxaa suurtogal ah in la dhiso tixraaca cabbirada. Tusaale ahaan, miiska DIM_REFERRER, furuhu waa isku darka saddex goobood.

Su'aasha fidinta muhiimka ah ee 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

Faafinta miiska wakiilka adeegsadaha waxa ku jiri kara macquulka bot, tusaale snippet 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

Miisaska isku-darka ah

Ugu dambeyntii, waxaan ku shubi doonaa miisaska wadar ahaan; tusaale ahaan, miiska maalinlaha ah waxaa lagu dhejin karaa sida soo socota:

Weydiinta SQL ee isku darka rarista

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

Xogta sqlite waxay kuu ogolaanaysaa inaad qorto su'aalo adag. WITH ka kooban diyaarinta xogta iyo furayaasha. Weydiinta ugu muhiimsan waxay ururisaa dhammaan tixraacyada cabbirrada.

Xaaladdu ma oggolaan doonto in dib loo soo raro taariikhda: CAST(STG.EVENT_DT AS INTEGER)> $param_epoch_from, halkaasoo cabbirku yahay natiijada codsiga
'Dooro COALESCE(MAX(EVENT_DT), '3600') SIDA LAST_EVENT_EPOCH KA FCT_ACCESS_USER_AGENT_DD'

Xaaladdu waxay soo shubi doontaa oo keliya maalinta buuxda: CAST(STG.EVENT_DT AS INTEGER) <strftime('%s', date('hadda',' bilowga maalinta'))

Tirinta boggaga ama faylasha waxa loo fuliyaa hab hore, iyada oo la raadinayo dhibic.

Warbixinada

Nidaamyada aragga ee adag, waxaa suurtagal ah in la abuuro qaab-meta-ku-saleysan walxaha xogta, si firfircoon u maamula filtarrada iyo xeerarka isku-darka. Ugu dambeyntiina, dhammaan qalabyada wanaagsan waxay abuuraan weydiinta SQL.

Tusaalahan, waxaanu ku abuuri doonaa su'aalo SQL oo diyaarsan waxaanan u kaydin doonaa aragti ahaan kaydka xogta - kuwani waa warbixino.

Muujinta

Bluff: Garaafyada quruxda badan ee JavaScript waxaa loo isticmaalay qalab muuqaal ahaan

Si tan loo sameeyo, waxay ahayd lagama maarmaan in la maro dhammaan warbixinnada iyadoo la adeegsanayo PHP oo la soo saaro faylka html ee miisaska leh.

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

Qalabku wuxuu si fudud u sawiraa jaantusyada natiijooyinka.

gunaanad

Isticmaalka falanqaynta shabakadda tusaale ahaan, maqaalku wuxuu qeexayaa hababka lagama maarmaanka u ah in la dhiso bakhaarada xogta. Sida laga arki karo natiijooyinka, qalabka ugu fudud ayaa ku filan falanqaynta qoto dheer iyo aragtida xogta.

Mustaqbalka, anagoo adeegsanayna kaydkan tusaale ahaan, waxaan isku dayi doonaa inaan hirgalino qaab dhismeedka sida si tartiib tartiib ah u bedelaya cabbirada, xogaha badan, heerarka isku dhafka iyo isku dhafka xogta ilo kala duwan.

Sidoo kale, aan si dhow u eegno qalabka ugu fudud ee lagu maareeyo hababka ETL oo ku salaysan hal miis.

Aan u soo laabano mowduuca cabbiraadda tayada xogta iyo hab-socodka otomatiga ah.

Waxaan baran doonaa dhibaatooyinka deegaanka farsamada iyo dayactirka kaydinta xogta, taas oo aan hirgelin doono server kaydinta khayraadka ugu yar, tusaale ahaan, ku salaysan Raspberry Pi.

Source: www.habr.com