steampipe plugin install aws

Table: aws_cloudtrail_trail - Query AWS CloudTrail Trail using SQL

AWS CloudTrail Trail is a service that enables governance, compliance, operational auditing, and risk auditing of your AWS account. With CloudTrail, you can log, continuously monitor, and retain account activity related to actions across your AWS infrastructure. It provides event history of your AWS account activity, including actions taken through the AWS Management Console, AWS SDKs, command line tools, and other AWS services.

Table Usage Guide

The aws_cloudtrail_trail table in Steampipe provides you with information about each trail within the AWS CloudTrail service. This table allows you, as a DevOps engineer, to query trail-specific details, including configuration settings, trail status, and associated metadata. You can utilize this table to gather insights on trails, such as CloudTrail configuration, trail status, and more. The schema outlines the various attributes of the trail for you, including the trail ARN, home region, log file validation, and associated tags.

Examples

Basic info

Explore which trails in your AWS CloudTrail service are multi-region. This can help you understand your trail configuration and manage resources effectively across different regions.

select
name,
home_region,
is_multi_region_trail
from
aws_cloudtrail_trail
select
name,
home_region,
is_multi_region_trail
from
aws_cloudtrail_trail

List trails that are not encrypted

Identify instances where trails in AWS CloudTrail are not encrypted. This can help in assessing the security posture of your AWS environment, and ensure that all trails are adequately protected.

select
name,
kms_key_id
from
aws_cloudtrail_trail
where
kms_key_id is null;
select
name,
kms_key_id
from
aws_cloudtrail_trail
where
kms_key_id is null;

List trails that store logs in publicly accessible S3 buckets

Discover the trails that are storing logs in publicly accessible S3 buckets. This is useful for identifying potential security risks associated with public access to sensitive data.

select
trail.name as trail_name,
bucket.name as bucket_name,
bucket.bucket_policy_is_public as is_publicly_accessible
from
aws_cloudtrail_trail as trail
join aws_s3_bucket as bucket on trail.s3_bucket_name = bucket.name
where
bucket.bucket_policy_is_public;
select
trail.name as trail_name,
bucket.name as bucket_name,
bucket.bucket_policy_is_public as is_publicly_accessible
from
aws_cloudtrail_trail as trail
join aws_s3_bucket as bucket on trail.s3_bucket_name = bucket.name
where
bucket.bucket_policy_is_public = 1;

List trails that store logs in an S3 bucket with versioning disabled

Determine the areas in which trails store logs in an S3 bucket with versioning disabled, allowing you to identify potential security risks and ensure data integrity.

select
trail.name as trail_name,
bucket.name as bucket_name,
logging
from
aws_cloudtrail_trail as trail
join aws_s3_bucket as bucket on trail.s3_bucket_name = bucket.name
where
not versioning_enabled;
select
trail.name as trail_name,
bucket.name as bucket_name,
logging
from
aws_cloudtrail_trail as trail
join aws_s3_bucket as bucket on trail.s3_bucket_name = bucket.name
where
versioning_enabled = 0;

List trails that do not send log events to CloudWatch Logs

Identify instances where trails in AWS CloudTrail are not actively logging events. This is useful in pinpointing potential security risks or gaps in logging policies.

select
name,
is_logging
from
aws_cloudtrail_trail
where
not is_logging;
select
name,
is_logging
from
aws_cloudtrail_trail
where
is_logging = 0;

List trails with log file validation disabled

Determine the areas in which log file validation is disabled within your AWS CloudTrail trails. This could be useful in identifying potential security risks or compliance issues.

select
name,
arn,
log_file_validation_enabled
from
aws_cloudtrail_trail
where
not log_file_validation_enabled;
select
name,
arn,
log_file_validation_enabled
from
aws_cloudtrail_trail
where
log_file_validation_enabled = 0;

List shadow trails

Explore which AWS CloudTrail Trails are configured to operate across multiple regions, helping you identify potential security risks or compliance issues. This query is particularly useful in pinpointing trails that are not located in their home region, assisting in efficient resource management.

select
name,
arn,
region,
home_region
from
aws_cloudtrail_trail
where
is_multi_region_trail
and home_region <> region;
select
name,
arn,
region,
home_region
from
aws_cloudtrail_trail
where
is_multi_region_trail = 1
and home_region != region;

Control examples

Schema for aws_cloudtrail_trail

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
account_idtext=, !=, ~~, ~~*, !~~, !~~*The AWS Account ID in which the resource is located.
advanced_event_selectorsjsonbDescribes the advanced event selectors that are configured for the trail.
akasjsonbArray of globally unique identifier strings (also known as) for the resource.
arntext=The Amazon Resource Name (ARN) of the trail.
cloudwatch_logs_role_arntextSpecifies the role for the CloudWatch Logs endpoint to assume to write to a user's log group.
event_selectorsjsonbDescribes the event selectors that are configured for the trail.
has_custom_event_selectorsbooleanSpecifies whether the trail has custom event selectors, or not.
has_insight_selectorsbooleanSpecifies whether a trail has insight types specified in an InsightSelector list, or not.
home_regiontextThe region in which the trail was created.
include_global_service_eventsbooleanSpecifies whether to include AWS API calls from AWS global services, or not.
insight_selectorsjsonbA JSON string that contains the insight types you want to log on a trail.
is_loggingbooleanSpecifies whether the CloudTrail is currently logging AWS API calls, or not.
is_multi_region_trailbooleanSpecifies whether the trail exists only in one region or exists in all regions.
is_organization_trailbooleanSpecifies whether the trail is an organization trail, or not.
kms_key_idtextSpecifies the KMS key ID that encrypts the logs delivered by CloudTrail.
latest_cloudwatch_logs_delivery_errortextDisplays any CloudWatch Logs error that CloudTrail encountered when attempting to deliver logs to CloudWatch Logs.
latest_cloudwatch_logs_delivery_timetimestamp with time zoneDisplays the most recent date and time when CloudTrail delivered logs to CloudWatch Logs.
latest_delivery_errortextDisplays any Amazon S3 error that CloudTrail encountered when attempting to deliver log files to the designated bucket.
latest_delivery_timetimestamp with time zoneSpecifies the date and time that CloudTrail last delivered log files to an account's Amazon S3 bucket.
latest_digest_delivery_errortextDisplays any Amazon S3 error that CloudTrail encountered when attempting to deliver a digest file to the designated bucket.
latest_digest_delivery_timetimestamp with time zoneSpecifies the date and time that CloudTrail last delivered a digest file to an account's Amazon S3 bucket.
latest_notification_errortextDisplays any Amazon SNS error that CloudTrail encountered when attempting to send a notification.
latest_notification_timetimestamp with time zoneSpecifies the date and time of the most recent Amazon SNS notification that CloudTrail has written a new log file to an account's Amazon S3 bucket.
log_file_validation_enabledbooleanSpecifies whether log file validation is enabled, or not.
log_group_arntextSpecifies an Amazon Resource Name (ARN), a unique identifier that represents the log group to which CloudTrail logs will be delivered.
nametext=The name of the trail.
partitiontextThe AWS partition in which the resource is located (aws, aws-cn, or aws-us-gov).
regiontextThe AWS Region in which the resource is located.
s3_bucket_nametextName of the Amazon S3 bucket into which CloudTrail delivers your trail files.
s3_key_prefixtextSpecifies the Amazon S3 key prefix that comes after the name of the bucket you have designated for log file delivery.
sns_topic_arntextSpecifies the ARN of the Amazon SNS topic that CloudTrail uses to send notifications when log files are delivered.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
start_logging_timetimestamp with time zoneSpecifies the most recent date and time when CloudTrail started recording API calls for an AWS account.
stop_logging_timetimestamp with time zoneSpecifies the most recent date and time when CloudTrail stopped recording API calls for an AWS account.
tagsjsonbA map of tags for the resource.
tags_srcjsonbA list of tags assigned to the trail.
titletextTitle of the resource.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- aws

You can pass the configuration to the command with the --config argument:

steampipe_export_aws --config '<your_config>' aws_cloudtrail_trail