Статистика сайту та своє маленьке сховище

Утиліта Webalizer та інструмент Google Analytics допомагали мені багато років отримувати уявлення про те, що відбувається на веб-сайтах. Наразі я розумію, що вони дають дуже мало корисної інформації. Маючи доступ до свого файлу access.log, розібратися зі статистикою дуже просто і для реалізації досить елементарних інструментів, таких як sqlite, html, sql і будь-якої скриптової мови програмування.

Джерелом даних Webalizer є файл access.log сервера. Так виглядають його стовпчики та цифри, з яких зрозумілий лише загальний обсяг трафіку:

Такі інструменти, як Google Analytics, збирають дані із завантаженої сторінки самостійно. Відображають нам пару діаграм та ліній, на основі яких часто складно зробити правильні висновки. Можливо, треба було докласти більше зусиль? Не знаю.

Отже, що мені хотілося побачити у статистиці відвідин сайту?

Трафік користувачів та ботів

Часто трафік сайтів має обмеження та необхідно бачити, скільки корисного трафіку використовується. Наприклад, так:

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_DT

З графіка видно постійна активність роботів. Цікаво було б детально вивчити найактивніших представників.

Настирливі боти

Класифікуємо ботів на основі інформації користувача агента. Додаткова статистика про денний трафік, кількість успішних і безуспішних запитів дає гарне уявлення про активність ботів.

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 10

В даному випадку результатом аналізу стало рішення про обмеження доступу до сайту шляхом додавання файлу robots.txt

User-agent: AhrefsBot
Disallow: /
User-agent: dotbot
Disallow: /
User-agent: bingbot
Crawl-delay: 5
Перші два боти зникли з таблиці, а роботи MS посунулися з перших рядків донизу.

День та час найбільшої активності

У трафіку видно підйоми. Щоб детально їх дослідити, необхідно виділити час їх виникнення, при цьому не обов'язково відображати всі години та дні вимірювання часу. Так буде простіше знайти окремі запити в лог файлі за необхідності детального аналізу.

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_DT

Спостерігаємо найактивніший годинник 11, 14 і 20 першого дня на графіку. А ось наступного дня о 13 годині активнічали боти.

Середня денна активність користувачів по тижнях

З активністю та трафіком трохи розібралися. Наступним питанням була активність користувачів. Для такої статистики бажані великі періоди агрегації, наприклад тиждень.

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_DT

Статистика за тиждень показує, що в середньому один користувач відкриває 1,6 сторінки на день. Кількість файлів, що запитуються, на одного користувача в даному випадку залежить від додавання нових файлів на сайт.

Всі запити та їх статуси

Webalizer завжди показував конкретні коди сторінок і хотілося завжди бачити просто кількість успішних запитів та помилок.

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

Звіт відображає запити, а не кліки (хіти), на відміну від LINE_CNT метрика REQUEST_CNT вважається COUNT(DISTINCT STG.REQUEST_NK). Мета — показати ефективні події, наприклад, боти MS сотні щодня опитують файл robots.txt і, у разі, такі опитування будуть пораховані один раз. Це дозволяє згладити стрибки на графіку.

З графіка можна побачити багато помилок – це неіснуючі сторінки. Результатом аналізу стало додавання перенаправлень із віддалених сторінок.

Помилкові запити

Для детального розгляду запитів можна навести детальну статистику.

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

У цьому списку будуть і всі продзвони, наприклад, запит до /wp-login.php Шляхом коригування правил переписування запитів сервером можна скоригувати реакцію сервера на подібні запити та надсилати їх на стартову сторінку.

Отже, кілька простих звітів на основі файлу лога сервера дають досить повну картину того, що відбувається на сайті.

Як отримати інформацію?

Бази даних sqlite цілком достатньо. Створимо таблиці: допоміжну для логування процесів ETL.

Стейдж таблиці, куди писатимемо лог файли засобами PHP. Дві таблиці агрегатів. Створимо денну таблицю зі статистикою за користувальницькими агентами та статусами запитів. Погодинну зі статистикою за запитами, групами статусів та агентів. Чотири таблиці відповідних вимірів.

В результаті вийшла наступна реляційна модель:

Модель даних

Скрипт для створення об'єкта в базі даних sqlite:

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);

Стейдж

У випадку з access.log файлом необхідно прочитати, розпарсувати та записати до бази всі запити. Це можна зробити безпосередньо засобами скриптової мови, або використовуючи засоби sqlite.

Формат лог файлу:

//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-]+) "(.*)" "(.*)"$/';

Пропагування ключів

Коли сирі дані перебувають у базі, потрібно записати у таблиці вимірів ключі, яких немає. Тоді буде можливою побудова посилання на вимірювання. Наприклад, у таблиці DIM_REFERRER ключем є комбінація трьох полів.

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 NULL

Пропагація в таблицю користувача агентів може містити логіку ботів, наприклад, уривок 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_BOT

Таблиці агрегатів

В останню чергу вантажитимемо таблиці агрегатів, наприклад, денна таблиця може завантажуватися наступним чином:

SQL запит завантаження агрегату

/* 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_ID

База даних SQLite дозволяє писати складні запити. WITH містить підготовку даних та ключів. Основний запит збирає всі посилання на виміри.

Умова не дасть ще раз завантажити історію: CAST(STG.EVENT_DT AS INTEGER) > $param_epoch_from, де параметр є результатом запиту
'SELECT COALESCE(MAX(EVENT_DT), '3600') AS LAST_EVENT_EPOCH FROM FCT_ACCESS_USER_AGENT_DD'

Умова завантажить тільки повний день: CAST(STG.EVENT_DT AS INTEGER) < strftime('%s', date('now', 'start of day'))

Підрахунок сторінок чи файлів здійснюється примітивним способом, пошуком точки.

Звіти

У складних системах візуалізації є можливість створювати мета-модель на основі об'єктів бази даних, динамічно керувати фільтрами та правилами агрегації. Зрештою, всі пристойні інструменти генерують SQL запит.

В даному прикладі ми створимо вже готові SQL запити і збережемо їх у вигляді в'ю в базі даних - це звіти.

Візуалізація

Як інструмент візуалізації використовувався Bluff: Beautiful graphs in JavaScript

Для цього потрібно за допомогою PHP пробігтися всіма репортами і згенерувати html файл з таблицями.

$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'
);

Інструмент просто візуалізує таблиці результатів.

Висновок

На прикладі Інтернет аналізу стаття визначає механізми, необхідні для побудови сховищ даних. Як видно з результатів, для глибокого аналізу та візуалізації даних досить найпростіших інструментів.

Надалі, на прикладі цього сховища, спробуємо реалізувати такі структури, як вимірювання, що повільно змінюються, метадані, рівні агрегації та інтеграцію даних з різних джерел.

Докладніше розглянемо найпростіший інструмент управління ETL процесами на основі однієї таблиці.

Повернемося до теми вимірювання якості даних та автоматизації цього процесу.

Вивчимо проблеми технічного оточення та обслуговування сховищ даних, для чого реалізуємо сервер сховища з мінімальними ресурсами, наприклад, на базі Raspberry Pi.

Джерело: habr.com