S3 Access logs queries

Find the DISTINCT IP addresses communicating with an S3 bucket and rank

The misconfiguration of S3 buckets is a major threat vector. If an open bucket is detected that was not intended to be world readable, it is of critical importance to understand if there were any inappropriate accesses.

This query will collect and rank all IP addresses accessing the bucket of interest. These should be reviewed to determine if any are outside your organization—if so, you may have had a data leak.

SELECT
 remoteip,
 count(1) AS total_rows
FROM panther_logs.public.aws_s3serveraccess
WHERE
  p_occurs_between('2021-01-01', '2021-02-01')
  AND
  bucket='somebucket'
GROUP BY remoteip
ORDER BY total_rows DESC