Skip to content

Conversation

@bbernays
Copy link
Collaborator

@bbernays bbernays commented Jun 15, 2023

Summary

PG SQL Query to generate entire org structure:

WITH RECURSIVE STRUCTURE AS
	(SELECT DISTINCT ON (ARN) ID,
			NULL AS PARENT_ID,
			'ROOT' AS TYPE,
			0 AS DEPTH
		FROM AWS_ORGANIZATIONS_ROOTS
		UNION SELECT CHILD.ID,
			CHILD.PARENT_ID,
			'ORGANIZATIONAL_UNIT' AS TYPE,
			DEPTH + 1 AS DEPTH
		FROM AWS_ORGANIZATIONS_ORGANIZATIONAL_UNIT_PARENTS CHILD
		JOIN STRUCTURE G ON G.ID = CHILD.PARENT_ID)
SELECT STRUCTURE.*,
	ACCOUNT_DETAIL.ARN,
	ACCOUNT_DETAIL.NAME,
	ACCOUNT_DETAIL.STATUS
FROM STRUCTURE
LEFT JOIN AWS_ORGANIZATIONS_ACCOUNT_PARENTS AS ACCOUNTS ON ACCOUNTS.PARENT_ID = STRUCTURE.ID
AND ACCOUNTS.TYPE = STRUCTURE.TYPE
JOIN AWS_ORGANIZATIONS_ACCOUNTS AS ACCOUNT_DETAIL ON ACCOUNT_DETAIL._CQ_ID = ACCOUNTS._CQ_PARENT_ID
ORDER BY DEPTH,
	STRUCTURE.ID

@github-actions
Copy link

This PR has the following changes to source plugin(s) tables:

  • Table aws_organizations_account_parents was added
  • Table aws_organizations_organizational_unit_parents was added
  • ⚠️ BREAKING CHANGE: Table aws_organizations_organizational_units: column account_id removed from table
  • ⚠️ BREAKING CHANGE: Table aws_organizations_organizational_units: column added with name request_account_id (PK) and type utf8

Copy link
Contributor

@jsonpr jsonpr left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Looks good!

@bbernays bbernays added the automerge Automatically merge once required checks pass label Jun 15, 2023
@kodiakhq kodiakhq bot merged commit f66995b into cloudquery:main Jun 15, 2023
kodiakhq bot pushed a commit that referenced this pull request Jun 20, 2023
🤖 I have created a release *beep* *boop*
---


## [19.0.0](plugins-source-aws-v18.4.0...plugins-source-aws-v19.0.0) (2023-06-20)


### ⚠ BREAKING CHANGES

* **aws:** Move `web_acl_arn` to its own table ([#11421](#11421))
* **aws:** Add support For fully describing the organizational hierarchy ([#11633](#11633))
* **aws:** Support all policy types in Identity Center ([#10985](#10985))

### This Release has the Following Changes to Tables
- Table `aws_cloudfront_functions` was added
- Table `aws_cloudfront_origin_access_identities` was added
- Table `aws_cloudfront_origin_request_policies` was added
- Table `aws_cloudfront_response_headers_policies` was added
- Table `aws_cloudtrail_channels` was added
- Table `aws_cloudtrail_imports` was added
- Table `aws_ec2_capacity_reservations` was added
- Table `aws_elbv2_load_balancer_web_acls` was added
- Table `aws_elbv2_load_balancers`: column `web_acl_arn` removed from table (:warning: breaking)
- Table `aws_organizations_account_parents` was added
- Table `aws_organizations_organizational_unit_parents` was added
- Table `aws_organizations_organizational_units`: column `account_id` removed from table (:warning: breaking)
- Table `aws_organizations_organizational_units`: column added with name `request_account_id (PK)` and type `utf8` (:warning: breaking)
- Table `aws_s3_buckets`: column added with name `policy_status` and type `json`
- Table `aws_ssoadmin_account_assignments` was removed (:warning: breaking)
- Table `aws_ssoadmin_instances`: primary key constraint added to column `instance_arn` (:warning: breaking)
- Table `aws_ssoadmin_instances`: primary key constraint removed from column `_cq_id` (:warning: breaking)
- Table `aws_ssoadmin_permission_set_account_assignments` was added
- Table `aws_ssoadmin_permission_set_customer_managed_policies` was added
- Table `aws_ssoadmin_permission_set_inline_policies` was added
- Table `aws_ssoadmin_permission_set_managed_policies` was added
- Table `aws_ssoadmin_permission_set_permissions_boundaries` was added
- Table `aws_ssoadmin_permission_sets`: column `inline_policy` removed from table (:warning: breaking)
- Table `aws_ssoadmin_permission_sets`: column added with name `instance_arn (PK)` and type `utf8` (:warning: breaking)
- Table `aws_ssoadmin_permission_sets`: primary key constraint added to column `permission_set_arn` (:warning: breaking)
- Table `aws_ssoadmin_permission_sets`: primary key constraint removed from column `_cq_id` (:warning: breaking)

### Features

* **aws-services:** Support newly added regions ([#11673](#11673)) ([8c0ab9d](8c0ab9d))
* **aws:** Add PolicyStatus to WrappedBucket ([#11657](#11657)) ([55d966a](55d966a))
* **aws:** Add support for Cloudfront Functions ([#11669](#11669)) ([102067a](102067a))
* **aws:** Add Support for Cloudtrail Channels ([#11670](#11670)) ([0dc13de](0dc13de))
* **aws:** Add Support for Cloudtrail Imports ([#11671](#11671)) ([c908289](c908289))
* **aws:** Add support for EC2 Capacity Reservations ([#11666](#11666)) ([70d6052](70d6052))
* **aws:** Add support For fully describing the organizational hierarchy ([#11633](#11633)) ([f66995b](f66995b))
* **aws:** Add Support for more Cloudfront Resources ([#11668](#11668)) ([52e6ad9](52e6ad9))
* **aws:** Support all policy types in Identity Center ([#10985](#10985)) ([a8ab255](a8ab255))


### Bug Fixes

* **aws:** Move `web_acl_arn` to its own table ([#11421](#11421)) ([cdda682](cdda682))
* **deps:** Update github.com/cloudquery/arrow/go/v13 digest to 1e68c51 ([#11637](#11637)) ([46043bc](46043bc))
* **deps:** Update github.com/cloudquery/arrow/go/v13 digest to 43638cb ([#11672](#11672)) ([3c60bbb](3c60bbb))
* **deps:** Update github.com/cloudquery/arrow/go/v13 digest to b0832be ([#11651](#11651)) ([71e8c29](71e8c29))
* **deps:** Update module github.com/aws/aws-sdk-go-v2 to v1.18.1 ([#11652](#11652)) ([4230b52](4230b52))
* **deps:** Update module github.com/aws/aws-sdk-go-v2/config to v1.18.27 ([#11653](#11653)) ([4b45408](4b45408))
* **deps:** Update module github.com/cloudquery/plugin-pb-go to v1.1.0 ([#11665](#11665)) ([d8947c9](d8947c9))
* Use ServiceAccountRegion multiplexer for aws_availability_zones ([#11686](#11686)) ([7f4788f](7f4788f))

---
This PR was generated with [Release Please](https://github.com/googleapis/release-please). See [documentation](https://github.com/googleapis/release-please#release-please).
@bbernays bbernays deleted the add-ou-structure-v2 branch July 6, 2023 11:09
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Labels

automerge Automatically merge once required checks pass

Projects

None yet

Development

Successfully merging this pull request may close these issues.

Support Syncing entire AWS Org Hierarchy

3 participants