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

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:XNUMX часот беа активни ботови.

Просечна дневна корисничка активност по недела

Малку ги средиме работите со активност и сообраќај. Следното прашање беше активноста на самите корисници. За таква статистика, пожелни се долги периоди на агрегација, како една недела.

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, каде што параметарот е резултат на барањето
„ИЗБЕРЕТЕ СОЈУВАЊЕ (MAX(EVENT_DT), „3600“) КАКО ПОСЛЕДЕН_EVENT_EPOCH ОД FCT_ACCESS_USER_AGENT_DD“

Условот ќе се вчита само цел ден: CAST(STG.EVENT_DT КАКО КОЛКУ КОЛКУ) < strftime('%s', датум ('сега', 'почеток на денот'))

Броењето страници или датотеки се врши на примитивен начин, со пребарување на точка.

Извештаи

Во сложените системи за визуелизација, можно е да се создаде мета-модел базиран на објекти на базата на податоци, динамички да се управува со филтри и правила за собирање. На крајот на краиштата, сите пристојни алатки генерираат SQL барање.

Во овој пример, ќе создадеме готови SQL барања и ќе ги зачуваме како прегледи во базата на податоци - тоа се извештаи.

Визуелизација

Блеф: Прекрасни графикони во 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.

Извор: www.habr.com