Add table aws_organizations_organizational_unit

[ajoga]

In AWS Organizations, the accounts can be arranged in Organizationnal units: https://docs.aws.amazon.com/organizations/latest/userguide/orgs_getting-started_concepts.html

This ticket suggests the creation of a table to enumerate these OUs.

API call to list OUs: https://docs.aws.amazon.com/organizations/latest/APIReference/API_ListOrganizationalUnitsForParent.html

[github-actions]

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.

[cbruno10]

Hey @ajoga , we're currently exploring how we can not only add this table, but future AWS Organizations tables as well. The tricky parts are around hierarchy and making sure we make it easy to look at parents/children from a particular spot in the hierarchy.

@ParthaI Can provide more updates as he gets closer to a working OU table.

[github-actions]

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]

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

[ParthaI]

Hello @ajoga, Apologies for the extended delay in responding to this issue. During this time, I've been actively exploring the AWS Organizations service APIs to enhance the table design in alignment with the feedback provided in both this PR and the associated issue.

We have yet to finalize the table design. Nevertheless, I have structured the tables based on the insights and discussions from the comments within the associated issues and PRs. While the exact utility for users and how they can effectively utilize these tables remains to be seen, our aim is to align them with the community's needs and requirements.

I have pushed the code changes in the branch issue-1674. It would be great if you could try it in the PR branch and share your feedback with us if the changes/design meets your requirements or if you need anything else from us.

Steps for testing out the code changes:

• git clone https://github.com/turbot/steampipe-plugin-aws.git
• cd steampipe-plugin-aws
• git checkout issue-1674
• Run the command make
• Run the query

Thank you!

---

Insights on the table design:

Available tables for the service AWS Organizations

• aws_organizations_root
• aws_organizations_account
• aws_organizations_organizational_unit
• aws_organizations_policy
• aws_organizations_policy_target

Understanding the Functionality of the aws_organizations_root Table

• The table will return the result about the root account if the account is a member of an organization.
• You must use the credentials of an account that belongs to an organization.
• The table will return an empty row if the account isn't a member of an organization instead of AWSOrganizationsNotInUseException.

Understanding the Functionality of the aws_organizations_account Table

• By default lists all the accounts in the organization.
• I have introduced an optional quals parent_id, we can use this column to minimize the result set.
• The parent_id value can be the ID of the Root Account or the ID of the Organizational Unit.
• If the parent_id has specified in where clause while quering the table, the table should lists the accounts in an organization that are contained by the specified target root or organizational unit (OU). If you specify the root, you get a list of all the accounts that aren't in any OU. If you specify an OU, you get a list of all the accounts in only that OU and not in any child OUs.

Understanding the Functionality of the aws_organizations_organizational_unit Table

• The table will return the Organizational Units for the root account if parent_id is not specified in the query parameter.
• If parent_id is specified in the query parameter then it will return the Organizational Units for the given parent.

The table aws_organizations_account and aws_organizations_organizational_unit can be join using the parent_id column.

Note: This operation can be called only from the organization's management account or by a member account that is a delegated administrator for an AWS service.

[ajoga]

Hello @ParthaI, thank you for the follow-up even after so long. I unfortunately dropped using Steampipe since I opened this ticket, and I can't invest time in trying this out anymore. Kudos for detailing so much the way to use the tables as the APIs can be... surprising.

I have one remark:

Understanding the Functionality of the aws_organizations_account Table

(...)
* If the parent_id has specified in where clause while quering the table, the table should lists the accounts in an organization that are contained by the specified target root or organizational unit (OU). If you specify the root, you get a list of all the accounts that aren't in any OU. If you specify an OU, you get a list of all the accounts in only that OU and not in any child OUs.

In terms of use case, not recursively listing accounts seems odd, nested OUs are very common[1] and I myself need to list accounts all the way down a hierarchy of OUs. Maybe an example could be given in the doc on how to list recursively accounts in a given OU, if possible?

[1] https://aws.amazon.com/blogs/mt/best-practices-for-organizational-units-with-aws-organizations/