DEV Community

AWS Community Builders profile image bob-bot
bob-bot for AWS Community Builders

Posted on

5 1 1

Query AWS CloudTrail Logs Locally with SQL

#aws #security #opensource #tailpipe

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

Next, install the AWS plugin:

tailpipe plugin install aws
Enter fullscreen mode Exit fullscreen mode

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

Collecting Logs

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

tailpipe collect aws_cloudtrail_log
Enter fullscreen mode Exit fullscreen mode

Want more history? Specify a start date:

tailpipe collect aws_cloudtrail_log --from 2024-01-01
Enter fullscreen mode Exit fullscreen mode

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

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

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

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

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.

Datadog
 Promoted

Image of Datadog

How to Diagram Your Cloud Architecture

Cloud architecture diagrams provide critical visibility into the resources in your environment and how they’re connected. In our latest eBook, AWS Solution Architects Jason Mimick and James Wenzel walk through best practices on how to build effective and professional diagrams.

Download the Free eBook

Top comments (2)

Collapse
 
thoroc profile image
thoroc • • Edited

Need to investigate but I am getting an Error:

Error: failed to get partition config: partition not found: aws_cloudtrail_log

according to hub.tailpipe.io/plugins/turbot/aws the config is under ~/.steampipe/config/aws.spc

Collapse
 
bobbot profile image
bob-bot •

@thoroc did you set up your partition yet?

For example, if you're going to collect and query AWS CloudTrail logs: hub.tailpipe.io/plugins/turbot/aws...

First, follow the getting started guide to configure your credentials profile in ~/.tailpipe/config/aws.tpc: hub.tailpipe.io/plugins/turbot/aws...
(You can use AWS CLI profiles, environment variables, or even Steampipe config, whatever works best for you.)

Then define the partition to point Tailpipe to your log source. For example:

connection "aws" "logging_account" {
  profile = "my-logging-account"
}

partition "aws_cloudtrail_log" "my_logs" {
  source "aws_s3_bucket" {
    connection = connection.aws.logging_account
    bucket     = "aws-cloudtrail-logs-bucket"
  }
}
Enter fullscreen mode Exit fullscreen mode

Once that’s in place, you should be good to go!

profile
AWS Community Builders

Create a simple OTP system with AWS Serverless cover image

Create a simple OTP system with AWS Serverless

Implement a One Time Password (OTP) system with AWS Serverless services including Lambda, API Gateway, DynamoDB, Simple Email Service (SES), and Amplify Web Hosting using VueJS for the frontend.

Read full post

👋 Kindness is contagious

Explore a trove of insights in this engaging article, celebrated within our welcoming DEV Community. Developers from every background are invited to join and enhance our shared wisdom.

A genuine "thank you" can truly uplift someone’s day. Feel free to express your gratitude in the comments below!

On DEV, our collective exchange of knowledge lightens the road ahead and strengthens our community bonds. Found something valuable here? A small thank you to the author can make a big difference.

Okay