Geekly articles weekly

网站统计信息和您的小型存储库

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!='na' THEN FCT.BYTES ELSE 0 END)/1000 AS 'Bots, KB', SUM(CASE WHEN USG.AGENT_BOT='na' 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 != 'na' 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!='na' THEN LINE_CNT ELSE 0 END) AS HIB, SUM(CASE WHEN AGENT_BOT='na' 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点，这些机器人开始活动了。

每周平均每日用户活动


随着活动和交通有点想通了。 下一个问题是用户本身的活动。 对于此类统计，需要大量的汇总时间，例如一周。

图片

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='na' /* 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 'na', AGENT_OS TEXT NOT NULL DEFAULT 'na', AGENT_ENGINE TEXT NOT NULL DEFAULT 'na', AGENT_DEVICE TEXT NOT NULL DEFAULT 'na', AGENT_BOT TEXT NOT NULL DEFAULT 'na', 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 'na' 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作为LAST_EVENT_EPOCH的SELECT COALESCE（MAX（EVENT_DT），\'3600 \'）

该条件将仅加载一整天：CAST（STG.EVENT_DT AS INTEGER）<strftime（“％s”，日期（“ now”，“ day 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）实现了存储服务器。

Source: https://habr.com/ru/post/zh-CN462337/

More articles:


All Articles