CloudTrail Lake queries
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'
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/
Ingesting data into AWS Data Lake using APIsasked 4 months ago
Athena query: Insufficient Lake Formation permission(s): Illegal permission combinationasked 4 months ago
Is it possible to specify DB snapshot in AWS Lake Formation?Accepted Answerasked a year ago
Using Cloud Trail Console to view all events in multi-account CloudTrail ( created via Organizations )Accepted Answerasked 25 days ago
cloudwatch or cloudtrail for lambda passrole attackasked 5 months ago
How to reduce the cost for cloudtrail loggingasked 5 months ago
Using Athena to query AWS Lake Formation databaseasked a month ago
Aurora Serverless Query Editor: restrict to read-only queriesasked 5 months ago
CloudTrail Lake queriesasked 24 days ago
How can I put JDBC or ODBC queries into a workgroup?asked 3 years ago