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_trailfrom aws_cloudtrail_trail
select name, home_region, is_multi_region_trailfrom 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_idfrom aws_cloudtrail_trailwhere kms_key_id is null;
select name, kms_key_idfrom aws_cloudtrail_trailwhere 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_accessiblefrom aws_cloudtrail_trail as trail join aws_s3_bucket as bucket on trail.s3_bucket_name = bucket.namewhere bucket.bucket_policy_is_public;
select trail.name as trail_name, bucket.name as bucket_name, bucket.bucket_policy_is_public as is_publicly_accessiblefrom aws_cloudtrail_trail as trail join aws_s3_bucket as bucket on trail.s3_bucket_name = bucket.namewhere 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, loggingfrom aws_cloudtrail_trail as trail join aws_s3_bucket as bucket on trail.s3_bucket_name = bucket.namewhere not versioning_enabled;
select trail.name as trail_name, bucket.name as bucket_name, loggingfrom aws_cloudtrail_trail as trail join aws_s3_bucket as bucket on trail.s3_bucket_name = bucket.namewhere 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_loggingfrom aws_cloudtrail_trailwhere not is_logging;
select name, is_loggingfrom aws_cloudtrail_trailwhere 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_enabledfrom aws_cloudtrail_trailwhere not log_file_validation_enabled;
select name, arn, log_file_validation_enabledfrom aws_cloudtrail_trailwhere 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_regionfrom aws_cloudtrail_trailwhere is_multi_region_trail and home_region <> region;
select name, arn, region, home_regionfrom aws_cloudtrail_trailwhere is_multi_region_trail = 1 and home_region != region;
Query examples
- cloudtrail_regional_trail_count
- cloudtrail_trail_bucket
- cloudtrail_trail_count
- cloudtrail_trail_encryption_table
- cloudtrail_trail_input
- cloudtrail_trail_log_file_validation
- cloudtrail_trail_log_file_validation_disabled_count
- cloudtrail_trail_logging
- cloudtrail_trail_logging_disabled_count
- cloudtrail_trail_logging_table
- cloudtrail_trail_multi_region
- cloudtrail_trail_multi_region_count
- cloudtrail_trail_overview
- cloudtrail_trail_regional
- cloudtrail_trail_tags
- cloudtrail_trail_unencrypted
- cloudtrail_trail_unencrypted_count
- cloudtrail_trails_for_cloudwatch_log_group
- cloudtrail_trails_for_kms_key
- cloudtrail_trails_for_s3_bucket
- cloudtrail_trails_for_sns_topic
- cloudwatch_log_groups_for_cloudtrail_trail
- kms_keys_for_cloudtrail_trail
- s3_buckets_for_cloudtrail_trail
- sns_topics_for_cloudtrail_trail
Control examples
- 1 CloudTrail should be enabled and configured with at least one multi-Region trail
- 2 CloudTrail should have encryption at rest enabled
- 2.1 Ensure CloudTrail is enabled in all regions
- 2.2 Ensure CloudTrail log file validation is enabled.
- 2.3 Ensure the S3 bucket used to store CloudTrail logs is not publicly accessible
- 2.4 Ensure CloudTrail trails are integrated with CloudWatch Logs
- 2.6 Ensure S3 bucket access logging is enabled on the CloudTrail S3 bucket
- 2.7 Ensure CloudTrail logs are encrypted at rest using KMS CMKs
- 3.1 Ensure a log metric filter and alarm exist for unauthorized API calls
- 3.1 Ensure CloudTrail is enabled in all regions
- 3.1 Ensure CloudTrail is enabled in all regions
- 3.1 Ensure CloudTrail is enabled in all regions
- 3.1 Ensure CloudTrail is enabled in all regions
- 3.1 Ensure CloudTrail is enabled in all regions
- 3.10 Ensure a log metric filter and alarm exist for security group changes
- 3.10 Ensure that Object-level logging for write events is enabled for S3 bucket
- 3.10 Ensure that Object-level logging for write events is enabled for S3 bucket
- 3.10 Ensure that Object-level logging for write events is enabled for S3 bucket
- 3.10 Ensure that Object-level logging for write events is enabled for S3 bucket
- 3.11 Ensure a log metric filter and alarm exist for changes to Network Access Control Lists (NACL)
- 3.11 Ensure that Object-level logging for read events is enabled for S3 bucket
- 3.11 Ensure that Object-level logging for read events is enabled for S3 bucket
- 3.11 Ensure that Object-level logging for read events is enabled for S3 bucket
- 3.11 Ensure that Object-level logging for read events is enabled for S3 bucket
- 3.12 Ensure a log metric filter and alarm exist for changes to network gateways
- 3.13 Ensure a log metric filter and alarm exist for route table changes
- 3.14 Ensure a log metric filter and alarm exist for VPC changes
- 3.2 Ensure a log metric filter and alarm exist for Management Console sign-in without MFA
- 3.2 Ensure CloudTrail log file validation is enabled
- 3.2 Ensure CloudTrail log file validation is enabled
- 3.2 Ensure CloudTrail log file validation is enabled
- 3.2 Ensure CloudTrail log file validation is enabled
- 3.2 Ensure CloudTrail log file validation is enabled.
- 3.3 Ensure a log metric filter and alarm exist for usage of "root" account
- 3.3 Ensure the S3 bucket used to store CloudTrail logs is not publicly accessible
- 3.3 Ensure the S3 bucket used to store CloudTrail logs is not publicly accessible
- 3.3 Ensure the S3 bucket used to store CloudTrail logs is not publicly accessible
- 3.3 Ensure the S3 bucket used to store CloudTrail logs is not publicly accessible
- 3.4 Ensure a log metric filter and alarm exist for IAM policy changes
- 3.4 Ensure CloudTrail trails are integrated with CloudWatch Logs
- 3.4 Ensure CloudTrail trails are integrated with CloudWatch Logs
- 3.4 Ensure CloudTrail trails are integrated with CloudWatch Logs
- 3.4 Ensure CloudTrail trails are integrated with CloudWatch Logs
- 3.4 Ensure S3 bucket access logging is enabled on the CloudTrail S3 bucket
- 3.5 Ensure a log metric filter and alarm exist for CloudTrail configuration changes
- 3.5 Ensure CloudTrail logs are encrypted at rest using KMS CMKs
- 3.6 Ensure a log metric filter and alarm exist for AWS Management Console authentication failures
- 3.6 Ensure S3 bucket access logging is enabled on the CloudTrail S3 bucket
- 3.6 Ensure S3 bucket access logging is enabled on the CloudTrail S3 bucket
- 3.6 Ensure S3 bucket access logging is enabled on the CloudTrail S3 bucket
- 3.6 Ensure S3 bucket access logging is enabled on the CloudTrail S3 bucket
- 3.7 Ensure a log metric filter and alarm exist for disabling or scheduled deletion of customer created CMKs
- 3.7 Ensure CloudTrail logs are encrypted at rest using KMS CMKs
- 3.7 Ensure CloudTrail logs are encrypted at rest using KMS CMKs
- 3.7 Ensure CloudTrail logs are encrypted at rest using KMS CMKs
- 3.7 Ensure CloudTrail logs are encrypted at rest using KMS CMKs
- 3.8 Ensure a log metric filter and alarm exist for S3 bucket policy changes
- 3.8 Ensure that Object-level logging for write events is enabled for S3 bucket
- 3.9 Ensure a log metric filter and alarm exist for AWS Config configuration changes
- 3.9 Ensure that Object-level logging for read events is enabled for S3 bucket
- 4 Ensure CloudTrail log file validation is enabled
- 4.1 Ensure a log metric filter and alarm exist for unauthorized API calls
- 4.1 Ensure a log metric filter and alarm exist for unauthorized API calls
- 4.1 Ensure a log metric filter and alarm exist for unauthorized API calls
- 4.1 Ensure unauthorized API calls are monitored
- 4.1 Ensure unauthorized API calls are monitored
- 4.10 Ensure a log metric filter and alarm exist for security group changes
- 4.10 Ensure a log metric filter and alarm exist for security group changes
- 4.10 Ensure a log metric filter and alarm exist for security group changes
- 4.10 Ensure security group changes are monitored
- 4.10 Ensure security group changes are monitored
- 4.11 Ensure a log metric filter and alarm exist for changes to Network Access Control Lists (NACL)
- 4.11 Ensure a log metric filter and alarm exist for changes to Network Access Control Lists (NACL)
- 4.11 Ensure a log metric filter and alarm exist for changes to Network Access Control Lists (NACL)
- 4.11 Ensure Network Access Control Lists (NACL) changes are monitored
- 4.11 Ensure Network Access Control Lists (NACL) changes are monitored
- 4.12 Ensure a log metric filter and alarm exist for changes to network gateways
- 4.12 Ensure a log metric filter and alarm exist for changes to network gateways
- 4.12 Ensure a log metric filter and alarm exist for changes to network gateways
- 4.12 Ensure changes to network gateways are monitored
- 4.12 Ensure changes to network gateways are monitored
- 4.13 Ensure a log metric filter and alarm exist for route table changes
- 4.13 Ensure a log metric filter and alarm exist for route table changes
- 4.13 Ensure a log metric filter and alarm exist for route table changes
- 4.13 Ensure route table changes are monitored
- 4.13 Ensure route table changes are monitored
- 4.14 Ensure a log metric filter and alarm exist for VPC changes
- 4.14 Ensure a log metric filter and alarm exist for VPC changes
- 4.14 Ensure a log metric filter and alarm exist for VPC changes
- 4.14 Ensure VPC changes are monitored
- 4.14 Ensure VPC changes are monitored
- 4.15 Ensure a log metric filter and alarm exists for AWS Organizations changes
- 4.15 Ensure a log metric filter and alarm exists for AWS Organizations changes
- 4.15 Ensure a log metric filter and alarm exists for AWS Organizations changes
- 4.15 Ensure AWS Organizations changes are monitored
- 4.15 Ensure AWS Organizations changes are monitored
- 4.2 Ensure a log metric filter and alarm exist for Management Console sign-in without MFA
- 4.2 Ensure a log metric filter and alarm exist for Management Console sign-in without MFA
- 4.2 Ensure a log metric filter and alarm exist for Management Console sign-in without MFA
- 4.2 Ensure management console sign-in without MFA is monitored
- 4.2 Ensure management console sign-in without MFA is monitored
- 4.3 Ensure a log metric filter and alarm exist for usage of 'root' account
- 4.3 Ensure a log metric filter and alarm exist for usage of 'root' account
- 4.3 Ensure a log metric filter and alarm exist for usage of "root" account
- 4.3 Ensure usage of 'root' account is monitored
- 4.3 Ensure usage of 'root' account is monitored
- 4.4 Ensure a log metric filter and alarm exist for IAM policy changes
- 4.4 Ensure a log metric filter and alarm exist for IAM policy changes
- 4.4 Ensure a log metric filter and alarm exist for IAM policy changes
- 4.4 Ensure IAM policy changes are monitored
- 4.4 Ensure IAM policy changes are monitored
- 4.5 Ensure a log metric filter and alarm exist for CloudTrail configuration changes
- 4.5 Ensure a log metric filter and alarm exist for CloudTrail configuration changes
- 4.5 Ensure a log metric filter and alarm exist for CloudTrail configuration changes
- 4.5 Ensure CloudTrail configuration changes are monitored
- 4.5 Ensure CloudTrail configuration changes are monitored
- 4.6 Ensure a log metric filter and alarm exist for AWS Management Console authentication failures
- 4.6 Ensure a log metric filter and alarm exist for AWS Management Console authentication failures
- 4.6 Ensure a log metric filter and alarm exist for AWS Management Console authentication failures
- 4.6 Ensure AWS Management Console authentication failures are monitored
- 4.6 Ensure AWS Management Console authentication failures are monitored
- 4.7 Ensure a log metric filter and alarm exist for disabling or scheduled deletion of customer created CMKs
- 4.7 Ensure a log metric filter and alarm exist for disabling or scheduled deletion of customer created CMKs
- 4.7 Ensure a log metric filter and alarm exist for disabling or scheduled deletion of customer created CMKs
- 4.7 Ensure disabling or scheduled deletion of customer created CMKs is monitored
- 4.7 Ensure disabling or scheduled deletion of customer created CMKs is monitored
- 4.8 Ensure a log metric filter and alarm exist for S3 bucket policy changes
- 4.8 Ensure a log metric filter and alarm exist for S3 bucket policy changes
- 4.8 Ensure a log metric filter and alarm exist for S3 bucket policy changes
- 4.8 Ensure S3 bucket policy changes are monitored
- 4.8 Ensure S3 bucket policy changes are monitored
- 4.9 Ensure a log metric filter and alarm exist for AWS Config configuration changes
- 4.9 Ensure a log metric filter and alarm exist for AWS Config configuration changes
- 4.9 Ensure a log metric filter and alarm exist for AWS Config configuration changes
- 4.9 Ensure AWS Config configuration changes are monitored
- 4.9 Ensure AWS Config configuration changes are monitored
- 5 Ensure CloudTrail trails are integrated with Amazon CloudWatch Logs
- All S3 buckets should log S3 data events in CloudTrail
- At least one CloudTrail trail should be enabled in the AWS account
- At least one enabled trail should be present in a region
- At least one multi-region AWS CloudTrail should be present in an account
- At least one trail should be enabled with security best practices
- CloudTrail multi region trails should be integrated with CloudWatch logs
- CloudTrail trail log file validation should be enabled
- CloudTrail trail logs should be encrypted with KMS CMK
- CloudTrail trail S3 buckets MFA delete should be enabled
- CloudTrail trails should be enabled in all regions
- CloudTrail trails should be integrated with CloudWatch logs
- CloudTrail trails should have insight selectors and logging enabled
- Ensure a log metric filter and alarm exist for AWS Config configuration changes
- Ensure a log metric filter and alarm exist for AWS Management Console authentication failures
- Ensure a log metric filter and alarm exist for AWS Management Console sign-in without MFA
- Ensure a log metric filter and alarm exist for changes to Network Access Control Lists (NACL)
- Ensure a log metric filter and alarm exist for changes to network gateways
- Ensure a log metric filter and alarm exist for CloudTrail configuration changes
- Ensure a log metric filter and alarm exist for disabling or scheduled deletion of customer managed keys
- Ensure a log metric filter and alarm exist for IAM policy changes
- Ensure a log metric filter and alarm exist for route table changes
- Ensure a log metric filter and alarm exist for S3 bucket policy changes
- Ensure a log metric filter and alarm exist for security group changes
- Ensure a log metric filter and alarm exist for unauthorized API calls
- Ensure a log metric filter and alarm exist for usage of 'root' account
- Ensure a log metric filter and alarm exist for VPC changes
- Ensure AWS Organizations changes are monitored
- Ensure S3 bucket access logging is enabled on the CloudTrail S3 bucket
- Ensure that Object-level logging for read events is enabled for S3 bucket
- Ensure that Object-level logging for write events is enabled for S3 bucket
- Ensure the S3 bucket CloudTrail logs to is not publicly accessible
- Lambda functions CloudTrail logging should be enabled
- S3 buckets object logging should be enabled
Schema for aws_cloudtrail_trail
Name | Type | Operators | Description |
---|---|---|---|
_ctx | jsonb | Steampipe context in JSON form. | |
account_id | text | =, !=, ~~, ~~*, !~~, !~~* | The AWS Account ID in which the resource is located. |
advanced_event_selectors | jsonb | Describes the advanced event selectors that are configured for the trail. | |
akas | jsonb | Array of globally unique identifier strings (also known as) for the resource. | |
arn | text | = | The Amazon Resource Name (ARN) of the trail. |
cloudwatch_logs_role_arn | text | Specifies the role for the CloudWatch Logs endpoint to assume to write to a user's log group. | |
event_selectors | jsonb | Describes the event selectors that are configured for the trail. | |
has_custom_event_selectors | boolean | Specifies whether the trail has custom event selectors, or not. | |
has_insight_selectors | boolean | Specifies whether a trail has insight types specified in an InsightSelector list, or not. | |
home_region | text | The region in which the trail was created. | |
include_global_service_events | boolean | Specifies whether to include AWS API calls from AWS global services, or not. | |
insight_selectors | jsonb | A JSON string that contains the insight types you want to log on a trail. | |
is_logging | boolean | Specifies whether the CloudTrail is currently logging AWS API calls, or not. | |
is_multi_region_trail | boolean | Specifies whether the trail exists only in one region or exists in all regions. | |
is_organization_trail | boolean | Specifies whether the trail is an organization trail, or not. | |
kms_key_id | text | Specifies the KMS key ID that encrypts the logs delivered by CloudTrail. | |
latest_cloudwatch_logs_delivery_error | text | Displays any CloudWatch Logs error that CloudTrail encountered when attempting to deliver logs to CloudWatch Logs. | |
latest_cloudwatch_logs_delivery_time | timestamp with time zone | Displays the most recent date and time when CloudTrail delivered logs to CloudWatch Logs. | |
latest_delivery_error | text | Displays any Amazon S3 error that CloudTrail encountered when attempting to deliver log files to the designated bucket. | |
latest_delivery_time | timestamp with time zone | Specifies the date and time that CloudTrail last delivered log files to an account's Amazon S3 bucket. | |
latest_digest_delivery_error | text | Displays any Amazon S3 error that CloudTrail encountered when attempting to deliver a digest file to the designated bucket. | |
latest_digest_delivery_time | timestamp with time zone | Specifies the date and time that CloudTrail last delivered a digest file to an account's Amazon S3 bucket. | |
latest_notification_error | text | Displays any Amazon SNS error that CloudTrail encountered when attempting to send a notification. | |
latest_notification_time | timestamp with time zone | Specifies 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_enabled | boolean | Specifies whether log file validation is enabled, or not. | |
log_group_arn | text | Specifies an Amazon Resource Name (ARN), a unique identifier that represents the log group to which CloudTrail logs will be delivered. | |
name | text | = | The name of the trail. |
partition | text | The AWS partition in which the resource is located (aws, aws-cn, or aws-us-gov). | |
region | text | The AWS Region in which the resource is located. | |
s3_bucket_name | text | Name of the Amazon S3 bucket into which CloudTrail delivers your trail files. | |
s3_key_prefix | text | Specifies the Amazon S3 key prefix that comes after the name of the bucket you have designated for log file delivery. | |
sns_topic_arn | text | Specifies the ARN of the Amazon SNS topic that CloudTrail uses to send notifications when log files are delivered. | |
sp_connection_name | text | =, !=, ~~, ~~*, !~~, !~~* | Steampipe connection name. |
sp_ctx | jsonb | Steampipe context in JSON form. | |
start_logging_time | timestamp with time zone | Specifies the most recent date and time when CloudTrail started recording API calls for an AWS account. | |
stop_logging_time | timestamp with time zone | Specifies the most recent date and time when CloudTrail stopped recording API calls for an AWS account. | |
tags | jsonb | A map of tags for the resource. | |
tags_src | jsonb | A list of tags assigned to the trail. | |
title | text | Title 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