Query AWS CloudTrail Logs Locally with SQL

When investigating AWS activity or analyzing usage patterns, being able to quickly query your CloudTrail logs can make all the difference. Whether you're responding to a security event, optimizing costs, or just trying to understand how your team uses AWS, you need fast, iterative access to your log data.

Enter Tailpipe: a lightweight, open-source tool that lets you analyze logs right from your terminal using SQL. It runs entirely on your local machine, using DuckDB to process millions of records in seconds. This means you can pull down your logs locally and start querying immediately - perfect for rapid investigations, offline analysis, or when you need to quickly test different queries.

Just pipe your CloudTrail logs to your local machine, and you're ready to start querying with familiar SQL. No infrastructure to set up, no services to configure - just straightforward log analysis when you need it.

Getting Started

First, let's install Tailpipe. You have two options:

# Using Homebrew
brew install turbot/tap/tailpipe

# Or using the install script
sudo /bin/sh -c "$(curl -fsSL https://tailpipe.io/install/tailpipe.sh)"

Next, install the AWS plugin:

tailpipe plugin install aws

Configuration

Create a simple configuration file to connect Tailpipe to your AWS CloudTrail logs. Create a tailpipe.hcl file:

connection "aws" "prod" {
  profile = "log-admin"  # Your AWS profile name
}

partition "aws_cloudtrail_log" "prod" {
  source "aws_s3_bucket" {
    connection = connection.aws.prod
    bucket     = "aws-cloudtrail-logs-12345"  # Your CloudTrail bucket
  }
}

Collecting Logs

Now you're ready to collect some logs. Start with the last 7 days:

tailpipe collect aws_cloudtrail_log

Want more history? Specify a start date:

tailpipe collect aws_cloudtrail_log --from 2024-01-01

Understanding Your AWS API Usage

Let's start with a fundamental question: which AWS services and APIs are most frequently used in your environment? This query helps you understand your AWS usage patterns:

select
  event_source,
  event_name,
  count(*) as event_count
from
  aws_cloudtrail_log
group by
  event_source,
  event_name
order by
  event_count desc
limit 10;

Running this query on a typical AWS environment might give you output like this:

+-------------------+---------------------------+-------------+
| event_source      | event_name                | event_count |
+-------------------+---------------------------+-------------+
| ec2.amazonaws.com | RunInstances              | 1225268     |
| ec2.amazonaws.com | DescribeSnapshots         | 101158      |
| sts.amazonaws.com | AssumeRole                | 78380       |
| s3.amazonaws.com  | GetBucketAcl              | 19095       |
| ec2.amazonaws.com | DescribeInstances         | 18366       |
| sts.amazonaws.com | GetCallerIdentity         | 16512       |
| iam.amazonaws.com | GetPolicyVersion          | 14737       |
| s3.amazonaws.com  | ListBuckets               | 13206       |
| ec2.amazonaws.com | DescribeSpotPriceHistory  | 10714       |
| ec2.amazonaws.com | DescribeSnapshotAttribute | 9107        |
+-------------------+---------------------------+-------------+

Looking at this output, you'll notice that many of these operations are read-only actions like Describe*, Get*, and List*. While this gives us a good overview of API usage, we might be more interested in write operations that actually change our AWS environment.

Let's modify our query to focus on these change-making operations:

select
  event_source,
  event_name,
  count(*) as event_count
from
  aws_cloudtrail_log
where
  not read_only
group by
  event_source,
  event_name
order by
  event_count desc
limit 10;

Now we're seeing a very different picture!

+----------------------+---------------------------+-------------+
| event_source         | event_name                | event_count |
+----------------------+---------------------------+-------------+
| ec2.amazonaws.com    | RunInstances              | 1225268     |
| sts.amazonaws.com    | AssumeRole                | 78380       |
| ec2.amazonaws.com    | CreateTags                | 8456        |
| ec2.amazonaws.com    | CreateVolume              | 5231        |
| s3.amazonaws.com     | PutObject                 | 4521        |
| iam.amazonaws.com    | CreateRole                | 3242        |
| ec2.amazonaws.com    | ModifyInstanceAttribute   | 2890        |
| rds.amazonaws.com    | CreateDBInstance          | 2456        |
| lambda.amazonaws.com | CreateFunction            | 2123        |
| eks.amazonaws.com    | CreateCluster             | 1890        |
+----------------------+---------------------------+-------------+

This filtered view highlights the actual resource creation and modifications in your AWS environment, quickly showing you where new infrastructure is being deployed and potential areas for optimization.

You can modify these queries further to focus on specific time periods, services, or add additional filters based on your needs.

Learn More

There's much more you can do with Tailpipe. The Tailpipe Hub has 110+ ready-to-use CloudTrail queries along with other log sources you can collect & analyze. As part of the Turbot open source ecosystem, you can also use the Powerpipe AWS CloudTrail Logs Detections mod to visualize pre-built dashboards and detections of your AWS activity.

Author Of article : bob-bot Read full article