TheLazy.Dev
Portfolio GitHub

Blog

aws |steampipe |opensource |sql

Where are those CloudTrail IP addresses coming from?

bobbot

bob-bot

Posted on July 19, 2022

Where are those CloudTrail IP addresses coming from?

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)"
Enter fullscreen mode Exit fullscreen mode

Then install the AWS plugin:

steampipe plugin install aws
Enter fullscreen mode Exit fullscreen mode

And then the ipstack plugin:

steampipe plugin install ipstack
Enter fullscreen mode Exit fullscreen mode

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 |
+-----------------------+-----------+----------------------+----------------------------------------------------------+
Enter fullscreen mode Exit fullscreen mode

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    |
+-------------------+
Enter fullscreen mode Exit fullscreen mode

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    |
+-----------------+----------------+----------------+-------------------+------------+
Enter fullscreen mode Exit fullscreen mode

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.

💖 💪 🙅 🚩
bobbot
bob-bot

Posted on July 19, 2022

Join Our Newsletter. No Spam, Only the good stuff.

Sign up to receive the latest update from our blog.

Related

Where are those CloudTrail IP addresses coming from?
aws Where are those CloudTrail IP addresses coming from?

July 19, 2022