🥇Статистика на сайта и малък архив

Утилитата Webalizer и инструментът Google Analytics ми помогнаха много години да получавам представа за това, какво се случва на уебсайтовете. В момента разбирам, че те дават много малко полезна информация. С достъпа до своя файл access.log, разбирането на статистиката е много просто и за реализацията са нужни само елементарни инструменти като sqlite, html, sql и всеки скриптов език за програмиране.

Източникът на данни за Webalizer е файлът access.log сървър. Така изглеждат колоните и цифрите, от които се разбира само общият обем на трафика:

Инструменти като Google Analytics събират данни от заредената страница сами. Показват ни няколко диаграми и линии, на базата на които често е трудно да се направят правилни изводи. Може би трябваше да положа повече усилия? Не знам.

И така, какво исках да видя в статистиката за посещаемостта на сайта?

Трафикът на потребителите и ботовете

Често трафикът на сайтовете е ограничен и е необходимо да виждаме колко полезен трафик се използва. Например, така:

SQL заявка за отчет

SELECT
1 as 'StackedArea: Трафик, генериран от потребители и ботове',
strftime('%d.%m', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Ден',
SUM(CASE WHEN USG.AGENT_BOT!='n.a.' THEN FCT.BYTES ELSE 0 END) / 1000 AS 'Ботове, КБ',
SUM(CASE WHEN USG.AGENT_BOT='n.a.' THEN FCT.BYTES ELSE 0 END) / 1000 AS 'Потребители, КБ'
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 заявка за отчет

ИЗБИРАТЕЛ 
1 КАТО 'Таблица: Досадни ботове',
MAX(USG.AGENT_BOT) КАТО 'Бот',
ROUND(SUM(FCT.BYTES) / 1000 / 14.0, 1) КАТО 'KB на ден',
ROUND(SUM(FCT.IP_CNT) / 14.0, 1) КАТО 'IPs на ден',
ROUND(SUM(CASE WHEN STS.STATUS_GROUP IN ('Грешка на клиента', 'Грешка на сървъра') ТЕ ПРЕДСТАВЛЯВАТ FCT.REQUEST_CNT / 14.0 ELSE 0 END), 1) КАТО 'Грешни заявки на ден',
ROUND(SUM(CASE WHEN STS.STATUS_GROUP IN ('Успешно', 'Пренасочване') ТЕ ПРЕДСТАВЛЯВАТ FCT.REQUEST_CNT / 14.0 ELSE 0 END), 1) КАТО 'Успешни заявки на ден',
USG.USER_AGENT_NK КАТО 'Агент' 
ОТ FCT_ACCESS_USER_AGENT_DD FCT,
     DIM_USER_AGENT USG,
     DIM_HTTP_STATUS STS 
КЪДЕТО FCT.DIM_USER_AGENT_ID = USG.DIM_USER_AGENT_ID
  И FCT.DIM_HTTP_STATUS_ID = STS.DIM_HTTP_STATUS_ID
  И USG.AGENT_BOT != 'n.a.'
  И datetime(FCT.EVENT_DT, 'unixepoch') >= date('now', '-14 days')
ГРУПИРАНЕ ПО USG.USER_AGENT_NK
ПОРЪЧАЙ ПО 3 НАМАЛЯВАЩО
LIMIT 10

В този случай резултатът от анализа беше решение за ограничаване на достъпа до сайта чрез добавяне в файла robots.txt

Потребителски агент: AhrefsBot Не позволявай: / Потребителски агент: dotbot Не позволявай: / Потребителски агент: bingbot Забавяне на индексирането: 5
Първите два бота изчезнаха от таблицата, а ботовете на MS отстъпиха от първите позиции надолу.

Денят и времето на най-голяма активност

В трафика се виждат повишения. За да ги проучите детайлно, е необходимо да се отдели време на тяхното възникване, като не е задължително да се показват всички часове и дни на измерване. Тази процедура улеснява откритията на отделни заявки в лог файла в случай на необходимост от детайлен анализ.

SQL заявка за отчет

ИЗБЕРЕТЕ
1 КАТО 'Ред: Ден и Час на Вхождения от Потребители и Ботов',
strftime('%d.%m-%H', datetime(EVENT_DT, 'unixepoch')) КАТО 'Дата Час',
HIB КАТО 'Ботове, Вхождения',
HIU КАТО 'Потребители, Вхождения'
ОТ (
	ИЗБЕРЕТЕ
	EVENT_DT,
	SUM(CASE WHEN AGENT_BOT!='n.a.' THEN LINE_CNT ELSE 0 END) КАТО HIB,
	SUM(CASE WHEN AGENT_BOT='n.a.' THEN LINE_CNT ELSE 0 END) КАТО HIU
	ОТ FCT_ACCESS_REQUEST_REF_HH
	КЪДЕТО datetime(EVENT_DT, 'unixepoch') >= date('now', '-14 day')
	ГРУПИРАЙ ПО EVENT_DT
	НАРЕДИ ПО SUM(LINE_CNT) DESC
	LIMIT 10
) НАРЕДИ ПО EVENT_DT

Наблюдаваме най-активните часове 11, 14 и 20 на първия ден на графика. На следващия ден в 13 часа ботовете бяха активни.

Средна дневна активност на потребителите по седмици

Със се активността и трафика малко се изяснихме. Следващият въпрос беше активността на самите потребители. За такава статистика са желателни по-дълги периоди на агрегация, например, седмица.

SQL заявка за отчет

ИЗБОР
1 КАТО 'Ред: Средна Дневна Активност на Потребителите по Седмици',
strftime('%W week', datetime(FCT.EVENT_DT, 'unixepoch')) КАТО 'Седмица',
ROUND(1.0*SUM(FCT.PAGE_CNT) / SUM(FCT.IP_CNT), 1) КАТО 'Страници на IP на Ден',
ROUND(1.0*SUM(FCT.FILE_CNT) / SUM(FCT.IP_CNT), 1) КАТО 'Файлове на IP на Ден'
ОТ
  FCT_ACCESS_USER_AGENT_DD FCT,
  DIM_USER_AGENT USG,
  DIM_HTTP_STATUS HST
КЪДЕТО FCT.DIM_USER_AGENT_ID=USG.DIM_USER_AGENT_ID
  И FCT.DIM_HTTP_STATUS_ID = HST.DIM_HTTP_STATUS_ID
  И USG.AGENT_BOT='n.a.' /* само потребители */
  И HST.STATUS_GROUP IN ('Успешен') /* добри страници */
  И datetime(FCT.EVENT_DT, 'unixepoch') > date('now', '-3 month')
ГРУПИРАЙ ПО strftime('%W week', datetime(FCT.EVENT_DT, 'unixepoch'))
НАРЕДИ ПО FCT.EVENT_DT

Статистиката за седмицата показва, че средно един потребител отваря 1,6 страници на ден. Броят на поисканите файлове на един потребител в този случай зависи от добавянето на нови файлове на сайта.

Всички заявки и техните статуси

Webalizer винаги е показвал конкретни кодове на страниците и аз винаги съм искал да виждам просто количеството на успешни заявки и грешки.

SQL заявка за отчет

SELECT
1 as 'Ред: Всички заявки по статус',
strftime('%d.%m', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Ден',
SUM(CASE WHEN STS.STATUS_GROUP='Успешни' THEN FCT.REQUEST_CNT ELSE 0 END) AS 'Успех',
SUM(CASE WHEN STS.STATUS_GROUP='Пренасочване' THEN FCT.REQUEST_CNT ELSE 0 END) AS 'Пренасочване',
SUM(CASE WHEN STS.STATUS_GROUP='Грешка на клиента' THEN FCT.REQUEST_CNT ELSE 0 END) AS 'Грешка на клиента',
SUM(CASE WHEN STS.STATUS_GROUP='Грешка на сървъра' THEN FCT.REQUEST_CNT ELSE 0 END) AS 'Грешка на сървъра'
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 'Таблица: Най-чести грешни заявки',
  REQ.REQUEST_NK AS 'Заявка',
  'Грешка' AS 'Статус на заявка',
  ROUND(SUM(FCT.LINE_CNT) / 14.0, 1) AS 'Посещения на ден',
  ROUND(SUM(FCT.IP_CNT) / 14.0, 1) AS 'IP адреси на ден',
  ROUND(SUM(FCT.BYTES) / 1000 / 14.0, 1) AS 'KB на ден'
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 ('Грешка от клиент', 'Грешка от сървър')
  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