Table: aws_cloudtrail_trails

SELECT
  'Ensure S3 bucket access logging is enabled on the CloudTrail S3 bucket'
    AS title,
  t.account_id,
  t.arn AS resource_id,
  CASE
  WHEN b.logging_target_bucket IS NULL OR b.logging_target_prefix IS NULL
  THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  aws_cloudtrail_trails AS t
  INNER JOIN aws_s3_buckets AS b ON t.s3_bucket_name = b.name;

SELECT
  'Ensure CloudTrail is enabled in all regions' AS title,
  aws_cloudtrail_trails.account_id,
  arn AS resource_id,
  CASE
  WHEN is_multi_region_trail = false
  OR (
      is_multi_region_trail = true
      AND (read_write_type != 'All' OR include_management_events = false)
    )
  THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  aws_cloudtrail_trails
  INNER JOIN aws_cloudtrail_trail_event_selectors ON
      aws_cloudtrail_trails.arn = aws_cloudtrail_trail_event_selectors.trail_arn
      AND aws_cloudtrail_trails.region
        = aws_cloudtrail_trail_event_selectors.region
      AND aws_cloudtrail_trails.account_id
        = aws_cloudtrail_trail_event_selectors.account_id;

SELECT
  'CloudTrail trails should be integrated with CloudWatch Logs' AS title,
  account_id,
  arn AS resource_id,
  CASE
  WHEN cloud_watch_logs_log_group_arn IS NULL
  OR (status->>'LatestCloudWatchLogsDeliveryTime')::TIMESTAMP
    < (now() - '1 days'::INTERVAL)
  THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  aws_cloudtrail_trails;

SELECT
  'Ensure CloudTrail log file validation is enabled' AS title,
  account_id,
  arn AS resource_id,
  CASE
  WHEN log_file_validation_enabled = false THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  aws_cloudtrail_trails;

SELECT
  'CloudTrail should have encryption at rest enabled' AS title,
  account_id,
  arn AS resource_id,
  CASE WHEN kms_key_id IS NULL THEN 'fail' ELSE 'pass' END AS status
FROM
  aws_cloudtrail_trails;