amazon-web-servicesamazon-cloudtrailamazon-athena

How to get Number of read and write events using Athana from Cloudtrail

I've to get the read and write events from a Cloudtrail per day for n days. How do we partition the query to get a column where event type (read/write) is also specified?

Tried referring to the documentation, couldn't find anything there. Please help in writing the query.

i've created the table from the cloudtrail console directly.

Solution

  • 'readonly' parameter is used for this. The events where 'read only' is false-> these are write events

    SELECT COUNT(*) as count , cast (from_iso8601_timestamp(eventtime) as date) as date , readonly
FROM "default"."sampleTable" 
WHERE cast (from_iso8601_timestamp(eventtime) as date) > current_date - interval '15' day
GROUP BY cast (from_iso8601_timestamp(eventtime) as date) , readonly ;