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

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

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

您可以使用 Amazon Athena 查询存储在您 Amazon S3 账户中的 Microsoft 互联网信息服务 (IIS) Web 服务器日志。虽然 IIS 使用各种不同的日志文件格式，本主题将介绍如何创建表架构以从 Athena 查询 W3C 扩展日志和 IIS 日志文件格式日志。

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

W3C 扩展日志文件格式

W3C 扩展日志文件数据格式具有空格分隔的字段。W3C 扩展日志中显示的字段由 Web 服务器管理员决定，后者将选择要包含哪些日志字段。以下示例日志数据具有 date, time、c-ip、s-ip、cs-method、cs-uri-stem、sc-status、sc-bytes、cs-bytes、time-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 查询编辑器中的查询结果。

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

合并日期和时间字段

以空格分隔的 date 和 time 字段是日志源数据中的单独条目，但您可以根据需要将它们合并到时间戳中。在 SELECT 或者 CREATE 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.aws.amazon.com/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 子句筛选条件，用于请求类型为 GET，以及 HTTP 状态代码为 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'

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

存储在 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 服务器日志模式的 grok 模式。与 Apache 日志不同，grok 模式使用 %{DATA:user_id} 而不是 %{USERNAME:user_id} 作为第三个字段来考虑 domain\user_id 中反斜杠的存在。有关使用 Grok SerDe 的更多信息，请参阅《Amazon Glue 开发人员指南https://docs.amazonaws.cn/glue/latest/dg/custom-classifier.html#custom-classifier-grok》中的 编写 Grok 自定义分类器。

要在 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 表中选择了请求接收时间、客户端请求的文本以及服务器状态代码。HTTP 状态代码 404（未找到页面）的 WHERE 子句筛选条件。

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

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

从 Athena 查询 IIS NCSA 日志的 HTTP 404 条目。
例 – 筛选来自特定域的成功请求

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

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 NCSA 日志的 HTTP 200 条目。