How to get the records from Amazon Athena for past week only

Question

I am writing a query to get Amazon Athena records for the past one week only. Here is what I wrote so far:

WITH events AS (
  SELECT
    event.eventVersion,
    event.eventID,
    event.eventTime,
    event.eventName,
    event.eventType,
    event.eventSource,
    event.awsRegion,
    event.sourceIPAddress,
    event.userAgent,  
    event.userIdentity.type AS userType,
    event.userIdentity.arn AS userArn,
    event.userIdentity.principalId as userPrincipalId,
    event.userIdentity.accountId as userAccountId,
    event.userIdentity.userName as userName
  FROM cloudtrail.events
  CROSS JOIN UNNEST (Records) AS r (event)
)
SELECT userName,sourceIPAddress,eventName,eventTime FROM events WHERE eventName='ConsoleLogin';

But I am not sure how to write it to extract records for the past 1 week only.

Please don't cross-post https://stackoverflow.com/questions/43537086/how-to-get-results-from-athena-for-the-past-week — Philᵀᴹ, Apr 21 '17 at 10:11
ohkie, i thought this more suited here . Still can you help @Philᵀᴹ — Kittystone, Apr 21 '17 at 10:13
eventTime is of type date? Then surely adding AND now() - eventTime < interval '1 week' to the WHERE clause would do it? — Colin 't Hart, Apr 21 '17 at 14:13
@Colin'tHart : Says SYNTAX_ERROR: line 20:106: '-' cannot be applied to timestamp with time zone, varchar — Kittystone, Apr 24 '17 at 04:10
Sounds like eventTime is a varchar. Seriously bad design which you should fix. — Colin 't Hart, Apr 24 '17 at 07:56

score 9 · Answer 1 · answered Jun 12 '18 at 19:26

@Philᵀᴹ's answer is almost there. I just used it on my query and found the fix. I would have commented, but don't have enough points, so here's the answer.

You have to use current_timestamp and then convert it to iso8601 format. Like so:

WITH events AS (
  SELECT
    event.eventVersion,
    event.eventID,
    event.eventTime,
    event.eventName,
    event.eventType,
    event.eventSource,
    event.awsRegion,
    event.sourceIPAddress,
    event.userAgent,  
    event.userIdentity.type AS userType,
    event.userIdentity.arn AS userArn,
    event.userIdentity.principalId as userPrincipalId,
    event.userIdentity.accountId as userAccountId,
    event.userIdentity.userName as userName
  FROM cloudtrail.events
  CROSS JOIN UNNEST (Records) AS r (event)
)
SELECT userName,sourceIPAddress,eventName,eventTime FROM events WHERE eventName='ConsoleLogin'
and eventTime > to_iso8601(current_timestamp - interval '7' day);

You can test the format you actually need by doing a test query like this:

SELECT to_iso8601(current_date - interval '7' day);

Returns: '2018-06-05'

SELECT to_iso8602(current_timestamp - interval '7' day);

Returns: '2018-06-05T19:25:21.331Z', which is the same format as event.eventTime, and that works.

score 2 · Answer 2 · answered Apr 21 '17 at 10:20

2

Amazon Athena uses Presto, so you can use any date functions that Presto provides. You'll be wanting to use current_date - interval '7' day, or similar.

WITH events AS (
  SELECT
    event.eventVersion,
    event.eventID,
    event.eventTime,
    event.eventName,
    event.eventType,
    event.eventSource,
    event.awsRegion,
    event.sourceIPAddress,
    event.userAgent,  
    event.userIdentity.type AS userType,
    event.userIdentity.arn AS userArn,
    event.userIdentity.principalId as userPrincipalId,
    event.userIdentity.accountId as userAccountId,
    event.userIdentity.userName as userName
  FROM cloudtrail.events
  CROSS JOIN UNNEST (Records) AS r (event)
)
SELECT userName,sourceIPAddress,eventName,eventTime FROM events WHERE eventName='ConsoleLogin'
and eventTime > current_date - interval '7' day;

Untested, I don't have access to a DB to test.

answered Apr 21 '17 at 10:20

Philᵀᴹ

31,762
10
83
107

1

SYNTAX_ERROR: line 20:110: '>' cannot be applied to varchar, date – Kittystone Apr 21 '17 at 10:44
1

I can't help any further without a test environment, sorry. Will delete my answer – Philᵀᴹ Apr 21 '17 at 10:45
1

i am also confused.. what could be wrong :( – Kittystone Apr 21 '17 at 10:49
2

@Philᵀᴹ Seems to me that error message would be a result of eventTime being a varchar... – Colin 't Hart Apr 24 '17 at 09:01
1

@Colin'tHart I get that, but don't have Athena handy to test fixing it – Philᵀᴹ Apr 24 '17 at 09:02
FYI -- Presto SQL is now Trino – JosephDoggie Jan 29 '21 at 21:22

How to get the records from Amazon Athena for past week only

2 Answers2