SELECT elb_name,count(*) AS request_countFROM elb_logsGROUP BY elb_nameORDER BY request_count DESC;
特定の時間内でのリクエスト数を調べる
SELECT elb_name,count(*) AS request_countFROM elb_logsWHERE 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_nameORDER BY backend_response_code, elb_name;
ELBの5XXレスポンスを調べる(ELBを絞る)
ELBを絞ってHTTPCode_Backend_5XXレスポンスが発生したか確認できます。
SELECT elb_name,backend_response_code,count(*) AS request_countFROM elb_logsWHERE elb_name LIKE 'elb名'AND backend_response_code >= '500'GROUP BY backend_response_code, elb_nameORDER BY backend_response_code, elb_name;
ELBの5XXレスポンスを調べる(ELB,時間を絞る)
時間を絞りHTTPCode_Backend_5XXレスポンスが発生したか確認できます。
SELECT elb_name,backend_response_code,count(*) AS request_countFROM elb_logsWHERE 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,urlORDER BY request_count DESC limit 5;
