لقد ساعدني برنامج 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 bots باستطلاع ملف 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 بكتابة استعلامات معقدة. مع يحتوي على إعداد البيانات والمفاتيح. يقوم الاستعلام الرئيسي بجمع كافة الإشارات إلى الأبعاد.
لن تسمح الحالة بتحميل السجل مرة أخرى: 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.
المصدر: www.habr.com