La utilidad Webalizer y la herramienta Google Analytics me han ayudado durante muchos años a tener una idea de lo que está sucediendo en los sitios web. Ahora entiendo que proporcionan muy poca información útil. Tener acceso a su archivo access.log, manejar estadísticas es muy simple y para la implementación de herramientas bastante básicas como sqlite, html, sql y cualquier lenguaje de programación de secuencias de comandos.
La fuente de datos para Webalizer es el archivo access.log del servidor. Así es como se ven sus columnas y números, de los cuales solo la cantidad total de tráfico es clara:
Herramientas como Google Analytics recopilan datos de la página cargada por su cuenta. Nos muestran un par de diagramas y líneas, en base a los cuales a menudo es difícil sacar las conclusiones correctas. ¿Quizás se necesitaba más esfuerzo? No lo se
Entonces, ¿qué quería ver en las estadísticas de visitas al sitio?
Tráfico de usuarios y bot
A menudo, el tráfico del sitio tiene un límite y usted necesita ver cuánto tráfico útil se usa. Por ejemplo, así:
Solicitud de informe SQLSELECT 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!='na' THEN FCT.BYTES ELSE 0 END)/1000 AS 'Bots, KB', SUM(CASE WHEN USG.AGENT_BOT='na' 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
Desde el gráfico puedes ver la actividad constante de los bots. Sería interesante estudiar en detalle los representantes más activos.
Bots molestos
Clasificamos los bots según la información del agente de usuario. Las estadísticas adicionales sobre el tráfico diario, el número de solicitudes exitosas y no exitosas dan una buena idea sobre la actividad de los bots.
Solicitud de informe 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 != 'na' AND datetime(FCT.EVENT_DT, 'unixepoch') >= date('now', '-14 day') GROUP BY USG.USER_AGENT_NK ORDER BY 3 DESC LIMIT 10
En este caso, el análisis resultó en una decisión de restringir el acceso al sitio agregando robots.txt al archivo
User-agent: AhrefsBot
Disallow: /
User-agent: dotbot
Disallow: /
User-agent: bingbot
Crawl-delay: 5Los primeros dos bots desaparecieron de la mesa, y los robots MS se movieron hacia abajo desde las primeras líneas.
Día y hora de la mayor actividad.
El tráfico se muestra. Para estudiarlos en detalle, es necesario identificar la hora de su ocurrencia, mientras que no es necesario mostrar todas las horas y días de medición de tiempo. Por lo tanto, será más fácil encontrar consultas individuales en el archivo de registro si necesita un análisis detallado.
Solicitud de informe 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!='na' THEN LINE_CNT ELSE 0 END) AS HIB, SUM(CASE WHEN AGENT_BOT='na' 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
Observamos las horas más activas 11, 14 y 20 del primer día en el gráfico. Pero al día siguiente a las 13 en punto los bots estaban activos.
Promedio semanal de actividad diaria del usuario
Con actividad y tráfico un poco resuelto. La siguiente pregunta fue la actividad de los propios usuarios. Para tales estadísticas, son deseables grandes períodos de agregación, por ejemplo, una semana.
Solicitud de informe 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='na' AND HST.STATUS_GROUP IN ('Successful') 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
Las estadísticas de la semana muestran que, en promedio, un usuario abre 1.6 páginas por día. La cantidad de archivos solicitados por usuario en este caso depende de agregar nuevos archivos al sitio.
Todas las solicitudes y sus estados.
Webalizer siempre mostró códigos de página específicos y siempre quiso ver solo la cantidad de solicitudes exitosas y errores.
Solicitud de informe 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_DT
El informe muestra solicitudes, no clics (hits), a diferencia de LINE_CNT, la métrica REQUEST_CNT se considera COUNT (DISTINCT STG.REQUEST_NK). El objetivo es mostrar eventos efectivos, por ejemplo, los bots de MS cientos de veces al día sondeando un archivo robots.txt y, en este caso, dichas encuestas se contarán una vez. Esto le permite suavizar los saltos en el gráfico.
En el gráfico puede ver muchos errores: estas son páginas inexistentes. El resultado del análisis fue la adición de redireccionamientos desde páginas remotas.
Solicitudes erróneas
Para una revisión detallada de las solicitudes, puede mostrar estadísticas detalladas.
Solicitud de informe 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 20
Esta lista contendrá todos los marcadores, por ejemplo, una solicitud a /wp-login.php. Al ajustar las reglas para reescribir las solicitudes del servidor, puede ajustar la respuesta del servidor a dichas solicitudes y enviarlas a la página de inicio.
Por lo tanto, algunos informes simples basados en el archivo de registro del servidor ofrecen una imagen bastante completa de lo que está sucediendo en el sitio.
¿Cómo obtener información?
La base de datos sqlite es suficiente. Creemos tablas: auxiliares para el registro de procesos ETL.
Tabla de escenario, donde escribiremos archivos de registro usando PHP. Dos tablas agregadas. Cree una tabla diaria con estadísticas sobre agentes de usuario y estados de solicitud. Cada hora con estadísticas sobre solicitudes, grupos de estado y agentes. Cuatro tablas de medidas relevantes.
El resultado es el siguiente modelo relacional:
Script para crear un objeto en la base de datos sqlite:
Creación de objetos 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 'na', AGENT_OS TEXT NOT NULL DEFAULT 'na', AGENT_ENGINE TEXT NOT NULL DEFAULT 'na', AGENT_DEVICE TEXT NOT NULL DEFAULT 'na', AGENT_BOT TEXT NOT NULL DEFAULT 'na', 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 caso del archivo access.log, debe leer, analizar y escribir todas las solicitudes en la base de datos. Esto se puede hacer directamente usando el lenguaje de scripting o usando sqlite.
Formato de archivo de registro:
Propaganda clave
Cuando los datos sin procesar están en la base de datos, debe registrar las claves que no están en las tablas de medidas. Entonces será posible construir una referencia a las mediciones. Por ejemplo, en la tabla DIM_REFERRER, la clave es una combinación de tres campos.
Consulta de propagación de clave SQL 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) 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
La propagación a la tabla de agente de usuario puede contener lógica de bot, por ejemplo, un extracto de 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 'na' END AS AGENT_BOT
Tablas de unidades
Por último, cargaremos tablas agregadas, por ejemplo, una tabla diaria se puede cargar de la siguiente manera:
Solicitud de carga agregada de SQL 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 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
La base de datos sqlite le permite escribir consultas complejas. WITH contiene la preparación de datos y claves. La consulta principal recopila todas las referencias a dimensiones.
La condición no permitirá cargar la historia nuevamente: CAST (STG.EVENT_DT AS INTEGER)> $ param_epoch_from, donde el parámetro es el resultado de la solicitud
'SELECCIONAR COALESCE (MAX (EVENT_DT), \' 3600 \ ') COMO LAST_EVENT_EPOCH FROM FCT_ACCESS_USER_AGENT_DD'
La condición cargará solo el día completo: CAST (STG.EVENT_DT AS INTEGER) <strftime ('% s', date ('now', 'start of day'))
El recuento de páginas o archivos se realiza de manera primitiva, buscando un punto.
Informes
En sistemas de visualización complejos, es posible crear un metamodelo basado en objetos de base de datos, administrar dinámicamente filtros y reglas de agregación. En definitiva, todas las herramientas decentes generan una consulta SQL.
En este ejemplo, crearemos consultas SQL listas para usar y las guardaremos como una vista en la base de datos; estos son los informes.
Visualización
Bluff: Se usaron gráficos hermosos en JavaScript como herramienta de visualización.
Para esto, era necesario usar PHP para revisar todos los informes y generar un archivo html con tablas.
$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' );
La herramienta simplemente visualiza las tablas de resultados.
Conclusión
Utilizando el análisis web como ejemplo, el artículo describe los mecanismos necesarios para construir almacenes de datos. Como se puede ver en los resultados, las herramientas más simples son suficientes para el análisis en profundidad y la visualización de datos.
En el futuro, utilizando el ejemplo de este almacenamiento, intentaremos implementar estructuras tales como
mediciones que cambian lentamente , datos maestros, niveles de agregación e integración de datos de diferentes fuentes.
Además, veremos más de cerca la
herramienta de administración de procesos ETL más simple basada en una tabla.
Volvamos al tema de medir la calidad de los datos y automatizar este proceso.
Estudiaremos los problemas del entorno técnico y el mantenimiento de los almacenes de datos, para lo cual implementamos un servidor de almacenamiento con recursos mínimos, por ejemplo, basado en la Raspberry Pi.