Setting up CloudTrail is essential to understanding your users' AWS API activity: what happened, by whom and from where. One field in the event logs is source IP address. But that doesn't tell you where in the world those AWS API actions are coming from. In this post we'll show how to use Steampipe with the AWS and ipstack plugins to enrich your CloudTrail events with location information.
As in previous posts we'll use AWS CloudShell for a quick-start experience that leverages your logged-in AWS credentials. Start your own CloudShell in the account with your CloudTrail information and follow along!
Setup
First install Steampipe:
sudo /bin/sh -c "$(curl -fsSL https://raw.githubusercontent.com/turbot/steampipe/main/install.sh)"
Then install the AWS plugin:
steampipe plugin install aws
And then the ipstack plugin:
steampipe plugin install ipstack
Find the CloudTrail CloudWatch Log Group
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. Steampipe has an associated table that reads CloudTrail event data from a CloudWatch log group that is configured to log events from a trail.
Now with Steampipe and the plugins installed, you can run steampipe query and write SQL queries that reference tables provided by the AWS and ipstack plugins. For starters, let's query the aws_cloudtrail_trail table to find the CloudTrail and the related CloudWatch Log Group we'll use in this example:
$ steampipe query
Welcome to Steampipe v0.15.0
For more information, type .help
> select
name,
region,
log_group_arn,
latest_delivery_time
from
aws_cloudtrail_trail
+-----------------------+-----------+----------------------+----------------------------------------------------------+
| name | region | latest_delivery_time | log_group_arn |
+-----------------------+-----------+----------------------+----------------------------------------------------------+
| cloudtrail-for-devto | us-east-1 | 2022-07-06T20:38:09Z | arn:aws:logs:us-east-1:810361751552:cloudtrail-cwg-devto |
+-----------------------+-----------+----------------------+----------------------------------------------------------+
List the IP addresses in the log
Now let's review the source IP addresses in that trail:
select
source_ip_address
from
aws_cloudtrail_trail_event
where
log_group_name = 'cloudtrail-cwg-devto'
and source_ip_address ~ '^\d+\.\d+' -- filter ipv4 addresses
+-------------------+
| source_ip_address |
+-------------------+
| 104.53.216.85 |
| 82.102.17.180 |
| 89.248.165.99 |
| 107.170.20.63 |
| 212.102.58.164 |
+-------------------+
Geolocate the IP addresses
Finally, let's join those addresses with ipstack_ip to find out where they are coming from:
with addrs as (
select
a.source_ip_address::inet
from
aws_cloudtrail_trail_event a
where
a.log_group_name = 'cloudtrail-cwg-devto'
and a.source_ip_address ~ '^\d+\.\d+' -- filter ipv4 addresses
)
select
a.source_ip_address as ip,
i.continent_name,
i.country_name,
i.region_name,
i.city
from
addrs a
join
ipstack_ip i
on
a.source_ip_address = i.ip
+-----------------+----------------+----------------+-------------------+------------+
| ip | continent_name | country_name | region_name | city |
+-----------------+----------------+----------------+-------------------+------------+
| 104.53.216.85 | North America | United States | California | Windsor |
| 82.102.17.180 | Europe | Spain | Madrid | Madrid |
| 89.248.165.99 | Europe | Netherlands | North Holland | Diemen |
| 107.170.20.63 | North America | United States | New York | Manhattan |
| 157.230.162.15 | North America | United States | California | Palo Alto |
| 212.102.58.164 | North America | United States | Illinois | Chicago |
+-----------------+----------------+----------------+-------------------+------------+
More ways to enrich IP addresses in logs
The Net plugin can provide reverse DNS lookups, the AbuseIPDB plugin looks for malicious activity associated with IP addresses, and the Shodan plugin scans for exploitable vulnerabilities. You can use the same technique shown here with these other plugins -- separately or in combination -- to further enrich IP addresses captured in your AWS CloudTrail logs.
Top comments (0)