主持人 > 博客 > 管理 > 網站統計資料和您自己的小儲存空間

網站統計資料和您自己的小儲存空間

多年來，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，其中參數是請求的結果
'從 FCT_ACCESS_USER_AGENT_DD 選擇 COALESCE(MAX(EVENT_DT), '3600') 作為 LAST_EVENT_EPOCH'

此條件將只載入全天： CAST(STG.EVENT_DT AS INTEGER) < strftime('%s', date('now', 'start of day'))

對頁面或文件進行計數是透過搜尋點以原始方式進行的。

報告

在複雜的視覺化系統中，可以基於資料庫物件建立元模型，動態管理篩選器和聚合規則。 最終，所有像樣的工具都會產生 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'
);

該工具只是將結果表可視化。

產量

本文以Web分析為例，描述了建構資料倉儲所需的機制。 從結果可以看出，最簡單的工具足以對資料進行深度分析和視覺化。

未來，我們將以此儲存庫為例，嘗試實現緩慢變化的維度、元資料、聚合層級以及不同來源資料的整合等結構。

另外，讓我們仔細看看基於單一表格管理 ETL 流程的最簡單工具。

讓我們回到測量數據品質和自動化過程的主題。

我們將研究資料儲存的技術環境和維護問題，為此我們將實作一個資源最少的儲存伺服器，例如基於Raspberry Pi。

來源： www.habr.com

添加評論