![[PukiWiki]](image/owl.png "[PukiWiki]")
WHERE userIdentity.arn != '' AND userIdentity.username != 'null'
WHERE regexp_like(eventName, '^(Create|RunInstances)') AND NOT(regexp_like(eventName, '^(CreateLog)'))
※ALB/ELBのログもs3 objectのownerが外部アカウントなので同じエラーが出ると思われる。
aws s3 ls --profile account-b s3://cloudtrailbucket-example/ # OK aws s3 cp --profile account-b s3://cloudtrailbucket-example/AWSLogs/111111111111/CloudTrail/ap-northeast-1/2018/01/01/111111111111_CloudTrail_ap-northeast-1_20180101T0000Z_****.json.gz ./ fatal error: An error occurred (403) when calling the HeadObject operation: Forbidden
{ "Sid": "AthenaRead", "Effect": "Allow", "Principal": { "AWS": "arn:aws:iam::222222222222:root" }, "Action": [ "s3:GetBucketLocation", "s3:GetObject", "s3:ListBucket", ], "Resource": [ "arn:aws:s3:::cloudtrailbucket-example", "arn:aws:s3:::cloudtrailbucket-example/*" ] }
aws s3api get-object-acl --profile account-a --bucket cloudtrailbucket-example --key AWSLogs/111111111111/CloudTrail/ap-northeast-1/2018/01/01/111111111111_CloudTrail_ap-northeast-1_20180101T0000Z_****.json.gz { "Owner": { "DisplayName": "aws_cloudtrail_ap-northeast-1", "ID": "***" }, "Grants": [ { "Grantee": { "DisplayName": "aws_cloudtrail_ap-northeast-1", "ID": "****", "Type": "CanonicalUser" }, "Permission": "FULL_CONTROL" }, { "Grantee": { "DisplayName": "account-a", "ID": "****", "Type": "CanonicalUser" }, "Permission": "FULL_CONTROL" } ] }
aws s3api list-buckets --query Owner.ID --output text --profile account-b
"Principal": { "AWS": [ "arn:aws:iam::222222222222:user/*", "arn:aws:iam::222222222222:role/*" ] },
aws sts get-caller-identity --profile account-b { "UserId": "AROAID2GEXAMPLEROLEID:botocore-session-1234567890" ... # または aws iam get-role --role-name assumerole-readonly --profile account-b --query 'Role.RoleId' AROAID2GEXAMPLEROLEID
"Principal": { "AWS": "*" }, ... "Condition": { "StringLike": { "aws:userid": [ "222222222222", "AROAID2GEXAMPLEROLEID:*" ] } }
CloudTrailはAPIのログが残るので、API名(eventName)を知る必要がある。
サンプル:
SELECT eventTime, eventSource, eventName, recipientAccountId, awsRegion, userIdentity.type AS userIdentity_type, userIdentity.arn AS userIdentity_arn, sourceIPAddress, userAgent FROM cloudtrail_logs_allregion WHERE eventName IN ('ConsoleLogin') AND DATE = '2019/01/02' ORDER BY eventtime DESC LIMIT 10;
s3://<bucket>/AWSLogs/<org id>/<accountid>/CloudTrail/<region>/<date>/<object.gz>
Partition Projection機能でパーティション管理を自動化する方法は次の項目参照
ATHENA_TABLE_NAME=cloudtrail_logs_allregion SOURCE_BUCKET=cloudtrail-example OUTPUT_BUCKET=aws-athena-query-result-123456789012-ap-northeast-1/cloudtrail-example/output/ AWS_PROFILE=example AWS_REGION=ap-northeasst-1 AWS_ACCOUNT_ID=123456789012 aws athena start-query-execution --profile $AWS_PROFILE --region $AWS_REGION --result-configuration OutputLocation="s3://$OUTPUT_BUCKET" --query-string \ "CREATE EXTERNAL TABLE $ATHENA_TABLE_NAME ( 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>>>, 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 ) COMMENT 'CloudTrail table for $SOURCE_BUCKET bucket' PARTITIONED BY ( region string, year string, month string, day string ) ROW FORMAT SERDE 'com.amazon.emr.hive.serde.CloudTrailSerde' STORED AS INPUTFORMAT 'com.amazon.emr.cloudtrail.CloudTrailInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION 's3://$SOURCE_BUCKET/AWSLogs/$AWS_ACCOUNT_ID/CloudTrail/' TBLPROPERTIES ('classification'='cloudtrail');"
PART_YEAR=2019 PART_MONTH=01 PART_DAY=02 FOR region IN $(aws ec2 describe-regions --profile $AWS_PROFILE --query 'Regions[].RegionName' | jq -r '.[]'); do \ aws athena start-query-execution --profile $AWS_PROFILE --region $AWS_REGION --result-configuration OutputLocation="s3://$OUTPUT_BUCKET" --query-string \ "ALTER TABLE $ATHENA_TABLE_NAME ADD PARTITION (region='$region',year='$PART_YEAR',month='$PART_MONTH',day='$PART_DAY') LOCATION 's3://$SOURCE_BUCKET/AWSLogs/$AWS_ACCOUNT_ID/CloudTrail/$region/$PART_YEAR/$PART_MONTH/$PART_DAY/'"; \ done
SELECT * FROM cloudtrail_logs_allregion WHERE (requestparameters LIKE '%dev-web01%') AND region = 'ap-northeast-1' AND YEAR = '2019' AND MONTH = '01' AND DAY = '02' ORDER BY eventtime ASC LIMIT 10;
CREATE EXTERNAL TABLE $ATHENA_TABLE_NAME ( 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>>>, 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 ) COMMENT 'CloudTrail table for $SOURCE_BUCKET bucket' PARTITIONED BY ( accountId string, region string, DATE string ) ROW FORMAT SERDE 'com.amazon.emr.hive.serde.CloudTrailSerde' STORED AS INPUTFORMAT 'com.amazon.emr.cloudtrail.CloudTrailInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION 's3://$SOURCE_BUCKET/AWSLogs/$AWS_ACCOUNT_ID/CloudTrail/' TBLPROPERTIES( "projection.enabled" = "true", "projection.date.type" = "date", "projection.date.range" = "2019/01/01,NOW", "projection.date.format" = "yyyy/MM/dd", "projection.date.interval" = "1" , "projection.date.interval.unit" = "DAYS", "projection.accountid.type" = "enum", "projection.accountid.values" = "123456789012,etc", "projection.region.type" = "enum", "projection.region.values" = "us-east-1,ap-northeast-1,etc", "storage.location.template" = "s3://$SOURCE_BUCKET/AWSLogs/${accountid}/CloudTrail/${region}/${date}" );
SELECT * FROM cloudtrail_logs_allregion WHERE (requestparameters LIKE '%dev-web01%') AND accountid = '123456789012' AND region = 'ap-northeast-1' AND DATE = '2019/01/02' ORDER BY eventtime ASC LIMIT 10;
