āĻāĻ¯āĻŧā§āĻŦāĻžāĻ˛āĻžāĻāĻāĻžāĻ° āĻāĻŦāĻ āĻā§āĻāĻ˛ āĻ ā§āĻ¯āĻžāĻ¨āĻžāĻ˛āĻŋāĻāĻŋāĻā§āĻ¸ āĻāĻŽāĻžāĻā§ āĻ āĻ¨ā§āĻ āĻŦāĻāĻ° āĻ§āĻ°ā§ āĻāĻ¯āĻŧā§āĻŦāĻ¸āĻžāĻāĻāĻā§āĻ˛āĻŋāĻ¤ā§ āĻā§ āĻāĻāĻā§ āĻ¤āĻžāĻ° āĻ āĻ¨ā§āĻ¤āĻ°ā§āĻĻā§āĻˇā§āĻāĻŋ āĻĒā§āĻ¤ā§ āĻ¸āĻžāĻšāĻžāĻ¯ā§āĻ¯ āĻāĻ°ā§āĻā§āĨ¤ āĻāĻāĻ¨ āĻāĻŽāĻŋ āĻŦā§āĻāĻ¤ā§ āĻĒāĻžāĻ°āĻŋ āĻ¯ā§ āĻ¤āĻžāĻ°āĻž āĻā§āĻŦ āĻāĻŽ āĻĻāĻ°āĻāĻžāĻ°ā§ āĻ¤āĻĨā§āĻ¯ āĻĒā§āĻ°āĻĻāĻžāĻ¨ āĻāĻ°ā§āĨ¤ āĻāĻĒāĻ¨āĻžāĻ° access.log āĻĢāĻžāĻāĻ˛ā§ āĻ ā§āĻ¯āĻžāĻā§āĻ¸ā§āĻ¸ āĻĨāĻžāĻāĻžāĻ° āĻĢāĻ˛ā§, āĻĒāĻ°āĻŋāĻ¸āĻāĻā§āĻ¯āĻžāĻ¨ āĻŦā§āĻāĻž āĻāĻŦāĻ sqlite, html, sql āĻāĻžāĻˇāĻž āĻāĻŦāĻ āĻ¯ā§āĻā§āĻ¨ā§ āĻ¸ā§āĻā§āĻ°āĻŋāĻĒā§āĻāĻŋāĻ āĻĒā§āĻ°ā§āĻā§āĻ°āĻžāĻŽāĻŋāĻ āĻāĻžāĻˇāĻžāĻ° āĻŽāĻ¤ā§ āĻŦā§āĻļ āĻŽā§āĻ˛āĻŋāĻ āĻ¸āĻ°āĻā§āĻāĻžāĻŽāĻā§āĻ˛āĻŋ āĻŦāĻžāĻ¸ā§āĻ¤āĻŦāĻžāĻ¯āĻŧāĻ¨ āĻāĻ°āĻž āĻā§āĻŦāĻ āĻ¸āĻšāĻāĨ¤
Webalizer-āĻāĻ° āĻĄā§āĻāĻž āĻā§āĻ¸ āĻšāĻ˛ āĻ¸āĻžāĻ°ā§āĻāĻžāĻ°ā§āĻ° access.log āĻĢāĻžāĻāĻ˛āĨ¤ āĻāĻāĻŋāĻ° āĻŦāĻžāĻ° āĻāĻŦāĻ āĻ¸āĻāĻā§āĻ¯āĻžāĻā§āĻ˛āĻŋ āĻĻā§āĻāĻ¤ā§ āĻāĻāĻ°āĻāĻŽ, āĻ¯ā§āĻāĻžāĻ¨ āĻĨā§āĻā§ āĻļā§āĻ§ā§āĻŽāĻžāĻ¤ā§āĻ° āĻā§āĻ°ā§āĻ¯āĻžāĻĢāĻŋāĻā§āĻ° āĻŽā§āĻ āĻĒāĻ°āĻŋāĻŽāĻžāĻŖ āĻ¸ā§āĻĒāĻˇā§āĻ:
āĻā§āĻāĻ˛ āĻ
ā§āĻ¯āĻžāĻ¨āĻžāĻ˛āĻŋāĻāĻŋāĻā§āĻ¸ā§āĻ° āĻŽāĻ¤ā§ āĻā§āĻ˛ āĻ˛ā§āĻĄ āĻāĻ°āĻž āĻĒā§āĻˇā§āĻ āĻž āĻĨā§āĻā§ āĻĄā§āĻāĻž āĻ¸āĻāĻā§āĻ°āĻš āĻāĻ°ā§āĨ¤ āĻ¤āĻžāĻ°āĻž āĻāĻŽāĻžāĻĻā§āĻ° āĻāĻ¯āĻŧā§āĻāĻāĻŋ āĻĄāĻžāĻ¯āĻŧāĻžāĻā§āĻ°āĻžāĻŽ āĻāĻŦāĻ āĻ˛āĻžāĻāĻ¨ āĻĻā§āĻāĻžāĻ¯āĻŧ, āĻ¯āĻžāĻ° āĻāĻĒāĻ° āĻāĻŋāĻ¤ā§āĻ¤āĻŋ āĻāĻ°ā§ āĻ¸āĻ āĻŋāĻ āĻ¸āĻŋāĻĻā§āĻ§āĻžāĻ¨ā§āĻ¤ā§ āĻĒā§āĻāĻāĻžāĻ¨ā§ āĻĒā§āĻ°āĻžāĻ¯āĻŧāĻļāĻ āĻāĻ āĻŋāĻ¨āĨ¤ āĻšāĻ¯āĻŧāĻ¤ā§ āĻāĻ°ā§ āĻā§āĻˇā§āĻāĻž āĻāĻ°āĻž āĻāĻāĻŋāĻ¤ āĻāĻŋāĻ˛? āĻāĻžāĻ¨āĻŋ āĻ¨āĻžāĨ¤
āĻ¤āĻžāĻšāĻ˛ā§, āĻāĻ¯āĻŧā§āĻŦāĻ¸āĻžāĻāĻ āĻāĻŋāĻāĻŋāĻāĻ° āĻĒāĻ°āĻŋāĻ¸āĻāĻā§āĻ¯āĻžāĻ¨ā§ āĻāĻŽāĻŋ āĻā§ āĻĻā§āĻāĻ¤ā§ āĻāĻžāĻ?
āĻŦā§āĻ¯āĻŦāĻšāĻžāĻ°āĻāĻžāĻ°ā§ āĻāĻŦāĻ āĻŦāĻ āĻā§āĻ°āĻžāĻĢāĻŋāĻ
āĻĒā§āĻ°āĻžāĻ¯āĻŧāĻļāĻ āĻ¸āĻžāĻāĻā§āĻ° āĻā§āĻ°āĻžāĻĢāĻŋāĻ āĻ¸ā§āĻŽāĻŋāĻ¤ āĻāĻŦāĻ āĻāĻāĻŋ āĻāĻ¤āĻāĻž āĻĻāĻ°āĻāĻžāĻ°ā§ āĻā§āĻ°ā§āĻ¯āĻžāĻĢāĻŋāĻ āĻŦā§āĻ¯āĻŦāĻšāĻžāĻ° āĻāĻ°āĻž āĻšāĻā§āĻā§ āĻ¤āĻž āĻĻā§āĻāĻ¤ā§ āĻšāĻŦā§āĨ¤ āĻāĻĻāĻžāĻšāĻ°āĻŖāĻ¸ā§āĻŦāĻ°ā§āĻĒ, āĻāĻ āĻŽāĻ¤:
āĻāĻ¸āĻāĻŋāĻāĻāĻ˛ āĻ°āĻŋāĻĒā§āĻ°ā§āĻ āĻā§āĻ¯ā§āĻ¯āĻŧāĻžāĻ°ā§
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
āĻā§āĻ°āĻžāĻĢāĻāĻŋ āĻŦāĻāĻā§āĻ˛āĻŋāĻ° āĻ§ā§āĻ°ā§āĻŦāĻ āĻāĻžāĻ°ā§āĻ¯āĻāĻ˛āĻžāĻĒ āĻĻā§āĻāĻžāĻ¯āĻŧāĨ¤ āĻāĻāĻŋ āĻ¸āĻŦāĻā§āĻ¯āĻŧā§ āĻ¸āĻā§āĻ°āĻŋāĻ¯āĻŧ āĻĒā§āĻ°āĻ¤āĻŋāĻ¨āĻŋāĻ§āĻŋāĻĻā§āĻ° āĻŦāĻŋāĻ¸ā§āĻ¤āĻžāĻ°āĻŋāĻ¤āĻāĻžāĻŦā§ āĻ āĻ§ā§āĻ¯āĻ¯āĻŧāĻ¨ āĻāĻ°āĻž āĻāĻāĻ°ā§āĻˇāĻŖā§āĻ¯āĻŧ āĻšāĻŦā§āĨ¤
āĻŦāĻŋāĻ°āĻā§āĻ¤āĻŋāĻāĻ° āĻŦāĻ
āĻāĻŽāĻ°āĻž āĻŦā§āĻ¯āĻŦāĻšāĻžāĻ°āĻāĻžāĻ°ā§ āĻāĻā§āĻ¨ā§āĻ āĻ¤āĻĨā§āĻ¯ā§āĻ° āĻāĻĒāĻ° āĻāĻŋāĻ¤ā§āĻ¤āĻŋ āĻāĻ°ā§ āĻŦāĻ āĻļā§āĻ°ā§āĻŖā§āĻŦāĻĻā§āĻ§ āĻāĻ°āĻŋāĨ¤ āĻĻā§āĻ¨āĻŋāĻ āĻā§āĻ°āĻžāĻĢāĻŋāĻā§āĻ° āĻ āĻ¤āĻŋāĻ°āĻŋāĻā§āĻ¤ āĻĒāĻ°āĻŋāĻ¸āĻāĻā§āĻ¯āĻžāĻ¨, āĻ¸āĻĢāĻ˛ āĻāĻŦāĻ āĻ āĻ¸āĻĢāĻ˛ āĻ āĻ¨ā§āĻ°ā§āĻ§ā§āĻ° āĻ¸āĻāĻā§āĻ¯āĻž āĻŦāĻ āĻāĻžāĻ°ā§āĻ¯āĻāĻ˛āĻžāĻĒ āĻ¸āĻŽā§āĻĒāĻ°ā§āĻā§ āĻāĻāĻāĻŋ āĻāĻžāĻ˛ āĻ§āĻžāĻ°āĻŖāĻž āĻĻā§āĻ¯āĻŧāĨ¤
āĻāĻ¸āĻāĻŋāĻāĻāĻ˛ āĻ°āĻŋāĻĒā§āĻ°ā§āĻ āĻā§āĻ¯ā§āĻ¯āĻŧāĻžāĻ°ā§
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
āĻĒā§āĻ°āĻĨāĻŽ āĻĻā§āĻāĻŋ āĻŦāĻ āĻā§āĻŦāĻŋāĻ˛ āĻĨā§āĻā§ āĻ
āĻĻā§āĻļā§āĻ¯ āĻšāĻ¯āĻŧā§ āĻā§āĻā§ āĻāĻŦāĻ āĻāĻŽāĻāĻ¸ āĻ°ā§āĻŦāĻāĻā§āĻ˛āĻŋ āĻĒā§āĻ°āĻĨāĻŽ āĻ˛āĻžāĻāĻ¨ āĻĨā§āĻā§ āĻ¨āĻŋāĻā§ āĻāĻ˛ā§ āĻā§āĻā§āĨ¤
āĻ¸āĻ°ā§āĻŦāĻļā§āĻ°ā§āĻˇā§āĻ āĻāĻžāĻ°ā§āĻ¯āĻāĻ˛āĻžāĻĒā§āĻ° āĻĻāĻŋāĻ¨ āĻāĻŦāĻ āĻ¸āĻŽāĻ¯āĻŧ
āĻ¯āĻžāĻ¨āĻāĻā§ āĻāĻ āĻžāĻ¨āĻžāĻŽāĻž āĻĻā§āĻļā§āĻ¯āĻŽāĻžāĻ¨āĨ¤ āĻ¤āĻžāĻĻā§āĻ° āĻŦāĻŋāĻ¸ā§āĻ¤āĻžāĻ°āĻŋāĻ¤āĻāĻžāĻŦā§ āĻ āĻ§ā§āĻ¯āĻ¯āĻŧāĻ¨ āĻāĻ°āĻžāĻ° āĻāĻ¨ā§āĻ¯, āĻ¤āĻžāĻĻā§āĻ° āĻ¸āĻāĻāĻāĻ¨ā§āĻ° āĻ¸āĻŽāĻ¯āĻŧāĻāĻŋ āĻšāĻžāĻāĻ˛āĻžāĻāĻ āĻāĻ°āĻž āĻĒā§āĻ°āĻ¯āĻŧā§āĻāĻ¨, āĻāĻŦāĻ āĻ¸āĻŽāĻ¯āĻŧ āĻĒāĻ°āĻŋāĻŽāĻžāĻĒā§āĻ° āĻ¸āĻŽāĻ¸ā§āĻ¤ āĻāĻ¨ā§āĻāĻž āĻāĻŦāĻ āĻĻāĻŋāĻ¨āĻā§āĻ˛āĻŋ āĻĒā§āĻ°āĻĻāĻ°ā§āĻļāĻ¨ āĻāĻ°āĻž āĻĒā§āĻ°āĻ¯āĻŧā§āĻāĻ¨ āĻšāĻ¯āĻŧ āĻ¨āĻžāĨ¤ āĻŦāĻŋāĻ¸ā§āĻ¤āĻžāĻ°āĻŋāĻ¤ āĻŦāĻŋāĻļā§āĻ˛ā§āĻˇāĻŖā§āĻ° āĻĒā§āĻ°āĻ¯āĻŧā§āĻāĻ¨ āĻšāĻ˛ā§ āĻāĻāĻŋ āĻ˛āĻ āĻĢāĻžāĻāĻ˛ā§ āĻĒā§āĻĨāĻ āĻ āĻ¨ā§āĻ°ā§āĻ§āĻā§āĻ˛āĻŋ āĻā§āĻāĻā§ āĻĒāĻžāĻāĻ¯āĻŧāĻž āĻ¸āĻšāĻ āĻāĻ°ā§ āĻ¤ā§āĻ˛āĻŦā§āĨ¤
āĻāĻ¸āĻāĻŋāĻāĻāĻ˛ āĻ°āĻŋāĻĒā§āĻ°ā§āĻ āĻā§āĻ¯ā§āĻ¯āĻŧāĻžāĻ°ā§
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 āĻŦāĻ āĻ¸āĻā§āĻ°āĻŋāĻ¯āĻŧ āĻāĻŋāĻ˛.
āĻ¸āĻĒā§āĻ¤āĻžāĻšā§ āĻāĻĄāĻŧ āĻŦā§āĻ¯āĻŦāĻšāĻžāĻ°āĻāĻžāĻ°ā§āĻ° āĻāĻžāĻ°ā§āĻ¯āĻāĻ˛āĻžāĻĒ
āĻāĻŽāĻ°āĻž āĻā§āĻ°āĻŋāĻ¯āĻŧāĻžāĻāĻ˛āĻžāĻĒ āĻāĻŦāĻ āĻā§āĻ°āĻžāĻĢāĻŋāĻā§āĻ° āĻ¸āĻžāĻĨā§ āĻāĻŋāĻ¨āĻŋāĻ¸āĻā§āĻ˛āĻŋ āĻāĻŋāĻā§āĻāĻž āĻ¸āĻžāĻāĻŋāĻ¯āĻŧā§āĻāĻŋāĨ¤ āĻĒāĻ°āĻŦāĻ°ā§āĻ¤ā§ āĻĒā§āĻ°āĻļā§āĻ¨ āĻāĻŋāĻ˛ āĻŦā§āĻ¯āĻŦāĻšāĻžāĻ°āĻāĻžāĻ°ā§āĻĻā§āĻ° āĻ¨āĻŋāĻā§āĻĻā§āĻ° āĻāĻžāĻ°ā§āĻ¯āĻāĻ˛āĻžāĻĒ. āĻāĻ āĻ§āĻ°āĻ¨ā§āĻ° āĻĒāĻ°āĻŋāĻ¸āĻāĻā§āĻ¯āĻžāĻ¨ā§āĻ° āĻāĻ¨ā§āĻ¯, āĻĻā§āĻ°ā§āĻ āĻāĻāĻ¤ā§āĻ°āĻŋāĻ¤āĻāĻ°āĻŖ āĻ¸āĻŽāĻ¯āĻŧāĻāĻžāĻ˛, āĻ¯ā§āĻŽāĻ¨ āĻāĻ āĻ¸āĻĒā§āĻ¤āĻžāĻš, āĻŦāĻžāĻā§āĻāĻ¨ā§āĻ¯āĻŧāĨ¤
āĻāĻ¸āĻāĻŋāĻāĻāĻ˛ āĻ°āĻŋāĻĒā§āĻ°ā§āĻ āĻā§āĻ¯ā§āĻ¯āĻŧāĻžāĻ°ā§
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 āĻ¸āĻ°ā§āĻŦāĻĻāĻž āĻ¨āĻŋāĻ°ā§āĻĻāĻŋāĻˇā§āĻ āĻĒā§āĻˇā§āĻ āĻž āĻā§āĻĄ āĻĻā§āĻāĻžāĻ¯āĻŧ āĻāĻŦāĻ āĻāĻŽāĻŋ āĻ¸āĻŦāĻ¸āĻŽāĻ¯āĻŧ āĻ¸āĻĢāĻ˛ āĻ āĻ¨ā§āĻ°ā§āĻ§ āĻāĻŦāĻ āĻ¤ā§āĻ°ā§āĻāĻŋāĻ° āĻ¸āĻāĻā§āĻ¯āĻž āĻĻā§āĻāĻ¤ā§ āĻā§āĻ¯āĻŧā§āĻāĻŋāĻ˛āĻžāĻŽāĨ¤
āĻāĻ¸āĻāĻŋāĻāĻāĻ˛ āĻ°āĻŋāĻĒā§āĻ°ā§āĻ āĻā§āĻ¯ā§āĻ¯āĻŧāĻžāĻ°ā§
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 āĻĢāĻžāĻāĻ˛āĻāĻŋ āĻĒā§āĻ˛ āĻāĻ°ā§ āĻāĻŦāĻ āĻāĻ āĻā§āĻˇā§āĻ¤ā§āĻ°ā§, āĻāĻ āĻāĻžāĻ¤ā§āĻ¯āĻŧ āĻĒā§āĻ˛āĻā§āĻ˛āĻŋ āĻāĻāĻŦāĻžāĻ° āĻāĻŖāĻ¨āĻž āĻāĻ°āĻž āĻšāĻŦā§ā§ˇ āĻāĻāĻŋ āĻāĻĒāĻ¨āĻžāĻā§ āĻā§āĻ°āĻžāĻĢā§ āĻāĻžāĻŽā§āĻĒāĻā§āĻ˛āĻŋāĻā§ āĻŽāĻ¸ā§āĻŖ āĻāĻ°āĻ¤ā§ āĻĻā§āĻ¯āĻŧāĨ¤
āĻā§āĻ°āĻžāĻĢ āĻĨā§āĻā§ āĻāĻĒāĻ¨āĻŋ āĻ āĻ¨ā§āĻ āĻ¤ā§āĻ°ā§āĻāĻŋ āĻĻā§āĻāĻ¤ā§ āĻĒāĻžāĻā§āĻā§āĻ¨ - āĻāĻā§āĻ˛āĻŋ āĻ āĻ¸ā§āĻ¤āĻŋāĻ¤ā§āĻŦāĻšā§āĻ¨ āĻĒā§āĻˇā§āĻ āĻžāĨ¤ āĻŦāĻŋāĻļā§āĻ˛ā§āĻˇāĻŖā§āĻ° āĻĢāĻ˛āĻžāĻĢāĻ˛ āĻāĻŋāĻ˛ āĻĻā§āĻ°āĻŦāĻ°ā§āĻ¤ā§ āĻĒā§āĻˇā§āĻ āĻžāĻā§āĻ˛āĻŋ āĻĨā§āĻā§ āĻĒā§āĻ¨āĻāĻ¨āĻŋāĻ°ā§āĻĻā§āĻļ āĻ¯ā§āĻ āĻāĻ°āĻžāĨ¤
āĻāĻžāĻ°āĻžāĻĒ āĻ āĻ¨ā§āĻ°ā§āĻ§
āĻ āĻ¨ā§āĻ°ā§āĻ§āĻā§āĻ˛āĻŋ āĻŦāĻŋāĻ¸ā§āĻ¤āĻžāĻ°āĻŋāĻ¤āĻāĻžāĻŦā§ āĻĒāĻ°ā§āĻā§āĻˇāĻž āĻāĻ°āĻ¤ā§, āĻāĻĒāĻ¨āĻŋ āĻŦāĻŋāĻ¸ā§āĻ¤āĻžāĻ°āĻŋāĻ¤ āĻĒāĻ°āĻŋāĻ¸āĻāĻā§āĻ¯āĻžāĻ¨ āĻĒā§āĻ°āĻĻāĻ°ā§āĻļāĻ¨ āĻāĻ°āĻ¤ā§ āĻĒāĻžāĻ°ā§āĻ¨āĨ¤
āĻāĻ¸āĻāĻŋāĻāĻāĻ˛ āĻ°āĻŋāĻĒā§āĻ°ā§āĻ āĻā§āĻ¯ā§āĻ¯āĻŧāĻžāĻ°ā§
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 āĻĒā§āĻ°āĻ¸ā§āĻ¸ āĻ˛āĻāĻŋāĻ āĻāĻ°āĻžāĻ° āĻāĻ¨ā§āĻ¯ āĻ¸āĻšāĻžāĻ¯āĻŧāĻāĨ¤
āĻā§āĻŦāĻŋāĻ˛ āĻ¸ā§āĻā§āĻ āĻ¯ā§āĻāĻžāĻ¨ā§ āĻāĻŽāĻ°āĻž āĻĒāĻŋāĻāĻāĻāĻĒāĻŋ āĻŦā§āĻ¯āĻŦāĻšāĻžāĻ° āĻāĻ°ā§ āĻ˛āĻ āĻĢāĻžāĻāĻ˛ āĻ˛āĻŋāĻāĻŦāĨ¤ āĻĻā§āĻāĻŋ āĻ¸āĻžāĻŽāĻā§āĻ°āĻŋāĻ āĻā§āĻŦāĻŋāĻ˛āĨ¤ āĻāĻ˛ā§āĻ¨ āĻŦā§āĻ¯āĻŦāĻšāĻžāĻ°āĻāĻžāĻ°ā§ āĻāĻā§āĻ¨ā§āĻ āĻāĻŦāĻ āĻ
āĻ¨ā§āĻ°ā§āĻ§ā§āĻ° āĻ
āĻŦāĻ¸ā§āĻĨāĻžāĻ° āĻĒāĻ°āĻŋāĻ¸āĻāĻā§āĻ¯āĻžāĻ¨ āĻ¸āĻš āĻāĻāĻāĻŋ āĻĻā§āĻ¨āĻŋāĻ āĻā§āĻŦāĻŋāĻ˛ āĻ¤ā§āĻ°āĻŋ āĻāĻ°āĻŋāĨ¤ āĻ
āĻ¨ā§āĻ°ā§āĻ§, āĻ¸ā§āĻā§āĻ¯āĻžāĻāĻžāĻ¸ āĻā§āĻ°ā§āĻĒ āĻāĻŦāĻ āĻāĻā§āĻ¨ā§āĻā§āĻ° āĻĒāĻ°āĻŋāĻ¸āĻāĻā§āĻ¯āĻžāĻ¨ āĻ¸āĻš āĻĒā§āĻ°āĻ¤āĻŋ āĻāĻŖā§āĻāĻžāĻ¯āĻŧāĨ¤ āĻĒā§āĻ°āĻžāĻ¸āĻā§āĻāĻŋāĻ āĻĒāĻ°āĻŋāĻŽāĻžāĻĒā§āĻ° āĻāĻžāĻ°āĻāĻŋ āĻā§āĻŦāĻŋāĻ˛āĨ¤
āĻĢāĻ˛āĻžāĻĢāĻ˛ āĻšāĻ˛ āĻ¨āĻŋāĻŽā§āĻ¨āĻ˛āĻŋāĻāĻŋāĻ¤ āĻ°āĻŋāĻ˛ā§āĻļāĻ¨āĻžāĻ˛ āĻŽāĻĄā§āĻ˛:
āĻ¤āĻĨā§āĻ¯ āĻŽāĻĄā§āĻ˛
āĻāĻāĻāĻŋ sqlite āĻĄāĻžāĻāĻžāĻŦā§āĻ¸ā§ āĻāĻāĻāĻŋ āĻŦāĻ¸ā§āĻ¤ā§ āĻ¤ā§āĻ°āĻŋ āĻāĻ°āĻ¤ā§ āĻ¸ā§āĻā§āĻ°āĻŋāĻĒā§āĻ:
āĻĄāĻŋāĻĄāĻŋāĻāĻ˛ āĻ āĻŦāĻā§āĻā§āĻ āĻ¤ā§āĻ°āĻŋ
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 āĻā§āĻŦāĻŋāĻ˛ā§, āĻā§āĻāĻŋ āĻ¤āĻŋāĻ¨āĻāĻŋ āĻā§āĻˇā§āĻ¤ā§āĻ°ā§āĻ° āĻ¸āĻŽāĻ¨ā§āĻŦāĻ¯āĻŧāĨ¤
āĻāĻ¸āĻāĻŋāĻāĻāĻ˛ āĻā§ āĻĒā§āĻ°āĻāĻžāĻ°ā§āĻ° āĻĒā§āĻ°āĻļā§āĻ¨
/* 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 INTEGEER) > $param_epoch_from, āĻ¯ā§āĻāĻžāĻ¨ā§ āĻĒā§āĻ¯āĻžāĻ°āĻžāĻŽāĻŋāĻāĻžāĻ°āĻāĻŋ āĻ
āĻ¨ā§āĻ°ā§āĻ§ā§āĻ° āĻĢāĻ˛āĻžāĻĢāĻ˛
'FCT_ACCESS_USER_AGENT_DD āĻĨā§āĻā§ COALESCE(MAX(EVENT_DT), '3600') LAST_EVENT_EPOCH āĻ¨āĻŋāĻ°ā§āĻŦāĻžāĻāĻ¨ āĻāĻ°ā§āĻ¨'
āĻļāĻ°ā§āĻ¤āĻāĻŋ āĻļā§āĻ§ā§āĻŽāĻžāĻ¤ā§āĻ° āĻĒā§āĻ°ā§ āĻĻāĻŋāĻ¨ āĻ˛ā§āĻĄ āĻšāĻŦā§: CAST(STG.EVENT_DT āĻĒā§āĻ°ā§āĻŖāĻ¸āĻāĻā§āĻ¯āĻž āĻšāĻŋāĻ¸āĻžāĻŦā§) < strftime(â%sâ, āĻ¤āĻžāĻ°āĻŋāĻ(âāĻāĻāĻ¨â, âāĻĻāĻŋāĻ¨ā§āĻ° āĻļā§āĻ°ā§â))
āĻĒā§āĻˇā§āĻ āĻž āĻŦāĻž āĻĢāĻžāĻāĻ˛ āĻāĻŖāĻ¨āĻž āĻāĻāĻāĻŋ āĻāĻĻāĻŋāĻŽ āĻāĻĒāĻžāĻ¯āĻŧā§ āĻŦāĻžāĻšāĻŋāĻ¤ āĻšāĻ¯āĻŧ, āĻāĻāĻāĻŋ āĻŦāĻŋāĻ¨ā§āĻĻā§ āĻ āĻ¨ā§āĻ¸āĻ¨ā§āĻ§āĻžāĻ¨ āĻāĻ°ā§.
āĻ°āĻŋāĻĒā§āĻ°ā§āĻ
āĻāĻāĻŋāĻ˛ āĻāĻŋāĻā§āĻ¯ā§āĻ¯āĻŧāĻžāĻ˛āĻžāĻāĻā§āĻļāĻ¨ āĻ¸āĻŋāĻ¸ā§āĻā§āĻŽā§, āĻĄāĻžāĻāĻžāĻŦā§āĻ¸ āĻ āĻŦāĻā§āĻā§āĻā§āĻ° āĻāĻĒāĻ° āĻāĻŋāĻ¤ā§āĻ¤āĻŋ āĻāĻ°ā§ āĻāĻāĻāĻŋ āĻŽā§āĻāĻž-āĻŽāĻĄā§āĻ˛ āĻ¤ā§āĻ°āĻŋ āĻāĻ°āĻž āĻ¸āĻŽā§āĻāĻŦ, āĻāĻ¤āĻŋāĻļā§āĻ˛āĻāĻžāĻŦā§ āĻĢāĻŋāĻ˛ā§āĻāĻžāĻ° āĻāĻŦāĻ āĻāĻāĻ¤ā§āĻ°āĻŋāĻ¤āĻāĻ°āĻŖā§āĻ° āĻ¨āĻŋāĻ¯āĻŧāĻŽāĻā§āĻ˛āĻŋ āĻĒāĻ°āĻŋāĻāĻžāĻ˛āĻ¨āĻž āĻāĻ°āĻž āĻ¸āĻŽā§āĻāĻŦāĨ¤ āĻļā§āĻˇ āĻĒāĻ°ā§āĻ¯āĻ¨ā§āĻ¤, āĻ¸āĻŽāĻ¸ā§āĻ¤ āĻļāĻžāĻ˛ā§āĻ¨ āĻ¸āĻ°āĻā§āĻāĻžāĻŽ āĻāĻāĻāĻŋ SQL āĻā§āĻ¯āĻŧā§āĻ°āĻŋ āĻ¤ā§āĻ°āĻŋ āĻāĻ°ā§āĨ¤
āĻāĻ āĻāĻĻāĻžāĻšāĻ°āĻŖā§, āĻāĻŽāĻ°āĻž āĻ°ā§āĻĄāĻŋāĻŽā§āĻĄ āĻāĻ¸āĻāĻŋāĻāĻāĻ˛ āĻā§āĻ¯āĻŧā§āĻ°āĻŋ āĻ¤ā§āĻ°āĻŋ āĻāĻ°āĻŦ āĻāĻŦāĻ āĻĄāĻžāĻāĻžāĻŦā§āĻ¸ā§ āĻāĻŋāĻ āĻšāĻŋāĻ¸āĻžāĻŦā§ āĻ¸ā§āĻ āĻāĻ°āĻŦ - āĻāĻāĻā§āĻ˛āĻŋ āĻšāĻ˛ āĻ°āĻŋāĻĒā§āĻ°ā§āĻāĨ¤
āĻāĻŋāĻā§āĻ¯ā§āĻ¯āĻŧāĻžāĻ˛āĻžāĻāĻā§āĻļāĻ¨
āĻŦā§āĻ˛āĻžāĻĢ: āĻāĻžāĻāĻžāĻ¸ā§āĻā§āĻ°āĻŋāĻĒā§āĻā§ āĻ¸ā§āĻ¨ā§āĻĻāĻ° āĻā§āĻ°āĻžāĻĢāĻā§āĻ˛āĻŋ āĻāĻāĻāĻŋ āĻāĻŋāĻā§āĻ¯ā§āĻ¯āĻŧāĻžāĻ˛āĻžāĻāĻā§āĻļāĻ¨ āĻā§āĻ˛ āĻšāĻŋāĻ¸āĻžāĻŦā§ āĻŦā§āĻ¯āĻŦāĻšā§āĻ¤ āĻšāĻ¯āĻŧā§āĻāĻŋāĻ˛
āĻāĻāĻŋ āĻāĻ°āĻžāĻ° āĻāĻ¨ā§āĻ¯, āĻĒāĻŋāĻāĻāĻāĻĒāĻŋ āĻŦā§āĻ¯āĻŦāĻšāĻžāĻ° āĻāĻ°ā§ āĻ¸āĻŽāĻ¸ā§āĻ¤ āĻĒā§āĻ°āĻ¤āĻŋāĻŦā§āĻĻāĻ¨ā§āĻ° āĻŽāĻ§ā§āĻ¯ āĻĻāĻŋāĻ¯āĻŧā§ āĻ¯ā§āĻ¤ā§ āĻāĻŦāĻ āĻā§āĻŦāĻŋāĻ˛ āĻ¸āĻš āĻāĻāĻāĻŋ āĻāĻāĻāĻāĻŋāĻāĻŽāĻāĻ˛ āĻĢāĻžāĻāĻ˛ āĻ¤ā§āĻ°āĻŋ āĻāĻ°āĻž āĻĒā§āĻ°āĻ¯āĻŧā§āĻāĻ¨ āĻāĻŋāĻ˛āĨ¤
$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 āĻĒā§āĻ°āĻā§āĻ°āĻŋāĻ¯āĻŧāĻžāĻā§āĻ˛āĻŋ āĻĒāĻ°āĻŋāĻāĻžāĻ˛āĻ¨āĻž āĻāĻ°āĻžāĻ° āĻāĻ¨ā§āĻ¯ āĻ¸āĻŦāĻā§āĻ¯āĻŧā§ āĻ¸āĻšāĻ āĻā§āĻ˛āĻāĻŋ āĻāĻ¨āĻŋāĻˇā§āĻ āĻāĻžāĻŦā§ āĻĻā§āĻā§ āĻ¨ā§āĻāĻ¯āĻŧāĻž āĻ¯āĻžāĻāĨ¤
āĻāĻ¸ā§āĻ¨ āĻĄā§āĻāĻžāĻ° āĻā§āĻŖāĻŽāĻžāĻ¨ āĻĒāĻ°āĻŋāĻŽāĻžāĻĒ āĻāĻŦāĻ āĻāĻ āĻĒā§āĻ°āĻā§āĻ°āĻŋāĻ¯āĻŧāĻžāĻāĻŋāĻā§ āĻ¸ā§āĻŦāĻ¯āĻŧāĻāĻā§āĻ°āĻŋāĻ¯āĻŧ āĻāĻ°āĻžāĻ° āĻŦāĻŋāĻˇāĻ¯āĻŧā§ āĻĢāĻŋāĻ°ā§ āĻāĻ¸āĻŋāĨ¤
āĻāĻŽāĻ°āĻž āĻĒā§āĻ°āĻ¯ā§āĻā§āĻ¤āĻŋāĻāĻ¤ āĻĒāĻ°āĻŋāĻŦā§āĻļ āĻāĻŦāĻ āĻĄā§āĻāĻž āĻ¸ā§āĻā§āĻ°ā§āĻā§āĻ° āĻ°āĻā§āĻˇāĻŖāĻžāĻŦā§āĻā§āĻˇāĻŖā§āĻ° āĻ¸āĻŽāĻ¸ā§āĻ¯āĻžāĻā§āĻ˛āĻŋ āĻ
āĻ§ā§āĻ¯āĻ¯āĻŧāĻ¨ āĻāĻ°āĻŦ, āĻ¯āĻžāĻ° āĻāĻ¨ā§āĻ¯ āĻāĻŽāĻ°āĻž āĻ¨ā§āĻ¯ā§āĻ¨āĻ¤āĻŽ āĻ¸āĻāĻ¸ā§āĻĨāĻžāĻ¨ āĻ¸āĻš āĻāĻāĻāĻŋ āĻ¸ā§āĻā§āĻ°ā§āĻ āĻ¸āĻžāĻ°ā§āĻāĻžāĻ° āĻŦāĻžāĻ¸ā§āĻ¤āĻŦāĻžāĻ¯āĻŧāĻ¨ āĻāĻ°āĻŦ, āĻāĻĻāĻžāĻšāĻ°āĻŖāĻ¸ā§āĻŦāĻ°ā§āĻĒ, āĻāĻāĻāĻŋ āĻ°āĻžāĻ¸ā§āĻĒāĻŦā§āĻ°āĻŋ āĻĒāĻžāĻ āĻāĻ° āĻāĻĒāĻ° āĻāĻŋāĻ¤ā§āĻ¤āĻŋ āĻāĻ°ā§āĨ¤
āĻāĻ¤ā§āĻ¸: www.habr.com