Lipalopalo tsa sebaka le polokelo ea hau e nyane

Webalizer le Google Analytics li nthusitse ho utloisisa se etsahalang liwebosaeteng ka lilemo tse ngata. Hona joale kea utloisisa hore li fana ka boitsebiso bo fokolang haholo ba bohlokoa. Ho fihlella faele ea hau ea access.log, ho bonolo haholo ho utloisisa lipalo-palo le ho kenya ts'ebetsong lisebelisoa tsa mantlha, joalo ka sqlite, html, puo ea sql le puo efe kapa efe ea scripting programming.

Mohloli oa data oa Webalizer ke faele ea phihlello.log ea seva. Sena ke tsela eo mekoallo le lipalo tsa eona li shebahalang ka eona, moo palo ea sephethephethe e hlakileng feela:

Lisebelisoa tse kang Google Analytics li bokella lintlha ho tsoa leqepheng le kentsoeng ka botsona. Ba re bontša litšoantšo le mela e 'maloa, e thehiloeng ho eona hangata ho leng thata ho etsa liqeto tse nepahetseng. Mohlomong boiteko bo eketsehileng bo ka be bo entsoe? Ha ke tsebe.

Joale, ke ne ke batla ho bona eng lipalo-palo tsa baeti ba sebaka sa marang-rang?

Sephethephethe sa basebelisi le bot

Hangata sephethephethe sa libaka se na le moeli 'me hoa hlokahala ho bona hore na ho sebelisoa sephethephethe se molemo hakae. Ka mohlala, joalo ka:

Potso ea tlaleho ea 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

graph e bonts'a ts'ebetso e sa feleng ea bots. Ho ka ba monate ho ithuta ka ho qaqileng baemeli ba mafolofolo ka ho fetisisa.

Li-bots tse khopisang

Re arola li-bots ho latela lintlha tsa moemeli oa mosebelisi. Lipalopalo tse ling mabapi le sephethephethe sa letsatsi le letsatsi, palo ea likopo tse atlehileng le tse sa atleheng li fana ka mohopolo o motle oa ts'ebetso ea bot.

Potso ea tlaleho ea 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

Tabeng ena, sephetho sa tlhahlobo e bile qeto ea ho thibela phihlello ea sebaka sa marang-rang ka ho se kenyelletsa faeleng ea robots.txt.

User-agent: AhrefsBot
Disallow: /
User-agent: dotbot
Disallow: /
User-agent: bingbot
Crawl-delay: 5
Li-bots tse peli tsa pele li ile tsa nyamela tafoleng, 'me liroboto tsa MS tsa theoha ho tloha meleng ea pele.

Letsatsi le nako ea ts'ebetso e kholo ka ho fetisisa

Li-upwings li bonahala sephethephetheng. Ho li ithuta ka botlalo, hoa hlokahala ho totobatsa nako ea ho hlaha ha tsona, 'me ha ho hlokahale ho bonts'a lihora tsohle le matsatsi a tekanyo ea nako. Sena se tla etsa hore ho be bonolo ho fumana likopo tsa motho ka mong faeleng ea log haeba ho hlokahala tlhahlobo e qaqileng.

Potso ea tlaleho ea 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

Re boloka lihora tse sebetsang ka ho fetisisa 11, 14 le 20 tsa letsatsi la pele chate. Empa letsatsing le hlahlamang ka 13:XNUMX bots e ne e sebetsa.

Kakaretso ea ts'ebetso ea letsatsi le letsatsi ea basebelisi ka beke

Re ile ra hlophisa lintho hanyane ka ts'ebetso le sephethephethe. Potso e latelang e ne e le mosebetsi oa basebelisi ka bobona. Bakeng sa lipalo-palo tse joalo, linako tse telele tsa ho bokella, tse kang beke, lia lakatseha.

Potso ea tlaleho ea 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

Lipalopalo tsa beke le beke li bontša hore ka karolelano mosebelisi a le mong o bula maqephe a 1,6 ka letsatsi. Palo ea lifaele tse kopiloeng ka mosebelisi ntlheng ena e ipapisitse le tlatsetso ea lifaele tse ncha sebakeng sa marang-rang.

Likopo tsohle le maemo a tsona

Webalizer e ne e lula e bonts'a likhoutu tsa maqephe a ikhethileng 'me ke ne ke lula ke batla ho bona palo ea likopo tse atlehileng le liphoso.

Potso ea tlaleho ea 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

Tlaleho e hlahisa likopo, eseng ho penya (tse otlang), ho fapana le LINE_CNT, metric ea REQUEST_CNT e baloa joalo ka COUNT(DISTINCT STG.REQUEST_NK). Sepheo ke ho bontša liketsahalo tse atlehang, mohlala, MS bots poll robots.txt faele ka makhetlo a makholo ka letsatsi 'me, tabeng ena, likhetho tse joalo li tla baloa hanngoe. Sena se o nolofalletsa ho theola likheo ho graph.

Ho tsoa ho kerafo u ka bona liphoso tse ngata - ana ke maqephe a seng teng. Sephetho sa tlhahlobo e bile ho eketsoa ha litokiso ho tsoa maqepheng a hole.

Likopo tse mpe

Ho hlahloba likopo ka botlalo, o ka bonts'a lipalo-palo tse felletseng.

Potso ea tlaleho ea 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

Lethathamo lena le tla boela le be le li-call tsohle, ka mohlala, kopo ea ho /wp-login.php Ka ho fetola melao ea ho ngola likōpo hape ke seva, u ka fetola tsela eo seva se arabelang ka eona ho likopo tse joalo ebe u li romela leqepheng la ho qala.

Kahoo, litlaleho tse 'maloa tse bonolo tse thehiloeng ho faele ea log ea seva li fana ka setšoantšo se feletseng sa se etsahalang sebakeng sa marang-rang.

Mokhoa oa ho fumana litaba?

Database ea sqlite e lekane. Ha re theheng litafole: tse thusang bakeng sa lits'ebetso tsa ETL tsa ho rema lifate.

Sethala sa tafole moo re tla ngola lifaele tsa log re sebelisa PHP. Litafole tse peli tse kopaneng. Ha re theheng tafole ea letsatsi le letsatsi e nang le lipalo-palo tsa liakhente tsa basebelisi le ho kopa maemo. Hora le lipalo-palo tsa likopo, lihlopha tsa maemo le baemeli. Litafole tse 'nè tsa litekanyo tse amehang.

Sephetho ke mohlala o latelang oa likamano:

Mohlala oa data

Script ho theha ntho sebakeng sa polokelo ea sebaka sa sqlite:

Tlhahiso ea ntho ea 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);

Sethala

Tabeng ea faele ea access.log, hoa hlokahala ho bala, ho hlalosa le ho ngola likōpo tsohle ho database. Sena se ka etsoa ka kotloloho ho sebelisa puo ea mongolo kapa ho sebelisa lisebelisoa tsa sqlite.

Sebopeho sa faele ea log:

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

Phatlalatso ea bohlokoa

Ha data e tala e le database, o hloka ho ngola linotlolo tse sieo litafoleng tsa litekanyo. Joale ho tla khoneha ho haha ​​​​tšupiso ea litekanyo. Mohlala, tafoleng ea DIM_REFERRER, senotlolo ke motsoako oa masimo a mararo.

SQL key propagation potso

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

Phatlalatso ho tafole ea moemeli oa mosebelisi e kanna ea ba le bot logic, mohlala sql snippet:

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

Litafole tse kopaneng

Qetellong, re tla kenya litafole tse kopaneng; mohlala, tafole ea letsatsi le letsatsi e ka beoa ka tsela e latelang:

Potso ea SQL bakeng sa ho kenya kakaretso

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

Database ea sqlite e u lumella ho ngola lipotso tse rarahaneng. WITH e na le ho lokisoa ha data le linotlolo. Potso ea mantlha e bokella litšupiso tsohle tsa litekanyo.

Boemo bo ke ke ba lumella ho kenya nalane hape: CAST(STG.EVENT_DT AS INTEGER) > $param_epoch_from, moo paramethara e leng sephetho sa kopo.
'KHETHA COALESCE(MAX(EVENT_DT),'3600') JOALOKA LAST_EVENT_EPOCH HO FROM FCT_ACCESS_USER_AGENT_DD'

Boemo bo tla bula feela letsatsi lohle: CAST(STG.EVENT_DT AS INTEGER) < strftime('%s', date('now', 'start of day'))

Ho bala maqephe kapa lifaele ho etsoa ka mokhoa oa khale, ka ho batla ntlha.

Litlaleho

Lits'ebetsong tse rarahaneng tsa pono, hoa khoneha ho theha meta-model e thehiloeng linthong tsa database, ho laola ka matla li-filters le melao ea ho kopanya. Qetellong, lisebelisoa tsohle tse ntle li hlahisa potso ea SQL.

Mohlaleng ona, re tla theha lipotso tsa SQL tse seng li entsoe ebe re li boloka e le maikutlo sebakeng sa polokelo ea litaba - tsena ke litlaleho.

Ponahalo

Bluff: Li-graph tse ntle ho JavaScript li sebelisitsoe e le sesebelisoa sa pono

Ho etsa sena, ho ne ho hlokahala ho feta litlalehong tsohle ho sebelisa PHP le ho hlahisa faele ea html e nang le litafole.

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

Sesebelisoa se bona feela litafole tsa liphetho.

fihlela qeto e

Ho sebelisa tlhahlobo ea marang-rang e le mohlala, sengoloa se hlalosa mekhoa e hlokahalang ho aha matlo a polokelo ea data. Joalokaha ho ka bonoa liphethong, lisebelisoa tse bonolo ka ho fetisisa li lekane bakeng sa tlhahlobo e tebileng le pono ea data.

Nakong e tlang, re sebelisa sebaka sena sa polokelo e le mohlala, re tla leka ho kenya ts'ebetsong mekhoa e kang litekanyo tse fetohang butle, metadata, maemo a ho bokellana le ho kopanya lintlha tse tsoang mehloling e fapaneng.

Hape, a re hlahlobisiseng sesebelisoa se bonolo ka ho fetisisa sa ho laola mekhoa ea ETL e thehiloeng tafoleng e le 'ngoe.

Ha re khutlele sehloohong sa ho lekanya boleng ba data le ho iketsetsa ts'ebetso ena.

Re tla ithuta mathata a tikoloho ea tekheniki le tlhokomelo ea polokelo ea data, eo re tla kenya ts'ebetsong seva sa polokelo se nang le lisebelisoa tse fokolang, mohlala, ho latela Raspberry Pi.

Source: www.habr.com