L'utilità Webalizer e lo strumento Google Analytics mi hanno aiutato per molti anni a comprendere cosa succede sui siti web. Ora capisco che forniscono poche informazioni utili. Con accesso al file access.log, analizzare le statistiche è molto semplice e richiede solo strumenti elementari come sqlite, html, linguaggio sql e qualsiasi linguaggio di programmazione script.
La fonte dei dati per Webalizer è il file access.log server. Ecco come appaiono le sue colonne e i numeri, dai quali si comprende solo il volume complessivo del traffico:
Strumenti come Google Analytics raccolgono dati dalla pagina caricata autonomamente. Ci mostrano alcune diagrammi e linee, sulla base dei quali spesso è difficile trarre conclusioni corrette. Forse avrei dovuto sforzarmi di più? Non lo so.
Quindi, cosa avrei voluto vedere nelle statistiche delle visite al sito?
Il traffico degli utenti e dei bot
Spesso il traffico dei siti ha limiti e è necessario vedere quanto traffico utile viene utilizzato. Ad esempio, così:
Richiesta SQL del report
SELEZIONA
1 as 'StackedArea: Traffico generato da utenti e bot',
strftime('%d.%m', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Giorno',
SUM(CASE WHEN USG.AGENT_BOT!='n.a.' THEN FCT.BYTES ELSE 0 END)/1000 AS 'Bot, KB',
SUM(CASE WHEN USG.AGENT_BOT='n.a.' THEN FCT.BYTES ELSE 0 END)/1000 AS 'Utenti, KB'
DA
FCT_ACCESS_USER_AGENT_DD FCT,
DIM_USER_AGENT USG
DOVE FCT.DIM_USER_AGENT_ID=USG.DIM_USER_AGENT_ID
E datetime(FCT.EVENT_DT, 'unixepoch') >= date('now', '-14 day')
GRUPPA PER strftime('%d.%m', datetime(FCT.EVENT_DT, 'unixepoch'))
ORDINA PER FCT.EVENT_DTDal grafico si osserva un'attività costante da parte dei bot. Sarebbe interessante esaminare in dettaglio i rappresentanti più attivi.
Bot fastidiosi
Classifichiamo i bot sulla base delle informazioni dell'agent utente. Statistiche aggiuntive sul traffico giornaliero, sul numero di richieste riuscite e non riuscite offrono una buona panoramica sull'attività dei bot.
Richiesta SQL del report
SELEZIONA
1 COME 'Tabella: Bot Fastidiosi',
MAX(USG.AGENT_BOT) COME 'Bot',
ROUND(SOMMA(FCT.BYTES) / 1000 / 14.0, 1) COME 'KB al Giorno',
ROUND(SOMMA(FCT.IP_CNT) / 14.0, 1) COME 'IPs al Giorno',
ROUND(SOMMA(CASE QUANDO STS.STATUS_GROUP IN ('Errore Client', 'Errore Server') ALLORA FCT.REQUEST_CNT / 14.0 ALTRO 0 FINE), 1) COME 'Richieste di Errore al Giorno',
ROUND(SOMMA(CASE QUANDO STS.STATUS_GROUP IN ('Successo', 'Reindirizzamento') ALLORA FCT.REQUEST_CNT / 14.0 ALTRO 0 FINE), 1) COME 'Richieste di Successo al Giorno',
USG.USER_AGENT_NK COME 'Agente'
DA FCT_ACCESS_USER_AGENT_DD FCT,
DIM_USER_AGENT USG,
DIM_HTTP_STATUS STS
DOVE FCT.DIM_USER_AGENT_ID = USG.DIM_USER_AGENT_ID
E FCT.DIM_HTTP_STATUS_ID = STS.DIM_HTTP_STATUS_ID
E USG.AGENT_BOT != 'n.a.'
E datetime(FCT.EVENT_DT, 'unixepoch') >= date('now', '-14 giorno')
RAGGRUPPA PER USG.USER_AGENT_NK
ORDINA PER 3 DESC
LIMITA 10In questo caso, il risultato dell'analisi ha portato alla decisione di limitare l'accesso al sito aggiungendo al file robots.txt
User-agent: AhrefsBot
Disallow: /
User-agent: dotbot
Disallow: /
User-agent: bingbot
Crawl-delay: 5
I primi due bot sono scomparsi dalla tabella, mentre i robot MS sono scivolati verso il basso.
Giorno e ora di maggiore attività
Nel traffico sono visibili dei picchi. Per esaminarli in dettaglio, è necessario identificare il momento della loro occorrenza, senza necessariamente visualizzare tutte le ore e i giorni di misurazione. In questo modo sarà più semplice trovare singole richieste nel file di log in caso di analisi dettagliata.
Richiesta SQL del report
SELECT
1 AS 'Linea: Giorno e Ora degli Accessi da Utenti e Bot',
strftime('%d.%m-%H', datetime(EVENT_DT, 'unixepoch')) AS 'Data e Ora',
HIB AS 'Bot, Accessi',
HIU AS 'Utenti, Accessi'
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_DTOsserviamo le ore di attività più attive, ovvero 11, 14 e 20 del primo giorno, nel grafico. Il giorno successivo, invece, i bot erano attivi alle 13.
Attività media quotidiana degli utenti per settimane
Abbiamo esaminato l'attività e il traffico. La prossima domanda riguarda l'attività degli utenti stessi. Per tale statistica sono suggeriti periodi di aggregazione più ampi, ad esempio, una settimana.
Richiesta SQL del report
SELECT
1 as 'Linea: Attività Media Giornaliera degli Utenti per Settimana',
strftime('%W settimana', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Settimana',
ROUND(1.0*SUM(FCT.PAGE_CNT)/SUM(FCT.IP_CNT),1) AS 'Pagine per IP al Giorno',
ROUND(1.0*SUM(FCT.FILE_CNT)/SUM(FCT.IP_CNT),1) AS 'File per IP al Giorno'
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.' /* solo utenti */
AND HST.STATUS_GROUP IN ('Successful') /* pagine valide */
AND datetime(FCT.EVENT_DT, 'unixepoch') >= date('now', '-3 month')
GROUP BY strftime('%W settimana', datetime(FCT.EVENT_DT, 'unixepoch'))
ORDER BY FCT.EVENT_DTLe statistiche della settimana mostrano che, in media, un utente apre 1,6 pagine al giorno. Il numero di file richiesti per utente dipende dall'aggiunta di nuovi file al sito.
Tutte le richieste e i loro stati
Webalizer ha sempre mostrato codici specifici delle pagine e si desiderava sempre vedere semplicemente il numero di richieste riuscite e di errori.
Richiesta SQL del report
SELECT
1 as 'Riga: Tutte le richieste per stato',
strftime('%d.%m', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Giorno',
SUM(CASE WHEN STS.STATUS_GROUP='Successful' THEN FCT.REQUEST_CNT ELSE 0 END) AS 'Successo',
SUM(CASE WHEN STS.STATUS_GROUP='Redirection' THEN FCT.REQUEST_CNT ELSE 0 END) AS 'Reindirizzamento',
SUM(CASE WHEN STS.STATUS_GROUP='Client Error' THEN FCT.REQUEST_CNT ELSE 0 END) AS 'Errore Cliente',
SUM(CASE WHEN STS.STATUS_GROUP='Server Error' THEN FCT.REQUEST_CNT ELSE 0 END) AS 'Errore Server'
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_DTIl report visualizza le richieste e non i clic (hits); a differenza di LINE_CNT, la metrica REQUEST_CNT viene calcolata come COUNT(DISTINCT STG.REQUEST_NK). L'obiettivo è mostrare eventi efficaci; ad esempio, i bot di Microsoft interrogano centinaia di volte al giorno il file robots.txt e, in questo caso, tali interrogazioni verranno contate una sola volta. Questo consente di smussare i picchi nel grafico.
Dal grafico si possono notare molti errori: si tratta di pagine inesistenti. L'analisi ha portato all'aggiunta di reindirizzamenti dalle pagine eliminate.
Richieste errate
Per un'analisi dettagliata delle richieste, è possibile generare statistiche dettagliate.
Richiesta SQL del report
SELECT
1 AS 'Tabella: Richieste di errore principali',
REQ.REQUEST_NK AS 'Richiesta',
'Errore' AS 'Stato richiesta',
ROUND(SUM(FCT.LINE_CNT) / 14.0, 1) AS 'Accessi al Giorno',
ROUND(SUM(FCT.IP_CNT) / 14.0, 1) AS 'IP al Giorno',
ROUND(SUM(FCT.BYTES)/1000 / 14.0, 1) AS 'KB al Giorno'
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 ('Errore Cliente', 'Errore Server')
AND datetime(FCT.EVENT_DT, 'unixepoch') >= date('now', '-14 day')
GROUP BY REQ.REQUEST_NK
ORDER BY 4 DESC
LIMIT 20In questo elenco si troveranno anche tutte le richieste, ad esempio, la richiesta a /wp-login.php. Correttamente modificando le regole di riscrittura delle richieste, server è possibile correggere la reazione del server a tali richieste e reindirizzarle alla homepage.
Quindi, alcuni semplici report basati sul file di log del server forniscono un quadro abbastanza completo di ciò che accade sul sito.
Come ottenere informazioni?
Le basi di dati sqlite sono più che sufficienti. Creeremo delle tabelle: una ausiliaria per il logging dei processi ETL.
Stage della tabella dove scriveremo i file di log tramite PHP. Due tabelle di aggregati. Creeremo una tabella giornaliera con le statistiche sui user-agent e sugli stati delle richieste. Una tabella oraria con statistiche sulle richieste, i gruppi di stati e gli agenti. Quattro tabelle di misurazioni corrispondenti.
Il risultato è stato il seguente modello relazionale:
Modello dati
Script per creare un oggetto nel database sqlite:
DDL creazione oggetto
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
Nel caso del file access.log è necessario leggere, analizzare e registrare nel database tutte le richieste. Questo può essere fatto o direttamente tramite un linguaggio di script, oppure utilizzando gli strumenti di sqlite.
Formato del file 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 delle chiavi
Quando i dati grezzi sono presenti nel database, è necessario registrare nelle tabelle delle misure le chiavi che non sono presenti. In questo modo sarà possibile costruire un riferimento alle misure. Ad esempio, nella tabella DIM_REFERRER, la chiave è una combinazione di tre campi.
Query SQL per la propagazione delle chiavi
/* 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 NULLLa propagazione nella tabella degli agenti utente può contenere la logica dei bot, ad esempio, un estratto di 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_BOTTabelle degli aggregati
Infine, caricheremo le tabelle degli aggregati, ad esempio, la tabella giornaliera può essere caricata nel seguente modo:
Query SQL per il caricamento dell'aggregato
/* 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_IDIl database sqlite consente di scrivere query complesse. WITH contiene la preparazione dei dati e delle chiavi. La query principale raccoglie tutti i riferimenti alle misure.
La condizione non permetterà di caricare nuovamente la cronologia: CAST(STG.EVENT_DT AS INTEGER) > $param_epoch_from, dove il parametro è il risultato della query
‘SELECT COALESCE(MAX(EVENT_DT), ‘3600’) AS LAST_EVENT_EPOCH FROM FCT_ACCESS_USER_AGENT_DD’
La condizione caricherà solo l'intera giornata: CAST(STG.EVENT_DT AS INTEGER) < strftime(‘%s’, date(‘now’, ‘start of day’))
Il conteggio delle pagine o dei file viene effettuato in modo primitivo, cercando un punto.
Report
Nei sistemi di visualizzazione complessi è possibile creare un meta-modello basato sugli oggetti del database, gestire dinamicamente filtri e regole di aggregazione. Alla fine, tutti gli strumenti decenti generano una query SQL.
In questo esempio creeremo query SQL già pronte e le salveremo come view nel database: questi sono i report.
Visualizzazione
Come strumento di visualizzazione è stato utilizzato Bluff: Beautiful graphs in JavaScript
Per fare questo, è stato necessario utilizzare PHP per scorrere tutti i report e generare un file html con le tabelle.
$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'
);Lo strumento visualizza semplicemente le tabelle dei risultati.
Risultato
Attraverso l'analisi web, l'articolo descrive i meccanismi necessari per costruire i data warehouse. Come si può vedere dai risultati, per un'analisi e visualizzazione approfondita dei dati, sono sufficienti strumenti molto semplici.
Successivamente, utilizzando questo data warehouse come esempio, cercheremo di implementare strutture come dimensioni a cambiamento lento, metadati, livelli di aggregazione e integrazione dei dati provenienti da varie fonti.
Inoltre, esamineremo un semplice strumento per gestire i processi ETL basato su una singola tabella.
Torniamo al tema della misurazione della qualità dei dati e dell'automazione di questo processo.
Esamineremo le problematiche ambientali e di gestione del data warehouse, implementando un server di archiviazione con risorse minime, ad esempio basato su Raspberry Pi.
Fonte: habr.com
