Webalizer və Google Analytics mənə uzun illərdir ki, vebsaytlarda baş verənlərlə bağlı fikir əldə etməyə kömək edir. İndi başa düşürəm ki, onlar çox az faydalı məlumat verirlər. Access.log faylınıza daxil olmaqla, statistikanı başa düşmək və sqlite, html, sql dili və istənilən skript proqramlaşdırma dili kimi kifayət qədər sadə alətləri tətbiq etmək çox asandır.
Webalizer üçün məlumat mənbəyi serverin access.log faylıdır. Onun çubuqları və nömrələri belə görünür, onlardan yalnız trafikin ümumi həcmi aydındır:
Google Analytics kimi alətlər yüklənmiş səhifədən məlumatları özləri toplayır. Bizə bir neçə diaqram və xətt göstərirlər ki, onların əsasında düzgün nəticə çıxarmaq çox vaxt çətindir. Bəlkə daha çox səy göstərmək lazım idi? bilmirəm.
Beləliklə, veb-sayt ziyarətçiləri statistikasında nə görmək istəyirdim?
İstifadəçi və bot trafiki
Çox vaxt sayt trafiki məhdud olur və nə qədər faydalı trafikdən istifadə olunduğunu görmək lazımdır. Məsələn, bu kimi:
SQL hesabat sorğusu
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_DTQrafik botların daimi fəaliyyətini göstərir. Ən fəal nümayəndələri ətraflı öyrənmək maraqlı olardı.
Narahat botlar
Biz istifadəçi agenti məlumatlarına əsasən botları təsnif edirik. Gündəlik trafikə dair əlavə statistika, uğurlu və uğursuz sorğuların sayı bot fəaliyyəti haqqında yaxşı fikir verir.
SQL hesabat sorğusu
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 10Bu halda, təhlilin nəticəsi robots.txt faylına əlavə etməklə sayta girişin məhdudlaşdırılması qərarı oldu.
User-agent: AhrefsBot
Disallow: /
User-agent: dotbot
Disallow: /
User-agent: bingbot
Crawl-delay: 5
İlk iki bot masadan itdi və MS robotları ilk sətirlərdən aşağıya doğru hərəkət etdi.
Ən böyük fəaliyyətin günü və vaxtı
Trafikdə yüksəlişlər görünür. Onları ətraflı öyrənmək üçün onların baş vermə vaxtını vurğulamaq lazımdır və vaxt ölçmənin bütün saatlarını və günlərini göstərmək lazım deyil. Bu, ətraflı təhlil tələb olunarsa, log faylında fərdi sorğuların tapılmasını asanlaşdıracaq.
SQL hesabat sorğusu
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_DTQrafikdə ilk günün ən aktiv saatlarını 11, 14 və 20-ni müşahidə edirik. Amma ertəsi gün saat 13:XNUMX-da botlar aktiv idi.
Həftəyə görə orta gündəlik istifadəçi fəaliyyəti
Fəaliyyət və trafiklə bir az şeyləri sıraladıq. Növbəti sual istifadəçilərin özlərinin aktivliyi olub. Belə statistika üçün bir həftə kimi uzun toplama dövrləri arzuolunandır.
SQL hesabat sorğusu
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_DTHəftəlik statistika göstərir ki, orta hesabla bir istifadəçi gündə 1,6 səhifə açır. Bu halda bir istifadəçi üçün tələb olunan faylların sayı sayta yeni faylların əlavə edilməsindən asılıdır.
Bütün sorğular və onların statusları
Webalizer həmişə xüsusi səhifə kodlarını göstərirdi və mən həmişə uğurlu sorğuların və səhvlərin sayını görmək istəyirdim.
SQL hesabat sorğusu
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_DTHesabat LINE_CNT-dən fərqli olaraq, REQUEST_CNT göstəricisi COUNT(DISTINCT STG.REQUEST_NK) kimi hesablanır, klikləri (xitləri) yox, sorğuları göstərir. Məqsəd effektiv hadisələri göstərməkdir, məsələn, MS botları robots.txt faylını gündə yüzlərlə dəfə sorğulayır və bu halda belə sorğular bir dəfə hesablanacaq. Bu, qrafikdəki atlamaları hamarlaşdırmağa imkan verir.
Qrafikdən bir çox səhvləri görə bilərsiniz - bunlar mövcud olmayan səhifələrdir. Təhlilin nəticəsi uzaq səhifələrdən yönləndirmələrin əlavə edilməsi oldu.
Səhv sorğular
Sorğuları ətraflı araşdırmaq üçün ətraflı statistik məlumatları göstərə bilərsiniz.
SQL hesabat sorğusu
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 20Bu siyahı həmçinin bütün zəngləri, məsələn, /wp-login.php ünvanına sorğunu ehtiva edəcək.
Beləliklə, server jurnalı faylına əsaslanan bir neçə sadə hesabat saytda baş verənlər haqqında kifayət qədər tam təsəvvür yaradır.
Məlumatı necə əldə etmək olar?
Sqlite verilənlər bazası kifayətdir. Cədvəllər yaradaq: ETL proseslərini qeyd etmək üçün köməkçi.
PHP istifadə edərək log faylları yazacağımız cədvəl mərhələsi. İki məcmu cədvəl. İstifadəçi agentləri və sorğu statusları ilə bağlı statistika ilə gündəlik cədvəl yaradaq. Sorğular, status qrupları və agentlər haqqında statistika ilə saatlıq. Müvafiq ölçmələrin dörd cədvəli.
Nəticə aşağıdakı əlaqə modelidir:
Məlumat modeli
Sqlite verilənlər bazasında obyekt yaratmaq üçün skript:
DDL obyektinin yaradılması
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);Mərhələ
access.log faylı vəziyyətində verilənlər bazasına bütün sorğuları oxumaq, təhlil etmək və yazmaq lazımdır. Bu, birbaşa skript dili və ya sqlite alətlərindən istifadə etməklə edilə bilər.
Günlük fayl formatı:
//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-]+) "(.*)" "(.*)"$/';
Əsas yayılma
Xam məlumatlar verilənlər bazasında olduqda, ölçmə cədvəllərinə orada olmayan açarları yazmalısınız. Sonra ölçmələrə istinad qurmaq mümkün olacaq. Məsələn, DIM_REFERRER cədvəlində açar üç sahənin birləşməsidir.
SQL açarının yayılması sorğusu
/* 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İstifadəçi agent cədvəlinə yayılma bot məntiqini ehtiva edə bilər, məsələn, sql parçası:
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_BOTCədvəlləri birləşdirin
Nəhayət, biz məcmu cədvəlləri yükləyəcəyik; məsələn, gündəlik cədvəl aşağıdakı kimi yüklənə bilər:
Aqreqatı yükləmək üçün SQL sorğusu
/* 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_IDSqlite verilənlər bazası mürəkkəb sorğular yazmağa imkan verir. WITH məlumatların və açarların hazırlanmasını ehtiva edir. Əsas sorğu ölçülərə dair bütün istinadları toplayır.
Şərt tarixçənin yenidən yüklənməsinə icazə verməyəcək: CAST(STG.EVENT_DT AS INTEGER) > $param_epoch_from, burada parametr sorğunun nəticəsidir
'FCT_ACCESS_USER_AGENT_DDDƏN SON_EVENT_EPOCH KİMİ COALESCE(MAX(EVENT_DT), '3600') SEÇİN'
Şərt yalnız tam günü yükləyəcək: CAST(STG.EVENT_DT AS INTEGER) < strftime('%s', date('indi', 'günün başlanğıcı'))
Səhifələrin və ya faylların sayılması ibtidai üsulla, bir nöqtənin axtarışı ilə həyata keçirilir.
Hesabatlar
Mürəkkəb vizuallaşdırma sistemlərində verilənlər bazası obyektləri əsasında metamodel yaratmaq, filtrləri və aqreqasiya qaydalarını dinamik şəkildə idarə etmək mümkündür. Nəhayət, bütün layiqli alətlər SQL sorğusu yaradır.
Bu nümunədə biz hazır SQL sorğuları yaradacağıq və onları verilənlər bazasında görünüşlər kimi saxlayacağıq - bunlar hesabatlardır.
Vizualizasiya
Bluff: JavaScript-də gözəl qrafiklər vizuallaşdırma vasitəsi kimi istifadə edilmişdir
Bunun üçün PHP-dən istifadə edərək bütün hesabatları gözdən keçirmək və cədvəlləri olan html faylı yaratmaq lazım idi.
$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'
);Alət sadəcə nəticələrin cədvəllərini vizuallaşdırır.
Buraxılış
Nümunə olaraq veb təhlilindən istifadə edərək, məqalə məlumat anbarlarının qurulması üçün lazım olan mexanizmləri təsvir edir. Nəticələrdən göründüyü kimi, məlumatların dərin təhlili və vizuallaşdırılması üçün ən sadə alətlər kifayətdir.
Gələcəkdə bu depodan nümunə kimi istifadə edərək, yavaş-yavaş dəyişən ölçülər, metadata, aqreqasiya səviyyələri və müxtəlif mənbələrdən verilənlərin inteqrasiyası kimi strukturları həyata keçirməyə çalışacağıq.
Həmçinin, bir cədvəl əsasında ETL proseslərini idarə etmək üçün ən sadə alətə daha yaxından nəzər salaq.
Gəlin məlumatların keyfiyyətinin ölçülməsi və bu prosesin avtomatlaşdırılması mövzusuna qayıdaq.
Biz texniki mühitin və məlumat anbarlarının saxlanması problemlərini öyrənəcəyik, bunun üçün minimal resursla, məsələn, Raspberry Pi əsasında saxlama serverini tətbiq edəcəyik.
Mənbə: www.habr.com