CloudTrail Lake SQL constraints - AWS CloudTrail

CloudTrail Lake SQL constraints

CloudTrail Lake queries are SQL strings. This section describes the allowed SQL language that you use to create queries.

Only SELECT statements are allowed. No query strings can change or mutate data. The API restricts the scope of a SELECT statement to the argument tree shown in the following template. Simple aggregations, conditions, and operators are allowed. A keyword, operator, or function that is not described in this section is disallowed. The event data store ID—the ID portion of the event data store's ARN—is the valid value for tables.

SELECT [ DISTINCT ] columns [ Aggregate ] [ FROM Tables event_data_store_ID] [ WHERE columns [ Conditions ] ] [ GROUP BY columns [ DISTINCT | Aggregate ] ] [ HAVING columns [ Aggregate | Conditions ] ] [ ORDER BY columns [ Aggregate | ASC | DESC | NULLS | FIRST | LAST ] [ LIMIT [ INT ] ]

Supported schema for event record fields

The following is the valid SQL schema for event record fields.

[ { "Name": "eventversion", "Type": "string" }, { "Name": "useridentity", "Type": "struct<type:string,principalid:string,arn:string,accountid:string,accesskeyid:string, username:string,sessioncontext:struct<attributes:struct<creationdate:timestamp, mfaauthenticated:string>,sessionissuer:struct<type:string,principalid:string,arn:string, accountid:string,username:string>,webidfederationdata:struct<federatedprovider:string, attributes:map<string,string>>,sourceidentity:string,ec2roledelivery:string, ec2issuedinvpc:string>,invokedby:string,identityprovider:string>" }, { "Name": "eventtime", "Type": "timestamp" }, { "Name": "eventsource", "Type": "string" }, { "Name": "eventname", "Type": "string" }, { "Name": "awsregion", "Type": "string" }, { "Name": "sourceipaddress", "Type": "string" }, { "Name": "useragent", "Type": "string" }, { "Name": "errorcode", "Type": "string" }, { "Name": "errormessage", "Type": "string" }, { "Name": "requestparameters", "Type": "map<string,string>" }, { "Name": "responseelements", "Type": "map<string,string>" }, { "Name": "additionaleventdata", "Type": "map<string,string>" }, { "Name": "requestid", "Type": "string" }, { "Name": "eventid", "Type": "string" }, { "Name": "readonly", "Type": "boolean" }, { "Name": "resources", "Type": "array<struct<accountid:string,type:string,arn:string,arnprefix:string>>" }, { "Name": "eventtype", "Type": "string" }, { "Name": "apiversion", "Type": "string" }, { "Name": "managementevent", "Type": "boolean" }, { "Name": "recipientaccountid", "Type": "string" }, { "Name": "sharedeventid", "Type": "string" }, { "Name": "annotation", "Type": "string" }, { "Name": "vpcendpointid", "Type": "string" }, { "Name": "serviceeventdetails", "Type": "map<string,string>" }, { "Name": "addendum", "Type": "map<string,string>" }, { "Name": "edgedevicedetails", "Type": "map<string,string>" }, { "Name": "insightdetails", "Type": "map<string,string>" }, { "Name": "eventcategory", "Type": "string" }, { "Name": "tlsdetails", "Type": "struct<tlsversion:string,ciphersuite:string,clientprovidedhostheader:string>" }, { "Name": "sessioncredentialfromconsole", "Type": "string" }, { "Name": "eventjson", "Type": "string" } { "Name": "eventjsonchecksum", "Type": "string" } ]

Aggregate functions and condition operators

The following are allowed Aggregate functions.

SUM MIN MAX AVG COUNT

The following are allowed Condition operators.

AND OR IN NOT IS (NOT) NULL LIKE < > <= >= <> != ( conditions ) #parenthesised conditions

Supported functions

The following are supported functions for CloudTrail Lake queries. For descriptions and examples, see JSON Functions and Operators on the Presto 0.266 documentation website.

Function
element_at(Map | Array, Object | number) ➝ Object
cardinality(Map | Array) ➝ BigInt
date convert functions (see following table)
map_values(Map) ➝ Array(Object)
map_keys(Map) ➝ Array(Object)
contains(array, object) ➝ boolean
array_distinct(array) ➝ array
array_max(array) ➝ Object, array_min(array)➝ Object
slice(array, start, length) ➝ array
json_parse(string) ➝ json
is_json_scalar(json) ➝ boolean
json_extract(json, string) ➝ Json
json_extract_scalar(json, string) ➝ string
json_format(json) ➝ string
json_array_contains(json_array, object) ➝ boolean
json_array_get(json_array, index) ➝ json
json_array_length(json_array)➝ bigInt

Supported date convert functions

For more information about the supported date and time functions, see Date and Time Functions and Operators on the Presto 0.266 documentation website.

Date convert functions

date

date_trunc

day

day_of_month

day_of_week

day_of_year

dow

doy

hour

millisecond

minute

month

quarter

second

week

week_of_year

year

year_of_week

yow