Table: azuredevops_team_member - Query Azure DevOps Team Members using SQL

Azure DevOps is a service within Microsoft Azure that supports development teams with version control, reporting, requirements management, project management, automated builds, lab management, testing, and release management capabilities. It provides a rich ecosystem for managing multi-stage, multi-environment, and multi-provider pipelines. Team Members in Azure DevOps are individuals who are part of a particular team, and their details, roles, and permissions can be managed and queried.

Table Usage Guide

The azuredevops_team_member table provides insights into team members within Azure DevOps. As a project manager or team lead, explore member-specific details through this table, including roles, permissions, and associated metadata. Utilize it to manage and understand team structure, roles, and permissions, and to ensure the right individuals have access to the right resources.

Examples

Basic info

Explore which team members hold administrative roles within your Azure DevOps project. This query could be beneficial for management or auditing purposes, providing a quick overview of team structures and roles in the project.

select
  id,
  display_name,
  is_team_admin,
  project_id,
  team_id,
  url
from
  azuredevops_team_member;

select
  id,
  display_name,
  is_team_admin,
  project_id,
  team_id,
  url
from
  azuredevops_team_member;

List team members who are admins

Explore which team members hold admin status in your Azure DevOps setup. This can help manage permissions and roles within your projects.

select
  id,
  display_name,
  is_team_admin,
  project_id,
  team_id,
  url
from
  azuredevops_team_member
where
  is_team_admin;

select
  id,
  display_name,
  is_team_admin,
  project_id,
  team_id,
  url
from
  azuredevops_team_member
where
  is_team_admin;

List deleted team members

Discover the segments that include team members who have been removed from your Azure DevOps team. This can be useful for auditing changes to team composition or identifying potential security issues.

select
  id,
  display_name,
  is_team_admin,
  project_id,
  team_id,
  url
from
  azuredevops_team_member
where
  is_deleted_in_origin;

select
  id,
  display_name,
  is_team_admin,
  project_id,
  team_id,
  url
from
  azuredevops_team_member
where
  is_deleted_in_origin;

List team members of a particular project

Explore which team members are part of a specific project in Azure DevOps. This is useful for project managers who need to quickly understand the composition of their project team, including who the team admin is.

select
  m.id as member_id,
  display_name,
  is_team_admin,
  project_id,
  team_id
from
  azuredevops_team_member as m,
  azuredevops_project as p
where
  m.project_id = p.id
  and p.name = 'private_project';

select
  m.id as member_id,
  display_name,
  is_team_admin,
  project_id,
  team_id
from
  azuredevops_team_member as m,
  azuredevops_project as p
where
  m.project_id = p.id
  and p.name = 'private_project';

List team members of a particular team

Explore which team members belong to a specific team in Azure DevOps, and determine their roles within the team. This can be particularly useful in understanding team composition and identifying team administrators.

select
  m.id as member_id,
  display_name,
  is_team_admin,
  team_id
from
  azuredevops_team_member as m,
  azuredevops_team as t
where
  m.team_id = t.id
  and t.name = 'private_project Team';

select
  m.id as member_id,
  display_name,
  is_team_admin,
  team_id
from
  azuredevops_team_member as m
  join azuredevops_team as t on m.team_id = t.id
where
  t.name = 'private_project Team';

Schema for azuredevops_team_member

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
descriptor	text		The descriptor is the primary way to reference the graph subject while the system is running. This field will uniquely identify the same graph subject across both Accounts and Organizations.
display_name	text		This is the non-unique display name of the graph subject. To change this field, you must alter its value in the source provider.
id	text		The member id.
is_deleted_in_origin	boolean		Check if the member is already deleted.
is_team_admin	boolean		Check if the member is the team admin.
links	jsonb		This field contains zero or more interesting links about the graph subject. These links may be invoked to obtain additional relationships or more detailed information about this graph subject.
organization	text	=, !=, ~~, ~~, !~~, !~~	The name of the organization.
project_id	text		The project id.
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.
team_id	text		The project id.
title	text		Title of the resource.
url	text		This url is the full route to the source resource of this graph subject.

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)" -- azuredevops

You can pass the configuration to the command with the --config argument:

steampipe_export_azuredevops --config '<your_config>' azuredevops_team_member