Webalizer sy Google Analytics dia nanampy ahy hahazo fahalalana momba ny zava-mitranga amin'ny tranokala nandritra ny taona maro. Takatro izao fa zara raha manome fanazavana mahasoa izy ireo. Ny fahazoana miditra amin'ny rakitra access.log anao dia tena mora ny mahazo ny antontan'isa sy ny fampiharana fitaovana tena fototra, toy ny sqlite, html, ny fiteny sql ary ny fiteny fandaharana scripting.
Ny loharanom-baovaon'ny Webalizer dia ny rakitra access.log an'ny server. Toy izao ny endriky ny barany sy ny isany, izay ny fitambaran'ny fifamoivoizana ihany no mazava:
Ny fitaovana toy ny Google Analytics dia manangona angona avy amin'ny pejy feno. Mampiseho kisary sy tsipika roa izy ireo, izay matetika sarotra ny manatsoaka hevitra marina. Sao dia nisy ezaka bebe kokoa natao? tsy mahalala.
Noho izany, inona no tiako ho hita ao amin'ny antontan'isa mpitsidika tranonkala?
Ny fifamoivoizana mpampiasa sy bot
Matetika dia voafetra ny fifamoivoizana amin'ny tranokala ary ilaina ny mijery ny habetsaky ny fifamoivoizana ampiasaina. Ohatra, toy izao:
SQL tatitra fangatahana
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_DTNy tabilao dia mampiseho ny hetsika tsy tapaka ataon'ny bots. Mahaliana ny mianatra amin'ny antsipiriany ireo solontena mavitrika indrindra.
Bots manelingelina
Manasokajy bots mifototra amin'ny fampahalalana momba ny mpiasan'ny mpampiasa. Ny antontan'isa fanampiny momba ny fifamoivoizana isan'andro, ny isan'ny fangatahana mahomby sy tsy mahomby dia manome hevitra tsara momba ny hetsika bot.
SQL tatitra fangatahana
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 10Amin'ity tranga ity, ny vokatry ny fanadihadiana dia ny fanapahan-kevitra hamerana ny fidirana amin'ny tranokala amin'ny fampidirana azy amin'ny rakitra robots.txt.
User-agent: AhrefsBot
Disallow: /
User-agent: dotbot
Disallow: /
User-agent: bingbot
Crawl-delay: 5
Nanjavona teo amin'ny latabatra ny bota roa voalohany, ary nidina avy teo amin'ny andalana voalohany ny robot MS.
Ny andro sy ny fotoana hanaovana asa lehibe indrindra
Ny fiakarana dia hita amin'ny fifamoivoizana. Mba handinihana azy ireo amin'ny antsipiriany, dia ilaina ny manasongadina ny fotoana nisehoany, ary tsy ilaina ny mampiseho ny ora sy ny andro rehetra amin'ny fandrefesana ny fotoana. Hanamora ny fitadiavana ny fangatahana tsirairay ao amin'ny rakitra log izany raha ilaina ny fanadihadiana amin'ny antsipiriany.
SQL tatitra fangatahana
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_DTHitantsika ny ora mavitrika indrindra 11, 14 ary 20 amin'ny andro voalohany amin'ny tabilao. Saingy ny ampitson'iny tamin'ny 13:XNUMX dia navitrika ny bots.
Salanisan'ny asan'ny mpampiasa isan'andro isan-kerinandro
Nandamina zavatra kely izahay tamin'ny hetsika sy ny fifamoivoizana. Ny fanontaniana manaraka dia ny asan'ny mpampiasa mihitsy. Ho an'ny antontan'isa toy izany dia ilaina ny fe-potoana fanangonana lava, toy ny herinandro.
SQL tatitra fangatahana
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_DTNy antontan'isa isan-kerinandro dia mampiseho fa amin'ny antsalany ny mpampiasa iray dia manokatra pejy 1,6 isan'andro. Ny isan'ny rakitra nangatahana isaky ny mpampiasa amin'ity tranga ity dia miankina amin'ny fampidirana rakitra vaovao amin'ny tranokala.
Ny fangatahana rehetra sy ny satany
Nasehon'ny Webalizer foana ny kaody pejy manokana ary tiako foana ny hahita ny isan'ny fangatahana sy lesoka mahomby.
SQL tatitra fangatahana
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_DTNy tatitra dia mampiseho fangatahana, fa tsy kitika (hits), tsy toy ny LINE_CNT, ny refy REQUEST_CNT dia kajy ho COUNT(DISTINCT STG.REQUEST_NK). Ny tanjona dia ny hampiseho hetsika mahomby, ohatra, ny fitsapan-kevitry ny MS bots ny rakitra robots.txt in-jato isan'andro ary, amin'ity tranga ity, ny fitsapan-kevitra toy izany dia isaina indray mandeha. Izany dia ahafahanao manitsy ny hitsambikina ao amin'ny grafika.
Avy amin'ny grafika dia afaka mahita fahadisoana maro ianao - pejy tsy misy ireo. Ny vokatry ny famakafakana dia ny fanampin'ny redirects avy amin'ny pejy lavitra.
Fangatahana ratsy
Mba handinihana ny fangatahana amin'ny antsipiriany dia azonao atao ny mampiseho antontan'isa amin'ny antsipiriany.
SQL tatitra fangatahana
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 20Ity lisitra ity dia ahitana ny antso rehetra, ohatra, ny fangatahana /wp-login.php Amin'ny alΓ lan'ny fanitsiana ny fitsipika momba ny fanoratana indray ny fangatahana ataon'ny mpizara, azonao atao ny manitsy ny fihetsiky ny mpizara amin'ny fangatahana toy izany ary mandefa azy ireo any amin'ny pejy fanombohana.
Noho izany, tatitra tsotra vitsivitsy mifototra amin'ny rakitra log server dia manome sary feno momba ny zava-mitranga ao amin'ny tranokala.
Ahoana no hahazoana vaovao?
Ny tahiry sqlite dia ampy. Andao hamorona latabatra: mpanampy amin'ny fizotry ny ETL.
Dingana latabatra izay hanoratana rakitra log mampiasa PHP. Tabilao roa mitambatra. Andao hamorona latabatra isan'andro miaraka amin'ny antontan'isa momba ny mpiasan'ny mpampiasa sy ny sata fangatahana. Isaky ny ora miaraka amin'ny antontan'isa momba ny fangatahana, vondrona sata ary mpiasa. Tabilao efatra misy fandrefesana mifandraika amin'izany.
Ny vokatr'izany dia izao manaraka izao modely fifandraisana:
Modely data
Script hamoronana zavatra ao anaty angon-drakitra sqlite:
Famoronana zavatra 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);DINGANA
Raha ny rakitra access.log dia ilaina ny mamaky, manara-maso ary manoratra ny fangatahana rehetra amin'ny tahiry. Azo atao izany na mivantana amin'ny alΓ lan'ny fiteny scripting na mampiasa fitaovana sqlite.
endrika rakitra log:
//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-]+) "(.*)" "(.*)"$/';
Fanelezana fanalahidy
Rehefa ao anaty angon-drakitra ny angona manta dia mila manoratra fanalahidy izay tsy ao amin'ny tabilao fandrefesana ianao. Avy eo dia azo atao ny manangana references amin'ny fandrefesana. Ohatra, ao amin'ny tabilao DIM_REFERRER, ny fanalahidy dia fitambaran'ny saha telo.
Query fampielezana fanalahidy 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 NULLNy fampielezana amin'ny latabatra mpampiasa mpampiasa dia mety misy lojika bot, ohatra ny sql snippet:
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_BOTTabilao mitambatra
Farany, hampiditra ny latabatra fitambarana isika; ohatra, ny latabatra isan'andro dia azo alaina toy izao:
Fanontaniana SQL ho an'ny fametrahana aggregate
/* 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_IDNy angon-drakitra sqlite dia ahafahanao manoratra fanontaniana sarotra. Ny WITH dia misy ny fanomanana ny angona sy ny fanalahidy. Ny fanontaniana lehibe dia manangona ny fanondroana rehetra momba ny refy.
Ny fepetra dia tsy hamela ny hamerenana ny tantara indray: CAST(STG.EVENT_DT AS INTEGER) > $param_epoch_from, izay misy ny parameter ny vokatry ny fangatahana
'FIDIO COALESCE(MAX(EVENT_DT), '3600') AS LAST_EVENT_EPOCH AVY FCT_ACCESS_USER_AGENT_DD'
Ny andro manontolo ihany no hiakatra ny fepetra: CAST(STG.EVENT_DT AS INTEGER) < strftime('%s', date(' now', 'first of day'))
Ny fanisana pejy na rakitra dia atao amin'ny fomba voalohany, amin'ny fitadiavana teboka iray.
tatitra
Amin'ny rafi-pijery sarotra dia azo atao ny mamorona meta-modely mifototra amin'ny zavatra angon-drakitra, mitantana ny sivana sy ny fitsipiky ny fanangonana. Amin'ny farany, ny fitaovana mendrika rehetra dia mamorona fangatahana SQL.
Amin'ity ohatra ity dia hamorona fangatahana SQL efa vita izahay ary hotehirizina ho toy ny fijery ao amin'ny angon-drakitra - tatitra ireo.
fijerena
Bluff: Ny sary tsara tarehy amin'ny JavaScript dia nampiasaina ho fitaovana fijerena sary
Mba hanaovana izany dia ilaina ny mamakivaky ny tatitra rehetra mampiasa PHP ary mamorona rakitra html misy tabilao.
$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'
);Mampiseho tabilao misy valiny fotsiny ilay fitaovana.
famaranana
Amin'ny fampiasana ny famakafakana amin'ny tranokala ho ohatra, ny lahatsoratra dia mamaritra ny rafitra ilaina amin'ny fananganana trano fanatobiana data. Araka ny hita amin'ny vokatra, ny fitaovana tsotra indrindra dia ampy ho an'ny famakafakana lalina sy fijerena ny angon-drakitra.
Amin'ny ho avy, amin'ny fampiasana ity tahiry ity ho ohatra, dia hiezaka ny hampihatra ireo rafitra toy ny fanovana tsikelikely ny refy, ny metadata, ny haavon'ny fanangonana ary ny fampidirana angona avy amin'ny loharano samihafa.
Ary koa, andao hojerentsika akaiky ny fitaovana tsotra indrindra amin'ny fitantanana ny fizotran'ny ETL mifototra amin'ny latabatra tokana.
Andao hiverina amin'ny lohahevitra momba ny fandrefesana ny kalitaon'ny angona sy ny fanaovana automatique an'ity dingana ity.
Hodinihintsika ny olan'ny tontolo ara-teknika sy ny fikojakojana ny fitahirizana angon-drakitra, izay hametrahantsika mpizara fitahirizana miaraka amin'ny loharanon-karena kely indrindra, ohatra, mifototra amin'ny Raspberry Pi.
Source: www.habr.com