🥇Website-statistik og dit lille datalager

Webalizer og Google Analytics har hjulpet mig i mange år med at få indsigt i, hvad der sker på hjemmesider. Nu forstår jeg, at de giver meget lidt nyttig information. Med adgang til min access.log-fil er det meget enkelt at analysere statistikken, og det kræver blot grundlæggende værktøjer såsom sqlite, html, sql og ethvert scriptsprog.

Datakilden for Webalizer er access.log-filen server. Sådan ser dens kolonner og tal ud, hvoraf kun den samlede mængde trafik er forståelig:

Værktøjer som Google Analytics indsamler data fra den indlæste side automatisk. De viser os et par diagrammer og linjer, hvor det ofte er svært at træffe de rigtige konklusioner. Måske skulle man have lagt mere arbejde i det? Jeg ved det ikke.

Så hvad ville jeg gerne se i statistikken for besøgende på hjemmesiden?

Bruger- og bottrafik

Ofte har hjemmesider et begrænset trafikniveau, og det er nødvendigt at se, hvor meget nyttig trafik der anvendes. For eksempel sådan her:

SQL-spørgselrapporten

SELECT
1 as 'StackedArea: Trafik genereret af Brugere og Bots',
strftime('%d.%m', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Dag',
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 'Brugere, 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

Grafen viser en konstant aktivitet af bots. Det ville være interessant at undersøge de mest aktive repræsentanter nærmere.

Plagsomme bots

Vi klassificerer bots baseret på oplysninger om brugeragenten. Yderligere statistik om daglig trafik, antal succesfulde og fejlede anmodninger giver et godt indtryk af botternes aktivitet.

SQL-spørgselrapporten

SELECT 
1 AS 'Tabel: Plagsomme Bots',
MAX(USG.AGENT_BOT) AS 'Bot',
ROUND(SUM(FCT.BYTES)\/1000 \/ 14.0, 1) AS 'KB per Dag',
ROUND(SUM(FCT.IP_CNT) \/ 14.0, 1) AS 'IPs per Dag',
ROUND(SUM(CASE WHEN STS.STATUS_GROUP IN ('Klientfejl', 'Serverfejl') THEN FCT.REQUEST_CNT \/ 14.0 ELSE 0 END), 1) AS 'Fejl Anmodninger per Dag',
ROUND(SUM(CASE WHEN STS.STATUS_GROUP IN ('Succesfuld', 'Omdirigering') THEN FCT.REQUEST_CNT \/ 14.0 ELSE 0 END), 1) AS 'Succes Anmodninger per Dag',
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

I dette tilfælde blev resultatet af analysen en beslutning om at begrænse adgangen til hjemmesiden ved at tilføje til robots.txt-filen.

Bruger-agent: AhrefsBot Forbudt: / Bruger-agent: dotbot Forbudt: / Bruger-agent: bingbot Crawl-forsinkelse: 5
De første to bots forsvandt fra tabellen, og MS-robotterne bevægede sig ned fra de øverste rækker.

Tidspunkt for højeste aktivitet

Der ses stigninger i trafikken. For at undersøge dem nærmere er det nødvendigt at udpege tidspunktet for deres opståen, og det er ikke nødvendigt at vise alle timer og dage for tidsmålingen. Det vil gøre det lettere at finde specifikke forespørgsler i logfilen, hvis en detaljeret analyse er nødvendig.

SQL-spørgselrapporten

SELECT
1 AS 'Linje: Dag og time for hits fra brugere og bots',
strftime('%d.%m-%H', datetime(EVENT_DT, 'unixepoch')) AS 'Dato og tid',
HIB AS 'Bots, hits',
HIU AS 'Brugere, 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

Vi ser de mest aktive timer den 11., 14. og 20. på den første dag på grafen. Men på den følgende dag var der aktivitet fra bots kl. 13.

Gennemsnitlig daglig aktivitet for brugere i uger

Vi har lidt styr på aktiviteten og trafikken. Det næste spørgsmål var selve brugernes aktivitet. For denne statistik er længere aggregeringsperioder ønskelige, for eksempel en uge.

SQL-spørgselrapporten

SELECT
1 as 'Linje: Gennemsnitlig daglig brugeraktivitet pr. uge',
strftime('%W uge', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Uge',
ROUND(1.0*SUM(FCT.PAGE_CNT)/SUM(FCT.IP_CNT),1) AS 'Sider pr. IP pr. dag',
ROUND(1.0*SUM(FCT.FILE_CNT)/SUM(FCT.IP_CNT),1) AS 'Filer pr. IP pr. dag'
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.' /* kun brugere */
  AND HST.STATUS_GROUP IN ('Successful') /* gode sider */
  AND datetime(FCT.EVENT_DT, 'unixepoch') > date('now', '-3 month')
GROUP BY strftime('%W uge', datetime(FCT.EVENT_DT, 'unixepoch'))
ORDER BY FCT.EVENT_DT

Statistikken for ugen viser, at en gennemsnitlig bruger åbner 1,6 sider om dagen. Antallet af anmodede filer pr. bruger afhænger i dette tilfælde af, hvor mange nye filer der tilføjes til siden.

Alle anmodninger og deres statusser

Webalizer har altid vist specifikke koder for siderne, og man ville altid gerne se antallet af succesfulde anmodninger og fejl.

SQL-spørgselrapporten

SELECT
1 as 'Linje: Alle anmodninger efter status',
strftime('%d.%m', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Dag',
SUM(CASE WHEN STS.STATUS_GROUP='Successful' THEN FCT.REQUEST_CNT ELSE 0 END) AS 'Succes',
SUM(CASE WHEN STS.STATUS_GROUP='Redirection' THEN FCT.REQUEST_CNT ELSE 0 END) AS 'Omdirigering',
SUM(CASE WHEN STS.STATUS_GROUP='Client Error' THEN FCT.REQUEST_CNT ELSE 0 END) AS 'Kunde Fejl',
SUM(CASE WHEN STS.STATUS_GROUP='Server Error' THEN FCT.REQUEST_CNT ELSE 0 END) AS 'Server Fejl'
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

Rapporten viser anmodninger og ikke klik (hits), i modsætning til LINE_CNT, beregnes metrikken REQUEST_CNT som COUNT(DISTINCT STG.REQUEST_NK). Målet er at vise effektive hændelser, for eksempel spørger MS-bots hundrede gange om dagen robots.txt-filen, og i dette tilfælde tælles sådanne forespørgsler kun én gang. Dette gør det muligt at udglatte udsving i grafen.

Fra grafen kan man se mange fejl — det er ikke-eksisterende sider. Resultatet af analysen resulterede i tilføjelsen af omdirigeringer fra de slettede sider.

Fejlagtige anmodninger

For en detaljeret gennemgang af anmodninger kan man udtrække detaljeret statistik.

SQL-spørgselrapporten

SELECT
  1 AS 'Tabel: Top Fejl Anmodninger',
  REQ.REQUEST_NK AS 'Anmodning',
  'Fejl' AS 'Anmodningsstatus',
  ROUND(SUM(FCT.LINE_CNT) / 14.0, 1) AS 'Hits pr. Dag',
  ROUND(SUM(FCT.IP_CNT) / 14.0, 1) AS 'IPs pr. Dag',
  ROUND(SUM(FCT.BYTES)/1000 / 14.0, 1) AS 'KB pr. Dag'
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

Denne liste vil også indeholde alle forespørgsler, for eksempel en anmodning til /wp-login.php. Ved at justere reglerne for omskrivning af anmodninger serveren kan serverens reaktion på sådanne anmodninger justeres, så de sendes til hjemmesiden.

Således giver nogle enkle rapporter baseret på serverlogfilen et tilstrækkeligt klart billede af, hvad der sker på hjemmesiden.

Hvordan får man information?

SQLite-databaser er ganske tilstrækkelige. Lad os oprette tabeller: en hjælpe tabel til logning af ETL-processer.

Staging table, hvor vi vil skrive logfiler med hjælp af PHP. To aggregattabeller. Vi opretter en daglig tabel med statistik over brugeragenter og anmodningsstatusser. En times tabel med statistik over anmodninger, statusgrupper og agenter. Fire tabeller med de relevante dimensioner.

Som resultat er den følgende relationelle model opnået:

Datamodel

Script til at oprette objekt i SQLite-databasen:

DDL opretter objekt

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

Staging

I tilfælde af access.log-filen er det nødvendigt at læse, parse og indtaste alle anmodninger i databasen. Dette kan gøres enten direkte ved hjælp af scriptsprog eller ved at bruge SQLite-værktøjer.

Formatet for logfilen:

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

Nøglepropagation

Når rådata er i databasen, skal der skrives nøgler til måltabellerne, som ikke allerede findes der. Så vil det være muligt at opbygge links til målingerne. For eksempel er nøglen i DIM_REFERRER-tabellen en kombination af tre felter.

SQL-forespørgsel for nøglepropagation

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

Propagation til brugeragentstabellen kan indeholde botlogik, for eksempel sql-uddraget:


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

Aggregattabeller

Til slut vil vi indlæse aggregattabellerne; for eksempel kan den daglige tabel indlæses på følgende måde:

SQL-forespørgsel for indlæsning af aggregat

/* 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-databasen tillader at skrive komplekse forespørgsler. WITH indeholder forberedelse af data og nøgler. Den primære forespørgsel samler alle links til målinger.

Betingelsen forhindrer at historikken bliver indlæst igen: CAST(STG.EVENT_DT AS INTEGER) > $param_epoch_from, hvor parameteren er resultatet af forespørgslen
‘SELECT COALESCE(MAX(EVENT_DT), ‘3600’) AS LAST_EVENT_EPOCH FROM FCT_ACCESS_USER_AGENT_DD’

Betingelsen indlæser kun hele dagen: CAST(STG.EVENT_DT AS INTEGER) < strftime(‘%s’, date(‘now’, ‘start of day’))

Antallet af sider eller filer tælles på en simpel måde ved at søge efter punktet.

Rapporter

I komplekse visualiseringssystemer er der mulighed for at skabe en meta-model baseret på databaste objekter, dynamisk styre filtre og aggregationsregler. I sidste ende genererer alle anstændige værktøjer SQL-forespørgsler.

I dette eksempel vil vi oprette allerede færdige SQL-forespørgsler og gemme dem som visninger i databasen — det er rapporterne.

Visualisering

Bluff blev brugt som visualiseringsværktøj: Beautiful graphs in JavaScript

For dette krævede det at bruge PHP til at gennemgå alle rapporterne og generere en html-fil med tabeller.

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

Værktøjet visualiserer blot resultatstyrings-tabeller.

Output

Artiklen beskriver på eksemplet med webanalyse de mekanismer, der er nødvendige for at opbygge datalagre. Som det fremgår af resultaterne, er de enkleste værktøjer tilstrækkelige til dybdegående analyse og visualisering af data.

I fremtiden vil vi prøve at implementere strukturer som langsomt ændrede dimensioner, metadata, aggregeringsniveauer og integration af data fra forskellige kilder med udgangspunkt i dette datalager.

Vi vil også se nærmere på et enkelt værktøj til administration af ETL-processer baseret på en enkelt tabel.

Lad os vende tilbage til emnet om måling af datakvalitet og automatisering af denne proces.

Vi vil undersøge problemerne med det tekniske miljø og vedligeholdelse af datalagre. Derfor vil vi opsætte en lagerserver med minimale ressourcer, f.eks. baseret på Raspberry Pi.

Kilde: habr.com