Gunearen estatistikak eta zure biltegiratze txikia

Webalizer-ek eta Google Analytics-ek urte askotan webguneetan gertatzen ari dena ezagutzen lagundu didate. Orain ulertzen dut oso informazio baliagarri gutxi ematen dutela. Zure access.log fitxategira sarbidea edukita, oso erraza da estatistikak ulertzea eta oinarrizko tresnak ezartzea, hala nola sqlite, html, sql lengoaia eta edozein scripting programazio lengoaia.

Webalizerren datu-iturria zerbitzariaren access.log fitxategia da. Hona hemen bere barrak eta zenbakiak nolakoak diren, eta bertatik trafiko-bolumen osoa bakarrik ikusten da:

Google Analytics bezalako tresnek kargatutako orrialdetik eurek biltzen dituzte datuak. Diagrama eta lerro pare bat erakusten dizkigute, eta horietan oinarrituta askotan zaila izaten da ondorio zuzenak ateratzea. Agian ahalegin gehiago egin beharko zen? Ez dakit.

Beraz, zer ikusi nahi nuen webguneko bisitarien estatistiketan?

Erabiltzaile eta bot trafikoa

Sarritan guneko trafikoa mugatua da eta ikusi behar da zenbat trafiko erabilgarria erabiltzen den. Adibidez, honela:

SQL txostenaren kontsulta

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

Grafikoak bot-en etengabeko jarduera erakusten du. Interesgarria litzateke ordezkari aktiboenak zehatz-mehatz aztertzea.

Bot gogaikarria

Botak sailkatzen ditugu erabiltzaile-agenteen informazioan oinarrituta. Eguneroko trafikoari buruzko estatistika gehigarriek, arrakastatsu eta arrakastarik gabeko eskaera kopuruak bot-aren jardueraren ideia ona ematen dute.

SQL txostenaren kontsulta

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

Kasu honetan, analisiaren emaitza gunerako sarbidea mugatzeko erabakia izan zen, robots.txt fitxategian gehituz.

User-agent: AhrefsBot
Disallow: /
User-agent: dotbot
Disallow: /
User-agent: bingbot
Crawl-delay: 5
Lehenengo bi robotak mahaitik desagertu ziren, eta MS robotak lehen lerroetatik behera mugitu ziren.

Jarduera handienaren eguna eta ordua

Igoerak ikusten dira trafikoan. Horiek zehatz-mehatz aztertzeko, haien agerraldiaren ordua nabarmendu behar da, eta ez da beharrezkoa denbora-neurketaren ordu eta egun guztiak bistaratzea. Horrela, errazagoa izango da erregistro-fitxategian eskaera indibidualak aurkitzea azterketa zehatza behar bada.

SQL txostenaren kontsulta

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

Lehenengo eguneko 11, 14 eta 20 ordu aktiboenak ikusten ditugu taulan. Baina hurrengo egunean 13:XNUMXetan bot-ak aktibo zeuden.

Erabiltzaileen eguneroko batez besteko jarduera asteka

Jarduerarekin eta trafikoarekin gauzak pixka bat konpondu genituen. Hurrengo galdera erabiltzaileen euren jarduera izan zen. Estatistika horietarako, agregazio-aldi luzeak komeni dira, aste bat adibidez.

SQL txostenaren kontsulta

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

Asteko estatistikek erakusten dute batez beste erabiltzaile batek egunean 1,6 orrialde irekitzen dituela. Kasu honetan erabiltzaile bakoitzeko eskatutako fitxategi kopurua gunera fitxategi berriak gehitzearen araberakoa da.

Eskaera guztiak eta haien egoerak

Webalizer-ek beti erakusten zituen orrialde-kode zehatzak eta beti ikusi nahi nuen arrakasta eta akatsen kopurua.

SQL txostenaren kontsulta

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

Txostenak eskaerak bistaratzen ditu, ez klikak (autsiak), LINE_CNT ez bezala, REQUEST_CNT metrikoa COUNT(DISTINCT STG.REQUEST_NK) gisa kalkulatzen da. Helburua gertaera eraginkorrak erakustea da, adibidez, MS bot-ek robots.txt fitxategia egunean ehunka aldiz galdetzen dute eta, kasu honetan, inkestak behin zenbatuko dira. Honek grafikoan jauziak leuntzeko aukera ematen du.

Grafikoan errore asko ikus ditzakezu - existitzen ez diren orrialdeak dira. Analisiaren emaitza urruneko orrialdeetatik birzuzenketak gehitzea izan zen.

Eskaera txarrak

Eskaerak zehatz-mehatz aztertzeko, estatistika zehatzak bistaratu ditzakezu.

SQL txostenaren kontsulta

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

Zerrenda honek dei guztiak ere edukiko ditu, adibidez, /wp-login.php-rako eskaera Zerbitzariaren eskaerak berridazteko arauak egokituz, zerbitzariaren erreakzioa egokitu dezakezu eskaera horiei eta hasierako orrialdera bidali.

Beraz, zerbitzariaren erregistro-fitxategian oinarritutako txosten sinple batzuek gunean gertatzen denaren irudi nahiko osoa ematen dute.

Nola lortu informazioa?

Sqlite datu-base bat nahikoa da. Sor ditzagun taulak: ETL prozesuak erregistratzeko laguntzailea.

Taula fasea non log fitxategiak idatziko ditugu PHP erabiliz. Bi taula agregatu. Sortu dezagun eguneroko taula bat erabiltzaile-agenteen eta eskaera-egoeren estatistikekin. Orduka, eskaeren, egoera taldeen eta eragileen estatistikekin. Neurri garrantzitsuen lau taula.

Emaitza erlazio-eredu hau da:

Datu-eredua

Sqlite datu-base batean objektu bat sortzeko script-a:

DDL objektuen sorrera

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

Etapa

Access.log fitxategiaren kasuan, beharrezkoa da eskaera guztiak datu-basean irakurri, analizatu eta idaztea. Hau zuzenean egin daiteke script-lengoaia erabiliz edo sqlite tresnak erabiliz.

Erregistro-fitxategiaren formatua:

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

Gakoen hedapena

Datu gordinak datu-basean daudenean, hor ez dauden gakoak idatzi behar dituzu neurketa-tauletan. Ondoren, neurrien erreferentzia bat eraikitzeko aukera izango da. Adibidez, DIM_REFERRER taulan, gakoa hiru eremuren konbinazioa da.

SQL gakoen hedapenaren kontsulta

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

Erabiltzaile-agenteen taulara hedatzeak bot logika izan dezake, adibidez sql zatia:

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

Agregatutako taulak

Azkenik, taula agregatuak kargatuko ditugu; adibidez, eguneko taula honela kargatu daiteke:

Agregatua kargatzeko SQL kontsulta

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

Sqlite datu-baseak kontsulta konplexuak idazteko aukera ematen du. WITH datuak eta gakoak prestatzea dauka. Kontsulta nagusiak dimentsioen erreferentzia guztiak biltzen ditu.

Baldintzak ez du historiala berriro kargatzen utziko: CAST(STG.EVENT_DT INTEGER AS) > $param_epoch_from, non parametroa eskaeraren emaitza den
'HAUSTU COALESCE(MAX(EVENT_DT), '3600') FCT_ACCESS_USER_AGENT_DD FROM_ACCESS_USER_AGENT_DD AS LAST_EVENT_EPOCH'

Baldintzak egun osoa bakarrik kargatuko du: CAST(STG.EVENT_DT INTEGER AS) < strftime('%s', date('orain', 'has the day'))

Orriak edo fitxategiak zenbatzea modu primitiboan egiten da, puntu bat bilatuz.

Txostenak

Bistaratze-sistema konplexuetan, datu-baseko objektuetan oinarritutako meta-eredu bat sortzea posible da, iragazkiak eta agregazio-arauak dinamikoki kudeatzea. Azken finean, tresna duin guztiek SQL kontsulta bat sortzen dute.

Adibide honetan, prest egindako SQL kontsultak sortuko ditugu eta datu-basean bista gisa gordeko ditugu; hauek txostenak dira.

Bistaratzea

Bluff: JavaScript-eko grafiko ederrak bistaratzeko tresna gisa erabili ziren

Horretarako, PHP erabiliz txosten guztiak pasatu eta taulekin html fitxategi bat sortzea beharrezkoa zen.

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

Tresnak emaitzen taulak bistaratzen ditu.

Irteera

Web analisia adibide gisa erabiliz, artikuluak datu biltegiak eraikitzeko beharrezkoak diren mekanismoak deskribatzen ditu. Emaitzetan ikus daitekeenez, tresna errazenak nahikoak dira datuen azterketa eta bistaratzeko sakona egiteko.

Etorkizunean, biltegi hau adibide gisa erabiliz, poliki-poliki aldatzen diren dimentsioak, metadatuak, agregazio-mailak eta iturri ezberdinetako datuen integrazioa bezalako egiturak ezartzen saiatuko gara.

Gainera, ikus ditzagun taula bakarrean oinarritutako ETL prozesuak kudeatzeko tresnarik errazena.

Itzul gaitezen datuen kalitatea neurtzearen eta prozesu hau automatizatzearen gaira.

Ingurune teknikoaren eta datuen biltegien mantentze-arazoak aztertuko ditugu, horretarako baliabide minimoekin biltegiratze zerbitzari bat ezarriko dugu, adibidez, Raspberry Pi batean oinarrituta.

Iturria: www.habr.com