Sitestatistiken en jo eigen lytse opslach

Webalizer en Google Analytics hawwe my in protte jierren holpen ynsjoch te krijen yn wat der bart op websiden. No begryp ik dat se heul min nuttige ynformaasje leverje. Mei tagong ta jo access.log-bestân, is it heul maklik om de statistiken te begripen en frij basale ark te ymplementearjen, lykas sqlite, html, de sql-taal en elke skriptprogrammearringstaal.

De gegevensboarne foar Webalizer is it access.log-bestân fan de tsjinner. Dit is hoe't de balken en nûmers derút sjogge, wêrfan allinich it totale folume fan ferkear dúdlik is:

Tools lykas Google Analytics sammelje gegevens fan 'e laden side sels. Se litte ús in pear diagrammen en linen sjen, basearre op wêrfan it faak lestich is om korrekte konklúzjes te lûken. Miskien hie der mear muoite dien wurde moatten? Wit net.

Dat, wat woe ik sjen yn 'e statistiken foar besikers fan' e webside?

Brûker en botferkear

Faak is sideferkear beheind en it is nedich om te sjen hoefolle nuttich ferkear wurdt brûkt. Bygelyks, lykas dit:

SQL rapport query

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

De grafyk toant de konstante aktiviteit fan bots. It soe nijsgjirrich wêze om de meast aktive fertsjintwurdigers yn detail te studearjen.

Ferfelende bots

Wy klassifisearje bots basearre op brûkersagentynformaasje. Oanfoljende statistiken oer deistich ferkear, oantal suksesfolle en net-suksesfolle oanfragen jouwe in goed idee fan botaktiviteit.

SQL rapport query

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

Yn dit gefal wie it resultaat fan 'e analyze it beslút om tagong ta de side te beheinen troch it ta te foegjen oan it robots.txt-bestân

User-agent: AhrefsBot
Disallow: /
User-agent: dotbot
Disallow: /
User-agent: bingbot
Crawl-delay: 5
De earste twa bots ferdwûn út 'e tafel, en de MS robots ferhuze del út de earste reëls.

Dei en tiid fan grutste aktiviteit

Opswaaien binne te sjen yn it ferkear. Om se yn detail te studearjen, is it nedich om de tiid fan har foarkommen te markearjen, en it is net nedich om alle oeren en dagen fan tiidmjitting te werjaan. Dit sil it makliker meitsje om yndividuele oanfragen te finen yn it lochbestân as detaillearre analyze nedich is.

SQL rapport query

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

Wy observearje de meast aktive oeren 11, 14 en 20 fan 'e earste dei op' e kaart. Mar de oare deis om 13 oere wiene de bots aktyf.

Gemiddelde deistige brûkersaktiviteit per wike

Wy hawwe de dingen in bytsje regele mei aktiviteit en ferkear. De folgjende fraach wie de aktiviteit fan de brûkers sels. Foar sokke statistiken binne lange aggregaasjeperioaden, lykas in wike, winsklik.

SQL rapport query

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

Wyklikse statistiken litte sjen dat gemiddeld ien brûker 1,6 siden per dei iepenet. It oantal oanfrege bestannen per brûker hinget yn dit gefal ôf fan it tafoegjen fan nije bestannen oan 'e side.

Alle oanfragen en harren status

Webalizer liet altyd spesifike sidekoades sjen en ik woe altyd gewoan it oantal suksesfolle oanfragen en flaters sjen.

SQL rapport query

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

It rapport toant fersiken, net klikken (hits), yn tsjinstelling ta LINE_CNT, wurdt de REQUEST_CNT-metriek berekkene as COUNT(DISTINCT STG.REQUEST_NK). It doel is om effektive eveneminten sjen te litten, bygelyks MS-bots ûndersiket it robots.txt-bestân hûnderten kearen deis en, yn dit gefal, sille sokke polls ien kear teld wurde. Hjirmei kinne jo sprongen yn 'e grafyk glêdje.

Ut de grafyk kinne jo in protte flaters sjen - dit binne net-besteande siden. It resultaat fan 'e analyze wie de tafoeging fan trochferwizings fan siden op ôfstân.

Min fersiken

Om oanfragen yn detail te ûndersiikjen, kinne jo detaillearre statistiken werjaan.

SQL rapport query

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

Dizze list sil ek alle oproppen befetsje, bygelyks in fersyk nei /wp-login.php Troch it oanpassen fan de regels foar it opnij skriuwen fan fersiken troch de tsjinner, kinne jo de reaksje fan de tsjinner op sokke fersiken oanpasse en stjoere nei de startside.

Dat, in pear ienfâldige rapporten basearre op it serverlogbestân jouwe in frij folslein byld fan wat der op 'e side bart.

Hoe kinne jo ynformaasje krije?

In sqlite-database is genôch. Litte wy tabellen oanmeitsje: helpmiddel foar it loggen fan ETL-prosessen.

Tabelpoadium wêr't wy logtriemmen sille skriuwe mei PHP. Twa gearstalde tabellen. Litte wy in deistige tabel oanmeitsje mei statistiken oer brûkersaginten en fersykstatusen. Stunden mei statistiken oer oanfragen, statusgroepen en aginten. Fjouwer tabellen fan relevante mjittingen.

It resultaat is it folgjende relaasjemodel:

Data Model

Skript om in objekt te meitsjen yn in sqlite-database:

DDL foarwerp oanmeitsjen

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

Stage

Yn it gefal fan it access.log-bestân is it nedich om alle oanfragen nei de databank te lêzen, te parsearjen en te skriuwen. Dit kin dien wurde of direkt mei in skripttaal of mei help fan sqlite-ark.

Log triemformaat:

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

Key propagaasje

As de rauwe gegevens yn 'e databank binne, moatte jo kaaien skriuwe dy't der net binne yn' e mjittabellen. Dan sil it mooglik wêze om in ferwizing nei de mjittingen te bouwen. Bygelyks, yn 'e DIM_REFERRER-tabel is de kaai in kombinaasje fan trije fjilden.

SQL kaai propagaasje query

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

Propagaasje nei de tabel mei brûkersagent kin botlogika befetsje, bygelyks it 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

Aggregate tabellen

As lêste sille wy de aggregearre tabellen laden; De deistige tabel kin bygelyks as folget laden wurde:

SQL-fraach foar it laden fan aggregaat

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

De sqlite-database lit jo komplekse fragen skriuwe. WITH befettet de tarieding fan gegevens en kaaien. De haadfraach sammelet alle ferwizings nei dimensjes.

De betingst lit de skiednis net wer laden: CAST(STG.EVENT_DT AS INTEGER) > $param_epoch_from, wêr't de parameter it resultaat is fan it fersyk
'SELECT COALESCE(MAX(EVENT_DT), '3600') AS LAST_EVENT_EPOCH FROM FCT_ACCESS_USER_AGENT_DD'

De betingst sil allinich de folsleine dei laden: CAST(STG.EVENT_DT AS INTEGER) < strftime('%s', date('no', 'begjin fan dei'))

It tellen fan siden of bestannen wurdt útfierd op in primitive manier, troch te sykjen nei in punt.

Ferslaggen

Yn komplekse fisualisaasjesystemen is it mooglik om in meta-model te meitsjen basearre op databankobjekten, filters dynamysk beheare en aggregaasjeregels. Uteinlik generearje alle fatsoenlike ark in SQL-query.

Yn dit foarbyld sille wy klearmakke SQL-query's meitsje en se bewarje as werjeften yn 'e databank - dit binne rapporten.

Fisualisaasje

Bluff: Moaie grafiken yn JavaScript waard brûkt as fisualisaasjeark

Om dit te dwaan, wie it nedich om troch alle rapporten te gean mei PHP en in html-bestân mei tabellen te generearjen.

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

It ark visualisearret gewoan tabellen mei resultaten.

konklúzje

Mei it brûken fan webanalyse as foarbyld, beskriuwt it artikel de meganismen dy't nedich binne om datapakhuzen te bouwen. Lykas út 'e resultaten kin wurde sjoen, binne de ienfâldichste ark genôch foar djippe analyse en fisualisaasje fan gegevens.

Yn 'e takomst, mei dit repository as foarbyld, sille wy besykje sokke struktueren te ymplementearjen lykas stadich feroarjende dimensjes, metadata, aggregaasjenivo's en yntegraasje fan gegevens út ferskate boarnen.

Litte wy ek in tichterby besjen op it ienfâldichste ark foar it behearen fan ETL-prosessen basearre op ien tabel.

Litte wy weromgean nei it ûnderwerp fan it mjitten fan gegevenskwaliteit en it automatisearjen fan dit proses.

Wy sille studearje de problemen fan de technyske omjouwing en ûnderhâld fan gegevens opslach, dêr't wy sille ymplemintearje in opslach tsjinner mei minimale boarnen, bygelyks, basearre op in Raspberry Pi.

Boarne: www.habr.com