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
的值为 ConfigurationItem
,eventtype
的值为 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
的值为 Evidence
,eventtype
的值为 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 文档网站中的日期与时间函数和运算符
日期转换函数 |
---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
高级多表查询支持
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
此版本添加了多表查询,即您可以跨多个事件数据存储运行的查询。以下是一个示例查询,它使用 UNION
和 UNION 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;