Amazon Route 53 Resolver クエリログのクエリ - Amazon Athena
Resolver クエリログ用のテーブルの作成パーティション化の例クエリの例

Amazon Route 53 Resolver クエリログのクエリ

Amazon Route 53 Resolver クエリログ用の Athena テーブルを作成して、それらを Athena からクエリできます。

Route 53 Resolver のクエリロギングは、VPC 内のリソースによって行われた DNS クエリ、インバウンドリゾルバーエンドポイントを使用するオンプレミスリソース、再帰的な DNS 解決にアウトバウンドリゾルバーエンドポイントを使用するクエリ、およびドメインリストをブロック、許可、またはモニタリングするために Route 53 Resolver DNS ファイアウォールルールを使用するクエリをログ記録するためのものです。Resolver クエリログ記録の詳細については、Amazon Route 53 デベロッパーガイドの「Resolver query logging」を参照してください。ログにある各フィールドについては、「Amazon Route 53 デベロッパーガイド」の「Resolver クエリログに表示される値」を参照してください。

Resolver クエリログ用のテーブルの作成

Athena コンソールのクエリエディタを使用して、Route 53 Resolver クエリログ用のテーブルを作成してクエリできます。

Route 53 Resolver クエリログ用の Athena テーブルを作成してクエリする

  1. https://console.aws.amazon.com/athena/ で Athena コンソールを開きます。

  2. Athena クエリエディタに以下の CREATE TABLE ステートメントを入力します。LOCATION 句の値を、Simple Storage Service (Amazon S3) の Resolver ログの場所に対応する値に置き換えます。

    CREATE EXTERNAL TABLE r53_rlogs (
  version string,
  account_id string,
  region string,
  vpc_id string,
  query_timestamp string,
  query_name string,
  query_type string,
  query_class
    string,
  rcode string,
  answers array<
    struct<
      Rdata: string,
      Type: string,
      Class: string>
    >,
  srcaddr string,
  srcport int,
  transport string,
  srcids struct<
    instance: string,
    resolver_endpoint: string
    >,
  firewall_rule_action string,
  firewall_rule_group_id string,
  firewall_domain_list_id string
 )
     
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://DOC-EXAMPLE-BUCKET/AWSLogs/aws_account_id/vpcdnsquerylogs/{vpc-id}/'

    Resolver クエリログデータは JSON 形式であるため、CREATE TABLE ステートメントは JSON SerDe ライブラリを使用してデータを分析します。

    注記

    SerDe では、各 JSON ドキュメントが、レコード内のフィールドを区切る行終端文字なしの、1 行のテキストに収まっていることを想定しています。JSON テキストがプリティプリント形式の場合、テーブルを作成した後にクエリを実行しようとすると、以下のようなエラーメッセージが表示される場合があります。「HIVE_CURSOR_ERROR: Row is not a valid JSON Object」、または「HIVE_CURSOR_ERROR: JsonParseException: Unexpected end-of-input: expected close marker for OBJECT」。詳細については、GitHub の OpenX SerDe のドキュメントで「JSON Data Files」(JSON データファイル) を参照してください。

  3. [Run query] (クエリの実行) を選択します。ステートメントが r53_rlogs という名前の Athena テーブルを作成します。このテーブルの列は Resolver ログデータの各フィールドを表します。

  4. Athena コンソールのクエリエディタで以下のクエリを実行して、テーブルが作成されたことを検証します。

    SELECT * FROM "r53_rlogs" LIMIT 10

パーティション化の例

次の例は、パーティション射影を使用し、VPC と日付で分割された Resolver クエリログの CREATE TABLE ステートメントを示しています。パーティション射影の詳細については、「Amazon Athena でのパーティション射影」を参照してください。

CREATE EXTERNAL TABLE r53_rlogs (
  version string,
  account_id string,
  region string,
  vpc_id string,
  query_timestamp string,
  query_name string,
  query_type string,
  query_class string,
  rcode string,
  answers array<
    struct<
      Rdata: string,
      Type: string,
      Class: string>
    >,
  srcaddr string,
  srcport int,
  transport string,
  srcids struct<
    instance: string,
    resolver_endpoint: string
    >,
  firewall_rule_action string,
  firewall_rule_group_id string,
  firewall_domain_list_id string
)
PARTITIONED BY (
`date` string,
`vpc` string
)
ROW FORMAT SERDE      'org.openx.data.jsonserde.JsonSerDe'
STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT          'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION              's3://DOC-EXAMPLE-BUCKET/route53-query-logging/AWSLogs/aws_account_id/vpcdnsquerylogs/'
TBLPROPERTIES(
'projection.enabled' = 'true',
'projection.vpc.type' = 'enum',
'projection.vpc.values' = 'vpc-6446ae02',
'projection.date.type' = 'date',
'projection.date.range' = '2023/06/26,NOW',
'projection.date.format' = 'yyyy/MM/dd',
'projection.date.interval' = '1',
'projection.date.interval.unit' = 'DAYS',
'storage.location.template' = 's3://DOC-EXAMPLE-BUCKET/route53-query-logging/AWSLogs/aws_account_id/vpcdnsquerylogs/${vpc}/${date}/'
)

クエリの例

以下の例は、Resolver クエリログで Athena から実行できるクエリをいくつか説明しています。

例 1 – ログを降順の query_timestamp 順にクエリする

以下のクエリは、query_timestamp 順で降順にログ結果を表示します。

SELECT * FROM "r53_rlogs"
ORDER BY query_timestamp DESC

例 2 – 指定された開始時刻と終了時刻の範囲内でログをクエリする

以下のクエリは、2020 年 9 月 24 日の午前 0 時から午前 8 時の間のログをクエリします。開始時刻と終了時刻は、独自の要件に従って置き換えます。

SELECT query_timestamp, srcids.instance, srcaddr, srcport, query_name, rcode
FROM "r53_rlogs"
WHERE (parse_datetime(query_timestamp,'yyyy-MM-dd''T''HH:mm:ss''Z')
     BETWEEN parse_datetime('2020-09-24-00:00:00','yyyy-MM-dd-HH:mm:ss') 
     AND parse_datetime('2020-09-24-00:08:00','yyyy-MM-dd-HH:mm:ss'))
ORDER BY query_timestamp DESC

例 3 – 指定された DNS クエリ名パターンに基づいてログをクエリする

以下のクエリは、クエリ名に文字列「example.com」が含まれたレコードを選択します。

SELECT query_timestamp, srcids.instance, srcaddr, srcport, query_name, rcode, answers
FROM "r53_rlogs"
WHERE query_name LIKE '%example.com%'
ORDER BY query_timestamp DESC

例 4 – 応答のないログリクエストをクエリする

以下のクエリは、リクエストが応答を受け取らなかったログエントリを選択します。

SELECT query_timestamp, srcids.instance, srcaddr, srcport, query_name, rcode, answers
FROM "r53_rlogs"
WHERE cardinality(answers) = 0

例 5 – 特定の回答が含まれるログをクエリする

以下のクエリは、answer.Rdata 値に指定された IP アドレスがあるログを表示します。

SELECT query_timestamp, srcids.instance, srcaddr, srcport, query_name, rcode, answer.Rdata
FROM "r53_rlogs"
CROSS JOIN UNNEST(r53_rlogs.answers) as st(answer)
WHERE answer.Rdata='203.0.113.16';