ΠΠ½Π°Π»ΠΈΠ·ΠΈΡΡΡ ΡΡΠ°ΡΠΈΡΡΠΈΠΊΡ ΡΠ°ΠΉΡΠ°, ΠΌΡ ΠΏΠΎΠ»ΡΡΠ°Π΅ΠΌ ΠΏΡΠ΅Π΄ΡΡΠ°Π²Π»Π΅Π½ΠΈΠ΅ ΠΎ ΡΠΎΠΌ, ΡΡΠΎ ΠΏΡΠΎΠΈΡΡ ΠΎΠ΄ΠΈΡ Ρ Π½ΠΈΠΌ. Π Π΅Π·ΡΠ»ΡΡΠ°ΡΡ ΠΌΡ ΡΠΎΠΏΠΎΡΡΠ°Π²Π»ΡΠ΅ΠΌ Ρ Π΄ΡΡΠ³ΠΈΠΌΠΈ Π·Π½Π°Π½ΠΈΡΠΌΠΈ ΠΎ ΠΏΡΠΎΠ΄ΡΠΊΡΠ΅ ΠΈΠ»ΠΈ ΡΠ΅ΡΠ²ΠΈΡΠ΅ ΠΈ ΡΡΠΈΠΌ ΡΠ»ΡΡΡΠ°Π΅ΠΌ Π½Π°Ρ ΠΎΠΏΡΡ.
ΠΠΎΠ³Π΄Π° Π°Π½Π°Π»ΠΈΠ· ΠΏΠ΅ΡΠ²ΡΡ ΡΠ΅Π·ΡΠ»ΡΡΠ°ΡΠΎΠ² Π·Π°Π²Π΅ΡΡΡΠ½, ΠΏΡΠΎΡΠ»ΠΎ ΠΎΡΠΌΡΡΠ»Π΅Π½ΠΈΠ΅ ΠΈΠ½ΡΠΎΡΠΌΠ°ΡΠΈΠΈ ΠΈ ΡΠ΄Π΅Π»Π°Π½Ρ Π²ΡΠ²ΠΎΠ΄Ρ, Π½Π°ΡΠΈΠ½Π°Π΅ΡΡΡ ΡΠ»Π΅Π΄ΡΡΡΠΈΠΉ ΡΡΠ°ΠΏ. ΠΠΎΠ·Π½ΠΈΠΊΠ°ΡΡ ΠΈΠ΄Π΅ΠΈ: Π° ΡΡΠΎ Π±ΡΠ΄Π΅Ρ, Π΅ΡΠ»ΠΈ ΠΏΠΎΡΠΌΠΎΡΡΠ΅ΡΡ Π½Π° Π΄Π°Π½Π½ΡΠ΅ Ρ Π΄ΡΡΠ³ΠΎΠΉ ΡΡΠΎΡΠΎΠ½Ρ?
ΠΠ° ΡΡΠΎΠΌ ΡΡΠ°ΠΏΠ΅ Π΅ΡΡΡ ΠΎΠ³ΡΠ°Π½ΠΈΡΠ΅Π½ΠΈΡ ΠΈΠ½ΡΡΡΡΠΌΠ΅Π½ΡΠΎΠ² Π°Π½Π°Π»ΠΈΠ·Π°. ΠΡΠΎ ΠΎΠ΄Π½Π° ΠΈΠ· ΠΏΡΠΈΡΠΈΠ½, ΠΏΠΎΡΠ΅ΠΌΡ ΠΌΠ½Π΅ Π±ΡΠ»ΠΎ Π½Π΅Π΄ΠΎΡΡΠ°ΡΠΎΡΠ½ΠΎ ΠΈΠ½ΡΡΡΡΠΌΠ΅Π½ΡΠ° Google Analytics, Π° ΠΈΠΌΠ΅Π½Π½ΠΎ, ΠΈΠ·-Π·Π° ΠΎΠ³ΡΠ°Π½ΠΈΡΠ΅Π½Π½ΠΎΠΉ Π²ΠΎΠ·ΠΌΠΎΠΆΠ½ΠΎΡΡΠΈ Π²ΠΈΠ΄Π΅ΡΡ ΡΠ²ΠΎΠΈ Π΄Π°Π½Π½ΡΠ΅ ΠΈ ΠΌΠ°Π½ΠΈΠΏΡΠ»ΠΈΡΠΎΠ²Π°ΡΡ ΠΈΠΌΠΈ.
ΠΡΠ΅Π³Π΄Π° Ρ ΠΎΡΠ΅Π»ΠΎΡΡ Π±ΡΡΡΡΠΎ Π·Π°Π³ΡΡΠ·ΠΈΡΡ Π±Π°Π·ΠΎΠ²ΡΠ΅ Π΄Π°Π½Π½ΡΠ΅ (ΠΌΠ°ΡΡΠ΅Ρ-Π΄Π°Π½Π½ΡΠ΅), Π΄ΠΎΠ±Π°Π²ΠΈΡΡ Π΄ΡΡΠ³ΠΎΠΉ ΡΡΠΎΠ²Π΅Π½Ρ Π°Π³ΡΠ΅Π³Π°ΡΠΈΠΈ ΠΈΠ»ΠΈ ΠΈΠ½Π°ΡΠ΅ ΠΈΠ½ΡΠ΅ΡΠΏΡΠ΅ΡΠΈΡΠΎΠ²Π°ΡΡ ΠΈΠΌΠ΅ΡΡΠΈΠ΅ΡΡ Π·Π½Π°ΡΠ΅Π½ΠΈΡ.
ΠΡΠΎ Π»Π΅Π³ΠΊΠΎ ΡΠ΄Π΅Π»Π°ΡΡ Π²
ΠΡΠ°ΠΊ, Π½Π° ΠΊΠ°ΠΊΠΈΠ΅ Π²ΠΎΠΏΡΠΎΡΡ ΠΌΠ½Π΅ Ρ ΠΎΡΠ΅Π»ΠΎΡΡ Π½Π°ΠΉΡΠΈ ΠΎΡΠ²Π΅Ρ?
Π§ΡΠΎ ΠΈ ΠΊΠΎΠ³Π΄Π° ΠΈΠ·ΠΌΠ΅Π½ΡΠ»ΠΎΡΡ Π½Π° ΡΠ°ΠΉΡΠ΅
ΠΡΡΠΎΡΠΈΡ ΠΈΠ·ΠΌΠ΅Π½Π΅Π½ΠΈΠΉ Π±Π°Π·ΠΎΠ²ΡΡ Π΄Π°Π½Π½ΡΡ (ΠΌΠ°ΡΡΠ΅Ρ-Π΄Π°Π½Π½ΡΡ ) Π²ΡΠ΅Π³Π΄Π° ΠΏΡΠ΅Π΄ΡΡΠ°Π²Π»ΡΠ΅Ρ ΡΠΎΠ±ΠΎΠΉ ΠΈΠ½ΡΠ΅ΡΠ΅Ρ.
SQL Π·Π°ΠΏΡΠΎΡ ΠΎΡΡΡΡΠ°
SELECT
1 as 'SideStackedBar: Content Updates by Months',
strftime('%m/%Y', datetime(UPDATE_DT, 'unixepoch')) AS 'Day',
COUNT(CASE WHEN PAGE_TITLE != 'n.a.' THEN DIM_REQUEST_ID END) AS 'Web page updates',
COUNT(CASE WHEN PAGE_DESCR = 'IMAGES' THEN DIM_REQUEST_ID END) AS 'Image uploads',
COUNT(CASE WHEN PAGE_DESCR = 'VIDEO' THEN DIM_REQUEST_ID END) AS 'Video uploads',
COUNT(CASE WHEN PAGE_DESCR = 'AUDIO' THEN DIM_REQUEST_ID END) AS 'Audio uploads'
FROM DIM_REQUEST
WHERE PAGE_TITLE != 'n.a.' OR PAGE_DESCR != 'n.a.'
GROUP BY strftime('%m/%Y', datetime(UPDATE_DT, 'unixepoch'))
ORDER BY UPDATE_DT
ΠΠ°ΠΏΡΠΈΠΌΠ΅Ρ, Π² ΠΊΠ°ΠΊΠΎΠΉ-ΡΠΎ ΠΌΠΎΠΌΠ΅Π½Ρ Π±ΡΠ»Π° ΠΏΡΠΎΠ²Π΅Π΄Π΅Π½Π° ΠΏΠΎΠΈΡΠΊΠΎΠ²Π°Ρ ΠΎΠΏΡΠΈΠΌΠΈΠ·Π°ΡΠΈΡ ΠΈΠ»ΠΈ Π΄ΠΎΠ±Π°Π²Π»Π΅Π½ΠΎ Π½ΠΎΠ²ΠΎΠ΅ ΡΠΎΠ΄Π΅ΡΠΆΠΈΠΌΠΎΠ΅ Π½Π° ΡΠ°ΠΉΡ, Π² ΡΠ²ΡΠ·ΠΈ Ρ ΡΡΠΈΠΌ ΠΎΠΆΠΈΠ΄Π°Π΅ΡΡΡ ΡΠ²Π΅Π»ΠΈΡΠ΅Π½ΠΈΠ΅ ΡΡΠ°ΡΠΈΠΊΠ°.
ΠΡΡΠΏΠΏΡ ΠΏΠΎΠ»ΡΠ·ΠΎΠ²Π°ΡΠ΅Π»Π΅ΠΉ
Π‘Π°ΠΌΡΠΌ ΠΏΡΠΎΡΡΡΠΌ ΠΏΡΠΈΠΌΠ΅ΡΠΎΠΌ Π³ΡΡΠΏΠΏΡ ΠΌΠΎΠΆΠ΅Ρ ΡΠ»ΡΠΆΠΈΡΡ ΠΏΠΎΠ»ΡΠ·ΠΎΠ²Π°ΡΠ΅Π»ΡΡΠΊΠΈΠΉ Π°Π³Π΅Π½Ρ ΠΈΠ»ΠΈ Π½Π°Π·Π²Π°Π½ΠΈΠ΅ ΠΎΠΏΠ΅ΡΠ°ΡΠΈΠ²Π½ΠΎΠΉ ΡΠΈΡΡΠ΅ΠΌΡ.
ΠΠ·ΠΌΠ΅ΡΠ΅Π½ΠΈΠ΅ ΠΏΠΎΠ»ΡΠ·ΠΎΠ²Π°ΡΠ΅Π»ΡΡΠΊΠΈΡ Π°Π³Π΅Π½ΡΠΎΠ² Π½Π°ΠΊΠΎΠΏΠΈΠ»ΠΎ Π² ΡΠ΅Π±Π΅ ΠΎΠΊΠΎΠ»ΠΎ ΡΡΡΡΡΠΈ Π·Π°ΠΏΠΈΡΠ΅ΠΉ ΠΈ ΠΌΠ½Π΅ ΠΈΠ½ΡΠ΅ΡΠ΅ΡΠ½ΠΎ Π±ΡΠ»ΠΎ ΡΠ²ΠΈΠ΄Π΅ΡΡ Π΄ΠΈΠ½Π°ΠΌΠΈΠΊΡ ΡΠ°ΡΠΏΡΠ΅Π΄Π΅Π»Π΅Π½ΠΈΡ Π°Π³Π΅Π½ΡΠΎΠ² Π² ΠΏΡΠ΅Π΄Π΅Π»Π°Ρ Π³ΡΡΠΏΠΏΡ.
SQL Π·Π°ΠΏΡΠΎΡ ΠΎΡΡΡΡΠ°
SELECT
1 AS 'SideStackedBar: User Agents',
AGENT_OS AS 'OS',
SUM(CASE WHEN AGENT_BOT = 'n.a.' THEN 1 ELSE 0 END ) AS 'User Agent of Users',
SUM(CASE WHEN AGENT_BOT != 'n.a.' THEN 1 ELSE 0 END ) AS 'User Agent of Bots'
FROM DIM_USER_AGENT
WHERE DIM_USER_AGENT_ID != -1
GROUP BY AGENT_OS
ORDER BY 3 DESC
ΠΠΎΠ»ΡΡΠ΅ Π²ΡΠ΅Π³ΠΎ ΡΠ°Π·Π»ΠΈΡΠ½ΡΡ ΠΊΠΎΠΌΠ±ΠΈΠ½Π°ΡΠΈΠΉ Π°Π³Π΅Π½ΡΠΎΠ² ΠΏΡΠΈΡ ΠΎΠ΄ΠΈΡ Π½Π° ΡΠ°ΠΉΡ ΠΈΠ· ΠΌΠΈΡΠ° Windows. Π ΡΠΈΡΠ»Π΅ Π½Π΅ΠΎΠΏΡΠ΅Π΄Π΅Π»ΡΠ½Π½ΡΡ ΠΎΠΊΠ°Π·Π°Π»ΠΈΡΡ ΡΠ°ΠΊΠΈΠ΅, ΠΊΠ°ΠΊ WhatsApp, PocketImageCache, PlayStation, SmartTV ΠΈ Ρ.ΠΏ.
ΠΠΊΡΠΈΠ²Π½ΠΎΡΡΡ Π³ΡΡΠΏΠΏ ΠΏΠΎΠ»ΡΠ·ΠΎΠ²Π°ΡΠ΅Π»Π΅ΠΉ ΠΏΠΎ Π½Π΅Π΄Π΅Π»ΡΠΌ
ΠΠ±ΡΠ΅Π΄ΠΈΠ½ΠΈΠ² Π½Π΅ΠΊΠΎΡΠΎΡΡΠ΅ Π³ΡΡΠΏΠΏΡ, ΠΌΠΎΠΆΠ½ΠΎ Π½Π°Π±Π»ΡΠ΄Π°ΡΡ ΡΠ°ΡΠΏΡΠ΅Π΄Π΅Π»Π΅Π½ΠΈΠ΅ ΠΈΡ Π°ΠΊΡΠΈΠ²Π½ΠΎΡΡΠΈ.
ΠΠ°ΠΏΡΠΈΠΌΠ΅Ρ, ΠΏΠΎΠ»ΡΠ·ΠΎΠ²Π°ΡΠ΅Π»ΠΈ ΠΊΠ»Π°ΡΡΠ΅ΡΠ° Linux ΠΏΠΎΡΡΠ΅Π±Π»ΡΡΡ Π±ΠΎΠ»ΡΡΠ΅ ΡΡΠ°ΡΠΈΠΊΠ° Π½Π° ΡΠ°ΠΉΡΠ΅, ΡΠ΅ΠΌ Π²ΡΠ΅ ΠΎΡΡΠ°Π»ΡΠ½ΡΠ΅.
SQL Π·Π°ΠΏΡΠΎΡ ΠΎΡΡΡΡΠ°
SELECT
1 as 'StackedBar: Traffic Volume by User OS and by Week',
strftime('%W week', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Week',
SUM(CASE WHEN USG.AGENT_OS IN ('Android', 'Linux') THEN FCT.BYTES ELSE 0 END)/1000 AS 'Android/Linux Users',
SUM(CASE WHEN USG.AGENT_OS IN ('Windows') THEN FCT.BYTES ELSE 0 END)/1000 AS 'Windows Users',
SUM(CASE WHEN USG.AGENT_OS IN ('Macintosh', 'iOS') THEN FCT.BYTES ELSE 0 END)/1000 AS 'Mac/iOS Users',
SUM(CASE WHEN USG.AGENT_OS IN ('n.a.', 'BlackBerry') THEN FCT.BYTES ELSE 0 END)/1000 AS 'Other'
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
ΠΠ½ΡΠ΅Π½ΡΠΈΠ²Π½ΠΎΠ΅ ΠΏΠΎΡΡΠ΅Π±Π»Π΅Π½ΠΈΠ΅ ΡΡΠ°ΡΠΈΠΊΠ°
ΠΠ· ΡΠ°Π±Π»ΠΈΡΡ Π²ΠΈΠ΄Π½Ρ Π½Π°ΠΈΠ±ΠΎΠ»Π΅Π΅ Π°ΠΊΡΠΈΠ²Π½ΡΠ΅ Π³ΡΡΠΏΠΏΡ ΠΏΠΎΠ»ΡΠ·ΠΎΠ²Π°ΡΠ΅Π»Π΅ΠΉ ΠΈ Π΄Π΅Π½Ρ ΠΈΡ
Π°ΠΊΡΠΈΠ²Π½ΠΎΡΡΠΈ.
ΠΠ°ΠΈΠ±ΠΎΠ»Π΅Π΅ Π°ΠΊΡΠΈΠ²Π½ΡΠ΅ ΠΎΡΠ½ΠΎΡΡΡΡΡ ΠΊ Linux ΠΊΠ»Π°ΡΡΠ΅ΡΡ.
SQL Π·Π°ΠΏΡΠΎΡ ΠΎΡΡΡΡΠ°
SELECT
1 AS 'Table: User Agent with Havy Usage',
strftime('%d.%m.%Y', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Day',
ROUND(1.0*SUM(FCT.BYTES)/1000000, 1) AS 'Traffic MB',
ROUND(1.0*SUM(FCT.IP_CNT)/SUM(1), 1) AS 'IPs',
ROUND(1.0*SUM(FCT.REQUEST_CNT)/SUM(1), 1) AS 'Requests',
USA.DIM_USER_AGENT_ID AS 'ID',
MAX(USA.USER_AGENT_NK) AS 'User Agent',
MAX(USA.AGENT_BOT) AS 'Bot'
FROM
FCT_ACCESS_USER_AGENT_DD FCT,
DIM_USER_AGENT USA
WHERE FCT.DIM_USER_AGENT_ID = USA.DIM_USER_AGENT_ID
AND datetime(FCT.EVENT_DT, 'unixepoch') >= date('now', '-30 day')
GROUP BY USA.DIM_USER_AGENT_ID, strftime('%d.%m.%Y', datetime(FCT.EVENT_DT, 'unixepoch'))
ORDER BY SUM(FCT.BYTES) DESC, FCT.EVENT_DT
LIMIT 10
ΠΡΠΏΠΎΠ»ΡΠ·ΡΡ Π°ΡΡΠΈΠ±ΡΡΡ Π΄Π΅Π½Ρ ΠΈ ID Π°Π³Π΅Π½ΡΠ°, ΠΏΠΎΡΠ²Π»ΡΠ΅ΡΡΡ Π²ΠΎΠ·ΠΌΠΎΠΆΠ½ΠΎΡΡΡ Π±ΡΡΡΡΠΎ Π½Π°ΠΉΡΠΈ ΠΈ ΠΏΡΠΎΡΠ»Π΅Π΄ΠΈΡΡ ΡΡΠ°ΡΠΈΡΡΠΈΠΊΡ ΠΏΠΎ Π΄Π½ΡΠΌ ΠΎΡΠ΄Π΅Π»ΡΠ½ΡΡ Π³ΡΡΠΏΠΏ ΠΏΠΎΠ»ΡΠ·ΠΎΠ²Π°ΡΠ΅Π»Π΅ΠΉ. Π ΡΠ»ΡΡΠ°Π΅ Π½Π΅ΠΎΠ±Ρ ΠΎΠ΄ΠΈΠΌΠΎΡΡΠΈ, ΠΌΠΎΠΆΠ½ΠΎ Π±ΡΡΡΡΠΎ Π½Π°ΠΉΡΠΈ Π΄Π΅ΡΠ°Π»ΡΠ½ΡΡ ΠΈΠ½ΡΠΎΡΠΌΠ°ΡΠΈΡ Π² ΡΡΠ΅ΠΉΠ΄ΠΆ ΡΠ°Π±Π»ΠΈΡΠ΅.
ΠΠ°ΠΊ ΠΏΠΎΠ»ΡΡΠΈΡΡ ΠΈΠ½ΡΠΎΡΠΌΠ°ΡΠΈΡ?
ΠΠ°Π·ΠΎΠ²ΡΠ΅ Π΄Π°Π½Π½ΡΠ΅ ΠΈ ΡΡΡΠ½ΠΎΡΡΠΈ
Π Π±Π°Π·ΠΎΠ²ΡΠΌ Π΄Π°Π½Π½ΡΠΌ ΠΎΡΠ½ΠΎΡΠΈΡΡΡ ΠΈΠ½ΡΠΎΡΠΌΠ°ΡΠΈΡ ΠΎ ΡΡΡΠ½ΠΎΡΡΡΡ : Π²Π΅Π± ΡΡΡΠ°Π½ΠΈΡΡ, ΠΊΠ°ΡΡΠΈΠ½ΠΊΠΈ, Π²ΠΈΠ΄Π΅ΠΎ ΠΈ Π°ΡΠ΄ΠΈΠΎ ΡΠΎΠ΄Π΅ΡΠΆΠΈΠΌΠΎΠ΅, Π² ΡΠ»ΡΡΠ°Π΅ ΠΌΠ°Π³Π°Π·ΠΈΠ½Π° β ΠΏΡΠΎΠ΄ΡΠΊΡΡ.
Π‘Π°ΠΌΠΈ ΡΡΡΠ½ΠΎΡΡΠΈ Π²ΡΠΏΠΎΠ»Π½ΡΡΡ ΡΠΎΠ»Ρ ΠΈΠ·ΠΌΠ΅ΡΠ΅Π½ΠΈΠΉ, Π° ΠΏΡΠΎΡΠ΅ΡΡ ΡΠΎΡ ΡΠ°Π½Π΅Π½ΠΈΡ ΠΈΠ·ΠΌΠ΅Π½Π΅Π½ΠΈΠΉ Π°ΡΡΠΈΠ±ΡΡΠΎΠ² Π½Π°Π·ΡΠ²Π°ΡΡ ΠΈΡΡΠΎΡΠΈΠ·Π°ΡΠΈΠ΅ΠΉ. Π Π±Π°Π·Π΅ Π΄Π°Π½Π½ΡΡ ΡΡΠΎΡ ΠΏΡΠΎΡΠ΅ΡΡ ΡΠ°ΡΡΠΎ ΡΠ΅Π°Π»ΠΈΠ·ΡΡΡ Π² ΡΠΎΡΠΌΠ΅ ΠΌΠ΅Π΄Π»Π΅Π½Π½ΠΎ ΠΈΠ·ΠΌΠ΅Π½ΡΡΡΠΈΡ ΡΡ ΠΈΠ·ΠΌΠ΅ΡΠ΅Π½ΠΈΠΉ (SCD).
ΠΡΡΠΎΡΠ½ΠΈΠΊΠΎΠΌ Π±Π°Π·ΠΎΠ²ΡΡ Π΄Π°Π½Π½ΡΡ ΠΌΠΎΠ³ΡΡ Π±ΡΡΡ ΡΠ°ΠΌΡΠ΅ ΡΠ°Π·Π½ΡΠ΅ ΡΠΈΡΡΠ΅ΠΌΡ, ΠΏΠΎΡΡΠΎΠΌΡ ΠΏΠΎΡΡΠΈ Π²ΡΠ΅Π³Π΄Π° ΠΈΡ Π½ΡΠΆΠ½ΠΎ ΠΈΠ½ΡΠ΅Π³ΡΠΈΡΠΎΠ²Π°ΡΡ.
ΠΠ΅Π΄Π»Π΅Π½Π½ΠΎ ΠΈΠ·ΠΌΠ΅Π½ΡΡΡΠ΅Π΅ΡΡ ΠΈΠ·ΠΌΠ΅ΡΠ΅Π½ΠΈΠ΅
ΠΠ·ΠΌΠ΅ΡΠ΅Π½ΠΈΠ΅ DIM_REQUEST Π±ΡΠ΄Π΅Ρ ΡΠΎΠ΄Π΅ΡΠΆΠ°ΡΡ ΠΈΠ½ΡΠΎΡΠΌΠ°ΡΠΈΡ ΠΎ Π·Π°ΠΏΡΠΎΡΠ°Ρ Π½Π° ΡΠ°ΠΉΡΠ΅ Π² ΠΈΡΡΠΎΡΠΈΡΠ΅ΡΠΊΠΎΠΉ ΡΠΎΡΠΌΠ΅.
Π’Π°Π±Π»ΠΈΡΠ° SCD2
CREATE TABLE DIM_REQUEST ( /* scd table for user requests */
DIM_REQUEST_ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
DIM_REQUEST_ID_HIST INTEGER NOT NULL DEFAULT -1,
REQUEST_NK TEXT NOT NULL DEFAULT 'n.a.', /* request without ?parameters */
PAGE_TITLE TEXT NOT NULL DEFAULT 'n.a.',
PAGE_DESCR TEXT NOT NULL DEFAULT 'n.a.',
PAGE_KEYWORDS TEXT NOT NULL DEFAULT 'n.a.',
DELETE_FLAG INTEGER NOT NULL DEFAULT 0,
UPDATE_DT INTEGER NOT NULL DEFAULT 0,
UNIQUE (REQUEST_NK, DIM_REQUEST_ID_HIST)
);
INSERT INTO DIM_REQUEST (DIM_REQUEST_ID) VALUES (-1);
ΠΠΎΠΏΠΎΠ»Π½ΠΈΡΠ΅Π»ΡΠ½ΠΎ ΠΊ Π½Π΅ΠΌΡ ΡΠΎΠ·Π΄Π°Π΄ΠΈΠΌ ΠΎΠ΄Π½ΠΎ ΠΏΡΠ΅Π΄ΡΡΠ°Π²Π»Π΅Π½ΠΈΠ΅, ΠΊΠΎΡΠΎΡΠΎΠ΅ Π²ΡΠ΅Π³Π΄Π° ΠΎΡΠΎΠ±ΡΠ°ΠΆΠ°Π΅Ρ Π²ΡΠ΅ Π·Π°ΠΏΠΈΡΠΈ Π² ΠΏΠΎΡΠ»Π΅Π΄Π½Π΅ΠΌ ΡΠΎΡΡΠΎΡΠ½ΠΈΠΈ. ΠΠ΅ΠΎΠ±Ρ ΠΎΠ΄ΠΈΠΌΠΎ Π΄Π»Ρ Π·Π°Π³ΡΡΠ·ΠΊΠΈ ΡΠ°ΠΌΠΎΠ³ΠΎ ΠΈΠ·ΠΌΠ΅ΡΠ΅Π½ΠΈΡ.
ΠΠΊΡΡΠ°Π»ΡΠ½ΠΎΠ΅ ΠΏΡΠ΅Π΄ΡΡΠ°Π²Π»Π΅Π½ΠΈΠ΅ SCD2
/* Content: actual view on scd table */
SELECT HI.DIM_REQUEST_ID,
HI.DIM_REQUEST_ID_HIST,
HI.REQUEST_NK,
HI.PAGE_TITLE,
HI.PAGE_DESCR,
HI.PAGE_KEYWORDS,
NK.CNT AS HIST_CNT,
HI.DELETE_FLAG,
strftime('%d.%m.%Y %H:%M', datetime(HI.UPDATE_DT, 'unixepoch')) AS UPDATE_DT
FROM
( SELECT REQUEST_NK, MAX(DIM_REQUEST_ID) AS DIM_REQUEST_ID, SUM(1) AS CNT
FROM DIM_REQUEST
GROUP BY REQUEST_NK
) NK,
DIM_REQUEST HI
WHERE 1 = 1
AND NK.REQUEST_NK = HI.REQUEST_NK
AND NK.DIM_REQUEST_ID = HI.DIM_REQUEST_ID;
Π ΠΏΡΠ΅Π΄ΡΡΠ°Π²Π»Π΅Π½ΠΈΠ΅, Π³Π΄Π΅ Π΄Π»Ρ ΠΊΠ°ΠΆΠ΄ΠΎΠΉ Π·Π°ΠΏΠΈΡΠΈ ΡΠΎΠ±ΡΠ°Π½Π° ΠΈΡΡΠΎΡΠΈΡΠ΅ΡΠΊΠ°Ρ ΠΈΠ½ΡΠΎΡΠΌΠ°ΡΠΈΡ. ΠΠ΅ΠΎΠ±Ρ ΠΎΠ΄ΠΈΠΌΠΎ Π΄Π»Ρ ΠΏΠΎΡΡΡΠΎΠ΅Π½ΠΈΡ ΠΈΡΡΠΎΡΠΈΡΠ΅ΡΠΊΠΈ Π²Π΅ΡΠ½ΠΎΠΉ ΡΠ²ΡΠ·ΠΈ Ρ ΡΠ°ΠΊΡΠ°ΠΌΠΈ.
ΠΡΡΠΎΡΠΈΡΠ΅ΡΠΊΠΎΠ΅ ΠΏΡΠ΅Π΄ΡΡΠ°Π²Π»Π΅Π½ΠΈΠ΅ SCD2
/* Content: actual view on scd table */
SELECT SCD.DIM_REQUEST_ID,
SCD.DIM_REQUEST_ID_HIST,
SCD.REQUEST_NK,
SCD.PAGE_TITLE,
SCD.PAGE_DESCR,
SCD.PAGE_KEYWORDS,
SCD.DELETE_FLAG,
CASE
WHEN HIS.UPDATE_DT IS NULL
THEN 1
ELSE 0 END ACTIVE_FLAG,
SCD.DIM_REQUEST_ID_HIST AS ID_FROM,
SCD.DIM_REQUEST_ID AS ID_TO,
CASE
WHEN SCD.DIM_REQUEST_ID_HIST=-1
THEN 3600
ELSE IFNULL(SCD.UPDATE_DT,3600)
END AS TIME_FROM,
CASE
WHEN HIS.UPDATE_DT IS NULL
THEN 253370764800
ELSE HIS.UPDATE_DT
END AS TIME_TO,
CASE
WHEN SCD.DIM_REQUEST_ID_HIST=-1
THEN STRFTIME('%d.%m.%Y %H:%M', DATETIME(3600, 'unixepoch'))
ELSE STRFTIME('%d.%m.%Y %H:%M', DATETIME(IFNULL(SCD.UPDATE_DT,3600), 'unixepoch'))
END AS ACTIVE_FROM,
CASE
WHEN HIS.UPDATE_DT IS NULL
THEN STRFTIME('%d.%m.%Y %H:%M', DATETIME(253370764800, 'unixepoch'))
ELSE STRFTIME('%d.%m.%Y %H:%M', DATETIME(HIS.UPDATE_DT, 'unixepoch'))
END AS ACTIVE_TO
FROM
DIM_REQUEST SCD
LEFT OUTER JOIN DIM_REQUEST HIS
ON SCD.REQUEST_NK = HIS.REQUEST_NK AND SCD.DIM_REQUEST_ID = HIS.DIM_REQUEST_ID_HIST;
ΠΠ³ΡΠ΅Π³Π°ΡΠΈΡ Π΄Π°Π½Π½ΡΡ
Π‘ΠΆΠ°ΡΠΈΠ΅ (Π°Π³ΡΠ΅Π³Π°ΡΠΈΡ) ΠΏΠΎΠ·Π²ΠΎΠ»ΡΠ΅Ρ ΠΎΡΠ΅Π½ΠΈΡΡ Π΄Π°Π½Π½ΡΠ΅ Π½Π° Π±ΠΎΠ»Π΅Π΅ Π²ΡΡΠΎΠΊΠΎΠΌ ΡΡΠΎΠ²Π½Π΅ ΠΈ ΠΎΠ±Π½Π°ΡΡΠΆΠΈΡΡ Π°Π½ΠΎΠΌΠ°Π»ΠΈΠΈ ΠΈ ΡΠ΅Π½Π΄Π΅Π½ΡΠΈΠΈ, ΠΊΠΎΡΠΎΡΡΠ΅ Π½Π΅ Π²ΠΈΠ΄Π½Ρ Π² Π΄Π΅ΡΠ°Π»ΡΠ½ΡΡ ΠΎΡΡΡΡΠ°Ρ .
ΠΠ°ΠΏΡΠΈΠΌΠ΅Ρ, Π² ΠΈΠ·ΠΌΠ΅ΡΠ΅Π½ΠΈΠ΅ Ρ ΠΊΠΎΠ΄Π°ΠΌΠΈ ΡΡΠ°ΡΡΡΠ° Π·Π°ΠΏΡΠΎΡΠΎΠ² DIM_HTTP_STATUS Π΄ΠΎΠ±Π°Π²ΠΈΠΌ Π³ΡΡΠΏΠΏΡ:
STATUS / GROUP
0xx / n.a.
1xx / Informational
2xx / Successful
3xx / Redirection
4xx / Client Error
5xx / Server Error
ΠΠ·ΠΌΠ΅ΡΠ΅Π½ΠΈΠ΅ ΠΏΠΎΠ»ΡΠ·ΠΎΠ²Π°ΡΠ΅Π»ΡΡΠΊΠΈΡ Π°Π³Π΅Π½ΡΠΎΠ² DIM_USER_AGENT Π±ΡΠ΄Π΅Ρ ΡΠΎΠ΄Π΅ΡΠΆΠ°ΡΡ Π°ΡΡΠΈΠ±ΡΡΡ AGENT_OS ΠΈ AGENT_BOT, ΠΎΡΠ²Π΅ΡΠ°ΡΡΠΈΠ΅ Π·Π° Π³ΡΡΠΏΠΏΡ. ΠΡ ΠΌΠΎΠΆΠ½ΠΎ Π·Π°ΠΏΠΎΠ»Π½ΡΡΡ Π² ΠΏΡΠΎΡΠ΅ΡΡΠ΅ ETL:
ΠΠ°Π³ΡΡΠ·ΠΊΠ° DIM_USER_AGENT
/* Propagate the user agent from access log */
INSERT INTO DIM_USER_AGENT (USER_AGENT_NK, AGENT_OS, AGENT_ENGINE, AGENT_DEVICE, AGENT_BOT, UPDATE_DT)
WITH CLS AS (
SELECT BROWSER
FROM STG_ACCESS_LOG WHERE LENGTH(BROWSER)>1
GROUP BY BROWSER
)
SELECT
CLS.BROWSER AS USER_AGENT_NK,
CASE
WHEN INSTR(CLS.BROWSER,'Macintosh')>0
THEN 'Macintosh'
WHEN INSTR(CLS.BROWSER,'iPhone')>0
OR INSTR(CLS.BROWSER,'iPad')>0
OR INSTR(CLS.BROWSER,'iPod')>0
OR INSTR(CLS.BROWSER,'Apple TV')>0
OR INSTR(CLS.BROWSER,'Darwin')>0
THEN 'iOS'
WHEN INSTR(CLS.BROWSER,'Android')>0
THEN 'Android'
WHEN INSTR(CLS.BROWSER,'X11;')>0 OR INSTR(CLS.BROWSER,'Wayland;')>0 OR INSTR(CLS.BROWSER,'linux-gnu')>0
THEN 'Linux'
WHEN INSTR(CLS.BROWSER,'BB10;')>0 OR INSTR(CLS.BROWSER,'BlackBerry')>0
THEN 'BlackBerry'
WHEN INSTR(CLS.BROWSER,'Windows')>0
THEN 'Windows'
ELSE 'n.a.' END AS AGENT_OS, -- OS
CASE
WHEN INSTR(CLS.BROWSER,'AppleCoreMedia')>0
THEN 'AppleWebKit'
WHEN INSTR(CLS.BROWSER,') ')>1 AND LENGTH(CLS.BROWSER)>INSTR(CLS.BROWSER,') ')
THEN COALESCE(SUBSTR(CLS.BROWSER, INSTR(CLS.BROWSER,') ')+2, LENGTH(CLS.BROWSER) - INSTR(CLS.BROWSER,') ')-1), 'N/A')
ELSE 'n.a.' END AS AGENT_ENGINE, -- Engine
CASE
WHEN INSTR(CLS.BROWSER,'iPhone')>0
THEN 'iPhone'
WHEN INSTR(CLS.BROWSER,'iPad')>0
THEN 'iPad'
WHEN INSTR(CLS.BROWSER,'iPod')>0
THEN 'iPod'
WHEN INSTR(CLS.BROWSER,'Apple TV')>0
THEN 'Apple TV'
WHEN INSTR(CLS.BROWSER,'Android ')>0 AND INSTR(CLS.BROWSER,'Build')>0
THEN COALESCE(SUBSTR(CLS.BROWSER, INSTR(CLS.BROWSER,'Android '), INSTR(CLS.BROWSER,'Build')-INSTR(CLS.BROWSER,'Android ')), 'n.a.')
WHEN INSTR(CLS.BROWSER,'Android ')>0 AND INSTR(CLS.BROWSER,'MIUI')>0
THEN COALESCE(SUBSTR(CLS.BROWSER, INSTR(CLS.BROWSER,'Android '), INSTR(CLS.BROWSER,'MIUI')-INSTR(CLS.BROWSER,'Android ')), 'n.a.')
ELSE 'n.a.' END AS AGENT_DEVICE, -- Device
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),'jobboersebot')>0 OR INSTR(LOWER(CLS.BROWSER),'jobkicks')>0
THEN 'job.de'
WHEN INSTR(LOWER(CLS.BROWSER),'mail.ru')>0
THEN 'mail.ru'
WHEN INSTR(LOWER(CLS.BROWSER),'baiduspider')>0
THEN 'baidu'
WHEN INSTR(LOWER(CLS.BROWSER),'mj12bot')>0
THEN 'majestic-12'
WHEN INSTR(LOWER(CLS.BROWSER),'duckduckgo')>0
THEN 'duckduckgo'
WHEN INSTR(LOWER(CLS.BROWSER),'bytespider')>0
THEN 'bytespider'
WHEN INSTR(LOWER(CLS.BROWSER),'360spider')>0
THEN 'so.360.cn'
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, -- Bot
STRFTIME('%s','now') AS UPDATE_DT
FROM CLS
LEFT OUTER JOIN DIM_USER_AGENT TRG
ON CLS.BROWSER = TRG.USER_AGENT_NK
WHERE TRG.DIM_USER_AGENT_ID IS NULL
ΠΠ½ΡΠ΅Π³ΡΠ°ΡΠΈΡ Π΄Π°Π½Π½ΡΡ
ΠΠΊΠ»ΡΡΠ°Π΅Ρ Π² ΡΠ΅Π±Ρ ΠΎΡΠ³Π°Π½ΠΈΠ·Π°ΡΠΈΡ ΠΏΠ΅ΡΠ΅Π΄Π°ΡΠΈ Π΄Π°Π½Π½ΡΡ ΠΈΠ· ΠΎΠΏΠ΅ΡΠ°ΡΠΈΠΎΠ½Π½ΠΎΠΉ ΡΠΈΡΡΠ΅ΠΌΡ Π² ΠΎΡΡΡΡΠ½ΡΡ. ΠΠ»Ρ ΡΡΠΎΠ³ΠΎ Π½Π΅ΠΎΠ±Ρ ΠΎΠ΄ΠΈΠΌΠΎ ΡΠΎΠ·Π΄Π°ΡΡ ΡΡΠ΅ΠΉΠ΄ΠΆ ΡΠ°Π±Π»ΠΈΡΡ ΡΠΎ ΡΡΡΡΠΊΡΡΡΠΎΠΉ, Π°Π½Π°Π»ΠΎΠ³ΠΈΡΠ½ΠΎΠΉ ΠΈΡΡΠΎΡΠ½ΠΈΠΊΡ.
Π ΡΡΠ΅ΠΉΠ΄ΠΆ ΠΈΠ½ΡΠΎΡΠΌΠ°ΡΠΈΡ ΠΎ Π²Π΅Π± ΡΡΡΠ°Π½ΠΈΡΠ°Ρ ΠΏΠΎΠΏΠ°Π΄Π°Π΅Ρ ΠΈΠ· Π±ΡΠΊΠ°ΠΏΠ° CMS Π² Π²ΠΈΠ΄Π΅ Π·Π°ΠΏΡΠΎΡΠΎΠ² Π²ΡΡΠ°Π²ΠΊΠΈ.
ΠΠ°Π³ΡΡΠ·ΠΊΠ° ΠΈΡΡΠΎΡΠΈΡΠ΅ΡΠΊΠΎΠΉ ΡΠ°Π±Π»ΠΈΡΡ DIM_REQUEST Π±Π°Π·ΠΎΠ²ΡΠΌΠΈ Π΄Π°Π½Π½ΡΠΌΠΈ ΠΏΡΠΎΠΈΡΡ ΠΎΠ΄ΠΈΡ Π² ΡΡΠΈ ΡΠ°Π³Π°: Π·Π°Π³ΡΡΠ·ΠΊΠ° Π½ΠΎΠ²ΡΡ ΠΊΠ»ΡΡΠ΅ΠΉ ΠΈ Π°ΡΡΠΈΠ±ΡΡΠΎΠ², ΠΎΠ±Π½ΠΎΠ²Π»Π΅Π½ΠΈΠ΅ ΡΡΡΠ΅ΡΡΠ²ΡΡΡΠΈΡ ΠΈ ΡΠΈΠΊΡΠΈΡΠΎΠ²Π°Π½ΠΈΠ΅ ΡΠ΄Π°Π»ΡΠ½Π½ΡΡ Π·Π°ΠΏΠΈΡΠ΅ΠΉ.
ΠΠ°Π³ΡΡΠ·ΠΊΠ° Π½ΠΎΠ²ΡΡ Π·Π°ΠΏΠΈΡΠ΅ΠΉ SCD2
/* Load request table SCD from master data */
INSERT INTO DIM_REQUEST (DIM_REQUEST_ID_HIST, REQUEST_NK, PAGE_TITLE, PAGE_DESCR, PAGE_KEYWORDS, DELETE_FLAG, UPDATE_DT)
WITH CLS AS ( -- prepare keys
SELECT
'/' || NAME AS REQUEST_NK,
TITLE AS PAGE_TITLE,
CASE WHEN DESCRIPTION = '' OR DESCRIPTION IS NULL
THEN 'n.a.' ELSE DESCRIPTION
END AS PAGE_DESCR,
CASE WHEN KEYWORDS = '' OR KEYWORDS IS NULL
THEN 'n.a.' ELSE KEYWORDS
END AS PAGE_KEYWORDS
FROM STG_CMS_MENU
WHERE CONTENT_TYPE != 'folder' -- only web pages
AND PAGE_TITLE != 'n.a.' -- master data which make sense
)
/* new records from stage: CLS */
SELECT
-1 AS DIM_REQUEST_ID_HIST,
CLS.REQUEST_NK,
CLS.PAGE_TITLE,
CLS.PAGE_DESCR,
CLS.PAGE_KEYWORDS,
0 AS DELETE_FLAG,
STRFTIME('%s','now') AS UPDATE_DT
FROM CLS
LEFT OUTER JOIN
(
SELECT
DIM_REQUEST_ID,
REQUEST_NK,
PAGE_TITLE,
PAGE_DESCR,
PAGE_KEYWORDS
FROM DIM_REQUEST_V_ACT
) TRG ON CLS.REQUEST_NK = TRG.REQUEST_NK
WHERE TRG.REQUEST_NK IS NULL -- no such record in data mart
ΠΠ±Π½ΠΎΠ²Π»Π΅Π½ΠΈΠ΅ Π°ΡΡΠΈΠ±ΡΡΠΎΠ² SCD2
/* Load request table SCD from master data */
INSERT INTO DIM_REQUEST (DIM_REQUEST_ID_HIST, REQUEST_NK, PAGE_TITLE, PAGE_DESCR, PAGE_KEYWORDS, DELETE_FLAG, UPDATE_DT)
WITH CLS AS ( -- prepare keys
SELECT
'/' || NAME AS REQUEST_NK,
TITLE AS PAGE_TITLE,
CASE WHEN DESCRIPTION = '' OR DESCRIPTION IS NULL
THEN 'n.a.' ELSE DESCRIPTION
END AS PAGE_DESCR,
CASE WHEN KEYWORDS = '' OR KEYWORDS IS NULL
THEN 'n.a.' ELSE KEYWORDS
END AS PAGE_KEYWORDS
FROM STG_CMS_MENU
WHERE CONTENT_TYPE != 'folder' -- only web pages
AND PAGE_TITLE != 'n.a.' -- master data which make sense
)
/* updated records from stage: CLS and build reference to history: HIST */
SELECT
HIST.DIM_REQUEST_ID AS DIM_REQUEST_ID_HIST,
HIST.REQUEST_NK,
CLS.PAGE_TITLE,
CLS.PAGE_DESCR,
CLS.PAGE_KEYWORDS,
0 AS DELETE_FLAG,
STRFTIME('%s','now') AS UPDATE_DT
FROM CLS,
DIM_REQUEST_V_ACT TRG,
DIM_REQUEST HIST
WHERE CLS.REQUEST_NK = TRG.REQUEST_NK
AND TRG.DIM_REQUEST_ID = HIST.DIM_REQUEST_ID
AND ( CLS.PAGE_TITLE != HIST.PAGE_TITLE /* changes only */
OR CLS.PAGE_DESCR != HIST.PAGE_DESCR
OR CLS.PAGE_KEYWORDS != HIST.PAGE_KEYWORDS )
Π£Π΄Π°Π»ΡΠ½Π½ΡΠ΅ Π·Π°ΠΏΠΈΡΠΈ SCD2
/* Load request table SCD from master data */
INSERT INTO DIM_REQUEST (DIM_REQUEST_ID_HIST, REQUEST_NK, PAGE_TITLE, PAGE_DESCR, PAGE_KEYWORDS, DELETE_FLAG, UPDATE_DT)
WITH CLS AS ( -- prepare keys
SELECT
'/' || NAME AS REQUEST_NK,
TITLE AS PAGE_TITLE
FROM STG_CMS_MENU
WHERE CONTENT_TYPE != 'folder' -- only web pages
AND PAGE_TITLE != 'n.a.' -- master data which make sense
)
/* deleted records in data mart: TRG */
SELECT
TRG.DIM_REQUEST_ID AS DIM_REQUEST_ID_HIST,
TRG.REQUEST_NK,
TRG.PAGE_TITLE,
TRG.PAGE_DESCR,
TRG.PAGE_KEYWORDS,
1 AS DELETE_FLAG,
STRFTIME('%s','now') AS UPDATE_DT
FROM (
SELECT
DIM_REQUEST_ID,
REQUEST_NK,
PAGE_TITLE,
PAGE_DESCR,
PAGE_KEYWORDS
FROM DIM_REQUEST_V_ACT
WHERE PAGE_TITLE != 'n.a.' -- track master data only
AND DELETE_FLAG = 0 -- not already deleted
) TRG
LEFT OUTER JOIN CLS ON TRG.REQUEST_NK = CLS.REQUEST_NK
WHERE CLS.REQUEST_NK IS NULL -- no such record in stage
ΠΠ°ΠΆΠ΄ΡΠΉ ΠΈΡΡΠΎΡΠ½ΠΈΠΊ Π΄Π°Π½Π½ΡΡ Π½Π΅ΠΎΠ±Ρ ΠΎΠ΄ΠΈΠΌΠΎ ΡΠΎΠΏΡΠΎΠ²ΠΎΠ΄ΠΈΡΡ ΡΠΎΡΠΌΠ°Π»ΡΠ½ΡΠΌ ΠΎΠΏΠΈΡΠ°Π½ΠΈΠ΅ΠΌ, Π½Π°ΠΏΡΠΈΠΌΠ΅Ρ, Π² ΡΠ°ΠΉΠ»Π΅ readme.txt:
ΠΠΎΠ»ΡΡΠ°ΡΠ΅Π»Ρ Π΄Π°Π½Π½ΡΡ ΡΠΎΡΠΌΠ°Π»ΡΠ½ΠΎ/ΡΠ΅Ρ Π½ΠΈΡΠ΅ΡΠΊΠΈ: ΠΈΠΌΡ, ΡΠ»Π΅ΠΊΡΡΠΎΠ½Π½ΡΠΉ Π°Π΄ΡΠ΅Ρ
ΠΠΎΡΡΠ°Π²ΡΠΈΠΊ Π΄Π°Π½Π½ΡΡ ΡΠΎΡΠΌΠ°Π»ΡΠ½ΠΎ/ΡΠ΅Ρ Π½ΠΈΡΠ΅ΡΠΊΠΈ: ΠΈΠΌΡ, ΡΠ»Π΅ΠΊΡΡΠΎΠ½Π½ΡΠΉ Π°Π΄ΡΠ΅Ρ
ΠΡΡΠΎΡΠ½ΠΈΠΊ Π΄Π°Π½Π½ΡΡ : ΠΏΡΡΡ ΠΊ ΡΠ°ΠΉΠ»Ρ, Π½Π°Π·Π²Π°Π½ΠΈΡ ΡΠ΅ΡΠ²ΠΈΡΠΎΠ²
ΠΠ½ΡΠΎΡΠΌΠ°ΡΠΈΡ ΠΎ Π΄ΠΎΡΡΡΠΏΠ΅ ΠΊ Π΄Π°Π½Π½ΡΠΌ: ΠΏΠΎΠ»ΡΠ·ΠΎΠ²Π°ΡΠ΅Π»ΠΈ ΠΈ ΠΏΠ°ΡΠΎΠ»ΠΈ
Π‘Ρ Π΅ΠΌΠ° Π΄Π²ΠΈΠΆΠ΅Π½ΠΈΡ Π΄Π°Π½Π½ΡΡ ΠΏΠΎΠΌΠΎΠΆΠ΅Ρ Π² ΠΏΡΠΎΡΠ΅ΡΡΠ΅ ΡΠΎΠΏΡΠΎΠ²ΠΎΠΆΠ΄Π΅Π½ΠΈΡ ΠΈ ΠΎΠ±Π½ΠΎΠ²Π»Π΅Π½ΠΈΡ, Π½Π°ΠΏΡΠΈΠΌΠ΅Ρ, Π² ΡΠ΅ΠΊΡΡΠΎΠ²ΠΎΠΌ Π²ΠΈΠ΄Π΅:
ΠΠ΅ΡΠ΅ΠΌΠ΅ΡΠ΅Π½ΠΈΠ΅ ΡΠ°ΠΉΠ»Π°. ΠΡΡΠΎΡΠ½ΠΈΠΊ: ftp.domain.net: /logs/access.log Π¦Π΅Π»Ρ: /var/www/access.log
Π§ΡΠ΅Π½ΠΈΠ΅ Π² ΡΡΠ΅ΠΉΠ΄ΠΆ. Π¦Π΅Π»Ρ: STG_ACCESS_LOG
ΠΠ°Π³ΡΡΠ·ΠΊΠ° ΠΈ ΡΡΠ°Π½ΡΡΠΎΡΠΌΠ°ΡΠΈΡ. Π¦Π΅Π»Ρ: FCT_ACCESS_REQUEST_REF_HH
ΠΠ°Π³ΡΡΠ·ΠΊΠ° ΠΈ ΡΡΠ°Π½ΡΡΠΎΡΠΌΠ°ΡΠΈΡ. Π¦Π΅Π»Ρ: FCT_ACCESS_USER_AGENT_DD
ΠΡΡΡΡ. Π¦Π΅Π»Ρ: /var/www/report.html
ΠΡΠ²ΠΎΠ΄
Π’Π°ΠΊΠΈΠΌ ΠΎΠ±ΡΠ°Π·ΠΎΠΌ, ΡΡΠ°ΡΡΡ ΠΎΠΏΠΈΡΡΠ²Π°Π΅Ρ ΡΠ°ΠΊΠΈΠ΅ ΠΌΠ΅Ρ
Π°Π½ΠΈΠ·ΠΌΡ, ΠΊΠ°ΠΊ ΠΈΠ½ΡΠ΅Π³ΡΠ°ΡΠΈΡ Π±Π°Π·ΠΎΠ²ΡΡ
Π΄Π°Π½Π½ΡΡ
ΠΈ Π²Π²Π΅Π΄Π΅Π½ΠΈΠ΅ Π½ΠΎΠ²ΡΡ
ΡΡΠΎΠ²Π½Π΅ΠΉ Π°Π³ΡΠ΅Π³Π°ΡΠΈΠΈ. ΠΠ½ΠΈ Π½ΡΠΆΠ½Ρ ΠΏΡΠΈ ΠΏΠΎΡΡΡΠΎΠ΅Π½ΠΈΠΈ Ρ
ΡΠ°Π½ΠΈΠ»ΠΈΡ Π΄Π°Π½Π½ΡΡ
Ρ ΡΠ΅Π»ΡΡ ΠΏΠΎΠ»ΡΡΠ΅Π½ΠΈΡ Π΄ΠΎΠΏΠΎΠ»Π½ΠΈΡΠ΅Π»ΡΠ½ΡΡ
Π·Π½Π°Π½ΠΈΠΉ ΠΈ ΡΠ»ΡΡΡΠ΅Π½ΠΈΡ ΠΊΠ°ΡΠ΅ΡΡΠ²Π° ΠΈΠ½ΡΠΎΡΠΌΠ°ΡΠΈΠΈ.
ΠΡΡΠΎΡΠ½ΠΈΠΊ: habr.com