Security Lake Athena Queries

s3_activity_by_bucket.sql

-- Number of entries per bucket for the past 7 days
with dataset as (
    SELECT json_extract_scalar(api.request.data, '$.bucketName') as bucketName, * FROM amazon_security_lake_table_ap_southeast_1_s3_data_2_0  
    WHERE accountid = '123459012'
)
SELECT bucketName, count(bucketName) as numberOfRecords FROM dataset
WHERE time_dt BETWEEN CURRENT_TIMESTAMP - INTERVAL '7' DAY AND CURRENT_TIMESTAMP
group by bucketName
ORDER BY numberOfRecords desc