Webalizer è Google Analytics m'hà aiutatu à capisce ciò chì succede nantu à i siti web per parechji anni. Avà capitu chì furnisce assai pocu infurmazione utile. Avè accessu à u vostru schedariu access.log, hè assai faciule di capiscenu e statistiche è di implementà strumenti abbastanza basi, cum'è sqlite, html, a lingua sql è qualsiasi lingua di prugrammazione di scrittura.
A fonte di dati per Webalizer hè u schedariu access.log di u servitore. Eccu ciò chì pareanu i so bars è numeri, da quale solu u voluminu tutale di u trafficu hè chjaru:
Strumenti cum'è Google Analytics recullanu dati da a pagina caricata. Ci mostranu un coppiu di diagrammi è linee, basatu nantu à quale hè spessu difficiuli di piglià cunclusioni currette. Forse si deve esse fattu più sforzu ? Ùn sapè.
Allora, chì vulia vede in e statistiche di i visitori di u situ web?
U trafficu di l'utilizatori è di u bot
Spessu u trafficu di u situ hè limitatu è hè necessariu di vede quantu trafficu utile hè utilizatu. Per esempiu, cusì:
Query di rapportu 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_DTU graficu mostra l'attività constante di i bots. Saria interessante di studià in dettu i rapprisintanti più attivi.
Bots fastidiosi
Classificamu i bots in basa di l'infurmazioni di l'agente di l'utilizatori. Statistiche supplementari nantu à u trafficu di ogni ghjornu, u numeru di richieste riesciute è senza successu dà una bona idea di l'attività di u bot.
Query di rapportu 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 10In questu casu, u risultatu di l'analisi hè stata a decisione di limità l'accessu à u situ aghjunghjendu à u schedariu robots.txt.
User-agent: AhrefsBot
Disallow: /
User-agent: dotbot
Disallow: /
User-agent: bingbot
Crawl-delay: 5
I primi dui bots sò spariti da a tavula, è i robots MS si movenu da e prime linee.
U ghjornu è l'ora di a maiò attività
I upswings sò visibili in u trafficu. Per studià in detail, hè necessariu di mette in risaltu u tempu di a so occurrence, è ùn hè micca necessariu di vede tutte l'ore è i ghjorni di misurazione di u tempu. Questu hà da fà più faciule per truvà e richieste individuali in u schedariu di log se hè necessariu un analisi detallatu.
Query di rapportu 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_DTObservemu l'ore più attivu 11, 14 è 20 di u primu ghjornu nantu à u graficu. Ma u ghjornu dopu à 13:XNUMX i bots eranu attivi.
Attività media di l'utilizatori ogni ghjornu per settimana
Avemu risoltu e cose un pocu cù l'attività è u trafficu. A quistione dopu era l'attività di l'utilizatori stessi. Per tali statistiche, longu periudi di aggregazione, cum'è una settimana, sò desiderate.
Query di rapportu 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_DTE statistiche settimanali mostranu chì in media un utilizatore apre 1,6 pagine per ghjornu. U numaru di schedarii dumandati per utilizatore in questu casu dipende di l'aghjunzione di novi schedari à u situ.
Tutte e dumande è i so stati
Webalizer hà sempre mostratu codici di pagina specifichi è aghju sempre vulsutu vede solu u numeru di richieste è errori successi.
Query di rapportu 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_DTU rapportu mostra richieste, micca clics (hits), à u cuntrariu di LINE_CNT, a metrica REQUEST_CNT hè calculata cum'è COUNT(DISTINCT STG.REQUEST_NK). L'obiettivu hè di mustrà avvenimenti efficaci, per esempiu, MS bots poll the robots.txt file centinaie di volte à ghjornu è, in questu casu, tali sondaggi seranu cuntatu una volta. Questu permette di liscia i salti in u graficu.
Da u graficu pudete vede parechji errori - queste sò pagine inesistenti. U risultatu di l'analisi era l'aghjunzione di redirects da e pagine remoti.
E dumande male
Per esaminà e dumande in dettaglio, pudete visualizà statistiche dettagliate.
Query di rapportu 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 20Questa lista cuntene ancu tutte e chjama, per esempiu, una dumanda à /wp-login.php Per aghjustà e regule per riscrive e richieste da u servitore, pudete aghjustà a reazione di u servitore à tali richieste è mandà à a pagina iniziale.
Allora, uni pochi di rapporti simplici basati nantu à u schedariu di log di u servitore dà una stampa abbastanza cumpleta di ciò chì succede in u situ.
Cumu uttene infurmazioni?
Una basa di dati sqlite hè abbastanza. Creemu tabelle: ausiliarii per logu prucessi ETL.
Fase di tavula induve scriveremu i schedarii di log cù PHP. Dui tavulini aggregati. Creemu una tavola di ogni ghjornu cù statistiche nantu à l'agenti di l'utilizatori è i stati di dumanda. Ogni ora cù statistiche nantu à e dumande, gruppi di statutu è agenti. Quattru tavule di misure pertinenti.
U risultatu hè u seguente mudellu relazionale:
U mudellu di dati
Script per creà un oggettu in una basa di dati sqlite:
Creazione di oggetti 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);Scena
In u casu di u schedariu access.log, hè necessariu di leghje, analizà è scrive tutte e dumande à a basa di dati. Questu pò esse fattu sia direttamente cù una lingua di scripting o cù e strumenti sqlite.
Formatu di u schedariu di 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-]+) "(.*)" "(.*)"$/';
Propagazione chjave
Quandu i dati crudi sò in a basa di dati, avete bisognu di scrive chjavi chì ùn sò micca quì in e tavule di misurazione. Allora serà pussibule di custruisce una riferenza à e misure. Per esempiu, in a tavola DIM_REFERRER, a chjave hè una cumminazione di trè campi.
Query di propagazione di chjave SQL
/* 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 NULLA propagazione à a tavola di l'agente d'utilizatore pò cuntene a logica di bot, per esempiu u snippet sql:
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_BOTTavule aggregate
Infine, caricheremu e tavule aggregate per esempiu, a tavola di ogni ghjornu pò esse caricata cum'è:
Query SQL per a carica aggregate
/* 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_IDA basa di dati sqlite permette di scrive dumande cumplesse. WITH cuntene a preparazione di dati è chjave. A quistione principale raccoglie tutte e referenze à dimensioni.
A cundizione ùn permetterà micca di carica di novu a storia: CAST(STG.EVENT_DT AS INTEGER) > $param_epoch_from, induve u paràmetru hè u risultatu di a dumanda
'SELECT COALESCE(MAX(EVENT_DT), '3600') AS LAST_EVENT_EPOCH FROM FCT_ACCESS_USER_AGENT_DD'
A cundizione caricarà solu u ghjornu sanu: CAST (STG.EVENT_DT AS INTEGER) < strftime ('%s', date ('avà', 'iniziu di u ghjornu')))
Cuntà pagine o schedari hè realizatu in modu primitivu, cercandu un puntu.
Rapporti
In i sistemi di visualizazione cumplessi, hè pussibule di creà un meta-mudellu basatu annantu à l'oggetti di basa di dati, gestisce dinamicamente i filtri è e regule di aggregazione. In ultimamente, tutti i strumenti decenti generanu una dumanda SQL.
In questu esempiu, creeremu e dumande SQL pronte è salvate cum'è vedute in a basa di dati - questi sò rapporti.
Visualizazione
Bluff: Belli grafici in JavaScript sò stati utilizati com'è strumentu di visualizazione
Per fà questu, era necessariu di passà per tutti i rapporti cù PHP è generà un schedariu html cù tavule.
$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'
);L'uttellu simpricimenti visualizes tavule di risultati.
cunchiusioni
Utilizendu l'analisi web cum'è un esempiu, l'articulu descrive i miccanismi necessarii per custruisce magazzini di dati. Comu pò esse vistu da i risultati, i strumenti più simplici sò abbastanza per l'analisi profonda è a visualizazione di dati.
In u futuru, aduprendu stu repository cum'è un esempiu, avemu da pruvà à implementà tali strutture cum'è dimensioni cambiante lentamente, metadata, livelli di aggregazione è integrazione di dati da diverse fonti.
Inoltre, fighjemu un ochju più vicinu à l'uttellu più simplice per a gestione di prucessi ETL basati nantu à una sola tavola.
Riturnemu à u tema di a misurazione di a qualità di dati è l'automatizazione di stu prucessu.
Studiaremu i prublemi di l'ambiente tecnicu è u mantenimentu di l'almacenamiento di dati, per quale implementeremu un servitore di almacenamiento cù risorse minimu, per esempiu, basatu nantu à un Raspberry Pi.
Source: www.habr.com