Table: aiven_account_team - Query Aiven Account Teams using SQL
An Aiven Account Team is a group of users within the Aiven platform that have access to certain projects. The team structure allows for easy management of permissions and roles within the platform. Each team can have multiple users and projects associated with it.
Table Usage Guide
The aiven_account_team
table provides insights into the team structure within Aiven. As a DevOps engineer or a team lead, you can explore team-specific details through this table, including member details, their roles, and associated projects. Use this table to manage and audit team permissions, roles and project associations effectively.
Examples
Basic info
Explore which team was created or updated at what time within your Aiven account. This can help you track changes and manage your teams effectively.
select id, name, account_id, create_time, update_timefrom aiven_account_team;
select id, name, account_id, create_time, update_timefrom aiven_account_team;
List members of the dev team
Identify the members of your development team, including their user IDs and real names, to gain insights into team composition. This can be particularly useful in scenarios where understanding team structure and membership is important for project management or resource allocation.
select m ->> 'user_id' as user_id, m ->> 'real_name' as user_name, aiven_account_team.id as team_id, name as team_namefrom aiven_account_team, jsonb_array_elements(members) as mwhere name = 'dev';
select json_extract(m.value, '$.user_id') as user_id, json_extract(m.value, '$.real_name') as user_name, aiven_account_team.id as team_id, name as team_namefrom aiven_account_team, json_each(members) as mwhere name = 'dev';
List teams which are not associated with any project
Determine the teams within an account that are not associated with any projects. This can be useful for identifying unused resources or potential misconfigurations.
select id as team_id, name as team_name, account_id, create_timefrom aiven_account_teamwhere projects = '[]';
select id as team_id, name as team_name, account_id, create_timefrom aiven_account_teamwhere projects = '[]';
List the admins of each account
Discover the segments that have administrative access in each account. This could be useful for auditing purposes, ensuring that only authorized individuals have administrative privileges.
select a.name as account_name, m ->> 'user_id' as user_id, m ->> 'real_name' as user_name, t.id as team_id, t.name as team_namefrom aiven_account as a, aiven_account_team as t, jsonb_array_elements(members) as mwhere t.account_id = a.id and t.id = a.owner_team_id;
select a.name as account_name, json_extract(m.value, '$.user_id') as user_id, json_extract(m.value, '$.real_name') as user_name, t.id as team_id, t.name as team_namefrom aiven_account as a, aiven_account_team as t, json_each(a.members) as mwhere t.account_id = a.id and t.id = a.owner_team_id;
List members who have read_only access to projects
This query is useful for identifying team members who have only read access to certain projects. This can be beneficial in managing user permissions and ensuring that sensitive projects are adequately protected.
select m ->> 'user_id' as user_id, m ->> 'real_name' as user_name, aiven_account_team.id as team_id, name as team_name, p ->> 'project_name' as project_name, p ->> 'team_type' as team_typefrom aiven_account_team, jsonb_array_elements(members) as m, jsonb_array_elements(projects) as pwhere p ->> 'team_type' = 'read_only';
select json_extract(m.value, '$.user_id') as user_id, json_extract(m.value, '$.real_name') as user_name, aiven_account_team.id as team_id, name as team_name, json_extract(p.value, '$.project_name') as project_name, json_extract(p.value, '$.team_type') as team_typefrom aiven_account_team, json_each(members) as m, json_each(projects) as pwhere json_extract(p.value, '$.team_type') = 'read_only';
Schema for aiven_account_team
Name | Type | Operators | Description |
---|---|---|---|
_ctx | jsonb | Steampipe context in JSON form, e.g. connection_name. | |
account_id | text | = | The account ID. |
create_time | timestamp with time zone | The create time of the team. | |
id | text | = | The team ID. |
members | jsonb | The members of the team. | |
name | text | The team name. | |
projects | jsonb | The projects associated to the team. | |
update_time | timestamp with time zone | The update time of the team. |
Export
This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.
You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh
script:
/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- aiven
You can pass the configuration to the command with the --config
argument:
steampipe_export_aiven --config '<your_config>' aiven_account_team