Amazon AthenaでELBのログを調査するときに使ったSQL

はじめに

今まではログの調査の際にはS3にあるELBのログをローカルに落としてgrepしたりしてましが、 Athenaを使ってログを解析してみましたので今回は調査のときに使ったSQLをご紹介します。 ご紹介するSQLを使えばあとは少し変えるだけでいろいろな調査が可能かと思います。

Athenaデータベース、テーブルの準備

Athenaを使用する際の手順は以下ブログを参考にしてください。

Amazon AthenaでELBログをSQLで解析する #reinvent

調査に使用したSQL

ELB毎のリクエスト数を調べる

SELECT elb_name,
count(*) AS request_count
FROM elb_logs
GROUP BY elb_name
ORDER BY request_count DESC;

特定の時間内でのリクエスト数を調べる

SELECT elb_name,
count(*) AS request_count
FROM elb_logs
WHERE request_timestamp >= '2017-07-24T00:00:00Z'
AND request_timestamp < '2017-07-24T23:59:59Z' GROUP BY elb_name ORDER BY request_count DESC; ``` ### 特定の時間内でのリクエスト数を調べる(ELBを絞る) ``` SELECT elb_name, count(*) AS request_count FROM elb_logs WHERE elb_name LIKE 'elb名' AND request_timestamp >= '2017-07-24T00:00:00Z'
AND request_timestamp < '2017-07-24T23:59:59Z' GROUP BY elb_name ORDER BY request_count DESC; ``` ### ELBの5XXレスポンスを調べる ELB毎にどのくらいHTTPCode_Backend_5XXレスポンスが発生したか確認できます。 ``` SELECT elb_name, backend_response_code, count(*) AS request_count FROM elb_logs WHERE backend_response_code >= '500'
GROUP BY backend_response_code, elb_name
ORDER BY backend_response_code, elb_name;

ELBの5XXレスポンスを調べる(ELBを絞る)

ELBを絞ってHTTPCode_Backend_5XXレスポンスが発生したか確認できます。

SELECT elb_name,
backend_response_code,
count(*) AS request_count
FROM elb_logs
WHERE elb_name LIKE 'elb名'
AND backend_response_code >= '500'
GROUP BY backend_response_code, elb_name
ORDER BY backend_response_code, elb_name;

ELBの5XXレスポンスを調べる(ELB,時間を絞る)

時間を絞りHTTPCode_Backend_5XXレスポンスが発生したか確認できます。

SELECT elb_name,
backend_response_code,
count(*) AS request_count
FROM elb_logs
WHERE elb_name LIKE 'elb名'
AND backend_response_code >= '500'
AND request_timestamp >= '2017-07-24T00:00:00Z'
AND request_timestamp < '2017-07-24T23:59:59Z' GROUP BY backend_response_code, elb_name ORDER BY backend_response_code, elb_name; ``` ### ELBの5XXレスポンスを調べる(url,elb_response_codeも表示させる) カウントしてsortしているのでURLを特定することができます。 ``` SELECT count(*) AS request_count, elb_name, url, elb_response_code, backend_response_code FROM elb_logs WHERE elb_name LIKE 'elb名' AND backend_response_code >= '500'
AND request_timestamp >= '2017-07-24T00:00:00Z'
AND request_timestamp < '2017-07-24T23:59:59Z' GROUP BY elb_name,url,elb_response_code,backend_response_code ORDER BY request_count DESC limit 10; ``` ### ELBの5XXレスポンスを調べる(urlも絞る) 複数のドメインがある場合に使用しました。 ``` SELECT count(*) AS request_count, elb_name, url, elb_response_code, backend_response_code FROM elb_logs WHERE elb_name LIKE 'elb名' AND backend_response_code >= '500'
AND url LIKE 'https://ドメイン:443/パス/%'
AND request_timestamp >= '2017-07-24T00:00:00Z'
AND request_timestamp < '2017-07-24T23:59:59Z' GROUP BY elb_name,url,elb_response_code,backend_response_code ORDER BY request_count DESC limit 10; ``` ### ELBの5XXレスポンスを調べる(url,user_agentも絞る) 特定のuser_agentを絞って確認したときに使用しました。 ``` SELECT count(*) AS request_count, elb_name, url, elb_response_code, backend_response_code, user_agent FROM elb_logs WHERE elb_name LIKE 'elb名' AND backend_response_code >= '500'
AND url LIKE 'https://ドメイン:443/パス/%'
AND user_agent LIKE '%ユーザーエージェント%'
AND request_timestamp >= '2017-07-24T00:00:00Z'
AND request_timestamp < '2017-07-24T23:59:59Z' GROUP BY elb_name,url,elb_response_code,backend_response_code,user_agent ORDER BY request_count DESC limit 10; ``` ### ELBのログを時系列で表示させる(時間,urlを絞る) 調査対象のログを時系列で確認する際に使用しました。 ``` SELECT elb_name, url, request_timestamp, elb_response_code, backend_response_code, user_agent FROM elb_logs WHERE elb_name LIKE 'elb名' AND url LIKE '%ドメイン%' AND user_agent LIKE 'https://ドメイン:443/パス/%' AND request_timestamp >= '2017-07-24T00:00:00Z'
AND request_timestamp < '2017-07-24T23:59:59Z' GROUP BY elb_name,url,request_timestamp,elb_response_code,backend_response_code,user_agent ORDER BY request_timestamp limit 10; ``` ### リクエストの多いIPアドレスを確認 ``` SELECT request_ip, count(*) AS request_count FROM elb_logs WHERE elb_name LIKE 'elb名' AND request_timestamp >= '2017-07-24T00:00:00Z'
AND request_timestamp < '2017-07-24T23:59:59Z' GROUP BY request_ip ORDER BY request_count DESC limit 5; ``` ### リクエストの多いIPアドレスを確認(urlも表示) ``` SELECT request_ip, url, count(*) AS request_count FROM elb_logs WHERE elb_name LIKE 'elb名' AND request_timestamp >= '2017-07-24T00:00:00Z'
AND request_timestamp < '2017-07-24T23:59:59Z'
GROUP BY request_ip,url
ORDER BY request_count DESC limit 5;

まとめ

Athenaを使ってログを調べてみましたのでSQLをご紹介しました。 実行結果は載せませんでしたがみなさんも実際に使ってみてください。