آمار سایت و فضای ذخیره سازی کوچک خودتان

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، که در آن پارامتر نتیجه درخواست است
'انتخاب COALESCE(MAX(EVENT_DT)، '3600') AS LAST_EVENT_EPOCH FROM FCT_ACCESS_USER_AGENT_DD'

شرط فقط تمام روز بارگیری می‌شود: CAST(STG.EVENT_DT AS INTEGER) < strftime('%s', date ('اکنون', 'شروع روز'))

شمارش صفحات یا فایل ها به روشی ابتدایی و با جستجوی یک نقطه انجام می شود.

گزارش ها

در سیستم های تجسم پیچیده، امکان ایجاد یک متا مدل بر اساس اشیاء پایگاه داده، مدیریت پویا فیلترها و قوانین تجمیع وجود دارد. در نهایت، همه ابزارهای مناسب یک پرس و جوی SQL ایجاد می کنند.

در این مثال، کوئری های آماده SQL ایجاد می کنیم و آنها را به عنوان view در پایگاه داده ذخیره می کنیم - اینها گزارش هستند.

تجسم

بلوف: نمودارهای زیبا در جاوا اسکریپت به عنوان ابزار تجسم استفاده شد

برای این کار لازم بود تمام گزارش ها با استفاده از 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