Site Statistiken an Är eege kleng Stockage

Webalizer a Google Analytics hunn mir gehollef Abléck ze kréien wat op Websäite fir vill Joren geschitt. Elo verstinn ech datt se ganz wéineg nëtzlech Informatioun ubidden. Wann Dir Zougang zu Ärer access.log Datei hutt, ass et ganz einfach d'Statistiken ze verstoen an ganz Basis Tools ëmzesetzen, wéi sqlite, html, d'sql Sprooch an all Skriptprogramméierungssprooch.

D'Datequell fir Webalizer ass d'Access.log Datei vum Server. Dëst ass wéi seng Baren an Zuelen ausgesinn, aus deenen nëmmen de Gesamtvolumen vum Traffic kloer ass:

Tools wéi Google Analytics sammelen Daten vun der gelueden Säit selwer. Si weisen eis e puer Diagrammer a Linnen, baséiert op deenen et dacks schwéier ass richteg Conclusiounen ze zéien. Vläicht hätt méi Efforte misse gemaach ginn? Weess net.

Also, wat wollt ech an der Websäit Visiteur Statistiken gesinn?

Benotzer a Bot Verkéier

Oft ass de Siteverkéier limitéiert an et ass néideg ze gesinn wéi vill nëtzlech Traffic benotzt gëtt. Zum Beispill, wéi dëst:

SQL Rapport Ufro

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

D'Grafik weist déi konstant Aktivitéit vu Bots. Et wier interessant déi aktivste Vertrieder am Detail ze studéieren.

Läscht Bots

Mir klassifizéieren Bots op Basis vun User Agent Informatioun. Zousätzlech Statistiken iwwer deegleche Verkéier, Zuel vun erfollegräichen an net erfollegräichen Ufroe ginn eng gutt Iddi vun der Botaktivitéit.

SQL Rapport Ufro

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

An dësem Fall war d'Resultat vun der Analyse d'Entscheedung fir den Zougang zum Site ze beschränken andeems se se an d'robots.txt Datei addéieren

User-agent: AhrefsBot
Disallow: /
User-agent: dotbot
Disallow: /
User-agent: bingbot
Crawl-delay: 5
Déi éischt zwee Bots verschwonnen aus dem Dësch, an der MS Roboter geplënnert erof aus den éischte Linnen.

Dag an Zäit vun der gréisster Aktivitéit

Opschwong sinn am Trafic ze gesinn. Fir se am Detail ze studéieren, ass et néideg d'Zäit vun hirem Optriede ze markéieren, an et ass net néideg fir all Stonnen an Deeg vun der Zäitmiessung ze weisen. Dëst wäert et méi einfach maachen individuell Ufroen an der Logdatei ze fannen wann detailléiert Analyse néideg ass.

SQL Rapport Ufro

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

Mir beobachten déi aktivst Stonnen 11, 14 an 20 vum éischten Dag op der Grafik. Ma den nächsten Dag um 13:XNUMX waren d'Boten aktiv.

Duerchschnëtt deeglech Benotzeraktivitéit pro Woch

Mir hunn d'Saachen e bëssen mat Aktivitéit an Traffic ausgesort. Déi nächst Fro war d'Aktivitéit vun de Benotzer selwer. Fir esou Statistike si laang Aggregatiounsperioden, wéi eng Woch, wënschenswäert.

SQL Rapport Ufro

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

Wöchentlech Statistike weisen datt am Duerchschnëtt ee Benotzer 1,6 Säiten pro Dag opmaacht. D'Zuel vun ugefrote Fichieren pro Benotzer an dësem Fall hänkt op der Zousätzlech vun neie Fichier'en op de Site.

All Ufroen an hir Status

Webalizer huet ëmmer spezifesch Säitcodes gewisen an ech wollt ëmmer just d'Zuel vun erfollegräichen Ufroen a Feeler gesinn.

SQL Rapport Ufro

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

De Bericht weist Ufroen, net Klicks (Hits), am Géigesaz zu LINE_CNT, gëtt d'REQUEST_CNT Metrik als COUNT (DISTINCT STG.REQUEST_NK) berechent. D'Zil ass effektiv Eventer ze weisen, zum Beispill MS Bots pollen d'robots.txt Datei honnerte Mol am Dag an an dësem Fall ginn esou Ëmfroen eemol gezielt. Dëst erlaabt Iech Spréng an der Grafik glat ze maachen.

Vun der Grafik kënnt Dir vill Feeler gesinn - dat sinn net existent Säiten. D'Resultat vun der Analyse war d'Additioun vu Viruleedungen vun Remote Säiten.

Schlecht Ufroen

Fir Ufroen am Detail z'ënnersichen, kënnt Dir detailléiert Statistike weisen.

SQL Rapport Ufro

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

Dës Lëscht enthält och all Uruff, zum Beispill, eng Ufro un /wp-login.php Andeems Dir d'Regele fir d'Rewriting vun Ufroe vum Server unzepassen, kënnt Dir d'Reaktioun vum Server op esou Ufroen upassen an se op d'Startsäit schécken.

Also, e puer einfache Berichter baséiert op der Serverlogdatei ginn e zimlech komplett Bild vun deem wat um Site geschitt.

Wéi Informatiounen ze kréien?

Eng sqlite Datebank ass genuch. Loosst eis Dëscher erstellen: Hëllef fir ETL Prozesser ze protokolléieren.

Table Etapp wou mir Logdateien mat PHP schreiwen. Zwee aggregéiert Dëscher. Loosst eis en deeglechen Dësch erstellen mat Statistiken iwwer Benotzeragenten an Ufro Statusen. Stonn mat Statistiken op Ufroen, Status Gruppen an Agenten. Véier Dëscher vun relevant Miessunge.

D'Resultat ass de folgende relationale Modell:

Datemodell

Skript fir en Objet an enger sqlite Datebank ze kreéieren:

DDL Objet Kreatioun

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

Etapp

Am Fall vun der access.log Datei ass et néideg all Ufroen an d'Datebank ze liesen, parséieren a schreiwen. Dëst kann entweder direkt mat enger Skriptsprooch oder mat sqlite Tools gemaach ginn.

Log Dateiformat:

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

Schlëssel Verbreedung

Wann d'Rohdaten an der Datebank sinn, musst Dir Schlësselen schreiwen déi net do sinn an d'Miesstabellen. Da wäert et méiglech sinn eng Referenz zu de Miessunge ze bauen. Zum Beispill, an der DIM_REFERRER Tabell ass de Schlëssel eng Kombinatioun vun dräi Felder.

SQL Schlëssel Verbreedung Ufro

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

Propagatioun op d'Benotzer Agent Tabelle kann Bot Logik enthalen, zum Beispill de 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

Aggregat Dëscher

Schlussendlech luede mir déi aggregéiert Dëscher; zum Beispill kann den alldeeglechen Dësch wéi follegt geluede ginn:

SQL Ufro fir Luede aggregéiert

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

D'sqlite Datebank erlaabt Iech komplex Ufroen ze schreiwen. MAT enthält d'Virbereedung vun Daten a Schlësselen. D'Haaptfro sammelt all Referenzen op Dimensiounen.

D'Konditioun erlaabt d'Geschicht net erëm ze lueden: CAST(STG.EVENT_DT AS INTEGER) > $param_epoch_from, wou de Parameter d'Resultat vun der Ufro ass
'SELECT COALESCE(MAX(EVENT_DT), '3600') AS LAST_EVENT_EPOCH FROM FCT_ACCESS_USER_AGENT_DD'

D'Konditioun lued nëmmen de ganzen Dag: CAST(STG.EVENT_DT AS INTEGER) < strftime('%s', date('now', 'Start of Day'))

Säiten oder Dateie zielen gëtt op eng primitiv Manéier duerchgefouert, andeems Dir no engem Punkt sicht.

Rapporten

A komplexe Visualiséierungssystemer ass et méiglech e Meta-Modell op Basis vun Datebankobjekter ze kreéieren, dynamesch Filteren an Aggregatiounsregelen ze managen. Schlussendlech generéieren all anstänneg Tools eng SQL Ufro.

An dësem Beispill wäerte mir fäerdeg SQL Ufroen erstellen an se als Usiichten an der Datebank späicheren - dat sinn Berichter.

Visualiséierung

Bluff: Schéin Grafiken a JavaScript gouf als Visualiséierungsinstrument benotzt

Fir dëst ze maachen, war et néideg fir all Berichter mat PHP duerchzegoen an eng HTML-Datei mat Dëscher ze generéieren.

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

Den Tool visualiséiert einfach Tabelle vun de Resultater.

Konklusioun

Mat Webanalyse als Beispill beschreift den Artikel d'Mechanismen déi néideg sinn fir Datelager ze bauen. Wéi kann aus de Resultater gesi ginn, sinn déi einfachst Tools genuch fir déif Analyse a Visualiséierung vun Daten.

An Zukunft, mat dësem Repository als Beispill, wäerte mir probéieren esou Strukturen ëmzesetzen wéi lues verännert Dimensiounen, Metadaten, Aggregatiounsniveauen an Integratioun vun Daten aus verschiddene Quellen.

Loosst eis och méi no kucken op dat einfachst Tool fir ETL Prozesser ze managen baséiert op enger eenzeger Tabell.

Komme mer zréck op d'Thema vun der Datequalitéit ze moossen an dëse Prozess automatiséieren.

Mir studéieren d'Problemer vum techneschen Ëmfeld an Ënnerhalt vun Datenspeicher, fir déi mir e Späicherserver mat minimale Ressourcen ëmsetzen, zum Beispill, baséiert op engem Raspberry Pi.

Source: will.com