タダです.
前回 DuckDB で CloudTrail ログを分析してみました.今回は ALB のアクセスログを分析してみます.
sadayoshi-tada.hatenablog.com
sadayoshi-tada.hatenablog.com
こちらの記事を参照させていただいてテーブルを作ってみました.
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 │ - │
└─────────┴────────────────────────────┴─────────────────────┴─────────────────┴────────────────────┴──────────────────────────────────────────┴──────────────┘
まとめ
DuckDB で ALB のアクセスログを分析するのを試してみました.
