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
Π Π°Π·ΠΏΡΠΎΡΡΡΠ°Π½Π΅Π½ΠΈΠ΅ΡΠΎ ΠΊΡΠΌ ΡΠ°Π±Π»ΠΈΡΠ°ΡΠ° Π½Π° ΠΏΠΎΡΡΠ΅Π±ΠΈΡΠ΅Π»ΡΠΊΠΈΡ Π°Π³Π΅Π½Ρ ΠΌΠΎΠΆΠ΅ Π΄Π° ΡΡΠ΄ΡΡΠΆΠ° Π±ΠΎΡ Π»ΠΎΠ³ΠΈΠΊΠ°, Π½Π°ΠΏΡΠΈΠΌΠ΅Ρ 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, ΠΊΡΠ΄Π΅ΡΠΎ ΠΏΠ°ΡΠ°ΠΌΠ΅ΡΡΡΡΡ Π΅ ΡΠ΅Π·ΡΠ»ΡΠ°ΡΡΡ ΠΎΡ Π·Π°ΡΠ²ΠΊΠ°ΡΠ°
'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 Π·Π°ΡΠ²ΠΊΠΈ ΠΈ ΡΠ΅ Π³ΠΈ Π·Π°ΠΏΠ°Π·ΠΈΠΌ ΠΊΠ°ΡΠΎ ΠΈΠ·Π³Π»Π΅Π΄ΠΈ Π² Π±Π°Π·Π°ΡΠ° Π΄Π°Π½Π½ΠΈ - ΡΠΎΠ²Π° ΡΠ° ΠΎΡΡΠ΅ΡΠΈ.
ΠΠΈΠ·ΡΠ°Π»ΠΈΠ·Π°ΡΠΈΡ
ΠΠ»ΡΡ: ΠΡΠ°ΡΠΈΠ²ΠΈΡΠ΅ Π³ΡΠ°ΡΠΈΠΊΠΈ Π² 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