steampipe plugin install aws

Table: aws_cloudtrail_query - Query AWS CloudTrail using SQL

The AWS CloudTrail is a service that enables governance, compliance, operational auditing, and risk auditing of your AWS account. It allows you to log, continuously monitor, and retain account activity related to actions across your AWS infrastructure. With CloudTrail, you can conduct security analysis, track changes to your AWS resources, and aid in compliance reporting.

Table Usage Guide

The aws_cloudtrail_query table in Steampipe provides you with information about CloudTrail events within AWS. This table allows you, as a DevOps engineer, to query event-specific details, including the identity of the API caller, the time of the API call, the source IP address of the API caller, and the request parameters made. You can utilize this table to gather insights on account activity, such as actions taken through the AWS Management Console, AWS SDKs, command line tools, and other AWS services. The schema outlines the various attributes of the CloudTrail event for you, including the event name, event time, event source, and associated tags.

Examples

Basic info

Gain insights into the status and efficiency of your AWS CloudTrail queries, including the number of events matched and scanned, to optimize resource usage and improve query performance. This can be particularly useful for troubleshooting and auditing purposes.

select
query_id,
event_data_store_arn,
query_status,
query_status,
creation_time,
events_matched,
events_scanned
from
aws_cloudtrail_query;
select
query_id,
event_data_store_arn,
query_status,
query_status,
creation_time,
events_matched,
events_scanned
from
aws_cloudtrail_query;

List queries that are failed

Determine the areas in which AWS CloudTrail queries have failed to gain insights into potential issues or bottlenecks within your system.

select
query_id,
event_data_store_arn,
query_status,
creation_time,
query_string,
execution_time_in_millis
from
aws_cloudtrail_query
where
query_status = 'FAILED';
select
query_id,
event_data_store_arn,
query_status,
creation_time,
query_string,
execution_time_in_millis
from
aws_cloudtrail_query
where
query_status = 'FAILED';

Get event data store details for the queries

Explore the relationship between specific queries and their corresponding event data stores in AWS CloudTrail, providing insights into the status, multi-region capability, and termination protection of these data stores.

select
q.query_id as query_id,
q.event_data_store_arn as event_data_store_arn,
s.name as event_data_store_name,
s.status as event_data_store_status,
s.multi_region_enabled as multi_region_enabled,
s.termination_protection_enabled as termination_protection_enabled,
s.updated_timestamp as event_data_store_updated_timestamp
from
aws_cloudtrail_query as q,
aws_cloudtrail_event_data_store as s
where
s.arn = q.event_data_store_arn;
select
q.query_id as query_id,
q.event_data_store_arn as event_data_store_arn,
s.name as event_data_store_name,
s.status as event_data_store_status,
s.multi_region_enabled as multi_region_enabled,
s.termination_protection_enabled as termination_protection_enabled,
s.updated_timestamp as event_data_store_updated_timestamp
from
aws_cloudtrail_query as q,
aws_cloudtrail_event_data_store as s
where
s.arn = q.event_data_store_arn;

List queries created within the last 3 days

Identify AWS CloudTrail queries that have been created within the last three days, allowing you to monitor recent query activity and understand their execution times.

select
query_id,
event_data_store_arn,
query_status,
creation_time,
query_string,
execution_time_in_millis
from
aws_cloudtrail_query
where
creation_time <= now() - interval '3' day;
select
query_id,
event_data_store_arn,
query_status,
creation_time,
query_string,
execution_time_in_millis
from
aws_cloudtrail_query
where
creation_time <= datetime('now', '-3 day');

Schema for aws_cloudtrail_query

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
account_idtext=, !=, ~~, ~~*, !~~, !~~*The AWS Account ID in which the resource is located.
bytes_scannedbigintGets metadata about a query, including the number of events that were matched, the total number of events scanned, the query run time in milliseconds, and the query's creation time.
creation_timetimestamp with time zone=, <=, <, >, >=The creation time of the query.
delivery_s3_uritextThe URI for the S3 bucket where CloudTrail delivered query results, if applicable.
delivery_statustextThe delivery status.
error_messagetextThe error message returned if a query failed.
event_data_store_arntext=The ID of the event data store.
events_matchedbigintThe number of events that matched a query.
events_scannedbigintThe number of events that the query scanned in the event data store.
execution_time_in_millisbigintThe query's run time, in milliseconds.
partitiontextThe AWS partition in which the resource is located (aws, aws-cn, or aws-us-gov).
query_idtext=The ID of the query.
query_statustext=The status of a query. Values for QueryStatus include QUEUED, RUNNING, FINISHED, FAILED, TIMED_OUT, or CANCELLED.
query_stringtextThe SQL code of a query.
regiontextThe AWS Region in which the resource is located.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
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_query