# 5 key questions to analyze your AWS CloudTrail logs

> Quickly surface potential privilege misuse or elevated access patterns in your environment - no SIEM required.

By Matty Stratton
Published: 2025-03-26

AWS CloudTrail logs give you a record of who did what, when. From a security perspective, analyzing this behavior helps identify patterns of excessive privilege, suspicious access, or unintended activity.

There are countless ways to explore your CloudTrail data, but if you're just trying to ask a few pointed questions without setting up your entire SIEM pipeline first, [Tailpipe](https://tailpipe.io) is a fast way to start. With a few SQL queries, you can quickly dig into your environment and surface potential risks, right from your terminal.

*We will assume you have Tailpipe installed and configured to collect your AWS CloudTrail logs. If not, you can [download Tailpipe](https://tailpipe.io/downloads) and install the [AWS plugin](https://hub.tailpipe.io/plugins/turbot/aws) to get started!*

>For all of these queries, you can paste them right into the Tailpipe interactive query shell by running `tailpipe query` or you can save them as `.sql` files and run them using `tailpipe query <filename.sql>`.

## Who is accessing my AWS account the most?

A good place to start is to list the top 5 actors by frequency, excluding AWS services and service roles (to focus on human users). Seeing high amounts of activity from unexpected users can be a signal for further investigation.

```sql
select
  user_identity.arn as "Actor",
  count(*) as "Logs"
from
  aws_cloudtrail_log
where
  user_identity.type != 'AWSService'
  and user_identity.arn not like '%AWSServiceRole%'
group by
  user_identity.arn
order by
  count(*) desc
limit
  5;
```

Using Tailpipe + sample data from the [flAWS challenge](http://flaws.cloud), we see an output like this:


```
+-----------------------------------------------------------------------------+--------+
| Actor                                                                       | Logs   |
+-----------------------------------------------------------------------------+--------+
| arn:aws:iam::811596193553:user/backup                                       | 915834 |
| arn:aws:iam::811596193553:user/Level6                                       | 905082 |
| arn:aws:sts::811596193553:assumed-role/flaws/i-aa2d3b42e5c6e801a            | 17208  |
| arn:aws:sts::811596193553:assumed-role/SecurityMonkey/secmonkey             | 12354  |
| arn:aws:iam::811596193553:root                                              | 10997  |
+-----------------------------------------------------------------------------+--------+
```

You can review this list to determine if any of these users are performing an unusual number of actions or if any of them are unauthorized.

## Is anyone logging in as the IAM root user?

Logging on as the root user is generally discouraged unless absolutely necessary, as it can lead to security vulnerabilities. Monitoring root user activity is crucial for maintaining a secure AWS environment. Use this query to identify any console logins by the root user:

```sql
select
  tp_timestamp as timestamp,
  tp_source_ip as source_ip
from
  aws_cloudtrail_log
where
  event_source = 'signin.amazonaws.com'
  and event_name = 'ConsoleLogin'
  and user_identity.type = 'Root'
  and error_code is null
order by
  event_time desc;
```

The output from this query looks like this:

```
+---------------------+-----------------+
| timestamp           | source_ip       |
+---------------------+-----------------+
| 2020-10-07 15:52:02 | 3.1.254.37      |
| 2020-10-06 21:10:45 | 3.1.254.37      |
| 2020-08-06 15:57:23 | 252.1.22.60     |
| 2020-05-22 18:09:17 | 252.1.22.60     |
| 2019-08-27 14:34:27 | 228.139.46.252  |
| 2019-08-23 15:47:30 | 228.139.46.252  |
+---------------------+-----------------+
```

We can review the timestamps and source IP addresses to determine if any of these logins are unauthorized or suspicious.

## Has anyone been granted the AdministratorAccess policy?

Assigning the `AdministratorAccess` policy grants **full access** to AWS resources, increasing the risk of unauthorized privilege escalation.  Monitoring for when this policy was attached to an IAM user helps enforce the principle of least privilege and ensures that administrative permissions are only assigned when necessary.

```sql
select
  tp_timestamp as timestamp,
  string_split(event_source, '.') [ 1 ] || ':' || event_name as operation,
  request_parameters ->> 'userName' as resource,
  user_identity.arn as actor,
  tp_source_ip as source_ip
from
  aws_cloudtrail_log
where
  event_source = 'iam.amazonaws.com'
  and event_name = 'AttachUserPolicy'
  and (request_parameters ->> 'policyArn') like 'arn:%:iam::aws:policy/AdministratorAccess'
  and error_code is null
order by
  event_time desc;
```

Let's review the result:

```
+---------------------+----------------------+----------+--------------------------------+-----------------+
| timestamp           | operation            | resource | actor                          | source_ip       |
+---------------------+----------------------+----------+--------------------------------+-----------------+
| 2017-05-16 03:03:56 | iam:AttachUserPolicy | piper    | arn:aws:iam::811596193553:root | 255.253.125.115 |
+---------------------+----------------------+----------+--------------------------------+-----------------+
```

We can see that the user `piper` has been granted very high permissions. If we don't recognize this user as requiring these permissions, this is a definite red flag!

## Has the master password of an RDS DB instance been modified?

Monitoring changes to the master password of Amazon RDS DB instances is essential for maintaining database security. Use this query to track when that password was modified:

```sql
select
  tp_timestamp as timestamp,
  request_parameters ->> 'dBInstanceIdentifier' as db_instance,
  user_identity.arn as actor,
  tp_source_ip as source_ip,
  aws_region as region,
from
  aws_cloudtrail_log
where
  event_source = 'rds.amazonaws.com'
  and event_name = 'ModifyDBInstance'
  and (request_parameters -> 'masterUserPassword') is not null
  and error_code is null
order by
  event_time desc;
```

The output will look similar to the following:

```
+---------------------+-------------+--------------------------------+------------+------------+
| timestamp           | db_instance | actor                          | source_ip  | region     | 
+---------------------+-------------+--------------------------------+------------+------------+
| 2020-10-06 21:10:45 | finance-db  | arn:aws:iam::811596193553:root | 3.1.254.37 | us-east-1  |
+---------------------+-------------+--------------------------------+------------+------------+
```

We can review the timestamps, database instance, and actors to determine if any of these password modifications are unauthorized or suspicious.

## Are any of my S3 buckets publicly accessible?

Public access to S3 buckets can expose sensitive data to unauthorized users, increasing the risk of data breaches. This query helps you detect when public access was granted to an S3 bucket by modifying its policy. Based upon the results, you can take immediate action to restrict public access if necessary.

```sql
select
  tp_timestamp as timestamp,
  string_split(event_source, '.') [ 1 ] || ':' || event_name as operation,
  request_parameters ->> 'bucketName' as bucket_name,
  user_identity.arn as actor,
  tp_source_ip as source_ip
from
  aws_cloudtrail_log
where
  event_source = 's3.amazonaws.com'
  and event_name = 'PutBucketPolicy'
  and (
    json_contains(
      request_parameters -> 'bucketPolicy',
      '{"Principal": "*"}'
    )
    or json_contains(
      request_parameters -> 'bucketPolicy',
      '{"Principal": {"AWS": "*"}}'
    )
  )
  and json_contains(
    request_parameters -> 'bucketPolicy',
    '{"Effect": "Allow"}'
  )
  and error_code is null
order by
  event_time desc;
```

This will result in output like this:

```
+---------------------+--------------------+------------------------------------------------------+--------------------------------+-----------------+
| timestamp           | operation          | bucket_name                                          | actor                          | source_ip       |
+---------------------+--------------------+------------------------------------------------------+--------------------------------+-----------------+
| 2017-02-26 20:07:12 | s3:PutBucketPolicy | theend-c1aad500c62e2a57cf12cebf93b282cf.flaws.cloud  | arn:aws:iam::811596193553:root | 255.253.125.115 |
| 2017-02-26 19:49:02 | s3:PutBucketPolicy | level5-8dbc21dbbd6129208d054061ef063f04.flaws.cloud  | arn:aws:iam::811596193553:root | 255.253.125.115 |
| 2017-02-12 22:19:13 | s3:PutBucketPolicy | b5677c799b465420d8e7b0a6689a0bb0c4afbc9e.flaws.cloud | arn:aws:iam::811596193553:root | 255.253.125.115 |
+---------------------+--------------------+------------------------------------------------------+--------------------------------+-----------------+
```

Review the bucket names, timestamps, and actors to determine if any of these public access grants are unexpected.

## Conclusion

These questions are just the beginning of what you can ask about your AWS CloudTrail logs using Tailpipe. Since they are all just SQL, it's easy to modify these queries for your own specific needs.

As a bonus, these queries are all built into the [Powerpipe AWS CloudTrail Detections mod](https://hub.powerpipe.io/mods/turbot/tailpipe-mod-aws-cloudtrail-log-detections), which you can use to visualize the results in interactive dashboards.

Download [Tailpipe](https://tailpipe.io/downloads) to give this a try! What other questions do you like to ask about your AWS security environment? [Join the conversation](http://turbot.com/community/join) and let us know!