CloudTrail Lake SQL 限制 - Amazon CloudTrail
Amazon Web Services 文档中描述的 Amazon Web Services 服务或功能可能因区域而异。要查看适用于中国区域的差异,请参阅中国的 Amazon Web Services 服务入门

CloudTrail Lake SQL 限制

CloudTrail Lake 查询是 SQL 字符串。本节介绍了用于创建查询而允许使用的 SQL 语言。

仅允许使用 SELECT 语句。没有查询字符串可以更改或变更数据。API 将 SELECT 语句的范围限制为以下模板中显示的参数树。允许简单的聚合、条件和联接运算符。不允许使用本节中未描述的关键字、运算符或函数。事件数据存储 ID(事件数据存储的 ARN 的 ID 部分)是表的有效值。

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 ] ]

CloudTrail 事件记录字段支持的架构

以下是 CloudTrail 事件记录字段的有效 SQL 架构。

[ { "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" } ]

Amazon Config 配置项目记录字段支持的架构

以下是配置项目记录字段的有效 SQL 架构。对于配置项目,eventcategory 的值为 ConfigurationItemeventtype 的值为 AwsConfigurationItem

[ { "Name": "eventversion", "Type": "string" }, { "Name": "eventcategory", "Type": "string" }, { "Name": "eventtype", "Type": "string" }, "Name": "eventid", "Type": "string" }, { "Name": "eventtime", "Type": "timestamp" }, { "Name": "awsregion", "Type": "string" }, { "Name": "recipientaccountid", "Type": "string" }, { "Name": "addendum", "Type": "map<string,string>" }, { "Name": "eventdata", "Type": "struct<configurationitemversion:string,configurationitemcapturetime: string,configurationitemstatus:string,configurationitemstateid:string,accountid:string, resourcetype:string,resourceid:string,resourcename:string,arn:string,awsregion:string, availabilityzone:string,resourcecreationtime:string,configuration:map<string,string>, supplementaryconfiguration:map<string,string>,relatedevents:string, relationships:struct<name:string,resourcetype:string,resourceid:string, resourcename:string>,tags:map<string,string>>" } ]

Amazon Audit Manager 证据记录字段支持的架构

以下是 Audit Manager 证据记录字段的有效 SQL 架构。对于 Audit Manager 证据记录字段,eventcategory 的值为 Evidenceeventtype 的值为 AwsAuditManagerEvidence。有关使用 Audit Manager 在 CloudTrail Lake 中聚合证据的更多信息,请参阅《Amazon Audit Manager 用户指南》中的证据查找器

[ { "Name": "eventversion", "Type": "string" }, { "Name": "eventcategory", "Type": "string" }, { "Name": "eventtype", "Type": "string" }, "Name": "eventid", "Type": "string" }, { "Name": "eventtime", "Type": "timestamp" }, { "Name": "awsregion", "Type": "string" }, { "Name": "recipientaccountid", "Type": "string" }, { "Name": "addendum", "Type": "map<string,string>" }, { "Name": "eventdata", "Type": "struct<attributes:map<string,string>,awsaccountid:string,awsorganization:string, compliancecheck:string,datasource:string,eventname:string,eventsource:string, evidenceawsaccountid:string,evidencebytype:string,iamid:string,evidenceid:string, time:timestamp,assessmentid:string,controlsetid:string,controlid:string, controlname:string,controldomainname:string,frameworkname:string,frameworkid:string, service:string,servicecategory:string,resourcearn:string,resourcetype:string, evidencefolderid:string,description:string,manualevidences3resourcepath:string, evidencefoldername:string,resourcecompliancecheck:string>" } ]

聚合函数、条件和联接运算符

允许以下 Aggregate 函数。

SUM MIN MAX AVG COUNT

允许以下 Condition 运算符。

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

允许以下 JOIN 运算符。有关运行多表查询的更多信息,请参阅 高级多表查询支持

UNION UNION ALL EXCEPT INTERSECT LEFT JOIN RIGHT JOIN INNER JOIN

支持的函数

以下是 CloudTrail Lake 查询支持的函数。有关描述和示例,请参阅 Presto 0.266 文档网站上的 JSON 函数和运算符

函数
element_at(映射 | 数组、对象 | 数字)➝ 对象
cardinality(映射 | 数组)➝ BigInt
日期转换函数(见下表)
map_value(映射)➝ 数组(对象)
map_keys(映射)➝ 数组(对象)
contains(数组、对象)➝ 布尔值
array_distinct(数组)➝ 数组
array_max(数组)➝ 对象,array_min(数组)➝ 对象
slice(数组、开始、长度)➝ 数组
json_parse(字符串)➝ json
is_json_scalar (json) ➝ 布尔值
json_extract(json,字符串)➝ Json
json_extract_scalar(json,字符串)➝ 字符串
json_format (json) ➝ 字符串
json_array_contains(json_array,对象)➝ 布尔值
json_array_get(json_array,索引)➝ json
json_array_length (json_array) ➝ bigInt

支持的日期转换函数

如需详细了解支持的日期和时间函数,请参阅 Presto 0.266 文档网站中的日期与时间函数和运算符

日期转换函数

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

高级多表查询支持

CloudTrail Lake 支持跨多个事件数据存储的高级查询语言。仅支持不包含子查询的查询。

要运行查询,请在 Amazon CLI 中使用 start-query 命令。以下是一个示例,它使用本节中的一个示例查询。

aws cloudtrail start-query --query-statement "Select eventId, eventName from EXAMPLEf852-4e8f-8bd1-bcf6cEXAMPLE UNION Select eventId, eventName from EXAMPLEg741-6y1x-9p3v-bnh6iEXAMPLE UNION ALL Select eventId, eventName from EXAMPLEb529-4e8f9l3d-6m2z-lkp5sEXAMPLE ORDER BY eventId LIMIT 10;"

响应是 QueryId 字符串。要获取查询的状态,请使用 start-query 返回的值 QueryId 运行 describe-query。如果查询成功,您可以运行 get-query-results 以获取结果。

UNION|UNION ALL|EXCEPT|INTERSECT

此版本添加了多表查询,即您可以跨多个事件数据存储运行的查询。以下是一个示例查询,它使用 UNIONUNION ALL 在三个事件数据存储(EDS1、EDS2 和 EDS3)中按它们的事件 ID 和事件名称查找事件。首先从每个事件数据存储中选择结果,然后将结果串联起来,按事件 ID 排序,并限制为十个事件。

Select eventId, eventName from EDS1 UNION Select eventId, eventName from EDS2 UNION ALL Select eventId, eventName from EDS3 ORDER BY eventId LIMIT 10;

LEFT|RIGHT|INNER JOIN

此版本添加了多表查询,即您可以跨多个事件数据存储运行的查询。以下是一个示例查询,它使用 LEFT JOIN 查找名为 eds2、映射到 edsB 的事件数据存储中的所有事件,这些事件与主(左侧)事件数据存储 edsA 中的事件匹配。返回的事件发生在 2020 年 1 月 1 日或之前,并且仅返回事件名称。

SELECT edsA.eventName, edsB.eventName, element_at(edsA.map, 'test') FROM eds1 as edsA LEFT JOIN eds2 as edsB ON edsA.eventId = edsB.eventId WHERE edsA.eventtime <= '2020-01-01' ORDER BY edsB.eventName;