Sayt statistikasi va o'zingizning kichik xotirangiz

Ko'p yillar davomida Webalizer va Google Analytics menga veb-saytlarda nima sodir bo'layotganini tushunishga yordam berdi. Endi men ular juda kam foydali ma'lumot berishlarini tushunaman. Access.log faylingizga kirish orqali statistikani tushunish juda oddiy va faqat SQLite, HTML, SQL va har qanday skript tili kabi asosiy vositalarni talab qiladi.

Webalizer uchun ma'lumotlar manbai access.log faylidir. serverUning ustunlari va raqamlari quyidagicha ko'rinadi, ulardan faqat umumiy trafik hajmi aniq ko'rinadi:

Google Analytics kabi vositalar yuklangan sahifadan ma'lumotlarni avtomatik ravishda to'playdi. Ular bizga bir nechta diagramma va chiziqlarni ko'rsatadilar, ulardan ko'pincha mazmunli xulosa chiqarish qiyin. Ehtimol, ko'proq harakat qilish kerak edi? Bilmadim.

Xo'sh, men saytga tashrif buyurish statistikasida nimani ko'rmoqchi edim?

Foydalanuvchi va bot trafiki

Veb-sayt trafiki ko'pincha cheklangan va qanchalik foydali trafik ishlatilayotganini ko'rish muhimdir. Masalan:

SQL hisobot so'rovi

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

Grafik doimiy bot faolligini ko'rsatadi. Eng faol vakillarni batafsil o'rganish qiziqarli bo'ladi.

Bezovta qiluvchi botlar

Biz botlarni foydalanuvchi agenti ma'lumotlari asosida tasniflaymiz. Kundalik trafik bo'yicha qo'shimcha statistika va muvaffaqiyatli va muvaffaqiyatsiz so'rovlar soni bot faoliyatini yaxshi tushunish imkonini beradi.

SQL hisobot so'rovi

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

Bunday holda, tahlil natijasi robots.txt fayliga qo'shish orqali saytga kirishni cheklash to'g'risida qaror qabul qilindi.

User-agent: AhrefsBot
Disallow: /
User-agent: dotbot
Disallow: /
User-agent: bingbot
Crawl-delay: 5
Dastlabki ikkita bot stoldan g'oyib bo'ldi va MS robotlari birinchi qatorlardan pastga siljidi.

Eng katta faollik kuni va vaqti

Yo'l harakati keskin ko'rinadi. Ularni batafsil ko'rib chiqish uchun ular sodir bo'lgan vaqtni ajratib ko'rsatish kerak, lekin vaqt o'lchovining har bir soati va kunini ko'rsatish shart emas. Agar batafsil tahlil zarur bo'lsa, bu jurnal faylida individual so'rovlarni topishni osonlashtiradi.

SQL hisobot so'rovi

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

Biz jadvalda eng faol soatlarni ko'ramiz: birinchi kuni 11, 14 va 20. Ammo ertasi kuni soat 13:00 da botlar faollashdi.

Hafta bo'yicha o'rtacha kunlik foydalanuvchi faolligi

Biz harakat va tirbandlikni biroz tartibga soldik. Keyingi savol foydalanuvchi faoliyatining o'zi edi. Bunday statistik ma'lumotlar uchun bir hafta kabi uzoqroq yig'ish davrlari maqsadga muvofiqdir.

SQL hisobot so'rovi

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

Haftalik statistika shuni ko'rsatadiki, o'rtacha bir foydalanuvchi kuniga 1,6 sahifa ochadi. Bu holda har bir foydalanuvchi uchun so'raladigan fayllar soni saytga yangi fayllar qo'shilishi bilan bog'liq.

Barcha so'rovlar va ularning holati

Webalizer har doim ma'lum sahifa kodlarini ko'rsatdi va men har doim muvaffaqiyatli so'rovlar va xatolar sonini ko'rishni xohlardim.

SQL hisobot so'rovi

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

Hisobot chertishlar (xitlar) emas, balki so'rovlarni ko'rsatadi. LINE_CNT dan farqli o'laroq, REQUEST_CNT ko'rsatkichi COUNT (DISTINCT STG.REQUEST_NK) sifatida hisoblanadi. Maqsad - samarali voqealarni ko'rsatish. Misol uchun, MS botlari robots.txt faylini kuniga yuzlab marta so'rashadi va bu holda bunday so'rovlar faqat bir marta hisobga olinadi. Bu grafikdagi har qanday tikanlarni yumshatishga yordam beradi.

Grafikda ko'plab xatolar ko'rsatilgan - bular mavjud bo'lmagan sahifalar. Tahlil natijasida o'chirilgan sahifalardan qayta yo'naltirishlar qo'shildi.

Noto'g'ri so'rovlar

So'rovlarni batafsil tekshirish uchun batafsil statistik ma'lumotlarni ko'rsatish mumkin.

SQL hisobot so'rovi

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

Ushbu ro'yxatda barcha qo'ng'iroqlar, masalan, /wp-login.php manziliga so'rov ham bo'ladi. So'rovni qayta yozish qoidalarini sozlash orqali. server Siz serverning bunday so'rovlarga javobini sozlashingiz va ularni boshlang'ich sahifaga yuborishingiz mumkin.

Shunday qilib, server jurnali fayliga asoslangan bir nechta oddiy hisobotlar saytda sodir bo'layotgan voqealar haqida to'liq tasavvur beradi.

Ma'lumotni qanday olish mumkin?

SQLite ma'lumotlar bazasi etarli. ETL jarayonlarini jurnalga kiritish uchun yordamchi jadvallar yarataylik.

PHP yordamida jurnal fayllarini yozadigan bosqich jadvallari. Ikki jamlangan jadval. Biz foydalanuvchi agenti va soʻrov holati boʻyicha statistik maʼlumotlardan iborat kundalik jadval yaratamiz. So'rov, status guruhlari va agentlar bo'yicha statistik ma'lumotlarga ega soatlik jadval. Tegishli o'lchamlarga ega to'rtta jadval.

Olingan munosabatlar modeli quyidagicha edi:

Ma'lumotlar modeli

Sqlite ma'lumotlar bazasida ob'ekt yaratish uchun skript:

DDL ob'ektini yaratish

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

Bosqich

Access.log faylida barcha so'rovlar o'qilishi, tahlil qilinishi va ma'lumotlar bazasiga yozilishi kerak. Bu to'g'ridan-to'g'ri skript tili yoki SQLite yordamida amalga oshirilishi mumkin.

Jurnal fayl formati:

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

Kalitlarning tarqalishi

Xom ma'lumotlar ma'lumotlar bazasiga kiritilgandan so'ng, u erda mavjud bo'lmagan o'lchov jadvallariga kalitlarni qo'shishingiz kerak. Bu o'lchamlarga havolalar yaratish imkonini beradi. Masalan, DIM_REFERRER jadvalida kalit uchta maydonning kombinatsiyasi hisoblanadi.

Kalitlarni tarqatish uchun SQL so'rovi

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

Foydalanuvchi agenti jadvaliga tarqalish bot mantig'ini o'z ichiga olishi mumkin, masalan, SQL fragmenti:

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

Birliklar jadvallari

Nihoyat, biz jamlangan jadvallarni yuklaymiz; Masalan, kunlik jadvalni quyidagicha yuklash mumkin:

Agregatni yuklash uchun SQL so'rovi

/* 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 ma'lumotlar bazasi murakkab so'rovlarni yozish imkonini beradi. WITH ma'lumotlar va asosiy tayyorgarlikni o'z ichiga oladi. Asosiy so'rov barcha o'lchov havolalarini to'playdi.

Shart tarixni qayta yuklashga ruxsat bermaydi: CAST(STG.EVENT_DT AS INTEGER) > $param_epoch_from, bunda parametr so‘rov natijasidir
'FCT_ACCESS_USER_AGENT_DDDAN SO'NGI_EVENT_DAVRAN OLIB COALESCE(MAX(EVENT_DT), '3600') TANLANING'

Shart faqat butun kunni yuklaydi: CAST(STG.EVENT_DT AS INTEGER) < strftime('%s', sana('hozir', 'kun boshi'))

Sahifalar yoki fayllarni hisoblash ibtidoiy usulda, nuqtani qidirish orqali amalga oshiriladi.

Hisobotlar

Murakkab vizualizatsiya tizimlari ma'lumotlar bazasi ob'ektlari asosida meta-model yaratish va filtrlar va yig'ish qoidalarini dinamik ravishda boshqarish imkoniyatini taklif qiladi. Oxir oqibat, barcha obro'li vositalar SQL so'rovini yaratadi.

Ushbu misolda biz tayyor SQL so'rovlarini yaratamiz va ularni ma'lumotlar bazasida ko'rinish sifatida saqlaymiz - bu hisobotlar.

Vizualizatsiya

Bluff: JavaScript-dagi chiroyli grafiklar vizualizatsiya vositasi sifatida ishlatilgan.

Buning uchun biz barcha hisobotlarni ko'rib chiqish va jadvallar bilan HTML faylni yaratish uchun PHP dan foydalanishimiz kerak edi.

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

Asbob shunchaki natijalar jadvallarini ingl.

xulosa

Misol sifatida veb-tahlildan foydalangan holda, maqolada ma'lumotlar omborlarini qurish uchun zarur bo'lgan mexanizmlar tasvirlangan. Natijalar shuni ko'rsatadiki, hatto eng oddiy vositalar ham ma'lumotlarni chuqur tahlil qilish va vizualizatsiya qilish uchun etarli.

Kelajakda ushbu saqlashni misol sifatida ishlatib, biz asta-sekin o'zgaruvchan o'lchamlar, metadata, yig'ish darajalari va turli manbalardan olingan ma'lumotlarni integratsiyalash kabi tuzilmalarni amalga oshirishga harakat qilamiz.

Shuningdek, biz bitta jadval asosida ETL jarayonlarini boshqarishning eng oddiy vositasini batafsil ko'rib chiqamiz.

Keling, ma'lumotlar sifatini o'lchash va bu jarayonni avtomatlashtirish mavzusiga qaytaylik.

Biz, masalan, Raspberry Pi asosidagi minimal resurslarga ega saqlash serverini joriy qilish orqali texnik muhit va maʼlumotlar omboriga xizmat koʻrsatishni oʻrganamiz.

Manba: www.habr.com