Where are those CloudTrail IP addresses coming from?
Tags: aws, steampipe, opensource, sql
Published: 2022-07-19 19:47:29

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](https://steampipe.io) with the [AWS](https://hub.steampipe.io/plugins/turbot/aws) and [ipstack](https://hub.steampipe.io/plugins/turbot/ipstack) plugins to enrich your CloudTrail events with location information.

As in previous [posts](https://dev.to/bobbot) 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](https://hub.steampipe.io/plugins/turbot/net) plugin can provide reverse DNS lookups, the [AbuseIPDB](https://hub.steampipe.io/plugins/turbot/abuseipdb) plugin looks for malicious activity associated with IP addresses, and the [Shodan](https://steampipe.io/blog/use-shodan-to-test-aws-public-ip) 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.