Webalizer i Google Analytics m'han ajudat a conèixer el que passa als llocs web durant molts anys. Ara entenc que proporcionen molt poca informació útil. Tenint accés al vostre fitxer access.log, és molt fàcil entendre les estadístiques i implementar eines força bàsiques, com ara sqlite, html, el llenguatge sql i qualsevol llenguatge de programació d'scripts.
La font de dades de Webalizer és el fitxer access.log del servidor. Així es veuen les seves barres i números, dels quals només queda clar el volum total de trànsit:
Eines com Google Analytics recullen dades de la pàgina carregada. Ens mostren un parell d'esquemes i línies, a partir dels quals sovint és difícil treure conclusions correctes. Potser s'hauria d'haver fet més esforç? No ho sé.
Aleshores, què volia veure a les estadístiques de visitants del lloc web?
Trànsit d'usuaris i bots
Sovint el trànsit del lloc és limitat i cal veure quant de trànsit útil s'utilitza. Per exemple, així:
Consulta d'informes 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_DTEl gràfic mostra l'activitat constant dels robots. Seria interessant estudiar amb detall els representants més actius.
Bots molestos
Classifiquem els bots en funció de la informació de l'agent d'usuari. Les estadístiques addicionals sobre el trànsit diari, el nombre de sol·licituds reeixides i no reeixides donen una bona idea de l'activitat del bot.
Consulta d'informes 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 10En aquest cas, el resultat de l'anàlisi va ser la decisió de restringir l'accés al lloc afegint-lo al fitxer robots.txt
User-agent: AhrefsBot
Disallow: /
User-agent: dotbot
Disallow: /
User-agent: bingbot
Crawl-delay: 5
Els dos primers robots van desaparèixer de la taula i els robots MS van baixar des de les primeres línies.
Dia i hora de major activitat
Els augments són visibles al trànsit. Per estudiar-los en detall, cal destacar l'hora de la seva aparició, i no cal mostrar totes les hores i dies de mesura del temps. Això farà que sigui més fàcil trobar sol·licituds individuals al fitxer de registre si es necessita una anàlisi detallada.
Consulta d'informes 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_DTObservem les hores més actives 11, 14 i 20 del primer dia al gràfic. Però l'endemà a les 13:XNUMX els bots estaven actius.
Activitat mitjana diària dels usuaris per setmana
Hem arreglat una mica les coses amb l'activitat i el trànsit. La següent pregunta va ser l'activitat dels mateixos usuaris. Per a aquestes estadístiques, són desitjables períodes d'agregació llargs, com ara una setmana.
Consulta d'informes 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_DTLes estadístiques setmanals mostren que de mitjana un usuari obre 1,6 pàgines al dia. El nombre de fitxers sol·licitats per usuari en aquest cas depèn de l'addició de fitxers nous al lloc.
Totes les sol·licituds i els seus estats
Webalizer sempre mostrava codis de pàgina específics i sempre volia veure només el nombre de sol·licituds i errors reeixits.
Consulta d'informes 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_DTL'informe mostra les sol·licituds, no els clics (accets), a diferència de LINE_CNT, la mètrica REQUEST_CNT es calcula com a COUNT(DISTINCT STG.REQUEST_NK). L'objectiu és mostrar esdeveniments efectius, per exemple, els robots de MS enquesten el fitxer robots.txt centenars de vegades al dia i, en aquest cas, aquestes enquestes es comptaran una vegada. Això us permet suavitzar els salts al gràfic.
Al gràfic es poden veure molts errors: són pàgines inexistents. El resultat de l'anàlisi va ser l'addició de redireccions des de pàgines remotes.
Peticions dolentes
Per examinar les sol·licituds en detall, podeu mostrar estadístiques detallades.
Consulta d'informes 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 20Aquesta llista també inclourà totes les trucades, per exemple, una sol·licitud a /wp-login.php En ajustar les regles de reescriptura de sol·licituds del servidor, podeu ajustar la reacció del servidor a aquestes sol·licituds i enviar-les a la pàgina d'inici.
Per tant, uns quants informes senzills basats en el fitxer de registre del servidor donen una imatge força completa del que està passant al lloc.
Com obtenir informació?
Una base de dades sqlite és suficient. Creem taules: auxiliar per registrar processos ETL.
Etapa de taula on escriurem fitxers de registre amb PHP. Dues taules agregades. Creem una taula diària amb estadístiques d'agents d'usuari i estats de sol·licitud. Cada hora amb estadístiques de sol·licituds, grups d'estat i agents. Quatre taules de mesures rellevants.
El resultat és el següent model relacional:
Model de dades
Script per crear un objecte en una base de dades sqlite:
Creació d'objectes 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);Etapa
En el cas del fitxer access.log, cal llegir, analitzar i escriure totes les peticions a la base de dades. Això es pot fer directament amb un llenguatge de script o amb eines sqlite.
Format del fitxer de registre:
//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-]+) "(.*)" "(.*)"$/';
Propagació de claus
Quan les dades en brut es troben a la base de dades, heu d'escriure les claus que no hi són a les taules de mesura. Aleshores serà possible construir una referència a les mesures. Per exemple, a la taula DIM_REFERRER, la clau és una combinació de tres camps.
Consulta de propagació de claus 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 propagació a la taula de l'agent d'usuari pot contenir lògica de bot, per exemple, el fragment 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_BOTTaules agregades
Finalment, carregarem les taules agregades; per exemple, la taula diària es pot carregar de la següent manera:
Consulta SQL per carregar l'agregat
/* 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_IDLa base de dades sqlite us permet escriure consultes complexes. WITH conté la preparació de dades i claus. La consulta principal recull totes les referències a dimensions.
La condició no permetrà tornar a carregar l'historial: CAST(STG.EVENT_DT AS INTEGER) > $param_epoch_from, on el paràmetre és el resultat de la sol·licitud
'SELECT COALESCE(MAX(EVENT_DT), '3600') COM LAST_EVENT_EPOCH FROM FCT_ACCESS_USER_AGENT_DD'
La condició només carregarà el dia sencer: CAST(STG.EVENT_DT COM INTEGER) < strftime('%s', date('ara', 'inici del dia'))
El recompte de pàgines o fitxers es realitza de manera primitiva, buscant un punt.
Informes
En sistemes de visualització complexos, és possible crear un metamodel basat en objectes de base de dades, gestionar de manera dinàmica filtres i regles d'agregació. En definitiva, totes les eines decents generen una consulta SQL.
En aquest exemple, crearem consultes SQL ja fetes i les desarem com a vistes a la base de dades: aquests són informes.
Visualització
Bluff: es van utilitzar gràfics bonics en JavaScript com a eina de visualització
Per fer-ho, calia recórrer tots els informes mitjançant PHP i generar un fitxer html amb taules.
$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'eina simplement visualitza taules de resultats.
Sortida
Utilitzant l'anàlisi web com a exemple, l'article descriu els mecanismes necessaris per construir magatzems de dades. Com es pot veure en els resultats, les eines més senzilles són suficients per a l'anàlisi i visualització de dades en profunditat.
En el futur, utilitzant aquest dipòsit com a exemple, intentarem implementar estructures com les dimensions que canvien lentament, les metadades, els nivells d'agregació i la integració de dades de diferents fonts.
A més, fem una ullada més de prop a l'eina més senzilla per gestionar processos ETL basats en una única taula.
Tornem al tema de mesurar la qualitat de les dades i automatitzar aquest procés.
Estudiarem la problemàtica de l'entorn tècnic i el manteniment dels emmagatzematges de dades, per a això implementarem un servidor d'emmagatzematge amb recursos mínims, per exemple, basat en un Raspberry Pi.
Font: www.habr.com