steampipe plugin install awssteampipe plugin install aws

Table: aws_cloudtrail_trail_event

AWS CloudTrail is an AWS service that helps you enable governance, compliance, and operational and risk auditing of your AWS account. Actions taken by a user, role, or an AWS service are recorded as events in CloudTrail. These events can be sent to a CloudWatch log group to allow for easy monitoring.

This table reads CloudTrail event data from a CloudWatch log group that is configured to log events from a trail.

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

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';

List ordered events that occurred between five to ten minutes ago

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;

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

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;

List events for a specific service (IAM) that occurred 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;

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

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;

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

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;

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

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;

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

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;

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

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;

.inspect aws_cloudtrail_trail_event

CloudTrail events from cloudwatch service.

Name	Type	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
access_key_id	text	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_id	text	The AWS Account ID in which the resource is located.
additional_event_data	jsonb	Additional data about the event that was not part of the request or response.
aws_region	text	The AWS region that the request was made to, such as us-east-2.
cloudtrail_event	jsonb	The CloudTrail event in the json format.
error_code	text	The AWS service error if the request returns an error.
error_message	text	If the request returns an error, the description of the error.
event_category	text	Shows the event category that is used in LookupEvents calls.
event_id	text	The ID of the event.
event_name	text	The name of the event returned.
event_source	text	The AWS service that the request was made to.
event_time	timestamp with time zone	The date and time the request was made, in coordinated universal time (UTC).
event_type	text	Identifies the type of event that generated the event record.
event_version	text	The version of the log event format.
filter	text	The cloudwatch filter pattern for the search.
log_group_name	text	The name of the log group to which this event belongs.
log_stream_name	text	The name of the log stream to which this event belongs.
partition	text	The AWS partition in which the resource is located (aws, aws-cn, or aws-us-gov).
read_only	boolean	Information about whether the event is a write event or a read event.
recipient_account_id	text	Represents the account ID that received this event.
region	text	The AWS Region in which the resource is located.
request_id	text	The value that identifies the request.
request_parameters	jsonb	The parameters, if any, that were sent with the request.
resources	jsonb	A list of resources referenced by the event returned.
response_elements	jsonb	The response element for actions that make changes (create, update, or delete actions).
shared_event_id	text	GUID generated by CloudTrail to uniquely identify CloudTrail events from the same AWS action that is sent to different AWS accounts.
source_ip_address	text	The IP address that the request was made from.
timestamp	timestamp with time zone	The time when the event occurred.
timestamp_ms	bigint	The time when the event occurred.
tls_details	jsonb	Shows 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_agent	text	The agent through which the request was made, such as the AWS Management Console, an AWS service, the AWS SDKs or the AWS CLI.
user_identifier	text	The name/arn of user/role that made the api call.
user_identity	jsonb	Information about the user that made the request.
user_type	text	The name of the event returned.
username	text	The user name of the user that made the api request.
vpc_endpoint_id	text	Identifies the VPC endpoint in which requests were made from a VPC to another AWS service, such as Amazon S3.

turbot/aws