ALB アクセスログのテーブルを作る

こちらの記事を参照させていただいてテーブルを作ってみました.

CREATE TABLE alb_log AS
SELECT *
FROM read_csv(
    '[ログファイルパス]',
    columns={
        'type': 'VARCHAR',
        'timestamp': 'TIMESTAMP',
        'elb': 'VARCHAR',
        'client_ip_port': 'VARCHAR',
        'target_ip_port': 'VARCHAR',
        'request_processing_time': 'DOUBLE',
        'target_processing_time': 'DOUBLE',
        'response_processing_time': 'DOUBLE',
        'elb_status_code': 'INTEGER',
        'target_status_code': 'VARCHAR',
        'received_bytes': 'BIGINT',
        'sent_bytes': 'BIGINT',
        'request': 'VARCHAR',
        'user_agent': 'VARCHAR',
        'ssl_cipher': 'VARCHAR',
        'ssl_protocol': 'VARCHAR',
        'target_group_arn': 'VARCHAR',
        'trace_id': 'VARCHAR',
        'domain_name': 'VARCHAR',
        'chosen_cert_arn': 'VARCHAR',
        'matched_rule_priority': 'VARCHAR',
        'request_creation_time': 'TIMESTAMP',
        'actions_executed': 'VARCHAR',
        'redirect_url': 'VARCHAR',
        'error_reason': 'VARCHAR',
        'target_port_list': 'VARCHAR',
        'target_status_code_list': 'VARCHAR',
        'classification': 'VARCHAR',
        'classification_reason': 'VARCHAR',
        'conn_trace_id': 'VARCHAR'
    },
    delim=' ',
    quote='"',
    escape='"',
    header=False,
    auto_detect=False
);
D show tables;
┌─────────┐
│  name   │
│ varchar │
├─────────┤
│ alb_log │
└─────────┘

参考記事

swfz.hatenablog.com

ALB アクセスログの分析クエリを実行する

AWS のドキュメントを参考にクエリをいくつか実行してみます.

クライアント IP アドレス別にグループ分けした HTTP リクエストの数を集計

D SELECT COUNT(request) AS
   count,
   request,
   SPLIT_PART(client_ip_port, ':', 1) as client_ip
  FROM alb_log
  GROUP BY request, client_ip
  ;
┌───────┬──────────────────────────────────────────┬───────────────┐
│ count │                 request                  │   client_ip   │
│ int64 │                 varchar                  │    varchar    │
├───────┼──────────────────────────────────────────┼───────────────┤
│     2 │ GET https://12.34.56.78:443/ HTTP/1.1     │ 123.45.657.89 │
│     1 │ GET https://12.34.56.78:443/xxx HTTP/1.1 │ 45.657.89.110 │
└───────┴──────────────────────────────────────────┴───────────────┘

ELB ステータスコードの値が200以外のリクエストを抽出

SELECT
      type,
      timestamp,
      client_ip_port,
      elb_status_code,
      target_status_code,
      request,
      error_reason
  FROM
      alb_log
  WHERE
      elb_status_code != 200;
┌─────────┬────────────────────────────┬─────────────────────┬─────────────────┬────────────────────┬──────────────────────────────────────────┬──────────────┐
│  type   │         timestamp          │   client_ip_port    │ elb_status_code │ target_status_code │                 request                  │ error_reason │
│ varchar │         timestamp          │       varchar       │      int32      │      varchar       │                 varchar                  │   varchar    │
├─────────┼────────────────────────────┼─────────────────────┼─────────────────┼────────────────────┼──────────────────────────────────────────┼──────────────┤
│ https   │ 2024-12-22 00:35:22.50771  │ 123.45.657.89:12345 │             403 │ -                  │ GET https://12.34.56.78:443/ HTTP/1.1     │ -            │
│ https   │ 2024-12-22 00:35:33.942067 │ 123.45.657.89:53456 │             403 │ -                  │ GET https://12.34.56.78:443/ HTTP/1.1     │ -            │
│ https   │ 2024-12-22 00:36:08.717268 │ 45.657.89.110:32134 │             403 │ -                  │ GET https://12.34.56.78:443/xxx HTTP/1.1 │ -            │
└─────────┴────────────────────────────┴─────────────────────┴─────────────────┴────────────────────┴──────────────────────────────────────────┴──────────────┘