False positive - redundant cloudtrails

[jchrisfarris]

Describe the bug Thrifty Mod returns incorrect number of Global Trails when Org Trails are in use

FooliOrgTrails-ModifyEventsCloudTrail-xJdAZqCR4abt is one of 18 global trails.

Steampipe version (steampipe -v) SPC v0.18.0

Plugin version (steampipe plugin list) Plugin 0.18.0

To reproduce In an AWS Org with 6 accounts and 3 CloudTrails defined by the organizational parent, the incorrect number of redundant trails is reported.

Create an AWS Org Create a child account Create three trails Note that the number of trails is 6 not three

Expected behavior The thrifty mod should not double count identical cloudtrails that are managed by the AWS Organizations management account.

Additional context May be related to https://github.com/turbot/steampipe-mod-aws-compliance/issues/536

[github-actions[bot]]

'This issue is stale because it has been open 60 days with no activity. Remove stale label or comment or this will be closed in 30 days.'

[github-actions[bot]]

This issue was closed because it has been stalled for 90 days with no activity.

[bigdatasourav]

Hey @jchrisfarris, sorry for the delayed response on this one.

I have made some changes to the query for the control called multiple_global_trails in this pull request. Could you please test it out and share your feedback with us?

[bigdatasourav]

Exiting behaviour -

> with global_trails as (
      select
        count(*) as total
      from
        aws_cloudtrail_trail
      where
        is_multi_region_trail
      )
    select
      arn as resource,
      case
        when total > 1 then 'alarm'
        else 'ok'
      end as status,
      case
        when total > 1 then name || ' is one of ' || total || ' global trails.'
        else name || ' is the only global trail.'
      end as reason
    from
      aws_cloudtrail_trail,
      global_trails
    where
      is_multi_region_trail;
+----------------------------------------------------------------------------+--------+--------------------------------------------------------+
| resource                                                                   | status | reason                                                 |
+----------------------------------------------------------------------------+--------+--------------------------------------------------------+
| arn:aws:cloudtrail:us-east-1:122324354354:trail/turbot-aab-us-east-1-trail | alarm  | turbot-aab-us-east-1-trail is one of 18 global trails. |
| arn:aws:cloudtrail:us-east-1:122324354354:trail/turbot-aab-us-east-1-trail | alarm  | turbot-aab-us-east-1-trail is one of 18 global trails. |
| arn:aws:cloudtrail:us-east-1:122324354354:trail/turbot-aab-us-east-1-trail | alarm  | turbot-aab-us-east-1-trail is one of 18 global trails. |
| arn:aws:cloudtrail:us-east-1:122324354354:trail/turbot-aab-us-east-1-trail | alarm  | turbot-aab-us-east-1-trail is one of 18 global trails. |
| arn:aws:cloudtrail:us-east-1:122324354354:trail/turbot-aab-us-east-1-trail | alarm  | turbot-aab-us-east-1-trail is one of 18 global trails. |
| arn:aws:cloudtrail:us-east-1:122324354354:trail/turbot-aab-us-east-1-trail | alarm  | turbot-aab-us-east-1-trail is one of 18 global trails. |
| arn:aws:cloudtrail:us-east-1:122324354354:trail/turbot-aab-us-east-1-trail | alarm  | turbot-aab-us-east-1-trail is one of 18 global trails. |
| arn:aws:cloudtrail:us-east-1:122324354354:trail/turbot-aab-us-east-1-trail | alarm  | turbot-aab-us-east-1-trail is one of 18 global trails. |
| arn:aws:cloudtrail:us-east-1:122324354354:trail/turbot-aab-us-east-1-trail | alarm  | turbot-aab-us-east-1-trail is one of 18 global trails. |
| arn:aws:cloudtrail:us-east-1:122324354354:trail/turbot-aab-us-east-1-trail | alarm  | turbot-aab-us-east-1-trail is one of 18 global trails. |
| arn:aws:cloudtrail:us-east-1:122324354354:trail/turbot-aab-us-east-1-trail | alarm  | turbot-aab-us-east-1-trail is one of 18 global trails. |
| arn:aws:cloudtrail:us-east-1:122324354354:trail/turbot-aab-us-east-1-trail | alarm  | turbot-aab-us-east-1-trail is one of 18 global trails. |
| arn:aws:cloudtrail:us-east-1:122324354354:trail/turbot-aab-us-east-1-trail | alarm  | turbot-aab-us-east-1-trail is one of 18 global trails. |
| arn:aws:cloudtrail:us-east-1:122324354354:trail/turbot-aab-us-east-1-trail | alarm  | turbot-aab-us-east-1-trail is one of 18 global trails. |
| arn:aws:cloudtrail:us-east-1:122324354354:trail/turbot-aab-us-east-1-trail | alarm  | turbot-aab-us-east-1-trail is one of 18 global trails. |
| arn:aws:cloudtrail:us-east-1:122324354354:trail/turbot-aab-us-east-1-trail | alarm  | turbot-aab-us-east-1-trail is one of 18 global trails. |
| arn:aws:cloudtrail:us-east-1:122324354354:trail/turbot-aab-us-east-1-trail | alarm  | turbot-aab-us-east-1-trail is one of 18 global trails. |
| arn:aws:cloudtrail:us-east-1:122324354354:trail/turbot-aab-us-east-1-trail | alarm  | turbot-aab-us-east-1-trail is one of 18 global trails. |
+----------------------------------------------------------------------------+--------+--------------------------------------------------------+

After query update -

with global_trails as (
      select
        account_id,
        count(*) as total
      from
        aws_cloudtrail_trail
      where
        is_multi_region_trail and region = home_region
      group by
        account_id,
        is_multi_region_trail
    )
    select
      arn as resource,
      case
        when total > 1 then 'alarm'
        else 'ok'
      end as status,
      case
        when total > 1 then name || ' is one of ' || total || ' global trails.'
        else name || ' is the only global trail.'
      end as reason
    from
      aws_cloudtrail_trail,
      global_trails
    where
      is_multi_region_trail
      and region = home_region;
+----------------------------------------------------------------------------+--------+------------------------------------------------------+
| resource                                                                   | status | reason                                               |
+----------------------------------------------------------------------------+--------+------------------------------------------------------+
| arn:aws:cloudtrail:us-east-1:122324354354:trail/turbot-aab-us-east-1-trail | ok     | turbot-aab-us-east-1-trail is the only global trail. |
+----------------------------------------------------------------------------+--------+------------------------------------------------------+

[bigdatasourav]

The above fix works as expected in our tests. We will release it soon. Please feel free to let us know if you face any issues.