查询存储在 Amazon S3 中的互联网信息服务器 (IIS) 日志 - Amazon Athena
Amazon Web Services 文档中描述的 Amazon Web Services 服务或功能可能因区域而异。要查看适用于中国区域的差异,请参阅中国的 Amazon Web Services 服务入门

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

查询存储在 Amazon S3 中的互联网信息服务器 (IIS) 日志

您可以使用 Amazon Athena 查询存储在您的 Amazon S3 帐户中的微软互联网信息服务 (IIS) Web 服务器日志。虽然 IIS 使用品种的日志文件格式,本主题介绍如何创建表模式以查询来自 Athena 的 W3C 扩展日志和 IIS 日志文件格式日志。

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

W3C 扩展日志文件格式

这些区域有:W3C 扩展日志文件数据格式具有空格分隔的字段。W3C 扩展日志中显示的字段由 Web 服务器管理员决定,后者选择要包含哪些日志字段。以下示例日志数据具有date, timec-ips-ipcs-methodcs-uri-stemsc-statussc-bytescs-bytestime-taken, 和cs-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 扩展日志创建表

在查询 W3C 扩展日志之前,必须先创建表架构,以便 Athena 可以读取日志数据。

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

  1. https://console.aws.amazon.com/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 扩展日志选择查询

以下示例查询从表中选择请求的日期、时间、请求目标和时间iis_w3c_logs。这些区域有:WHERE子句过滤器,用于 HTTP 方法为GET,HTTP 状态代码200(成功).

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

下图显示了 Athena 查询 Editor 中的查询结果。


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

合并日期和时间字段

以空格分隔的datetime字段是日志源数据中的单独条目,但您可以根据需要将它们合并到时间戳中。使用concat()日期解析 ()函数选择或者创建表作为选择查询连接日期和时间列并将其转换为时间戳格式。以下示例使用 CTAS 查询创建一个新表,其中包含derived_timestampcolumn.

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.aws.amazon.com/athena/ 打开 Athena 控制台。

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

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

    2. 修改位置 's3://中的值bucket-name/IIS 日志文件文件夹/' 指向 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子句筛选请求类型为GET,HTTP 状态代码200(成功). 在查询中,请注意,前导空间s在' 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'

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


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

NCSA 日志文件格式

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

以下示例显示了 IIS 所记录的 NCSA 通用日志格式的数据。

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 NCSA 日志创建表

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

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

  1. https://console.aws.amazon.com/athena/ 打开 Athena 控制台。

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

    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 NCSA 日志的选择查询示例

例 — 过滤 404 错误

下面的示例查询选择请求接收时间、客户端请求的文本以及从iis_ncsa_logs表。这些区域有:WHEREHTTP 状态代码的子句筛选器404(未找到页面)。

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

下图显示了 Athena 查询 Editor 中的查询结果。


                        从 Athena 查询 IIS NCSA 日志的 HTTP404条目。

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

以下示例查询从iis_ncsa_logs表。这些区域有: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 查询 Editor 中的查询结果。


                        从 Athena 查询 IIS NCSA 日志的 HTTP200条目。