# AWS account attribution by product teams and inspector findings grouping

**URL:** <https://community.cloudquery.io/t/aws-account-attribution-by-product-teams-and-inspector-findings-grouping/296>\
**Category:** CloudQuery Plugins\
**Created:** [March 15, 2024, 12:35pm UTC](https://community.cloudquery.io/t/aws-account-attribution-by-product-teams-and-inspector-findings-grouping/296 "2024-03-15T12:35:28Z")\
**Posts on this page:** 14\
**Page:** 1

<div class="post-metadata">

**Author:** ![helping-ladybird](https://avatars.discourse-cdn.com/v4/letter/h/46a35a/32.png) [@helping-ladybird](https://community.cloudquery.io/u/helping-ladybird)\
**Post date:** [March 15, 2024, 12:35pm UTC](https://community.cloudquery.io/t/aws-account-attribution-by-product-teams-and-inspector-findings-grouping/296/1 "2024-03-15T12:35:28Z")

</div>

AWS, Attributing Accounts to Product Teams

I want to attribute the data from AWS such that I could look up/group etc. the rows based on the name of the team that manages the AWS account.

For example, I would like to get the Inspector findings that belong to all accounts that a specific team owns.

This could be done with some sort of relations and transformations, I’m sure, but has anyone done this already?

(More info in the thread)

---

<div class="post-metadata">

**Author:** ![ben](https://sea1.discourse-cdn.com/flex001/user_avatar/community.cloudquery.io/ben/32/88_2.png) [@ben](https://community.cloudquery.io/u/ben)\
**Post date:** [March 15, 2024, 12:46pm UTC](https://community.cloudquery.io/t/aws-account-attribution-by-product-teams-and-inspector-findings-grouping/296/2 "2024-03-15T12:46:04Z")

</div>

In your organization, how do you currently connect an AWS account to a team?

---

<div class="post-metadata">

**Author:** ![helping-ladybird](https://avatars.discourse-cdn.com/v4/letter/h/46a35a/32.png) [@helping-ladybird](https://community.cloudquery.io/u/helping-ladybird)\
**Post date:** [March 15, 2024, 12:48pm UTC](https://community.cloudquery.io/t/aws-account-attribution-by-product-teams-and-inspector-findings-grouping/296/3 "2024-03-15T12:48:42Z")

</div>

We have the owner/team of the account documented in Confluence.

My current attempt:

I created 3 tables in my database:

```python
from sqlalchemy import Column, ForeignKey, Integer, String, Table, PrimaryKeyConstraint
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship

Base = declarative_base()

# Define the association table for the many-to-many relationship
team_accounts = Table('team_accounts', Base.metadata,
    Column('team_id', Integer, ForeignKey('product_teams.team_id')),
    Column('account_id', String(12), ForeignKey('aws_accounts.account_id')),
    PrimaryKeyConstraint('team_id', 'account_id')
)

class AwsAccount(Base):
    __tablename__ = 'aws_accounts'

    account_id = Column(String(12), primary_key=True) # AWS Account ID
    friendly_name = Column(String(100)) # AWS account alias
    team_id = Column(Integer, ForeignKey('product_teams.team_id'))
    env = Column(String(100)) # Is the account used as Production, Dev, or Test 
    team = relationship("ProductTeam", back_populates="accounts")

class ProductTeam(Base):
    __tablename__ = 'product_teams'

    team_id = Column(Integer, primary_key=True)
    team_name = Column(String(100), unique=True) # Name of product team
    unit = Column(String(100)) # Business unit
    accounts = relationship("AwsAccount", secondary=team_accounts, back_populates="team")

```

Then I tried to learn how to use dbt to attribute the findings to teams:

```sql
{% set aws_accounts_table = 'aws_accounts' %}
{% set product_teams_table = 'product_teams' %}
{% set findings_table = 'aws_inspector2_findings' %}

SELECT
    a.account_id,
    a.friendly_name AS account_friendly_name,
    t.team_id,
    t.team_name,
    f.arn AS finding_id,
    f.severity,
    f.description,
    f.last_observed_at,
    f.first_observed_at,
    f.type,
    f.exploit_available,
    f.title,
    f.status
FROM
    {{ aws_accounts_table }} a
JOIN
    {{ product_teams_table }} t ON a.team_id = t.team_id
JOIN
    {{ findings_table }} f ON a.account_id = f.aws_account_id

```

This did technically work, but I feel like there is a better way to do it that doesn’t require me to basically copy each AWS table. I assume there is a way to do this so that I don’t have to write the same dbt query with different column names for each either. Additionally, this doesn’t let me do a query like:

```sql
SELECT aws_inspector2_findings FROM product_teams WHERE team_name = 'team1'

```

---

<div class="post-metadata">

**Author:** ![pretty-wolf](https://avatars.discourse-cdn.com/v4/letter/p/7feea3/32.png) [@pretty-wolf](https://community.cloudquery.io/u/pretty-wolf)\
**Post date:** [March 15, 2024, 12:58pm UTC](https://community.cloudquery.io/t/aws-account-attribution-by-product-teams-and-inspector-findings-grouping/296/4 "2024-03-15T12:58:35Z")

</div>

I use AWS account tags for this personally, but I realize that’s not for everyone.

[Here is the documentation on AWS tagging](https://docs.aws.amazon.com/organizations/latest/userguide/orgs_tagging.html).

We use that to know where to send fallback notifications on resources under an account without a direct link to a specific team/group.

---

<div class="post-metadata">

**Author:** ![helping-ladybird](https://avatars.discourse-cdn.com/v4/letter/h/46a35a/32.png) [@helping-ladybird](https://community.cloudquery.io/u/helping-ladybird)\
**Post date:** [March 15, 2024, 1:00pm UTC](https://community.cloudquery.io/t/aws-account-attribution-by-product-teams-and-inspector-findings-grouping/296/5 "2024-03-15T13:00:21Z")

</div>

yeah, i have been trying to get that properly done, but for now i have to do with what i have 😅

---

<div class="post-metadata">

**Author:** ![pretty-wolf](https://avatars.discourse-cdn.com/v4/letter/p/7feea3/32.png) [@pretty-wolf](https://community.cloudquery.io/u/pretty-wolf)\
**Post date:** [March 15, 2024, 1:00pm UTC](https://community.cloudquery.io/t/aws-account-attribution-by-product-teams-and-inspector-findings-grouping/296/6 "2024-03-15T13:00:31Z")

</div>

ha I know that feeling

---

<div class="post-metadata">

**Author:** ![ben](https://sea1.discourse-cdn.com/flex001/user_avatar/community.cloudquery.io/ben/32/88_2.png) [@ben](https://community.cloudquery.io/u/ben)\
**Post date:** [March 15, 2024, 1:00pm UTC](https://community.cloudquery.io/t/aws-account-attribution-by-product-teams-and-inspector-findings-grouping/296/7 "2024-03-15T13:00:54Z")

</div>

A few things:

1. I would switch the order of the joins so that the base table is the `findings_table` rather than `aws_accounts_table`:

2. I would investigate using a view. That way you have a single table where each row has all of the information you are interested in. Depending on how much you are using DBT, you can materialize the query you have and it will appear like a table.

---

<div class="post-metadata">

**Author:** ![helping-ladybird](https://avatars.discourse-cdn.com/v4/letter/h/46a35a/32.png) [@helping-ladybird](https://community.cloudquery.io/u/helping-ladybird)\
**Post date:** [March 15, 2024, 1:31pm UTC](https://community.cloudquery.io/t/aws-account-attribution-by-product-teams-and-inspector-findings-grouping/296/8 "2024-03-15T13:31:46Z")

</div>

By the way, do you happen to know how I can extract a value from a column that contains JSON?  
(The SecurityHub findings severity column contains a JSON string where one of the keys is “Label” that I want to extract into a new column for easier querying.)

---

<div class="post-metadata">

**Author:** ![ben](https://sea1.discourse-cdn.com/flex001/user_avatar/community.cloudquery.io/ben/32/88_2.png) [@ben](https://community.cloudquery.io/u/ben)\
**Post date:** [March 15, 2024, 1:38pm UTC](https://community.cloudquery.io/t/aws-account-attribution-by-product-teams-and-inspector-findings-grouping/296/9 "2024-03-15T13:38:50Z")

</div>

You are using Postgres, right?

---

<div class="post-metadata">

**Author:** ![ben](https://sea1.discourse-cdn.com/flex001/user_avatar/community.cloudquery.io/ben/32/88_2.png) [@ben](https://community.cloudquery.io/u/ben)\
**Post date:** [March 15, 2024, 1:53pm UTC](https://community.cloudquery.io/t/aws-account-attribution-by-product-teams-and-inspector-findings-grouping/296/10 "2024-03-15T13:53:32Z")

</div>

Postgres has built-in functionality that allows you to extract a value from a JSON object: [PostgreSQL JSON Functions](https://www.postgresql.org/docs/9.3/functions-json.html)

---

<div class="post-metadata">

**Author:** ![helping-ladybird](https://avatars.discourse-cdn.com/v4/letter/h/46a35a/32.png) [@helping-ladybird](https://community.cloudquery.io/u/helping-ladybird)\
**Post date:** [March 15, 2024, 4:04pm UTC](https://community.cloudquery.io/t/aws-account-attribution-by-product-teams-and-inspector-findings-grouping/296/11 "2024-03-15T16:04:27Z")

</div>

What is your setup for that like? I found out that at least for some of the accounts, the tagging has actually been done already.

---

<div class="post-metadata">

**Author:** ![ben](https://sea1.discourse-cdn.com/flex001/user_avatar/community.cloudquery.io/ben/32/88_2.png) [@ben](https://community.cloudquery.io/u/ben)\
**Post date:** [March 15, 2024, 4:09pm UTC](https://community.cloudquery.io/t/aws-account-attribution-by-product-teams-and-inspector-findings-grouping/296/12 "2024-03-15T16:09:11Z")

</div>

Tagging is a rabbit hole that typically requires organizational buy-in and automated tooling to enforce. Many companies have successfully adopted [CloudCustodian](https://github.com/cloud-custodian/cloud-custodian) for something like this.

---

<div class="post-metadata">

**Author:** ![helping-ladybird](https://avatars.discourse-cdn.com/v4/letter/h/46a35a/32.png) [@helping-ladybird](https://community.cloudquery.io/u/helping-ladybird)\
**Post date:** [March 15, 2024, 4:20pm UTC](https://community.cloudquery.io/t/aws-account-attribution-by-product-teams-and-inspector-findings-grouping/296/13 "2024-03-15T16:20:26Z")

</div>

I’m thinking more in the CQ and dbt, etc. end, as it does seem that the tags are set up, but I previously had no access to them.

---

<div class="post-metadata">

**Author:** ![pretty-wolf](https://avatars.discourse-cdn.com/v4/letter/p/7feea3/32.png) [@pretty-wolf](https://community.cloudquery.io/u/pretty-wolf)\
**Post date:** [March 15, 2024, 4:23pm UTC](https://community.cloudquery.io/t/aws-account-attribution-by-product-teams-and-inspector-findings-grouping/296/14 "2024-03-15T16:23:14Z")

</div>

This goes over it pretty well from when I was at Zapier, but as @ben hinted, it did very much rely on Cloud-Custodian. [https://marcyoung.us/post/kubecon\_2021/](https://marcyoung.us/post/kubecon_2021/)

It’s not for everyone and scales differently, but that’s how we did it.