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

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

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表。

  1. CREATE EXTERNAL TABLE cloudtrail_logs (
  2.   eventversion STRING,
  3.   useridentity STRUCT<
  4.     type:STRING,
  5.     principalid:STRING,
  6.     arn:STRING,
  7.     accountid:STRING,
  8.     invokedby:STRING,
  9.     accesskeyid:STRING,
  10.     userName:STRING,
  11.     sessioncontext:STRUCT<
  12.       attributes:STRUCT<
  13.         mfaauthenticated:STRING,
  14.         creationdate:STRING>>,
  15.     sessionissuer:STRUCT<
  16.       type:STRING,
  17.       principalId:STRING,
  18.       arn:STRING, 
  19.       accountId:STRING,
  20.       userName:STRING>,
  21.     ec2RoleDelivery:string,
  22.     webIdFederationData:map<string,string>
  23.   >,
  24.   eventtime STRING,
  25.   eventsource STRING,
  26.   eventname STRING,
  27.   awsregion STRING,
  28.   sourceipaddress STRING,
  29.   useragent STRING,
  30.   errorcode STRING,
  31.   errormessage STRING,
  32.   requestparameters STRING,
  33.   responseelements STRING,
  34.   additionaleventdata STRING,
  35.   requestid STRING,
  36.   eventid STRING,
  37.   resources ARRAY<STRUCT<
  38.     arn:STRING,
  39.     accountid:STRING,
  40.     type:STRING>>,
  41.   eventtype STRING,
  42.   apiversion STRING,
  43.   readonly STRING,
  44.   recipientaccountid STRING,
  45.   serviceeventdetails STRING,
  46.   sharedeventid STRING,
  47.   vpcendpointid STRING,
  48.   tlsDetails struct<
  49.     tlsVersion:string,
  50.     cipherSuite:string,
  51.     clientProvidedHostHeader:string>
  52. )
  53. PARTITIONED BY (region string, year string, month string, day string)
  54. ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
  55. STORED AS INPUTFORMAT 'com.amazon.emr.cloudtrail.CloudTrailInputFormat'
  56. OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
  57. LOCATION 's3://<S3_BUCKET>/AWSLogs/<ACCOUNT_ID>/CloudTrail/'
  58. 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.

  1. CREATE EXTERNAL TABLE cloudtrail_logs (
  2. eventversion STRING,
  3. useridentity STRUCT&lt;
  4.                type:STRING,
  5.                principalid:STRING,
  6.                arn:STRING,
  7.                accountid:STRING,
  8.                invokedby:STRING,
  9.                accesskeyid:STRING,
  10.                userName:STRING,
  11.   sessioncontext:STRUCT&lt;
  12.     attributes:STRUCT&lt;
  13.                mfaauthenticated:STRING,
  14.                creationdate:STRING&gt;,
  15.     sessionissuer:STRUCT&lt;  
  16.                type:STRING,
  17.                principalId:STRING,
  18.                arn:STRING, 
  19.                accountId:STRING,
  20.                userName:STRING&gt;,
  21.     ec2RoleDelivery:string,
  22.     webIdFederationData:map&lt;string,string&gt;
  23.   &gt;
  24. &gt;,
  25. eventtime STRING,
  26. eventsource STRING,
  27. eventname STRING,
  28. awsregion STRING,
  29. sourceipaddress STRING,
  30. useragent STRING,
  31. errorcode STRING,
  32. errormessage STRING,
  33. requestparameters STRING,
  34. responseelements STRING,
  35. additionaleventdata STRING,
  36. requestid STRING,
  37. eventid STRING,
  38. resources ARRAY&lt;STRUCT&lt;
  39.                arn:STRING,
  40.                accountid:STRING,
  41.                type:STRING&gt;&gt;,
  42. eventtype STRING,
  43. apiversion STRING,
  44. readonly STRING,
  45. recipientaccountid STRING,
  46. serviceeventdetails STRING,
  47. sharedeventid STRING,
  48. vpcendpointid STRING,
  49. tlsDetails struct&lt;
  50.   tlsVersion:string,
  51.   cipherSuite:string,
  52.   clientProvidedHostHeader:string&gt;
  53. )
  54. PARTITIONED BY (region string, year string, month string, day string)
  55. ROW FORMAT SERDE &#39;org.apache.hive.hcatalog.data.JsonSerDe&#39;
  56. STORED AS INPUTFORMAT &#39;com.amazon.emr.cloudtrail.CloudTrailInputFormat&#39;
  57. OUTPUTFORMAT &#39;org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat&#39;
  58. LOCATION &#39;s3://&lt;S3_BUCKET&gt;/AWSLogs/&lt;ACCOUNT_ID&gt;/CloudTrail/&#39;
  59. TBLPROPERTIES (&#39;has_encrypted_data&#39; = &#39;true&#39;);

Am I missing anything ?

答案1

得分: 0

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

  1. CREATE EXTERNAL TABLE cloudtrail_logs (
  2.     eventVersion STRING,
  3.     userIdentity STRUCT<
  4.         type: STRING,
  5.         principalId: STRING,
  6.         arn: STRING,
  7.         accountId: STRING,
  8.         invokedBy: STRING,
  9.         accessKeyId: STRING,
  10.         userName: STRING,
  11.         sessionContext: STRUCT<
  12.             attributes: STRUCT<
  13.                 mfaAuthenticated: STRING,
  14.                 creationDate: STRING>,
  15.             sessionIssuer: STRUCT<
  16.                 type: STRING,
  17.                 principalId: STRING,
  18.                 arn: STRING,
  19.                 accountId: STRING,
  20.                 username: STRING>,
  21.             ec2RoleDelivery: STRING,
  22.             webIdFederationData: MAP<STRING,STRING>>>,
  24.     eventTime STRING,
  25.     eventSource STRING,
  26.     eventName STRING,
  27.     awsRegion STRING,
  28.     sourceIpAddress STRING,
  29.     userAgent STRING,
  30.     errorCode STRING,
  31.     errorMessage STRING,
  32.     requestParameters STRING,
  33.     responseElements STRING,
  34.     additionalEventData STRING,
  35.     requestId STRING,
  36.     eventId STRING,
  38.     resources ARRAY<STRUCT<
  39.         arn: STRING,
  40.         accountId: STRING,
  41.         type: STRING>>,
  43.     eventType STRING,
  44.     apiVersion STRING,
  45.     readOnly STRING,
  46.     recipientAccountId STRING,
  47.     serviceEventDetails STRING,
  48.     sharedEventID STRING,
  49.     vpcEndpointId STRING,
  51.     tlsDetails STRUCT<
  52.         tlsVersion: STRING,
  53.         cipherSuite: STRING,
  54.         clientProvidedHostHeader: STRING>
  55. )
  56. COMMENT 'CloudTrail表格用于存储桶'
  57. ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
  58. STORED AS INPUTFORMAT 'com.amazon.emr.cloudtrail.CloudTrailInputFormat'
  59. OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
  60. LOCATION 's3://<BUCKET_NAME>/AWSLogs/<ACCOUNT_ID>/CloudTrail/'
  61. TBLPROPERTIES ('classification'='cloudtrail');
英文:

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

  1. CREATE EXTERNAL TABLE cloudtrail_logs (
  2.     eventVersion STRING,
  3.     userIdentity STRUCT&lt;
  4.         type: STRING,
  5.         principalId: STRING,
  6.         arn: STRING,
  7.         accountId: STRING,
  8.         invokedBy: STRING,
  9.         accessKeyId: STRING,
  10.         userName: STRING,
  11.         sessionContext: STRUCT&lt;
  12.             attributes: STRUCT&lt;
  13.                 mfaAuthenticated: STRING,
  14.                 creationDate: STRING&gt;,
  15.             sessionIssuer: STRUCT&lt;
  16.                 type: STRING,
  17.                 principalId: STRING,
  18.                 arn: STRING,
  19.                 accountId: STRING,
  20.                 username: STRING&gt;,
  21.             ec2RoleDelivery: STRING,
  22.             webIdFederationData: MAP&lt;STRING,STRING&gt;&gt;&gt;,
  23.     eventTime STRING,
  24.     eventSource STRING,
  25.     eventName STRING,
  26.     awsRegion STRING,
  27.     sourceIpAddress STRING,
  28.     userAgent STRING,
  29.     errorCode STRING,
  30.     errorMessage STRING,
  31.     requestParameters STRING,
  32.     responseElements STRING,
  33.     additionalEventData STRING,
  34.     requestId STRING,
  35.     eventId STRING,
  36.     resources ARRAY&lt;STRUCT&lt;
  37.         arn: STRING,
  38.         accountId: STRING,
  39.         type: STRING&gt;&gt;,
  40.     eventType STRING,
  41.     apiVersion STRING,
  42.     readOnly STRING,
  43.     recipientAccountId STRING,
  44.     serviceEventDetails STRING,
  45.     sharedEventID STRING,
  46.     vpcEndpointId STRING,
  47.     tlsDetails STRUCT&lt;
  48.         tlsVersion: STRING,
  49.         cipherSuite: STRING,
  50.         clientProvidedHostHeader: STRING&gt;
  51. )
  52. COMMENT &#39;CloudTrail table for bucket&#39;
  53. ROW FORMAT SERDE &#39;org.apache.hive.hcatalog.data.JsonSerDe&#39;
  54. STORED AS INPUTFORMAT &#39;com.amazon.emr.cloudtrail.CloudTrailInputFormat&#39;
  55. OUTPUTFORMAT &#39;org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat&#39;
  56. LOCATION &#39;s3://&lt;BUCKET_NAME&gt;/AWSLogs/&lt;ACCOUNT_ID&gt;/CloudTrail/&#39;
  57. 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:

确定