A

Official

Premium

AWS Cloudtrail source integration documentation

Name: CloudQuery AWS Cloudtrail Integration
Author: CloudQuery

This plugin is in preview.

AWS Cloudtrail plugin pulls event data from existing S3 Bucket to send data to destination

Publisher

cloudquery

Latest version

v1.0.0

Type

Source

Platforms

Date Published

Start Syncing

Overview Licenses

Overview #

The CloudQuery AWS CloudTrail Events plugin reads the log files that an AWS CloudTrail trail delivers to an S3 bucket and loads the individual CloudTrail event records into any supported CloudQuery destination (e.g. PostgreSQL, BigQuery, Snowflake, and more).

Each row is one entry of the Records array of a delivered log file, so you get the same event data the CloudTrail console shows, but queryable with SQL and retained for as long as your destination keeps it.

Both single-account trails and organization trails are supported.

Authentication #

The plugin authenticates with AWS using the standard AWS SDK credential chain, so any credential source the SDK understands works: environment variables, a shared credentials/config file, EC2/ECS/EKS instance metadata (IMDS, task roles, IRSA), or a role assumed from those.

Required permissions #

The credentials need to read the trail's settings and the bucket it delivers to:

cloudtrail:GetTrail on the trail named by trail_arn
s3:ListBucket on arn:aws:s3:::<bucket> (this also covers the HeadBucket that locates the bucket's region)
s3:GetObject on arn:aws:s3:::<bucket>/*

If the bucket is encrypted with a customer managed KMS key, the credentials also need kms:Decrypt on that key.

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": "cloudtrail:GetTrail",
      "Resource": "arn:aws:cloudtrail:us-east-1:123456789012:trail/my-trail"
    },
    {
      "Effect": "Allow",
      "Action": "s3:ListBucket",
      "Resource": "arn:aws:s3:::my-cloudtrail-bucket"
    },
    {
      "Effect": "Allow",
      "Action": "s3:GetObject",
      "Resource": "arn:aws:s3:::my-cloudtrail-bucket/*"
    }
  ]
}

When the trail and its bucket live in different accounts — the usual log-archive setup — cloudtrail:GetTrail is needed in the trail's account and the S3 permissions in the bucket's. Use role_to_assume for the pair only if one set of credentials cannot cover both.

Environment variables #

Set the credentials in the environment the sync runs in:

export AWS_ACCESS_KEY_ID=...
export AWS_SECRET_ACCESS_KEY=...
export AWS_SESSION_TOKEN=...   # only for temporary credentials

Shared credentials file #

Point the plugin at a named profile with local_profile:

spec:
  trail_arn: "arn:aws:cloudtrail:us-east-1:123456789012:trail/my-trail"
  local_profile: "security-audit"

With the following ~/.aws/credentials:

[default]
aws_access_key_id=xxxx
aws_secret_access_key=xxxx

[security-audit]
aws_access_key_id=xxxx
aws_secret_access_key=xxxx

Assuming a role #

Trails frequently deliver into a dedicated log-archive account. Use role_to_assume to read such a bucket:

spec:
  trail_arn: "arn:aws:cloudtrail:us-east-1:123456789012:trail/org-trail"
  role_to_assume:
    arn: "arn:aws:iam::123456789012:role/CloudQueryCloudTrailReader"
    external_id: "my-external-id"
    session_name: "cloudquery"

The role is assumed from whatever credentials the SDK chain resolves first, or from local_profile when that is set. Because syncs run unattended, only static and role-based credentials are supported; interactive flows such as SSO device authorization are not.

Configuration #

This example syncs a CloudTrail bucket to a single destination. The (top level) source spec section is described in the Source Spec Reference.

kind: source
# Common source-plugin configuration
spec:
  name: awscloudtrail
  path: cloudquery/awscloudtrail
  version: "v1.0.0"
  tables: ["*"]
  destinations: ["postgresql"]
  # CloudTrail Events specific configuration
  spec:
    trail_arn: "arn:aws:cloudtrail:us-east-1:123456789012:trail/my-trail"
    # Optional parameters
    # start_date: "2024-01-01"
    # end_date: "2024-02-01"
    # accounts: ["123456789012", "210987654321"]
    # regions: ["us-east-1", "eu-west-1"]
    # concurrency: 20
    # local_profile: "my-aws-profile"
    # role_to_assume:
    #   arn: "arn:aws:iam::123456789012:role/CloudQueryCloudTrailReader"
    #   external_id: "my-external-id"

CloudTrail Events Spec #

This is the (nested) spec used by the CloudTrail Events source plugin.

trail_arn (string) (required)
ARN of the trail to sync, e.g. arn:aws:cloudtrail:us-east-1:123456789012:trail/my-trail. For a multi-region trail, use the ARN in the region the trail was created in rather than a shadow copy of it.
Nothing else about the trail is configured. On every sync the plugin calls cloudtrail:GetTrail and takes the S3 bucket (S3BucketName), the key prefix (S3KeyPrefix) and whether log file integrity validation is on (LogFileValidationEnabled) from the trail itself, then locates the bucket's region with a HeadBucket. Reconfiguring the trail in AWS therefore takes effect on the next sync with no config change.
start_date (string) (optional, default: 30 days ago)
Start of the time range to sync. Objects belonging to a day before this point in time are neither listed nor downloaded. Accepts:
- an RFC3339 timestamp, e.g. 2024-01-01T00:00:00Z
- a date, e.g. 2024-01-01
- a relative value, e.g. 30 days ago, 12 hours ago, now
The default of 30 days ago exists because CloudTrail buckets routinely hold years of logs. Set an explicit date such as 2020-01-01 to backfill everything the bucket holds.
end_date (string) (optional, default: empty)
End of the time range to sync, in the same formats as start_date. Objects belonging to a day after this point in time are neither listed nor downloaded.
Leave it unset to keep following the trail: every sync then covers everything the bucket holds at the time it runs. Set it together with start_date (e.g. start_date: 2024-01-01 with end_date: 2024-02-01) to sync one fixed window. Relative bounds work as a rolling window, e.g. start_date: 30 days ago with end_date: 7 days ago; a range whose end_date is before its start_date is rejected.
Both bounds are day granular, because that is how CloudTrail partitions its keys: the days start_date and end_date fall on are synced in full, so a window can return events from up to a day outside it. Filter on event_time in the destination when you need the range to the second.
accounts ([]string) (optional, default: empty)
Accounts to sync, as 12 digit AWS account IDs, e.g. ["123456789012", "210987654321"].
Empty (the default) syncs every account the trail delivers to. For an organization trail that is every member account with logs in the bucket, so this is how an organization trail is narrowed to the accounts you care about. Prefixes belonging to an account outside the list are neither listed nor downloaded, and the excluded accounts' directories are not walked at all, so a scoped sync costs fewer S3 requests as well as fewer rows.
regions ([]string) (optional, default: empty)
Regions to sync, e.g. ["us-east-1", "eu-west-1"].
Empty (the default) syncs every region the trail delivers to, which for a multi-region trail is every region it covers. Matching ignores case, so US-East-1 and us-east-1 mean the same thing.
Both lists are allow-lists and combine: accounts with two entries and regions with three syncs at most the six pairs they cross. An entry the bucket holds no prefixes for is logged as a warning and does not fail the sync, because a member account that has not delivered anything yet is indistinguishable from a typo.
concurrency (integer) (optional, default: 20)
Number of account/region trail prefixes synced in parallel. Each prefix downloads several of its log objects in parallel on top of this.
local_profile (string) (optional, default: empty)
Name of a profile in your shared AWS credentials/config files to authenticate with. When combined with role_to_assume, the profile provides the base credentials used to assume the role.
role_to_assume (object) (optional)
Assume a role before reading the bucket. Useful when the bucket lives in a different account than the credentials the plugin starts with.
- arn (string) (required)
  ARN of the role to assume.
- session_name (string) (optional)
  Session name to use when assuming the role.
- external_id (string) (optional)
  External ID to use when assuming the role.

How the sync works #

Bucket layout #

CloudTrail delivers gzipped JSON objects under a date-partitioned key layout:

<prefix>/AWSLogs/<account-id>/CloudTrail/<region>/<YYYY>/<MM>/<DD>/<account-id>_CloudTrail_<region>_<YYYYMMDD>T<HHmm>Z_<random>.json.gz

Organization trails insert the organization ID:

<prefix>/AWSLogs/<org-id>/<account-id>/CloudTrail/<region>/<YYYY>/<MM>/<DD>/...

Starting from the bucket and prefix GetTrail reported, the plugin discovers the .../CloudTrail/<region>/ prefixes with delimited listings and then reads each of them. One trail fans out over an account/region pair each, many of them for an organization trail. Insight (CloudTrail-Insight) prefixes are never read. When accounts or regions is set, the discovery walk skips what they rule out, so an excluded account's directory is never listed and an excluded region's prefix never read.

The account, organization and region encoded in the key are exposed as the source_account_id, source_organization_id and source_region columns, alongside source_bucket, source_key and source_last_modified.

Digest files #

A trail with log file integrity validation enabled writes an hourly digest file naming every log file it delivered during that hour, into a sibling of the log prefix:

<prefix>/AWSLogs/[<org-id>/]<account-id>/CloudTrail-Digest/<region>/<YYYY>/<MM>/<DD>/<account-id>_CloudTrail-Digest_<region>_<trail-name>_<home-region>_<YYYYMMDD>T<HHmmss>Z.json.gz

When GetTrail reports LogFileValidationEnabled, the plugin reads those digests instead of listing the log objects, and downloads exactly the log files they name.

Digests are the better source of truth for what there is to read:

A digest names the log files of the trail that wrote it, and no others. A listing of the log prefix cannot tell them apart, so where several trails deliver to the same bucket and prefix, only the digest-driven sync reads the configured trail's log files rather than all of them.
A digest is CloudTrail's own signed statement of which log files it delivered, so objects added to the prefix by anything other than CloudTrail are not read.
CloudTrail delivers a digest even for an hour with no API activity, so an hour with nothing in it is a positive answer rather than an absent one.
A digest is delivered once its log files are, which makes an incremental sync resume on a timestamp the cursor can be compared against exactly. Late deliveries no longer depend on the bounded-lateness assumption the key-stamp pruning makes (see Incremental syncs) — a log file named days ago is still read when the digest naming it has only just landed.

The cost is freshness: a log file becomes visible to a sync when its digest is delivered, up to about an hour after the log file itself. A sync running more often than hourly picks those events up on a later run rather than the first one.

Because a digest is partitioned by the hour it closed, it can sort after the log files it names — a log file written just before midnight is digested on the day after. Digest listings therefore cover two days either side of the window, and the log files they turn up are still filtered on their own day, so start_date and end_date mean the same thing in both modes.

If the digest prefix exists but holds nothing for the window being synced — which is what a trail looks like when validation was enabled after it, or turned off again — the sync logs a warning and falls back to listing the log objects for that window.

Date pruning #

Keys below a .../CloudTrail/<region>/ prefix and below its CloudTrail-Digest sibling both begin with <YYYY>/<MM>/<DD>/, so they sort chronologically and both ends of the window are enforced on the listing itself:

start_date becomes an S3 StartAfter value of <prefix><YYYY>/<MM>/<DD>, so S3 never returns keys from earlier days. Old objects are therefore neither listed nor downloaded, which keeps the first sync of a bucket with years of history cheap.
end_date stops the listing at the first key belonging to a later day, so the trail is not paged through to its end either.

The keys of the boundary days are listed in full, and log files whose day falls outside the window are dropped without being downloaded.

A log file's name also ends in a <YYYYMMDD>T<HHmm>Z stamp, and the part of the name before it is constant within one trail, so keys sort chronologically within a day as well as across days. Incremental syncs use this to prune below a whole day — see below. Digest names put the trail name ahead of their stamp and so only sort that way within one trail; digests are listed a whole day at a time, which costs nothing because a day holds 24 of them.

Incremental syncs #

The table is incremental. After a successful sync the plugin stores the timestamp at which that sync started listing in the state backend, keyed by trail_arn and delivery prefix, so every account/region pair carries its own cursor. The next sync skips every object whose S3 LastModified is older than that timestamp, rewound by one hour.

LastModified rather than "the last key seen" is used as the cursor because keys are only chronological within one account/region prefix; across prefixes they interleave, so a single key cursor could not be advanced safely. The one-hour rewind absorbs CloudTrail's delivery latency (a log file appears minutes after the events it contains) and its lack of strict delivery ordering. Syncing is therefore at-least-once: a bounded window of objects is re-read, and the duplicate rows collapse in the destination because _cq_id is derived from event_id and event_time.

S3 cannot filter on LastModified, so the cursor is also pushed into StartAfter as far as the key layout allows: the day the rewound cursor falls on is listed from a <YYYYMMDD>T<HHmm>Z stamp two hours below it rather than from midnight. Without this, an hourly sync of a busy trail pages through an average of half a day of keys to find the objects delivered in the last hour, and a sync running just before midnight pages through a full day. The two-hour pad is what makes it safe: a log file is named for the batch window it covers and delivered afterwards, so the stamp is always earlier than the LastModified the cursor compares against. A file delivered more than three hours after the window it is named for (two hours of pad plus the one-hour rewind) is missed — the same bounded-lateness assumption the rewind already makes.

start_date is not refined this way; the day it falls on stays listed in full.

When the sync reads digest files, the cursor is compared against the digest's own LastModified instead, and the log files a surviving digest names are read whatever day they belong to. That is exact rather than bounded: a log file delivered long after the window it is named for is still synced, because the digest that names it was delivered after the cursor. The one-hour rewind still applies, to absorb digests delivered out of order.

Changing trail_arn starts from a fresh cursor rather than silently skipping objects. Moving the trail to another bucket or prefix does not: the cursor is keyed by the trail, and the pairs below the new prefix are discovered fresh anyway.

With an end_date in the past the cursor moves past the window after the first sync. Because CloudTrail can deliver a log file for a day inside the window after that window closed, the window stays listed on every later sync, but only objects stored after the cursor are downloaded: a repeat sync of a completed window reads keys and no objects.

Example Queries #

Find the identities that triggered the most failed API calls:

SELECT
  user_identity->>'arn' AS principal_arn,
  error_code,
  count(*) AS failures
FROM awscloudtrail_events
WHERE error_code IS NOT NULL
GROUP BY 1, 2
ORDER BY failures DESC
LIMIT 25;

List console sign-ins that were not protected by MFA:

SELECT
  event_time,
  recipient_account_id,
  user_identity->>'userName' AS user_name,
  source_ip_address,
  additional_event_data->>'MFAUsed' AS mfa_used
FROM awscloudtrail_events
WHERE event_name = 'ConsoleLogin'
  AND additional_event_data->>'MFAUsed' = 'No'
ORDER BY event_time DESC;

Show every use of the account root user:

SELECT event_time, event_source, event_name, source_ip_address, user_agent
FROM awscloudtrail_events
WHERE user_identity->>'type' = 'Root'
ORDER BY event_time DESC;

Find IAM and security-group changes made in the last seven days:

SELECT event_time, recipient_account_id, event_source, event_name, user_identity->>'arn' AS principal_arn
FROM awscloudtrail_events
WHERE read_only = false
  AND event_source IN ('iam.amazonaws.com', 'ec2.amazonaws.com')
  AND event_name ~ '^(Create|Delete|Put|Attach|Detach|Authorize|Revoke|Update)'
  AND event_time > now() - interval '7 days'
ORDER BY event_time DESC;

Break down write activity per account and service:

SELECT recipient_account_id, event_source, count(*) AS write_events
FROM awscloudtrail_events
WHERE read_only = false
GROUP BY 1, 2
ORDER BY write_events DESC;

Find the source IPs with the widest reach across accounts:

SELECT
  source_ip_address,
  count(DISTINCT recipient_account_id) AS accounts,
  count(DISTINCT event_name) AS distinct_actions,
  count(*) AS events
FROM awscloudtrail_events
WHERE source_ip_address NOT LIKE '%.amazonaws.com'
GROUP BY 1
HAVING count(DISTINCT recipient_account_id) > 1
ORDER BY events DESC;

Show the S3 buckets touched by data events, using the resources array:

SELECT
  resource->>'ARN' AS resource_arn,
  event_name,
  count(*) AS events
FROM awscloudtrail_events,
  jsonb_array_elements(resources) AS resource
WHERE resource->>'type' = 'AWS::S3::Object'
GROUP BY 1, 2
ORDER BY events DESC
LIMIT 50;

Correlate a cross-account event by its shared_event_id:

SELECT recipient_account_id, event_time, event_source, event_name, user_identity->>'arn' AS principal_arn
FROM awscloudtrail_events
WHERE shared_event_id IS NOT NULL
ORDER BY shared_event_id, event_time;

Check which calls still negotiate an outdated TLS version:

SELECT
  tls_details->>'tlsVersion' AS tls_version,
  event_source,
  count(*) AS calls
FROM awscloudtrail_events
WHERE tls_details->>'tlsVersion' IN ('TLSv1', 'TLSv1.1')
GROUP BY 1, 2
ORDER BY calls DESC;

See how much of the bucket a sync covered:

SELECT
  source_account_id,
  source_region,
  count(DISTINCT source_key) AS log_files,
  count(*) AS events,
  min(event_time) AS earliest_event,
  max(event_time) AS latest_event
FROM awscloudtrail_events
GROUP BY 1, 2
ORDER BY events DESC;

Licenses #

The following tools / packages are used in this plugin:

Name	License
github.com/adrg/xdg	MIT
github.com/apache/arrow-go/v18	Apache-2.0
github.com/apache/arrow/go/v13	Apache-2.0
github.com/apapsch/go-jsonmerge/v2	MIT
github.com/aws/aws-sdk-go-v2	Apache-2.0
github.com/aws/aws-sdk-go-v2/aws/protocol/eventstream	Apache-2.0
github.com/aws/aws-sdk-go-v2/config	Apache-2.0
github.com/aws/aws-sdk-go-v2/credentials	Apache-2.0
github.com/aws/aws-sdk-go-v2/feature/ec2/imds	Apache-2.0
github.com/aws/aws-sdk-go-v2/internal/configsources	Apache-2.0
github.com/aws/aws-sdk-go-v2/internal/endpoints/v2	Apache-2.0
github.com/aws/aws-sdk-go-v2/internal/sync/singleflight	BSD-3-Clause
github.com/aws/aws-sdk-go-v2/internal/v4a	Apache-2.0
github.com/aws/aws-sdk-go-v2/service/internal/accept-encoding	Apache-2.0
github.com/aws/aws-sdk-go-v2/service/internal/checksum	Apache-2.0
github.com/aws/aws-sdk-go-v2/service/internal/presigned-url	Apache-2.0
github.com/aws/aws-sdk-go-v2/service/internal/s3shared	Apache-2.0
github.com/aws/aws-sdk-go-v2/service/licensemanager	Apache-2.0
github.com/aws/aws-sdk-go-v2/service/marketplacemetering	Apache-2.0
github.com/aws/aws-sdk-go-v2/service/s3	Apache-2.0
github.com/aws/aws-sdk-go-v2/service/signin	Apache-2.0
github.com/aws/aws-sdk-go-v2/service/sso	Apache-2.0
github.com/aws/aws-sdk-go-v2/service/ssooidc	Apache-2.0
github.com/aws/aws-sdk-go-v2/service/sts	Apache-2.0
github.com/aws/smithy-go	Apache-2.0
github.com/aws/smithy-go/internal/sync/singleflight	BSD-3-Clause
github.com/bahlo/generic-list-go	BSD-3-Clause
github.com/buger/jsonparser	MIT
github.com/cenkalti/backoff/v5	MIT
github.com/cespare/xxhash/v2	MIT
github.com/cloudquery/cloudquery-api-go	MPL-2.0
github.com/cloudquery/codegen/jsonschema/docs	MPL-2.0
github.com/cloudquery/plugin-pb-go	MPL-2.0
github.com/cloudquery/plugin-sdk/v2/internal/glob	MIT
github.com/cloudquery/plugin-sdk/v2/schema	MIT
github.com/cloudquery/plugin-sdk/v2/types	MPL-2.0
github.com/cloudquery/plugin-sdk/v4	MPL-2.0
github.com/cloudquery/plugin-sdk/v4/glob	MIT
github.com/cloudquery/plugin-sdk/v4/scalar	MIT
github.com/davecgh/go-spew/spew	ISC
github.com/getsentry/sentry-go	MIT
github.com/ghodss/yaml	MIT
github.com/go-logr/logr	Apache-2.0
github.com/go-logr/stdr	Apache-2.0
github.com/goccy/go-json	MIT
github.com/google/flatbuffers/go	Apache-2.0
github.com/google/uuid	BSD-3-Clause
github.com/grpc-ecosystem/go-grpc-middleware/v2/interceptors	Apache-2.0
github.com/grpc-ecosystem/grpc-gateway/v2	BSD-3-Clause
github.com/hashicorp/go-cleanhttp	MPL-2.0
github.com/hashicorp/go-retryablehttp	MPL-2.0
github.com/invopop/jsonschema	MIT
github.com/klauspost/compress	Apache-2.0
github.com/klauspost/compress/internal/snapref	BSD-3-Clause
github.com/klauspost/compress/zstd/internal/xxhash	MIT
github.com/mailru/easyjson	MIT
github.com/mattn/go-colorable	MIT
github.com/mattn/go-isatty	MIT
github.com/oapi-codegen/runtime	Apache-2.0
github.com/pierrec/lz4/v4	BSD-3-Clause
github.com/pmezard/go-difflib/difflib	BSD-3-Clause
github.com/rs/zerolog	MIT
github.com/samber/lo	MIT
github.com/santhosh-tekuri/jsonschema/v6	Apache-2.0
github.com/spf13/cobra	Apache-2.0
github.com/spf13/pflag	BSD-3-Clause
github.com/stretchr/testify	MIT
github.com/thoas/go-funk	MIT
github.com/wk8/go-ordered-map/v2	Apache-2.0
github.com/zeebo/xxh3	BSD-2-Clause
go.opentelemetry.io/auto/sdk	Apache-2.0
go.opentelemetry.io/otel	Apache-2.0
go.opentelemetry.io/otel/exporters/otlp/otlplog/otlploghttp	Apache-2.0
go.opentelemetry.io/otel/exporters/otlp/otlpmetric/otlpmetrichttp	Apache-2.0
go.opentelemetry.io/otel/exporters/otlp/otlptrace	Apache-2.0
go.opentelemetry.io/otel/exporters/otlp/otlptrace/otlptracehttp	Apache-2.0
go.opentelemetry.io/otel/log	Apache-2.0
go.opentelemetry.io/otel/metric	Apache-2.0
go.opentelemetry.io/otel/sdk	Apache-2.0
go.opentelemetry.io/otel/sdk/log	Apache-2.0
go.opentelemetry.io/otel/sdk/metric	Apache-2.0
go.opentelemetry.io/otel/trace	Apache-2.0
go.opentelemetry.io/proto/otlp	Apache-2.0
golang.org/x/exp	BSD-3-Clause
golang.org/x/net	BSD-3-Clause
golang.org/x/sync	BSD-3-Clause
golang.org/x/sys	BSD-3-Clause
golang.org/x/text	BSD-3-Clause
golang.org/x/xerrors	BSD-3-Clause
google.golang.org/genproto/googleapis/api/httpbody	Apache-2.0
google.golang.org/genproto/googleapis/rpc/status	Apache-2.0
google.golang.org/grpc	Apache-2.0
google.golang.org/protobuf	BSD-3-Clause
gopkg.in/yaml.v2	Apache-2.0
gopkg.in/yaml.v3	MIT

Loading plugin documentation