查询存储在 Amazon S3 中的 Internet Information Server (IIS) 日志 - Amazon Athena
AWS 文档中描述的 AWS 服务或功能可能因区域而异。要查看适用于中国区域的差异,请参阅中国的 AWS 服务入门

本文属于机器翻译版本。若本译文内容与英语原文存在差异,则一律以英文原文为准。

查询存储在 Amazon S3 中的 Internet Information Server (IIS) 日志

您可以使用 Amazon Athena 查询存储在您的 Amazon S3 账户中的 Microsoft Internet Information Services (IIS) Web 服务器日志。IIS 使用日志文件格式的各种 形式,但本主题介绍如何创建表架构以从 Athena 中查询 W3C 扩展和 IIS 日志文件格式日志。

由于 W3C 扩展版和 IIS 日志文件格式使用单字符分隔符(分别为空格和逗号),并且没有用引号引起来的值,因此您可以使用 LazySimpleSerDeAthena 为它们创建 表。

W3C 扩展日志文件格式

W3C extended (W3C 扩展) 日志文件数据格式具有空格分隔的字段。W3C 扩展日志中显示的字段由 Web 服务器管理员确定,该管理员选择要包含哪些日志字段。以下示例日志数据具有字段 date, timec-ips-ipcs-methodcs-uri-stemsc-statussc-bytescs-bytestime-takencs-version

2020-01-19 22:48:39 203.0.113.5 198.51.100.2 GET /default.html 200 540 524 157 HTTP/1.0 2020-01-19 22:49:40 203.0.113.10 198.51.100.12 GET /index.html 200 420 324 164 HTTP/1.0 2020-01-19 22:50:12 203.0.113.12 198.51.100.4 GET /image.gif 200 324 320 358 HTTP/1.0 2020-01-19 22:51:44 203.0.113.15 198.51.100.16 GET /faq.html 200 330 324 288 HTTP/1.0

在 Athena 中为 W3C 扩展日志创建表

您必须先创建表架构以便 Athena 可以读取日志数据,然后才能查询 W3C 扩展日志。

在 Athena 中为 W3C 扩展日志创建表

  1. https://console.amazonaws.cn/athena/ 打开 Athena 控制台。

  2. 将类似以下内容的 DDL 语句粘贴到 Athena 控制台中,并注意以下几点:

    1. 添加或删除示例中的列,以对应于要查询的日志中的字段。

    2. W3C 扩展日志文件格式的列名称包含连字符 (-)。但是,根据 Athena 命名约定,示例 CREATE TABLE 语句将它们替换为下划线 (_)。

    3. 要指定空格分隔符,请使用 FIELDS TERMINATED BY ' '

    4. 修改 LOCATION 's3://bucket-name/w3c-log-folder/' 中的值以指向 Amazon S3 中的 W3C 扩展日志。

    CREATE EXTERNAL TABLE `iis_w3c_logs`( date_col string, time_col string, c_ip string, s_ip string, cs_method string, cs_uri_stem string, sc_status string, sc_bytes string, cs_bytes string, time_taken string, cs_version string ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ' ' STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION 's3://bucket-name/w3c-log-folder/'
  3. 在 Athena 控制台中运行查询以注册 iis_w3c_logs 表。查询完成后,日志已准备就绪,可供您从 Athena 查询。

示例 W3C 扩展日志 Select 查询

以下示例查询从表 iis_w3c_logs 中选择请求所使用的日期、时间、请求目标和时间。 子句将筛选 HTTP 方法为 WHERE 且 HTTP 状态代码为 GET (成功) 的情况。200

SELECT date_col, time_col, cs_uri_stem, time_taken FROM iis_w3c_logs WHERE cs_method = 'GET' AND sc_status = '200'

下图显示了 Athena 查询编辑器中的查询结果。


                    示例查询结果是 Athena 中存储的 W3C 扩展日志文件的 Amazon S3。

组合日期和时间字段

以空格分隔的 datetime 字段是日志源数据中的单独条目,但您可以将其合并为时间戳(如果需要)。在 SELECTCREATE TABLE AS SELECT 查询中使用 concat()date_parse() 函数将日期和时间列连接并转换为时间戳格式。以下示例使用 CTAS 查询创建一个包含 derived_timestamp 列的新表。

CREATE TABLE iis_w3c_logs_w_timestamp AS SELECT date_parse(concat(date_col,' ', time_col),'%Y-%m-%d %H:%i:%s') as derived_timestamp, c_ip, s_ip, cs_method, cs_uri_stem, sc_status, sc_bytes, cs_bytes, time_taken, cs_version FROM iis_w3c_logs

在创建表后,您可以直接查询新的时间戳列,如以下示例所示。

SELECT derived_timestamp, cs_uri_stem, time_taken FROM iis_w3c_logs_w_timestamp WHERE cs_method = 'GET' AND sc_status = '200'

下图显示了查询的结果。


                    包含派生时间戳列的 表上的 W3C 扩展日志文件查询结果。

IIS 日志文件格式

与 W3C 扩展格式不同,IIS 日志文件格式具有一组固定的字段,并且包含逗号作为分隔符。将逗号视为分隔符,并将逗号后面的空格视为下一个字段的开头。LazySimpleSerDe

以下示例显示了 IIS 日志文件格式的示例数据。

203.0.113.15, -, 2020-02-24, 22:48:38, W3SVC2, SERVER5, 198.51.100.4, 254, 501, 488, 200, 0, GET, /index.htm, -, 203.0.113.4, -, 2020-02-24, 22:48:39, W3SVC2, SERVER6, 198.51.100.6, 147, 411, 388, 200, 0, GET, /about.html, -, 203.0.113.11, -, 2020-02-24, 22:48:40, W3SVC2, SERVER7, 198.51.100.18, 170, 531, 468, 200, 0, GET, /image.png, -, 203.0.113.8, -, 2020-02-24, 22:48:41, W3SVC2, SERVER8, 198.51.100.14, 125, 711, 868, 200, 0, GET, /intro.htm, -,

在 Athena 中为 IIS 日志文件创建表

要在 Amazon S3 中查询 IIS 日志文件格式日志,您首先要创建一个表架构,以便 Athena 可以读取日志数据。

在 Athena 中为 IIS 日志文件格式日志创建表

  1. https://console.amazonaws.cn/athena/ 打开 Athena 控制台。

  2. 将以下 DDL 语句粘贴到 Athena 控制台中,并注意以下几点:

    1. 要指定逗号分隔符,请使用 FIELDS TERMINATED BY ','

    2. 修改 LOCATION 's3:// 中的值bucket-name/iis-log-file-folder/' 以指向 Amazon S3 中的 IIS 日志格式日志文件。

    CREATE EXTERNAL TABLE `iis_format_logs`( client_ip_address string, user_name string, request_date string, request_time string, service_and_instance string, server_name string, server_ip_address string, time_taken_millisec string, client_bytes_sent string, server_bytes_sent string, service_status_code string, windows_status_code string, request_type string, target_of_operation string, script_parameters string ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION 's3://bucket-name/iis-log-file-folder/'
  3. 在 Athena 控制台中运行查询以注册 iis_format_logs 表。查询完成后,日志已准备就绪,可供您从 Athena 查询。

示例 IIS 日志格式选择查询

以下示例查询从表 iis_format_logs 中选择请求日期、请求时间、请求目标和所用时间(以毫秒为单位)。 子句将筛选请求类型为 WHERE 且 HTTP 状态代码为 GET (成功) 的情况。200请注意,在查询中, 和 中的前导空间' GET'是成功完成查询所必需的。' 200'

SELECT request_date, request_time, target_of_operation, time_taken_millisec FROM iis_format_logs WHERE request_type = ' GET' AND service_status_code = ' 200'

下图显示了示例数据的查询结果。


                    示例查询结果为存储在 Athena 中的 IIS 日志文件格式日志文件的 Amazon S3。

NSHA 日志文件的格式

IIS 还使用 NSHA Logging 格式,该格式具有固定数量的 ASCII 文本格式字段(用空格分隔)。该结构类似于用于 Apache 访问日志的常见日志格式。NSHA 通用日志数据格式中的字段包括客户端 IP 地址、客户端 ID(通常未使用)、域\\用户 ID、收到的请求时间戳、客户端请求的文本、服务器状态代码以及返回到客户端的对象的大小。

以下示例显示了为 IIS 记录的数据 (NSHA 通用日志格式)。

198.51.100.7 - ExampleCorp\Li [10/Oct/2019:13:55:36 -0700] "GET /logo.gif HTTP/1.0" 200 232 198.51.100.14 - AnyCompany\Jorge [24/Nov/2019:10:49:52 -0700] "GET /index.html HTTP/1.1" 200 2165 198.51.100.22 - ExampleCorp\Mateo [27/Dec/2019:11:38:12 -0700] "GET /about.html HTTP/1.1" 200 1287 198.51.100.9 - AnyCompany\Nikki [11/Jan/2020:11:40:11 -0700] "GET /image.png HTTP/1.1" 404 230 198.51.100.2 - ExampleCorp\Ana [15/Feb/2019:10:12:22 -0700] "GET /favicon.ico HTTP/1.1" 404 30 198.51.100.13 - AnyCompany\Saanvi [14/Mar/2019:11:40:33 -0700] "GET /intro.html HTTP/1.1" 200 1608 198.51.100.11 - ExampleCorp\Xiulan [22/Apr/2019:10:51:34 -0700] "GET /group/index.html HTTP/1.1" 200 1344

在 Athena 中为 IIS NSHA 日志创建表

对于 CREATE TABLE 语句,您可以使用 Grok SerDe 和类似于 Apache Web 服务器日志的 grok 模式。与 Apache 日志不同,grok 模式对第三个字段使用 %{DATA:user_id} 而不是 %{USERNAME:user_id} 以考虑到 domain\user_id 中存在反斜杠。 有关使用 Grok SerDe 的更多信息,请参阅 中的编写 Grok 自定义分类器。AWS Glue 开发人员指南

在 Athena 中为 IIS NSHA Web 服务器日志创建表

  1. https://console.amazonaws.cn/athena/ 打开 Athena 控制台。

  2. 将以下 DDL 语句粘贴到 Athena 查询编辑器中。修改 LOCATION 's3://bucket-name/iis-ncsa-logs/' 中的值以指向您在 Amazon S3 中的 IIS NSHA 日志。

    CREATE EXTERNAL TABLE iis_ncsa_logs( client_ip string, client_id string, user_id string, request_received_time string, client_request string, server_status string, returned_obj_size string ) ROW FORMAT SERDE 'com.amazonaws.glue.serde.GrokSerDe' WITH SERDEPROPERTIES ( 'input.format'='^%{IPV4:client_ip} %{DATA:client_id} %{DATA:user_id} %{GREEDYDATA:request_received_time} %{QUOTEDSTRING:client_request} %{DATA:server_status} %{DATA: returned_obj_size}$' ) STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION 's3://bucket-name/iis-ncsa-logs/';
  3. 在 Athena 控制台中运行查询以注册 iis_ncsa_logs 表。查询完成后,日志已准备就绪,可供您从 Athena 查询。

IIS NSHA 日志的示例 Select 查询

例 – 针对 404 错误进行筛选

以下示例查询从 iis_ncsa_logs 表中选择请求接收时间、客户端请求的文本以及服务器状态代码。HTTP 状态代码 WHERE404 子句筛选(找不到页面)。

SELECT request_received_time, client_request, server_status FROM iis_ncsa_logs WHERE server_status = '404'

下图显示了 Athena 查询编辑器中的查询结果。


                        从 Athena 查询 IIS NSHA 日志以查找 HTTP 404 条目。

例 – 筛选来自特定域的成功请求

以下示例查询从 iis_ncsa_logs 表中选择用户 ID、请求接收时间、客户端请求的文本以及服务器状态代码。子句将筛选来自 WHERE 域中用户且 HTTP 状态代码为 200(成功)的请求。AnyCompany

SELECT user_id, request_received_time, client_request, server_status FROM iis_ncsa_logs WHERE server_status = '200' AND user_id LIKE 'AnyCompany%'

下图显示了 Athena 查询编辑器中的查询结果。


                        从 Athena 查询 IIS NSHA 日志以查找 HTTP 200 条目。