Organization trails are a mechanism for capturing AWS CloudTrail logs across entire AWS 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 YAML snippets create AWS Glue tables for organization trail logs, which can be queried using Amazon Athena. The snippets are intended to be equivalent.
The tables that are created utilize partition projections, which allows for efficient, cost-effective querying of the log data in Amazon 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:
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.