How to track Cloudtrail API calls from all Organizations AWS accounts using Athena

About Cloudtrail

AWS CloudTrail is a service that records AWS API calls and events for AWS accounts. This service can save logs as JSON text files in compressed gzip format (*.json.gzip) in S3 Bucket.

About Athena

AWS Athena is an interactive analytics service for large-scale data analysis, offering users the ability to perform SQL queries on data stored in S3 Bucket quickly, flexibly, and cost-effectively.

About Organizations

AWS Organizations is a service that allows companies to manage multiple AWS accounts in a single hierarchical structure. It consolidates billing for these accounts, also helps automate account and resource management, as well as the enforcement of security and compliance policies across all accounts.

How these services work together

By integrating AWS Organizations with AWS CloudTrail, we can monitor and audit all activities performed in your AWS accounts via Athena.

The purpose of the article is to demonstrate a way to perform SQL query in athena to search for information from all AWS accounts and regions of an Organizations, instead of performing queries by region and specific AWS account.

Note: queries will have lower performance due to the larger amount of data, but depending on the occasion this may be useful.

Steps

1. Create a Organization trail.
2. Create a Athena database and configure location query result.
3. Run a Athena query to create table.
4. Run a Athena query to obtain the trail logs.

3 - Run a Athena query to create table

Values that should be overridden for your environment:

• LOCATION
  • bucket-trail: Name of the S3 bucket where the trail log is saved.
  • o-123456789: Organization ID.
• storage.location.template
  • bucket-trail: Name of the S3 bucket where the trail log is saved.
  • o-123456789: Organization ID.
• projection.timestamp.range
  • '2024/02/01,NOW': Datetime range to track the trail logs.
• projection.accountid.values
  • '012345678912,219876543210': AWS accounts IDs to track the trail logs.
• projection.region.values
  • 'us-east-1,sa-east-1': AWS regions to track the trail logs.

Example Athena query to create table:

CREATE EXTERNAL TABLE cloudtrail_logs_all_accounts(
  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: STRUCT<
        federatedProvider: STRING,
        attributes: 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,
  readOnly STRING,
  resources ARRAY<STRUCT<
    arn: STRING,
    accountId: STRING,
    type: STRING>>,
  eventType STRING,
  apiVersion STRING,
  recipientAccountId STRING,
  serviceEventDetails STRING,
  sharedEventID STRING,
  vpcendpointid STRING,
  tlsDetails struct<
    tlsVersion:string,
    cipherSuite:string,
    clientProvidedHostHeader:string>
)
PARTITIONED BY ( 
  `timestamp` string, 
  `region` string, 
  `accountid` 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://bucket-trail/AWSLogs/o-123456789'
TBLPROPERTIES (
  'storage.location.template'='s3://bucket-trail/AWSLogs/o-123456789/${accountid}/CloudTrail/${region}/${timestamp}', 
  'projection.enabled'='true', 
  'projection.timestamp.type'='date', 
  'projection.timestamp.format'='yyyy/MM/dd', 
  'projection.timestamp.interval'='1', 
  'projection.timestamp.interval.unit'='DAYS', 
  'projection.timestamp.range'='2024/02/01,NOW', 
  'projection.accountid.type'='enum', 
  'projection.accountid.values'='012345678912,219876543210', 
  'projection.region.type'='enum', 
  'projection.region.values'='us-east-1,sa-east-1'
)

Run a Athena query to obtain the trail logs

Values that should be overridden for your environment:

• FROM cloudtrail_logs
  • cloudtrail_logs: Name of the table created above.
• WHERE userIdentity.sessionContext.sessionIssuer.arn = 'arn:aws:iam::012345678912:role/ROLE-NAME'
  • 'arn:aws:iam::012345678912:role/ROLE-NAME': Identity arn to track your trail log, be it role or user.

Example Athena query to track identity trail logs:

SELECT *
FROM cloudtrail_logs
WHERE userIdentity.sessionContext.sessionIssuer.arn = 'arn:aws:iam::012345678912:role/ROLE-NAME'

IAM Policy example required to execute the above queries after configuring the log trail and the Athena database:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "AthenaRunQuery",
            "Effect": "Allow",
            "Action": [
                "athena:StartQueryExecution",
                "athena:GetQueryExecution",
                "athena:GetQueryResults",
                "athena:StopQueryExecution"
            ],
            "Resource": [
                "arn:aws:athena:us-east-1:012345678912:workgroup/*",
                "arn:aws:athena:us-east-1:012345678912:queryExecution/*"
            ]
        },
        {
            "Sid": "S3BucketLoggingAccess",
            "Effect": "Allow",
            "Action": [
                "s3:GetBucketLocation",
                "s3:GetObject",
                "s3:ListBucket",
                "s3:ListBucketMultipartUploads",
                "s3:ListMultipartUploadParts",
                "s3:AbortMultipartUpload",
                "s3:PutObject*"
            ],
            "Resource": [
                "arn:aws:s3:::bucket-query-result",
                "arn:aws:s3:::bucket-query-result/*"
            ]
        },
        {
            "Sid": "S3BucketQueryAccess",
            "Effect": "Allow",
            "Action": [
                "s3:GetObject",
                "s3:ListBucket",
                "s3:GetBucketLocation"
            ],
            "Resource": [
                "arn:aws:s3:::bucket-trail",
                "arn:aws:s3:::bucket-trail/*"
            ]
        },
        {
            "Sid": "GlueWrite",
            "Effect": "Allow",
            "Action": [
                "glue:CreateTable",
                "glue:DeleteTable",
                "glue:BatchCreatePartition"
            ],
            "Resource": "arn:aws:glue:us-east-1:012345678912:*"
        },
        {
            "Sid": "GlueRead",
            "Action": [
                "glue:GetTable*",
                "glue:List*",
                "glue:GetPartition*",
                "glue:GetDatabase"
            ],
            "Effect": "Allow",
            "Resource": "arn:aws:glue:us-east-1:012345678912:*"
        }
    ]
}