Webalizer và Google Analytics đã giúp tôi hiểu rõ hơn về những gì đang diễn ra trên các trang web trong nhiều năm. Bây giờ tôi hiểu rằng họ cung cấp rất ít thông tin hữu ích. Có quyền truy cập vào tệp access.log của bạn, bạn có thể dễ dàng hiểu số liệu thống kê và triển khai các công cụ khá cơ bản, chẳng hạn như sqlite, html, ngôn ngữ sql và bất kỳ ngôn ngữ lập trình tập lệnh nào.
Nguồn dữ liệu cho Webalizer là tệp access.log của máy chủ. Các thanh và số của nó trông như thế này, từ đó chỉ có tổng lưu lượng truy cập là rõ ràng:
Các công cụ như Google Analytics tự thu thập dữ liệu từ trang được tải. Họ cho chúng ta xem một số sơ đồ và đường nét, dựa vào đó thường khó đưa ra kết luận chính xác. Có lẽ nên nỗ lực nhiều hơn? Không biết.
Vì vậy, tôi muốn thấy gì trong số liệu thống kê về khách truy cập trang web?
Lưu lượng người dùng và bot
Thông thường lưu lượng truy cập trang web bị hạn chế và cần phải xem lưu lượng truy cập hữu ích đang được sử dụng là bao nhiêu. Ví dụ như thế này:
Truy vấn báo cáo 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_DTBiểu đồ cho thấy hoạt động liên tục của bot. Sẽ rất thú vị nếu nghiên cứu chi tiết những đại diện tích cực nhất.
Bot phiền toái
Chúng tôi phân loại bot dựa trên thông tin tác nhân người dùng. Số liệu thống kê bổ sung về lưu lượng truy cập hàng ngày, số lượng yêu cầu thành công và không thành công mang lại ý tưởng hay về hoạt động của bot.
Truy vấn báo cáo 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 10Trong trường hợp này, kết quả phân tích là quyết định hạn chế quyền truy cập vào trang web bằng cách thêm nó vào tệp robots.txt
User-agent: AhrefsBot
Disallow: /
User-agent: dotbot
Disallow: /
User-agent: bingbot
Crawl-delay: 5
Hai robot đầu tiên biến mất khỏi bàn và các robot MS di chuyển xuống từ hàng đầu tiên.
Ngày và giờ hoạt động lớn nhất
Sự gia tăng có thể nhìn thấy được trong giao thông. Để nghiên cứu chúng một cách chi tiết, cần nêu rõ thời gian xuất hiện của chúng và không nhất thiết phải hiển thị tất cả các giờ, ngày đo thời gian. Điều này sẽ giúp việc tìm kiếm các yêu cầu riêng lẻ trong tệp nhật ký trở nên dễ dàng hơn nếu cần phân tích chi tiết.
Truy vấn báo cáo 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_DTChúng tôi quan sát các giờ hoạt động tích cực nhất vào các giờ 11, 14 và 20 của ngày đầu tiên trên biểu đồ. Nhưng ngày hôm sau lúc 13:XNUMX các bot vẫn hoạt động.
Hoạt động trung bình hàng ngày của người dùng theo tuần
Chúng tôi đã sắp xếp mọi thứ một chút với hoạt động và lưu lượng truy cập. Câu hỏi tiếp theo là hoạt động của chính người dùng. Đối với những số liệu thống kê như vậy, thời gian tổng hợp dài, chẳng hạn như một tuần, là điều mong muốn.
Truy vấn báo cáo 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_DTThống kê hàng tuần cho thấy trung bình một người dùng mở 1,6 trang mỗi ngày. Số lượng tệp được yêu cầu cho mỗi người dùng trong trường hợp này phụ thuộc vào việc bổ sung tệp mới vào trang web.
Tất cả các yêu cầu và trạng thái của chúng
Webalizer luôn hiển thị mã trang cụ thể và tôi luôn muốn xem số lượng yêu cầu và lỗi thành công.
Truy vấn báo cáo 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_DTBáo cáo hiển thị các yêu cầu chứ không phải số lần nhấp chuột (lần truy cập), không giống như LINE_CNT, số liệu REQUEST_CNT được tính là COUNT(DISTINCT STG.REQUEST_NK). Mục đích là hiển thị các sự kiện hiệu quả, ví dụ: các bot MS thăm dò tệp robots.txt hàng trăm lần một ngày và trong trường hợp này, các cuộc thăm dò như vậy sẽ được tính một lần. Điều này cho phép bạn làm mượt các bước nhảy trong biểu đồ.
Từ biểu đồ, bạn có thể thấy nhiều lỗi - đây là những trang không tồn tại. Kết quả phân tích là việc bổ sung các chuyển hướng từ các trang từ xa.
Yêu cầu xấu
Để kiểm tra các yêu cầu một cách chi tiết, bạn có thể hiển thị số liệu thống kê chi tiết.
Truy vấn báo cáo 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 20Danh sách này cũng sẽ chứa tất cả các cuộc gọi, ví dụ: yêu cầu tới /wp-login.php. Bằng cách điều chỉnh các quy tắc viết lại yêu cầu của máy chủ, bạn có thể điều chỉnh phản ứng của máy chủ đối với các yêu cầu đó và gửi chúng đến trang bắt đầu.
Vì vậy, một số báo cáo đơn giản dựa trên tệp nhật ký máy chủ sẽ cung cấp một bức tranh khá đầy đủ về những gì đang xảy ra trên trang web.
Làm thế nào để có được thông tin?
Một cơ sở dữ liệu sqlite là đủ. Hãy tạo các bảng: phụ trợ để ghi nhật ký các quy trình ETL.
Giai đoạn bảng nơi chúng ta sẽ viết các tệp nhật ký bằng PHP. Hai bảng tổng hợp. Hãy tạo một bảng hàng ngày với số liệu thống kê về tác nhân người dùng và trạng thái yêu cầu. Hàng giờ với số liệu thống kê về yêu cầu, nhóm trạng thái và đại lý. Bốn bảng đo lường liên quan.
Kết quả là mô hình quan hệ sau:
Mô hình dữ liệu
Tập lệnh tạo một đối tượng trong cơ sở dữ liệu sqlite:
Tạo đối tượng 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);Sân khấu
Trong trường hợp tệp access.log, cần phải đọc, phân tích cú pháp và ghi tất cả các yêu cầu vào cơ sở dữ liệu. Điều này có thể được thực hiện trực tiếp bằng ngôn ngữ kịch bản hoặc sử dụng các công cụ sqlite.
Định dạng tệp nhật ký:
//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-]+) "(.*)" "(.*)"$/';
Tuyên truyền khóa
Khi dữ liệu thô có trong cơ sở dữ liệu, bạn cần ghi các khóa không có vào bảng đo. Sau đó sẽ có thể xây dựng một tham chiếu đến các phép đo. Ví dụ: trong bảng DIM_REFERRER, khóa là sự kết hợp của ba trường.
Truy vấn lan truyền khóa 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 NULLViệc truyền tới bảng tác nhân người dùng có thể chứa logic bot, ví dụ như đoạn mã 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_BOTbảng tổng hợp
Cuối cùng, chúng ta sẽ tải các bảng tổng hợp; ví dụ: bảng hàng ngày có thể được tải như sau:
Truy vấn SQL để tải tổng hợp
/* 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_IDCơ sở dữ liệu sqlite cho phép bạn viết các truy vấn phức tạp. VỚI chứa việc chuẩn bị dữ liệu và khóa. Truy vấn chính thu thập tất cả các tham chiếu đến thứ nguyên.
Điều kiện sẽ không cho phép tải lại lịch sử: CAST(STG.EVENT_DT AS INTEGER) > $param_epoch_from, trong đó tham số là kết quả của yêu cầu
‘CHỌN COALESCE(MAX(EVENT_DT), ‘3600’) NHƯ LAST_EVENT_EPOCH TỪ FCT_ACCESS_USER_AGENT_DD’
Điều kiện sẽ chỉ tải cả ngày: CAST(STG.EVENT_DT AS INTEGER) < strftime('%s', date('now', 'start of day'))
Việc đếm trang hoặc tập tin được thực hiện một cách nguyên thủy, bằng cách tìm kiếm một điểm.
Báo cáo
Trong các hệ thống trực quan hóa phức tạp, có thể tạo siêu mô hình dựa trên các đối tượng cơ sở dữ liệu, quản lý động các bộ lọc và quy tắc tổng hợp. Cuối cùng, tất cả các công cụ phù hợp đều tạo ra truy vấn SQL.
Trong ví dụ này, chúng tôi sẽ tạo các truy vấn SQL được tạo sẵn và lưu chúng dưới dạng dạng xem trong cơ sở dữ liệu - đây là các báo cáo.
Hình dung
Vô tội vạ: Các biểu đồ đẹp trong JavaScript được sử dụng làm công cụ trực quan hóa
Để làm được điều này, cần phải xem qua tất cả các báo cáo bằng PHP và tạo một tệp html có các bảng.
$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'
);Công cụ này chỉ đơn giản là hiển thị các bảng kết quả.
Đầu ra
Lấy phân tích web làm ví dụ, bài viết mô tả các cơ chế cần thiết để xây dựng kho dữ liệu. Như có thể thấy từ kết quả, các công cụ đơn giản nhất cũng đủ để phân tích sâu và trực quan hóa dữ liệu.
Trong tương lai, lấy kho lưu trữ này làm ví dụ, chúng tôi sẽ cố gắng triển khai các cấu trúc như thứ nguyên, siêu dữ liệu, mức độ tổng hợp và tích hợp dữ liệu từ các nguồn khác nhau thay đổi chậm.
Ngoài ra, chúng ta hãy xem xét kỹ hơn công cụ đơn giản nhất để quản lý các quy trình ETL dựa trên một bảng duy nhất.
Hãy quay lại chủ đề đo lường chất lượng dữ liệu và tự động hóa quá trình này.
Chúng tôi sẽ nghiên cứu các vấn đề về môi trường kỹ thuật và bảo trì kho lưu trữ dữ liệu mà chúng tôi sẽ triển khai một máy chủ lưu trữ với tài nguyên tối thiểu, chẳng hạn như dựa trên Raspberry Pi.
Nguồn: www.habr.com