App Runner環境で生じた障害調査とAWS WAFによる暫定対策を実施しました

TEMP_S3='s3://athena-temp-ap-northeast-1-000000000000/'
aws s3 mb ${TEMP_S3}

LOG_S3='s3://cloudfront-logs-ap-northeast-1-00000000/xxxx/'
aws s3 sync --dryrun --exclude '*' --include '*2024-06-04-0[0,1,2,3]*.gz' ${LOG_S3} ${TEMP_S3} 
aws s3 sync --exclude '*' --include '*2024-06-04-0[0,1,2,3]*gz' ${LOG_S3} ${TEMP_S3} 

CREATE EXTERNAL TABLE IF NOT EXISTS cloudfront_standard_logs (
  `date` DATE,
  time STRING,
  x_edge_location STRING,
  sc_bytes BIGINT,
  c_ip STRING,
  cs_method STRING,
  cs_host STRING,
  cs_uri_stem STRING,
  sc_status INT,
  cs_referrer STRING,
  cs_user_agent STRING,
  cs_uri_query STRING,
  cs_cookie STRING,
  x_edge_result_type STRING,
  x_edge_request_id STRING,
  x_host_header STRING,
  cs_protocol STRING,
  cs_bytes BIGINT,
  time_taken FLOAT,
  x_forwarded_for STRING,
  ssl_protocol STRING,
  ssl_cipher STRING,
  x_edge_response_result_type STRING,
  cs_protocol_version STRING,
  fle_status STRING,
  fle_encrypted_fields INT,
  c_port INT,
  time_to_first_byte FLOAT,
  x_edge_detailed_result_type STRING,
  sc_content_type STRING,
  sc_content_len BIGINT,
  sc_range_start BIGINT,
  sc_range_end BIGINT
)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t'
LOCATION 's3://athena-temp-ap-northeast-1-000000000000/'
TBLPROPERTIES ( 'skip.header.line.count'='2' )

select
  a.*,
  a.required_time_sum / b.required_time_sum * 100
from
  (
    SELECT
      c_ip,
      substr(cs_user_agent, 1, 32) as cs_ua,
      count(1) as request_count,
      sum(time_taken) as required_time_sum
    FROM
      "default"."cloudfront_standard_logs"
    group by
      c_ip,
      cs_user_agent
    order by
      sum(time_taken) desc
  ) a, (
    SELECT
      sum(time_taken) as required_time_sum
    FROM
      "default"."cloudfront_standard_logs"
  ) b
order by
  a.required_time_sum desc
limit
  10

SELECT
  substr(time, 1, 4) || '0' as time_HHm0,
  count(1) as request_count,
  sum(time_taken) as required_time_sum
FROM
  "default"."cloudfront_standard_logs"
where
  c_ip = '2600:1900:2000:**::*:**'
group by
  substr(time, 1, 4) || '0'
order by
  time_HHm0