# Introducing Tailpipe: select * from logs;

> Lightweight open source SIEM for instant log insights. Analyze millions of events in seconds, right from your terminal

By Turbot Team
Published: 2025-01-30


Tailpipe brings easy, powerful, and consistent log collection and analysis right into your terminal. It provides:

- **Unified log collection**. Tailpipe [plugins](https://hub.tailpipe.io/plugins) — for AWS, GCP, Azure, and more — all work the same way. No more juggling a hodgepodge of collectors.

- **SQL-based analysis**: Query diverse log sources using SQL, the data access standard that's been rocking it for decades.

Because Tailpipe runs locally, you keep control of your data without needing to provision complex infrastructure. The collection process maps log records to schemas with common fields like IP address, so you can correlate log events from sources within one cloud platform (e.g. AWS CloudTrail and ALB logs) and across other supported sources. 

Tailpipe's engine, DuckDB, slices through hundreds of millions of log records in seconds. Tailpipe also works with Powerpipe, enabling a [new breed of mods](https://hub.powerpipe.io?engines=tailpipe) that run MITRE ATT&CK-aligned detections and provide interactive visualization of your log data.

Let's dive in and see how it works!

## Collect AWS CloudTrail logs

Start by [downloading and installing](https://tailpipe.io/downloads) Tailpipe:

```
brew install turbot/tap/tailpipe
```

or

```
sudo /bin/sh -c "$(curl -fsSL https://tailpipe.io/install/tailpipe.sh)"
```

Then install the [AWS plugin](https://hub.tailpipe.io/plugins/turbot/aws/):

```
tailpipe plugin install aws
```

Now you can collect your first batch of log records. This command collects logs for the most recent 7 days:

```
tailpipe collect aws_cloudtrail_log
```

Want more? Specify a start date:

```
tailpipe collect aws_cloudtrail_log --from 2024-01-1
```

`aws_cloudtrail_log` is the name of a [table](https://hub.tailpipe.io/plugins/turbot/aws/tables/aws_cloudtrail_log) defined and populated by the AWS plugin. Of course there's configuration behind the scenes, but not much! It can be as simple as:

```hcl
connection "aws" "prod" {
  profile = "log-admin"
}

partition "aws_cloudtrail_log" "prod" {
  source "aws_s3_bucket" {
    connection = connection.aws.prod
    bucket     = "aws-cloudtrail-logs-12345"
  }
```
The [connection](/docs/manage/connection) authenticates Tailpipe using an AWS profile. The `partition` defines a [source](/docs/manage/source) which names the S3 bucket where the logs accumulate. 

And that's it! With this minimal setup you can collect days, months, or years of log data, right up to today. If you run the same command tomorrow, Tailpipe will pick up where it left off and collect one more day of log data. 

## Query CloudTrail logs

The first thing you'll want to know is how many records you collected. 

```sql
tailpipe query "select count(*) from aws_cloudtrail_log"
```

After that, the sky's the limit. The [schema](https://tailpipe.io/plugins/turbot/aws/tables/aws_cloudtrail_log#schema) for the table will give you a sense of what's possible, but there's no need to start from a blank slate. The plugin documentation provides common examples:

```sql
-- List the top 10 events and how many times they were called.

 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;
```

```bash
+-------------------+---------------------------+-------------+
| 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        |
+-------------------+---------------------------+-------------+
```

Using [basic SQL idioms](https://tailpipe.io/docs/sql) you can explore your logs, find patterns, and extract insights. 

## From ad-hoc analysis to formal detection

While ad-hoc SQL queries are a handy way to explore and investigate, you'd also like a more systematic way to identify patterns that indicate security threats, compliance violations, or operational issues. A **detection** is a formalized query that looks for such patterns. The new Tailpipe-powered [Powerpipe mods](https://hub.powerpipe.io?engines=tailpipe) provide libraries of detections packaged as benchmarks aligned with the MITRE ATT&CK framework.

To see the [AWS CloudTrail Log Detections](https://hub-powerpipe-io/mods/turbot/aws_cloudtrail_log_detections) mod in action, first [install Powerpipe](https://powerpipe.io/downloads) if you haven't already. Then:

```
mkdir cloudtrail_logs
cd cloudtrail_log
powerpipe mod install github.com/turbot/tailpipe-mod-aws-detections
powerpipe server
```

Visit http://localhost:9033 in a browser and select *MITRE ATT&CK v16.1 for CloudTrail Logs*. The view defaults to the most recent 7 days, if you've collected more you can use the **Custom** date range widget to expand the view. 

![](/images/blog/cloudtrail-detections-by-mitre.png)

The detections are powered by a set of [well-documented queries](https://hub.powerpipe.io/mods/turbot/tailpipe-mod-aws-cloudtrail-log-detections/queries) that report one or more log rows when they detect named patterns. Overwhelmed by too many rows? Use Powerpipe's new [inline filters](https://powerpipe.io/blog/row-filters-date-inputs-and-more) to refine the view by iteratively discarding benign items in order to focus on a potentially malicious subset.

## Roll your own detections

Like [Steampipe](https://steampipe.io), Tailpipe is an open source engine that powers an ecosystem of mods, also open source, that share a common style: HCL to configure [controls](https://powerpipe.io/docs/powerpipe-hcl/control) and [detections](https://powerpipe.io/docs/powerpipe-hcl/detection), SQL to flow data into them. The patterns encoded in Tailpipe mods are simple and easy to emulate. Here's the query for the `IAM Root User Console Login` detection shown in the screenshot.

```sql
select
  *
from
  aws_cloudtrail_log
where
  event_source = 'signin.amazonaws.com'
  and event_name = 'ConsoleLogin'
  and user_identity.type = 'Root'
  and json_extract_string(response_elements, '$.ConsoleLogin') = 'Success'
```

Hunting for patterns like this requires no special SQL expertise. If you know what you're looking for, it's trivial to write a query to find it. You can package such queries as detections and combine those into [benchmarks](https://powerpipe.io/docs/powerpipe-hcl/benchmark) that extend existing mods or implement new ones.

## See it in Action

Watch this demo of both the Tailpipe CLI and Powerpipe Detection Mods:

<div className="flex justify-center">
<iframe 
    class="youtube-video" 
    src="https://www.youtube-nocookie.com/embed/IR9MK1DMvW4"
    frameBorder="0" 
    allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share"
    allowFullScreen
    title="Introducing Tailpipe: select * from logs;"
>
</iframe>
</div>

## Getting started

To get started with Tailpipe: [download](/downloads) the tool, follow the [tutorial](/docs), and explore the [plugins](https://hub.tailpipe.io#plugins) and [mods](https://hub.powerpipe.io?engines=tailpipe). Then start collecting, querying, and visualizing your logs and [let us know](https://turbot.com/community/join) how it goes!