Webalizer e Google Analytics mi aiutano da molti anni a ottenere informazioni dettagliate su ciò che accade sui siti Web. Ora capisco che forniscono pochissime informazioni utili. Avendo accesso al tuo file access.log, è molto facile comprendere le statistiche e implementare strumenti abbastanza basilari, come sqlite, html, il linguaggio sql e qualsiasi linguaggio di programmazione di scripting.
L'origine dati per Webalizer è il file access.log del server. Ecco come appaiono le barre e i numeri, da cui risulta chiaro solo il volume totale del traffico:
Strumenti come Google Analytics raccolgono dati stessi dalla pagina caricata. Ci mostrano un paio di diagrammi e linee, in base alle quali spesso è difficile trarre conclusioni corrette. Forse si sarebbe dovuto fare uno sforzo maggiore? Non lo so.
Allora, cosa volevo vedere nelle statistiche sui visitatori del sito web?
Traffico di utenti e bot
Spesso il traffico sul sito è limitato ed è necessario vedere quanto traffico utile viene utilizzato. Ad esempio, in questo modo:
Interrogazione del rapporto 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_DTIl grafico mostra l'attività costante dei bot. Sarebbe interessante studiare in dettaglio i rappresentanti più attivi.
Bot fastidiosi
Classifichiamo i bot in base alle informazioni dell'agente utente. Ulteriori statistiche sul traffico giornaliero, sul numero di richieste riuscite e non riuscite danno una buona idea dell'attività del bot.
Interrogazione del rapporto 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 questo caso il risultato dell'analisi è stata la decisione di limitare l'accesso al sito aggiungendolo al file robots.txt
User-agent: AhrefsBot
Disallow: /
User-agent: dotbot
Disallow: /
User-agent: bingbot
Crawl-delay: 5
I primi due robot sono scomparsi dal tavolo e i robot MS sono scesi dalle prime file.
Giorno e ora di maggiore attività
Le riprese sono visibili nel traffico. Per studiarli nel dettaglio è necessario evidenziare l'ora in cui si sono verificati, e non è necessario visualizzare tutte le ore e i giorni di misurazione del tempo. Ciò renderà più semplice trovare le singole richieste nel file di registro se è necessaria un'analisi dettagliata.
Interrogazione del rapporto 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_DTOsserviamo le ore 11, 14 e 20 più attive del primo giorno sul grafico. Ma il giorno dopo alle 13:XNUMX i bot erano attivi.
Attività media giornaliera degli utenti per settimana
Abbiamo sistemato un po' le cose con l'attività e il traffico. La domanda successiva riguardava l'attività degli utenti stessi. Per tali statistiche sono auspicabili periodi di aggregazione lunghi, ad esempio una settimana.
Interrogazione del rapporto 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_DTLe statistiche settimanali mostrano che in media un utente apre 1,6 pagine al giorno. Il numero di file richiesti per utente in questo caso dipende dall'aggiunta di nuovi file al sito.
Tutte le richieste e i relativi stati
Webalizer mostrava sempre codici di pagina specifici e volevo sempre vedere solo il numero di richieste ed errori riusciti.
Interrogazione del rapporto 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_DTIl rapporto visualizza le richieste, non i clic (risultati). 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 MS interrogano il file robots.txt centinaia di volte al giorno e, in questo caso, tali sondaggi verranno conteggiati una volta. Ciò consente di attenuare i salti nel grafico.
Dal grafico puoi vedere molti errori: queste sono pagine inesistenti. Il risultato dell'analisi è stata l'aggiunta di reindirizzamenti da pagine remote.
Cattive richieste
Per esaminare nel dettaglio le richieste è possibile visualizzare statistiche dettagliate.
Interrogazione del rapporto 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 20Questo elenco conterrà anche tutte le chiamate, ad esempio una richiesta a /wp-login.php Modificando le regole per la riscrittura delle richieste da parte del server, è possibile adattare la reazione del server a tali richieste e inviarle alla pagina iniziale.
Pertanto, alcuni semplici report basati sul file di registro del server forniscono un quadro abbastanza completo di ciò che sta accadendo sul sito.
Come ottenere informazioni?
È sufficiente un database SQLite. Creiamo tabelle: ausiliarie per la registrazione dei processi ETL.
Fase della tabella in cui scriveremo i file di registro utilizzando PHP. Due tabelle aggregate. Creiamo una tabella giornaliera con le statistiche sugli user agent e sugli stati delle richieste. Oraria con statistiche su richieste, gruppi di stato e agenti. Quattro tabelle di misurazioni rilevanti.
Il risultato è il seguente modello relazionale:
Modello di dati
Script per creare un oggetto in un database 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);Palcoscenico
Nel caso del file access.log, è necessario leggere, analizzare e scrivere tutte le richieste nel database. Questo può essere fatto direttamente utilizzando un linguaggio di scripting o utilizzando gli strumenti SQLite.
Formato del file di registro:
//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 nel database, è necessario scrivere le chiavi che non sono presenti nelle tabelle di misurazione. Quindi sarà possibile costruire un riferimento alle misurazioni. Ad esempio, nella tabella DIM_REFERRER, la chiave è una combinazione di tre campi.
Query di propagazione della chiave 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 NULLLa propagazione alla tabella dell'agente utente può contenere la logica del bot, ad esempio lo 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_BOTTabelle aggregate
Infine caricheremo le tabelle aggregate; ad esempio la tabella giornaliera potrà essere caricata così:
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 ti consente di scrivere query complesse. WITH contiene la preparazione dei dati e delle chiavi. La query principale raccoglie tutti i riferimenti alle dimensioni.
La condizione non consentirà di caricare nuovamente la cronologia: CAST(STG.EVENT_DT AS INTEGER) > $param_epoch_from, dove il parametro è il risultato della richiesta
'SELECT COALESCE(MAX(EVENT_DT), '3600') COME 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 si effettua in modo primitivo, cercando un punto.
Rapporti
Nei sistemi di visualizzazione complessi è possibile creare un metamodello basato su oggetti di database, gestire dinamicamente filtri e regole di aggregazione. In definitiva, tutti gli strumenti decenti generano una query SQL.
In questo esempio, creeremo query SQL già pronte e le salveremo come visualizzazioni nel database: questi sono report.
Visualizzazione
Bluff: come strumento di visualizzazione sono stati utilizzati bellissimi grafici in JavaScript
Per fare ciò è stato necessario esaminare tutti i report utilizzando PHP 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 tabelle di risultati.
conclusione
Utilizzando l'analisi web come esempio, l'articolo descrive i meccanismi necessari per costruire data warehouse. Come si può vedere dai risultati, gli strumenti più semplici sono sufficienti per un’analisi approfondita e la visualizzazione dei dati.
In futuro, usando questo repository come esempio, proveremo a implementare strutture come dimensioni, metadati, livelli di aggregazione che cambiano lentamente e integrazione di dati provenienti da diverse fonti.
Inoltre, diamo uno sguardo più da vicino allo strumento più semplice per la gestione dei processi ETL basati su un'unica tabella.
Torniamo al tema della misurazione della qualità dei dati e dell'automazione di questo processo.
Studieremo i problemi dell'ambiente tecnico e della manutenzione degli archivi di dati, per i quali implementeremo un server di archiviazione con risorse minime, ad esempio, basato su un Raspberry Pi.
Fonte: habr.com