Утилитата 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 заявка за отчет
SELECT
1 AS 'Таблица: Натрапчиви Ботове',
MAX(USG.AGENT_BOT) AS 'Бот',
ROUND(SUM(FCT.BYTES)\/1000\/14.0, 1) AS 'КБ на Ден',
ROUND(SUM(FCT.IP_CNT)\/14.0, 1) AS 'IP адреси на Ден',
ROUND(SUM(CASE WHEN STS.STATUS_GROUP IN ('Client Error', 'Server Error') THEN FCT.REQUEST_CNT\/14.0 ELSE 0 END), 1) AS 'Грешки на Заявки на Ден',
ROUND(SUM(CASE WHEN STS.STATUS_GROUP IN ('Successful', 'Redirection') THEN FCT.REQUEST_CNT\/14.0 ELSE 0 END), 1) AS 'Успешни Заявки на Ден',
USG.USER_AGENT_NK AS 'Агент'
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 'Ред: Ден и час на посещения от потребители и ботове',
strftime('%d.%m-%H', datetime(EVENT_DT, 'unixepoch')) AS 'Дата Време',
HIB AS 'Ботове, Посещения',
HIU AS 'Потребители, Посещения'
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 'Ред: Средна дневна активност на потребителите по седмица',
strftime('%W week', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Седмица',
ROUND(1.0*SUM(FCT.PAGE_CNT)/SUM(FCT.IP_CNT),1) AS 'Страници на IP на ден',
ROUND(1.0*SUM(FCT.FILE_CNT)/SUM(FCT.IP_CNT),1) AS 'Файлове на IP на ден'
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.' /* само потребители */
AND HST.STATUS_GROUP IN ('Успешен') /* добри страници */
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 'Ред: Всички заявки по статус',
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: Красиви графики в 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
