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Таблиғ ба ҷадвали агенти корбар метавонад мантиқи ботро дар бар гирад, масалан snippet 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') АЗ FCT_ACCESS_USER_AGENT_DD-ро ҳамчун ОХИР_ЗАМР ИНТИХОБ КУНЕД'
Шарт танҳо рӯзи пурраро бор мекунад: CAST(STG.EVENT_DT AS INTEGER) < strftime('%s', сана('акнун', 'оғози рӯз'))
Ҳисобкунии саҳифаҳо ё файлҳо ба тариқи ибтидоӣ, тавассути ҷустуҷӯи нуқта анҷом дода мешавад.
Ҳисоботҳо
Дар системаҳои мураккаби визуализатсия имкон дорад, ки дар асоси объектҳои пойгоҳи додаҳо мета-модели эҷод кунед, филтрҳо ва қоидаҳои ҷамъкуниро динамикӣ идора кунед. Дар ниҳоят, ҳама асбобҳои мувофиқ дархости 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 -ро татбиқ мекунем.
Манбаъ: will.com