steampipe plugin install aws

Table: aws_cloudtrail_trail_event - Query AWS CloudTrail Events using SQL

AWS CloudTrail Events are records of activity within your AWS environment. This service captures all API calls for your account, including calls made via the AWS Management Console, SDKs, and CLI. It provides a history of AWS API calls for your account, including API calls made via the AWS Management Console, AWS SDKs, command line tools, and higher-level AWS services.

Table Usage Guide

The aws_cloudtrail_trail_event table in Steampipe provides you with information about each trail event within AWS CloudTrail. This table allows you, as a DevOps engineer, to query event-specific details, including event time, event name, resources involved, and more. You can utilize this table to gather insights on trail events, such as event source, user identity, and request parameters. The schema outlines the various attributes of the CloudTrail event for you, including the event ID, event version, read only, and associated tags.

Important Notes

  • You must specify log_group_name in a where clause in order to use this table.
  • For improved performance, it is advised that you use the optional qual timestamp to limit the result set to a specific time period.
  • This table supports optional quals. Queries with optional quals are optimised to use CloudWatch filters. Optional quals are supported for the following columns:
    • access_key_id
    • aws_region (region of the event, useful in case of multi-region trails)
    • error_code
    • event_category
    • event_id
    • event_name
    • event_source
    • filter
    • log_stream_name
    • region
    • source_ip_address
    • timestamp
    • username

Examples

List events that occurred over the last five minutes

This query is useful for gaining insights into recent activity within your AWS environment. It provides a quick overview of the events that have taken place in the last five minutes, which can be particularly useful for immediate incident response or real-time monitoring.

select
event_name,
event_source,
event_time,
user_type,
username,
user_identifier,
jsonb_pretty(response_elements) as response_elements
from
aws_cloudtrail_trail_event
where
log_group_name = 'aws-cloudtrail-log-group-name'
and timestamp >= now() - interval '5 minutes';
select
event_name,
event_source,
event_time,
user_type,
username,
user_identifier,
json(response_elements) as response_elements
from
aws_cloudtrail_trail_event
where
log_group_name = 'aws-cloudtrail-log-group-name'
and timestamp >= datetime('now', '-5 minutes');

List ordered events that occurred between five to ten minutes ago

Explore the sequence of events that occurred within a specific time frame in the recent past. This can be useful for auditing activities, identifying anomalies, or tracking user behaviour within a given period.

select
event_name,
event_source,
event_time,
user_type,
username,
user_identifier,
jsonb_pretty(response_elements) as response_elements
from
aws_cloudtrail_trail_event
where
log_group_name = 'aws-cloudtrail-log-group-name'
and timestamp between (now() - interval '10 minutes')
and (now() - interval '5 minutes')
order by
event_time asc;
select
event_name,
event_source,
event_time,
user_type,
username,
user_identifier,
json(response_elements) as response_elements
from
aws_cloudtrail_trail_event
where
log_group_name = 'aws-cloudtrail-log-group-name'
and timestamp between (datetime('now', '-10 minutes'))
and (datetime('now', '-5 minutes'))
order by
event_time asc;

List all action events, i.e., not ReadOnly that occurred over the last hour

Explore which action events have occurred in the last hour on AWS Cloudtrail. This is useful for identifying recent activities that have potentially altered your system.

select
event_name,
event_source,
event_time,
user_type,
username,
user_identifier,
jsonb_pretty(response_elements) as response_elements
from
aws_cloudtrail_trail_event
where
log_group_name = 'aws-cloudtrail-log-group-name'
and not read_only
and timestamp >= now() - interval '1 hour'
order by
event_time asc;
select
event_name,
event_source,
event_time,
user_type,
username,
user_identifier,
json(response_elements) as response_elements
from
aws_cloudtrail_trail_event
where
log_group_name = 'aws-cloudtrail-log-group-name'
and not read_only
and timestamp >= datetime('now', '-1 hours')
order by
event_time asc;

List events for a specific service (IAM) that occurred over the last hour

This query allows users to monitor recent activity for a specific service, in this case, AWS's Identity and Access Management (IAM). It is particularly useful for security audits, as it provides a chronological overview of events, including who initiated them and what actions were taken, over the last hour.

select
event_name,
event_source,
event_time,
user_type,
user_identifier,
jsonb_pretty(request_parameters) as request_parameters,
jsonb_pretty(response_elements) as response_elements
from
aws_cloudtrail_trail_event
where
log_group_name = 'aws-cloudtrail-log-group-name'
and event_source = 'iam.amazonaws.com'
and timestamp >= now() - interval '1 hour'
order by
event_time asc;
select
event_name,
event_source,
event_time,
user_type,
user_identifier,
json(request_parameters) as request_parameters,
json(response_elements) as response_elements
from
aws_cloudtrail_trail_event
where
log_group_name = 'aws-cloudtrail-log-group-name'
and event_source = 'iam.amazonaws.com'
and timestamp >= datetime('now', '-1 hour')
order by
event_time asc;

List events for an IAM user (steampipe) that occurred over the last hour

Explore which events have occurred on your system over the past hour that are associated with a specific IAM user. This can help in monitoring user activity and identifying potential security concerns.

select
event_name,
event_source,
event_time,
user_type,
username,
user_identifier,
jsonb_pretty(request_parameters) as request_parameters,
jsonb_pretty(response_elements) as response_elements
from
aws_cloudtrail_trail_event
where
log_group_name = 'aws-cloudtrail-log-group-name'
and username = 'steampipe'
and timestamp >= now() - interval '1 hour'
order by
event_time asc;
select
event_name,
event_source,
event_time,
user_type,
username,
user_identifier,
request_parameters,
response_elements
from
aws_cloudtrail_trail_event
where
log_group_name = 'aws-cloudtrail-log-group-name'
and username = 'steampipe'
and timestamp >= datetime('now', '-1 hour')
order by
event_time asc;

List events performed by IAM users that occurred over the last hour

Determine the activities undertaken by IAM users within the past hour in your AWS environment. This can help in understanding user behaviors, monitoring security, and auditing purposes.

select
event_name,
event_source,
event_time,
user_type,
username,
user_identifier,
jsonb_pretty(request_parameters) as request_parameters,
jsonb_pretty(response_elements) as response_elements
from
aws_cloudtrail_trail_event
where
log_group_name = 'aws-cloudtrail-log-group-name'
and user_type = 'IAMUser'
and timestamp >= now() - interval '1 hour'
order by
event_time asc;
select
event_name,
event_source,
event_time,
user_type,
username,
user_identifier,
request_parameters,
response_elements
from
aws_cloudtrail_trail_event
where
log_group_name = 'aws-cloudtrail-log-group-name'
and user_type = 'IAMUser'
and timestamp >= datetime('now', '-1 hours')
order by
event_time asc;

List events performed with an assumed role that occurred over the last hour

Explore which actions were carried out using an assumed role in the past hour. This is useful in monitoring and auditing for any unusual or unauthorized activities.

select
event_name,
event_source,
event_time,
user_type,
username,
user_identifier,
jsonb_pretty(request_parameters) as request_parameters,
jsonb_pretty(response_elements) as response_elements
from
aws_cloudtrail_trail_event
where
log_group_name = 'aws-cloudtrail-log-group-name'
and user_type = 'AssumedRole'
and timestamp >= now() - interval '1 hour'
order by
event_time asc;
select
event_name,
event_source,
event_time,
user_type,
username,
user_identifier,
request_parameters,
response_elements
from
aws_cloudtrail_trail_event
where
log_group_name = 'aws-cloudtrail-log-group-name'
and user_type = 'AssumedRole'
and timestamp >= datetime('now', '-1 hours')
order by
event_time asc;

List events that were not successfully executed that occurred over the last hour

Identify instances where events were not executed successfully in the past hour. This is useful for monitoring system performance and quickly addressing any operational issues.

select
event_name,
event_source,
event_time,
error_code,
error_message,
user_type,
username,
user_identifier,
jsonb_pretty(request_parameters) as request_parameters,
jsonb_pretty(response_elements) as response_elements
from
aws_cloudtrail_trail_event
where
log_group_name = 'aws-cloudtrail-log-group-name'
and error_code is not null
and timestamp >= now() - interval '1 hour'
order by
event_time asc;
select
event_name,
event_source,
event_time,
error_code,
error_message,
user_type,
username,
user_identifier,
request_parameters,
response_elements
from
aws_cloudtrail_trail_event
where
log_group_name = 'aws-cloudtrail-log-group-name'
and error_code is not null
and timestamp >= datetime('now', '-1 hours')
order by
event_time asc;

Filter examples

For more information on CloudWatch log filters, please refer to Filter Pattern Syntax.

List events originating from a specific IP address range that occurred over the last hour

Explore which events have originated from a specific IP address range in the last hour. This is useful for understanding and monitoring recent activity and potential security incidents related to that IP range.

select
event_name,
event_source,
event_time,
error_code,
error_message,
user_type,
username,
user_identifier,
jsonb_pretty(request_parameters) as request_parameters,
jsonb_pretty(response_elements) as response_elements
from
aws_cloudtrail_trail_event
where
log_group_name = 'aws-cloudtrail-log-group-name'
and filter = '{ $.sourceIPAddress = 203.189.* }'
and timestamp >= now() - interval '1 hour'
order by
event_time asc;
select
event_name,
event_source,
event_time,
error_code,
error_message,
user_type,
username,
user_identifier,
request_parameters,
response_elements
from
aws_cloudtrail_trail_event
where
log_group_name = 'aws-cloudtrail-log-group-name'
and json_extract(filter, '$.sourceIPAddress') like '203.189.%'
and timestamp >= datetime('now', '-1 hour')
order by
event_time asc;

Schema for aws_cloudtrail_trail_event

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
access_key_idtext=The AWS access key ID that was used to sign the request. If the request was made with temporary security credentials, this is the access key ID of the temporary credentials.
account_idtext=, !=, ~~, ~~*, !~~, !~~*The AWS Account ID in which the resource is located.
additional_event_datajsonbAdditional data about the event that was not part of the request or response.
aws_regiontext=The AWS region that the request was made to, such as us-east-2.
cloudtrail_eventjsonbThe CloudTrail event in the json format.
error_codetext=The AWS service error if the request returns an error.
error_messagetextIf the request returns an error, the description of the error.
event_categorytext=Shows the event category that is used in LookupEvents calls.
event_idtext=The ID of the event.
event_nametext=The name of the event returned.
event_sourcetext=The AWS service that the request was made to.
event_timetimestamp with time zoneThe date and time the request was made, in coordinated universal time (UTC).
event_typetextIdentifies the type of event that generated the event record.
event_versiontextThe version of the log event format.
filtertext=The cloudwatch filter pattern for the search.
log_group_nametext=The name of the log group to which this event belongs.
log_stream_nametext=The name of the log stream to which this event belongs.
partitiontextThe AWS partition in which the resource is located (aws, aws-cn, or aws-us-gov).
read_onlyboolean=Information about whether the event is a write event or a read event.
recipient_account_idtextRepresents the account ID that received this event.
regiontext=The AWS Region in which the resource is located.
request_idtextThe value that identifies the request.
request_parametersjsonbThe parameters, if any, that were sent with the request.
resourcesjsonbA list of resources referenced by the event returned.
response_elementsjsonbThe response element for actions that make changes (create, update, or delete actions).
shared_event_idtextGUID generated by CloudTrail to uniquely identify CloudTrail events from the same AWS action that is sent to different AWS accounts.
source_ip_addresstext=The IP address that the request was made from.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
timestamptimestamp with time zone>, >=, =, <, <=The time when the event occurred.
timestamp_msbigintThe time when the event occurred.
tls_detailsjsonbShows information about the Transport Layer Security (TLS) version, cipher suites, and the FQDN of the client-provided host name of a service API call.
user_agenttextThe agent through which the request was made, such as the AWS Management Console, an AWS service, the AWS SDKs or the AWS CLI.
user_identifiertextThe name/arn of user/role that made the api call.
user_identityjsonbInformation about the user that made the request.
user_typetext=The name of the event returned.
usernametext=The user name of the user that made the api request.
vpc_endpoint_idtextIdentifies the VPC endpoint in which requests were made from a VPC to another AWS service, such as Amazon S3.

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_event