Edited at

今さらだけど、AWS ELBのログ解析にAthenaを用いたら、簡単で便利だった


きっかけ

トラブルの原因を調べるなどの理由で、ELBのアクセスログを解析する必要に迫られることは多いと思います。

私は、当初、ログを格納しているS3から、ファイルをLinuxサーバーにダウンロードしてgrepやawkで集計したり、Windows PCに移してExcelのピボットで集計したりしていました。

ただ、ログファイルは、ノードや時間ごとに細かく分かれており、意外と手間がかかります。

AWSには、S3上のデータを、S3に格納したままSQLで操作できるAthenaというサービスがあります。このAthenaを使って、ログ解析を試みたところ、非常に簡単で便利でした。

SQLクエリーを工夫することで、毎時ごとのアクセス数なども手軽に集計できます。


ALBとCLB

Application Load BalancerとClassic Load Balancerとでは、アクセスログに記録される項目や、項目名が異なります。ここでは、Application Load Balancerで説明しますが、ここで紹介するSQLクエリーは、項目名を変更すれば、Classic Load Balancerでも利用できます。


テーブル定義

ELBのアクセスログは、スペース区切りの単なるテキストファイルです。まずは、SQLで扱うために、テーブルの構造を定義しましょう。

具体的には、CREATE EXTERNAL TABLE文を使います。

CREATE EXTERNAL TABLE IF NOT EXISTS <table_name> (

type string,
time string,
elb string,
client_ip string,
client_port int,
target_ip string,
target_port int,
request_processing_time double,
target_processing_time double,
response_processing_time double,
elb_status_code string,
target_status_code string,
received_bytes bigint,
sent_bytes bigint,
request_verb string,
request_url string,
request_proto string,
user_agent string,
ssl_cipher string,
ssl_protocol string,
target_group_arn string,
trace_id string,
domain_name string,
chosen_cert_arn string,
matched_rule_priority string,
request_creation_time string,
actions_executed string,
redirect_url string,
new_field string
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
'serialization.format' = '1',
'input.regex' =
'([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*):([0-9]*) ([^ ]*)[:-]([0-9]*) ([-.0-9]*) ([-.0-9]*) ([-.0-9]*) (|[-0-9]*) (-|[-0-9]*) ([-0-9]*) ([-0-9]*) \"([^ ]*) ([^ ]*) (- |[^ ]*)\" \"([^\"]*)\" ([A-Z0-9-]+) ([A-Za-z0-9.-]*) ([^ ]*) \"([^\"]*)\" \"([^\"]*)\" \"([^\"]*)\" ([-.0-9]*) ([^ ]*) \"([^\"]*)\"($| \"[^ ]*\")(.*)')
LOCATION 's3://<bucket_name>/AWSLogs/<account_id>/elasticloadbalancing/<region>/yyyy/mm/dd/';

最初のCREATE EXTERNAL TABLE文と、最後のLOCATION文は、お使いの環境に合わせて修正してください(それぞれテーブル名とログのリソースURIを指定します)。

なお、リソースURIですが、s3://<bucket_name>/AWSLogs/<account_id>/elasticloadbalancing/<region>/であれば、そのELBの全ログが対象になり、日付を加えてs3://<bucket_name>/AWSLogs/<account_id>/elasticloadbalancing/<region>/yyyy/mm/dd/とすると、その日付(UTC)に保管されたログだけに限定されます。Athenaはスキャンするデータの容量で課金されるため、私は、日付を指定し、スキャン対象を限定して使っています。


SQLクエリーの実行

準備ができたら、SQLクエリーを実行しましょう。

▼指定時間内で、アクセスが多いIPのトップ100

SELECT client_ip,

count(*) AS request_count
FROM <table_name>
WHERE time >= '2018-10-01T03:00'
AND time < '2018-10-01T09:00'
GROUP BY client_ip
ORDER BY request_count DESC limit 100;

▼ターゲット処理時間が1秒以上のURL(指定時間内/処理時間が大きい順)

SELECT time,

client_ip,
request_url,
target_processing_time
FROM <table_name>
WHERE time >= '2018-10-01T03:00'
AND time < '2018-10-01T09:00'
AND target_processing_time >= 1
ORDER BY target_processing_time DESC;


毎時ごとの集計

ネットで調べて簡単には見つからなかったのが、この「毎時ごと」の集計でした。

Athenaの場合、substr関数が利用できるため、timeフィールドのyyyy/mm/dd:hhを切り出してGROUP BY句を使っています。

▼毎時ごとのターゲット処理時間とアクセス数(タイムアウト分は除外)

SELECT substr(time,1,13) AS every_hour,

avg(target_processing_time) AS average_time,
count(*) AS request_count
FROM <table_name>
WHERE target_processing_time > 0
GROUP BY substr(time,1,13)
ORDER BY every_hour;

▼毎時ごとのタイムアウト数

SELECT substr(time,1,13) AS every_hour,

count(*) AS request_count
FROM <table_name>
WHERE target_processing_time < 0
GROUP BY substr(time,1,13)
ORDER BY every_hour;


ダウンロード用CSVファイル

どうしても手元にログファイルが必要になったら、下記のSQLクエリーで1つのCSVファイルとしてダウンロードすることもできます。

▼指定時間内の全項目

SELECT *

FROM <table_name>
WHERE time >= '2018-10-01T03:00'
AND time < '2018-10-01T09:00'
ORDER BY time;


まとめ

Athenaを使うことで、さまざまな集計が手軽に行えます。

SQLクエリー次第では、さらに複雑な集計も可能です。

今後、役立つクエリーができたら、ここで紹介したいと思っています。


参考

▼Amazon Athena ユーザーガイド - Application Load Balancer ログのクエリ

https://docs.aws.amazon.com/ja_jp/athena/latest/ug/application-load-balancer-logs.html