无法使用Athena查询KMS CMK加密的S3存储桶中的CloudTrail日志

huangapple
go评论81阅读模式
英文:

Not able to Athena query CloudTrail Logs from KMS CMK Encrypted S3 bucket

问题

我已创建了使用KMS CMK加密的S3存储桶，并配置CloudTrail日志存储到同一存储桶中。
CloudTrail成功将日志存储在该存储桶中。
但是，当我在Athena中执行SELECT * FROM cloudtrail_logs时，结果中没有返回任何记录。

我使用以下查询在Athena中创建了cloudtrail_logs表。

CREATE EXTERNAL TABLE cloudtrail_logs (
  eventversion STRING,
  useridentity STRUCT<
    type:STRING,
    principalid:STRING,
    arn:STRING,
    accountid:STRING,
    invokedby:STRING,
    accesskeyid:STRING,
    userName:STRING,
    sessioncontext:STRUCT<
      attributes:STRUCT<
        mfaauthenticated:STRING,
        creationdate:STRING>>,
    sessionissuer:STRUCT<
      type:STRING,
      principalId:STRING,
      arn:STRING, 
      accountId:STRING,
      userName:STRING>,
    ec2RoleDelivery:string,
    webIdFederationData:map<string,string>
  >,
  eventtime STRING,
  eventsource STRING,
  eventname STRING,
  awsregion STRING,
  sourceipaddress STRING,
  useragent STRING,
  errorcode STRING,
  errormessage STRING,
  requestparameters STRING,
  responseelements STRING,
  additionaleventdata STRING,
  requestid STRING,
  eventid STRING,
  resources ARRAY<STRUCT<
    arn:STRING,
    accountid:STRING,
    type:STRING>>,
  eventtype STRING,
  apiversion STRING,
  readonly STRING,
  recipientaccountid STRING,
  serviceeventdetails STRING,
  sharedeventid STRING,
  vpcendpointid STRING,
  tlsDetails struct<
    tlsVersion:string,
    cipherSuite:string,
    clientProvidedHostHeader:string>
)
PARTITIONED BY (region string, year string, month string, day string)
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
STORED AS INPUTFORMAT 'com.amazon.emr.cloudtrail.CloudTrailInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION 's3://<S3_BUCKET>/AWSLogs/<ACCOUNT_ID>/CloudTrail/'
TBLPROPERTIES ('has_encrypted_data' = 'true');

我是否漏掉了什么？

英文:

I have created S3 bucket encrypted with KMS CMK and configured CloudTrail Logs to store into that same bucket.
CloudTrail is storing logs in the bucket successfully.
However when I execute SELECT * FROM cloudtrail_logs in Athena, it does not return any records in results.

I used below query to create cloudtrail_logs table in Athena.

CREATE EXTERNAL TABLE cloudtrail_logs (
eventversion STRING,
useridentity STRUCT&lt;
               type:STRING,
               principalid:STRING,
               arn:STRING,
               accountid:STRING,
               invokedby:STRING,
               accesskeyid:STRING,
               userName:STRING,
  sessioncontext:STRUCT&lt;
    attributes:STRUCT&lt;
               mfaauthenticated:STRING,
               creationdate:STRING&gt;,
    sessionissuer:STRUCT&lt;  
               type:STRING,
               principalId:STRING,
               arn:STRING, 
               accountId:STRING,
               userName:STRING&gt;,
    ec2RoleDelivery:string,
    webIdFederationData:map&lt;string,string&gt;
  &gt;
&gt;,
eventtime STRING,
eventsource STRING,
eventname STRING,
awsregion STRING,
sourceipaddress STRING,
useragent STRING,
errorcode STRING,
errormessage STRING,
requestparameters STRING,
responseelements STRING,
additionaleventdata STRING,
requestid STRING,
eventid STRING,
resources ARRAY&lt;STRUCT&lt;
               arn:STRING,
               accountid:STRING,
               type:STRING&gt;&gt;,
eventtype STRING,
apiversion STRING,
readonly STRING,
recipientaccountid STRING,
serviceeventdetails STRING,
sharedeventid STRING,
vpcendpointid STRING,
tlsDetails struct&lt;
  tlsVersion:string,
  cipherSuite:string,
  clientProvidedHostHeader:string&gt;
)
PARTITIONED BY (region string, year string, month string, day string)
ROW FORMAT SERDE &#39;org.apache.hive.hcatalog.data.JsonSerDe&#39;
STORED AS INPUTFORMAT &#39;com.amazon.emr.cloudtrail.CloudTrailInputFormat&#39;
OUTPUTFORMAT &#39;org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat&#39;
LOCATION &#39;s3://&lt;S3_BUCKET&gt;/AWSLogs/&lt;ACCOUNT_ID&gt;/CloudTrail/&#39;
TBLPROPERTIES (&#39;has_encrypted_data&#39; = &#39;true&#39;);

Am I missing anything ?

答案1

得分: 0

我已通过以下查询创建Athena表来解决此问题：

CREATE EXTERNAL TABLE cloudtrail_logs (
    eventVersion STRING,
    userIdentity STRUCT<
        type: STRING,
        principalId: STRING,
        arn: STRING,
        accountId: STRING,
        invokedBy: STRING,
        accessKeyId: STRING,
        userName: STRING,
        sessionContext: STRUCT<
            attributes: STRUCT<
                mfaAuthenticated: STRING,
                creationDate: STRING>,
            sessionIssuer: STRUCT<
                type: STRING,
                principalId: STRING,
                arn: STRING,
                accountId: STRING,
                username: STRING>,
            ec2RoleDelivery: STRING,
            webIdFederationData: MAP<STRING,STRING>>>,

    eventTime STRING,
    eventSource STRING,
    eventName STRING,
    awsRegion STRING,
    sourceIpAddress STRING,
    userAgent STRING,
    errorCode STRING,
    errorMessage STRING,
    requestParameters STRING,
    responseElements STRING,
    additionalEventData STRING,
    requestId STRING,
    eventId STRING,

    resources ARRAY<STRUCT<
        arn: STRING,
        accountId: STRING,
        type: STRING>>,

    eventType STRING,
    apiVersion STRING,
    readOnly STRING,
    recipientAccountId STRING,
    serviceEventDetails STRING,
    sharedEventID STRING,
    vpcEndpointId STRING,

    tlsDetails STRUCT<
        tlsVersion: STRING,
        cipherSuite: STRING,
        clientProvidedHostHeader: STRING>
)
COMMENT 'CloudTrail表格用于存储桶'
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
STORED AS INPUTFORMAT 'com.amazon.emr.cloudtrail.CloudTrailInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION 's3://<BUCKET_NAME>/AWSLogs/<ACCOUNT_ID>/CloudTrail/'
TBLPROPERTIES ('classification'='cloudtrail');
英文:

I have fixed this issue by creating Athena table using below query:

CREATE EXTERNAL TABLE cloudtrail_logs (
    eventVersion STRING,
    userIdentity STRUCT&lt;
        type: STRING,
        principalId: STRING,
        arn: STRING,
        accountId: STRING,
        invokedBy: STRING,
        accessKeyId: STRING,
        userName: STRING,
        sessionContext: STRUCT&lt;
            attributes: STRUCT&lt;
                mfaAuthenticated: STRING,
                creationDate: STRING&gt;,
            sessionIssuer: STRUCT&lt;
                type: STRING,
                principalId: STRING,
                arn: STRING,
                accountId: STRING,
                username: STRING&gt;,
            ec2RoleDelivery: STRING,
            webIdFederationData: MAP&lt;STRING,STRING&gt;&gt;&gt;,
    eventTime STRING,
    eventSource STRING,
    eventName STRING,
    awsRegion STRING,
    sourceIpAddress STRING,
    userAgent STRING,
    errorCode STRING,
    errorMessage STRING,
    requestParameters STRING,
    responseElements STRING,
    additionalEventData STRING,
    requestId STRING,
    eventId STRING,
    resources ARRAY&lt;STRUCT&lt;
        arn: STRING,
        accountId: STRING,
        type: STRING&gt;&gt;,
    eventType STRING,
    apiVersion STRING,
    readOnly STRING,
    recipientAccountId STRING,
    serviceEventDetails STRING,
    sharedEventID STRING,
    vpcEndpointId STRING,
    tlsDetails STRUCT&lt;
        tlsVersion: STRING,
        cipherSuite: STRING,
        clientProvidedHostHeader: STRING&gt;
)
COMMENT &#39;CloudTrail table for bucket&#39;
ROW FORMAT SERDE &#39;org.apache.hive.hcatalog.data.JsonSerDe&#39;
STORED AS INPUTFORMAT &#39;com.amazon.emr.cloudtrail.CloudTrailInputFormat&#39;
OUTPUTFORMAT &#39;org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat&#39;
LOCATION &#39;s3://&lt;BUCKET_NAME&gt;/AWSLogs/&lt;ACCOUNT_ID&gt;/CloudTrail/&#39;
TBLPROPERTIES (&#39;classification&#39;=&#39;cloudtrail&#39;);

huangapple
  • 本文由 发表于 2023年5月29日 19:11:16
  • 转载请务必保留本文链接：https://go.coder-hub.com/76356832.html
  • amazon-athena
  • amazon-cloudtrail
  • amazon-kms
  • amazon-s3
  • amazon-web-services
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定