Ndekọ saịtị na obere nchekwa nke gị

Webalizer na Google Analytics enyerela m aka ịghọta ihe na-eme na weebụsaịtị ruo ọtụtụ afọ. Ugbu a aghọtara m na ha na-enye ozi bara uru dị ntakịrị. N'inweta faịlụ access.log gị, ọ dị mfe ịghọta ọnụ ọgụgụ na iji mejuputa ngwaọrụ ndị bụ isi, dị ka sqlite, HTML, asụsụ sql na asụsụ mmemme ọ bụla.

Ebe data maka Webalizer bụ faịlụ access.log nke nkesa. Nke a bụ ihe ogwe ya na ọnụ ọgụgụ ya dị, nke naanị mkpokọta okporo ụzọ doro anya:

Ngwa dị ka Google Analytics na-anakọta data site na ibe eburu n'obi n'onwe ha. Ha na-egosi anyị ihe osise di na nwunye na ahịrị, dabere na nke ọ na-esikarị ike ịbịaru nkwubi okwu ziri ezi. Ma eleghị anya, ekwesịrị ime mgbalị ọzọ? Amaghị.

Yabụ, kedu ihe m chọrọ ịhụ na ọnụ ọgụgụ ndị ọbịa webụsaịtị?

Onye ọrụ na okporo ụzọ bot

Ọtụtụ mgbe okporo ụzọ saịtị na-ejedebe na ọ dị mkpa ịhụ otú okporo ụzọ bara uru na-eji. Dịka ọmụmaatụ, dịka nke a:

Ajụjụ mkpesa 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

Eserese ahụ na-egosi ọrụ bot mgbe niile. Ọ ga-adọrọ mmasị ịmụ n'ụzọ zuru ezu ndị nnọchiteanya kacha arụ ọrụ.

bots na-akpasu iwe

Anyị na-ekewa bots dabere na ozi onye ọrụ. Ọnụ ọgụgụ ndị ọzọ na okporo ụzọ kwa ụbọchị, ọnụ ọgụgụ nke arịrịọ na-aga nke ọma na nke na-enweghị ihe ịga nke ọma na-enye echiche dị mma nke ọrụ bot.

Ajụjụ mkpesa 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

N'okwu a, nsonaazụ nyocha ahụ bụ mkpebi igbochi ịbanye na saịtị ahụ site n'ịgbakwunye ya na faịlụ robots.txt.

User-agent: AhrefsBot
Disallow: /
User-agent: dotbot
Disallow: /
User-agent: bingbot
Crawl-delay: 5
Bots abụọ mbụ kwụsịrị na tebụl, na robots MS si na ahịrị mbụ gbadata.

Ụbọchị na oge ọrụ kachasị

Upswings na-ahụ anya na okporo ụzọ. Iji mụọ ha n'ụzọ zuru ezu, ọ dị mkpa ịkọwapụta oge ihe omume ha, ọ dịghịkwa mkpa igosipụta awa niile na ụbọchị nke nha oge. Nke a ga-eme ka ọ dịrị mfe ịchọta arịrịọ onye ọ bụla na faịlụ ndekọ ma ọ bụrụ na achọrọ nyocha zuru ezu.

Ajụjụ mkpesa 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

Anyị na-ahụ oge kacha arụ ọrụ 11, 14 na 20 nke ụbọchị mbụ na eserese ahụ. Ma n'echi ya na 13:XNUMX bots na-arụ ọrụ.

Nkezi ọrụ onye ọrụ kwa ụbọchị n'izu

Anyị ji ọrụ na okporo ụzọ dozie ihe. Ajụjụ na-esote bụ ọrụ nke ndị ọrụ n'onwe ha. Maka ọnụ ọgụgụ ndị dị otú ahụ, ogologo oge nchịkọta, dị ka otu izu, bụ ihe na-achọsi ike.

Ajụjụ mkpesa 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

Ọnụ ọgụgụ kwa izu na-egosi na na nkezi, otu onye ọrụ na-emepe ibe 1,6 kwa ụbọchị. Ọnụọgụ faịlụ achọrọ maka onye ọrụ na nke a dabere na mgbakwunye nke faịlụ ọhụrụ na saịtị ahụ.

Arịrịọ niile na ọkwa ha

Webalizer na-egosi mgbe niile koodu ibe akwụkwọ yana achọrọ m ịhụ naanị ọnụọgụ arịrịọ na mperi na-aga nke ọma.

Ajụjụ mkpesa 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

Akụkọ a gosipụtara arịrịọ, ọ bụghị ịpị (hits), n'adịghị ka LINE_CNT, a na-agbakọ metric REQUEST_CNT dị ka COUNT(DISTINCT STG.REQUEST_NK). Ebumnuche bụ igosi mmemme dị irè, dịka ọmụmaatụ, MS bots poll na robots.txt faịlụ ọtụtụ narị ugboro kwa ụbọchị na, na nke a, a ga-agụta ntuli aka dị otú ahụ otu ugboro. Nke a na-enye gị ohere ịmegharị jumps na eserese.

Site na eserese ị nwere ike ịhụ ọtụtụ mperi - ndị a bụ ibe adịghị adị. Nsonaazụ nke nyocha ahụ bụ mgbakwunye nke redirects sitere na ibe ndị dịpụrụ adịpụ.

Arịrịọ ọjọọ

Iji nyochaa arịrịọ n'ụzọ zuru ezu, ị nwere ike igosipụta ọnụ ọgụgụ zuru ezu.

Ajụjụ mkpesa 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

Ndepụta a ga-enwekwa oku niile, dịka ọmụmaatụ, arịrịọ maka /wp-login.php Site n'ịgbanwe iwu maka idegharị arịrịọ site na ihe nkesa, ị nwere ike ịhazigharị mmeghachi omume nke ihe nkesa na arịrịọ ndị dị otú ahụ wee ziga ha na ibe mmalite.

Yabụ, akụkọ ole na ole dị mfe dabere na faịlụ ndekọ ihe nkesa na-enye nkọwa zuru oke nke ihe na-eme na saịtị ahụ.

Kedu ka esi enweta ozi?

Ebe nchekwa data sqlite ezuola. Ka anyị mepụta tebụl: inyeaka maka ịbanye usoro ETL.

Okpokoro okpokoro ebe anyị ga-eji PHP dee faịlụ ndekọ. Tebụl mkpokọta abụọ. Ka anyị mepụta tebụl kwa ụbọchị nwere ọnụ ọgụgụ na ndị ọrụ ma rịọ ọkwa. Kwa elekere nwere ọnụ ọgụgụ na arịrịọ, ọkwa otu na ndị nnọchite anya. Tebụl anọ nke nha dị mkpa.

Nsonaazụ bụ ụdị njikọ a:

Ụdị data

Ederede iji mepụta ihe na nchekwa data sqlite:

Ihe okike 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);

Ogbo

N'ihe banyere faịlụ access.log, ọ dị mkpa ịgụ, kọọ ma dee arịrịọ niile na nchekwa data. Enwere ike ime nke a ozugbo site na iji asụsụ ederede ma ọ bụ jiri ngwaọrụ sqlite.

Ụdị faịlụ ndekọ:

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

Mgbasa isi

Mgbe data raw dị na nchekwa data, ịkwesịrị ide igodo ndị na-anọghị na tebụl nha. Mgbe ahụ, ọ ga-ekwe omume ịmepụta ntụaka maka nha. Dịka ọmụmaatụ, na tebụl DIM_REFERRER, igodo bụ ngwakọta nke ubi atọ.

Ajụjụ mgbasa ozi igodo 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

Mgbasa na tebụl onye ọrụ nwere ike ịnwe mgbagha bot, dịka ọmụmaatụ 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

Mkpokọta tebụl

N'ikpeazụ, anyị ga-ebu tebụl nchịkọta; dịka ọmụmaatụ, enwere ike ibunye tebụl kwa ụbọchị dị ka ndị a:

Ajụjụ SQL maka nbudata mkpokọta

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

Ebe nchekwa data sqlite na-enye gị ohere ide ajụjụ dị mgbagwoju anya. WITH nwere nkwadebe nke data na igodo. Ajụjụ bụ isi na-anakọta ntụaka niile na akụkụ.

Ọnọdụ ahụ agaghị ekwe ka ibunye akụkọ ihe mere eme ọzọ: CAST(STG.EVENT_DT AS INTEGER)> $param_epoch_from, ebe oke bụ nsonaazụ nke arịrịọ a.
'Họrọ COALESCE(MAX(EVENT_DT),'3600') KA LAST_EVENT_EPOCH SITE FCT_ACCESS_USER_AGENT_DD'

Ọnọdụ a ga-ebu naanị ụbọchị zuru ezu: CAST(STG.EVENT_DT AS INTEGER) <strftime('%s', date('ugbu a','mmalite ụbọchị')))

A na-eme agụta ibe ma ọ bụ faịlụ n'ụzọ ochie, site n'ịchọ isi ihe.

Akụkọ

N'ime usoro nhụta anya dị mgbagwoju anya, ọ ga-ekwe omume ịmepụta meta-model dabere na ihe nchekwa data, jikwaa nzacha na iwu nchịkọta. N'ikpeazụ, ngwaọrụ niile dị mma na-emepụta ajụjụ SQL.

N'ihe atụ a, anyị ga-emepụta ajụjụ SQL emebere ma chekwaa ha dị ka echiche na nchekwa data - ndị a bụ akụkọ.

Anya

Bluff: A na-eji eserese ndị mara mma na Javascript dị ka ngwa nhụta anya

Iji mee nke a, ọ dị mkpa ịgafe akụkọ niile site na iji PHP wee mepụta faịlụ html na tebụl.

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

Ngwa ahụ na-egosipụta naanị tebụl nsonaazụ.

nkwubi

N'iji nyocha weebụ dịka ọmụmaatụ, isiokwu ahụ na-akọwa usoro dị mkpa iji wuo ụlọ nkwakọba ihe data. Dị ka a na-ahụ site na nsonaazụ ya, ngwá ọrụ kachasị mfe zuru ezu maka nyocha miri emi na ịhụ anya nke data.

N'ọdịnihu, na-eji ebe nchekwa a dị ka ihe atụ, anyị ga-agbalị ime ihe ndị dị otú ahụ dị ka nwayọọ nwayọọ na-agbanwe akụkụ, metadata, ọkwa nchịkọta na ntinye nke data sitere na isi mmalite dị iche iche.

Ọzọkwa, ka anyị lebakwuo anya na ngwá ọrụ kachasị mfe maka ijikwa usoro ETL dabere na otu tebụl.

Ka anyị laghachi na isiokwu nke ịlele ogo data na imezi usoro a.

Anyị ga-amụ nsogbu nke gburugburu teknụzụ na nhazi nke nchekwa data, nke anyị ga-emejuputa ihe nkesa nchekwa na obere ihe onwunwe, dịka ọmụmaatụ, dabere na Raspberry Pi.

isi: www.habr.com