[More snippets](https://gist.github.com/farski/a00fe39bf1eff0a9131d685560e620d0)

### Organization Trail Logs

[Organization trails](https://docs.aws.amazon.com/awscloudtrail/latest/userguide/creating-trail-organization.html) are a mechanism for capturing [AWS CloudTrail](https://aws.amazon.com/cloudtrail/) logs across entire [AWS Organizations](https://aws.amazon.com/organizations/). They are similar to standard CloudTrail logs, but have a slightly different file organization structure in S3, which affects their table definitions.

The included SQL and [CloudFormation](https://aws.amazon.com/cloudformation/) YAML snippets create [AWS Glue](https://aws.amazon.com/glue/) tables for organization trail logs, which can be queried using [Amazon Athena](https://aws.amazon.com/athena/). The snippets are intended to be equivalent.

The tables that are created utilize [partition projections](https://docs.aws.amazon.com/athena/latest/ug/partition-projection.html), which allows for efficient, cost-effective querying of the log data in [Amazon S3](https://aws.amazon.com/s3/), without the need to manual create new partitions. Instead, the partitions are created based on the filesnames of the log files. Using various portions of the filenames, daily partitions are available when querying the log data for specific regions and accounts.

When using a snippet to create a table, you must set the `values` of the `region` and `account` partition projections. Each of these expects a comma-separeted list (e.g., `us-east-1,us-west-2` or `111122223333,888899990000`), and determines which regions and accounts within the organization to create partitions for. There is no limit, but Athena recommends keeping each partition to 12 or fewer. This allows you to create a single Glue table for multiple regions and accounts, rather than needing to create individual tables for each.

When querying a table that includes the partitions, the `SELECT` should include a `WHERE` clause that filters data covered by the range of the partition. For example:

```sql
SELECT *
FROM __DATABASE_NAME__.__TABLE_NAME__ 
WHERE "date" >= date_format(current_date - interval '7' day, '%Y/%m/%d')
  AND region IN ('us-east-1,us-west-2')
  AND account IN ('111122223333')
```

The `date`, `region`, and `account` fields in the `WHERE` clause match the projections configured on the table. The name of these projections is arbitrary.

Note that when records from CloudTrail logs include fields similar to the projections, such as `awsRegion` or `eventTime`, you must still use the specific projection fields to utilize partitioning. You can, for example, use borh the `date` and `eventTime` fields in a `WHERE` clause to access certain partitions, and also filter more specifically by the event time.