By using AWS re:Post, you agree to the Terms of Use
/CloudTrail Lake queries/

CloudTrail Lake queries

0

I'm looking into Cloudtrail Lake and need tips/help on regarding queries. The given Query returns records as expected, however I need to queries where todays date is taken into consideratio,. This without having to rewrite the eventTimes dates every time.

Is there a function like now(), current_date() etc?

I see that supported date and time functions are using Presto 0.266 syntax, but are not able to find a solution to this issue https://docs.aws.amazon.com/awscloudtrail/latest/userguide/query-limitations.html

SELECT
    awsRegion, recipientAccountId, count(*
    ) as numRec
FROM
   $EDS_ID
WHERE
    eventTime >= '2022-05-01 00:00:00'  #I want something like  now() - 7 days
    and eventSource = 'states.amazonaws.com' 
1 Answers
0

I don't think you can do that in CloudTrail Lake - you might need to look into using Athena. CloudTrail Lake has a big plus in being a managed service with less setup and learning curve than Athena, but it can be more expensive and as you've found it's pretty limiting. In Athena you can do e.g.: where timestamp >= date_format(now() - interval '7' day,'%Y/%m/%d') See this article for more info: "Querying AWS CloudTrail - Athena vs CloudTrail Lake" - https://www.linkedin.com/pulse/querying-aws-cloudtrail-athena-vs-lake-steve-kinsman/

answered 24 days ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions