Table: azuredevops_group - Query Azure DevOps Groups using SQL

Azure DevOps Groups is a feature in Azure DevOps that allows for the management of user and group permissions across the platform. It enables administrators to control access to resources such as repositories, pipelines, and boards. Azure DevOps Groups simplifies the process of managing permissions by allowing users to be added to or removed from multiple resources at once.

Table Usage Guide

The azuredevops_group table provides insights into groups within Azure DevOps. As a DevOps engineer, explore group-specific details through this table, including group membership, permissions, and associated metadata. Utilize it to manage user permissions across multiple resources effectively and ensure the right level of access for each user.

Examples

Basic info

Explore which Azure DevOps groups are active within your domain. This is useful for understanding user access and permissions within your organization.

select
  principal_name,
  display_name,
  membership_state,
  domain,
  origin,
  description
from
  azuredevops_group;

select
  principal_name,
  display_name,
  membership_state,
  domain,
  origin,
  description
from
  azuredevops_group;

List inactive groups

Identify groups within your Azure DevOps that are currently inactive. This could be useful for optimizing resource use, ensuring security, or maintaining an organized workspace.

select
  principal_name,
  display_name,
  membership_state,
  domain,
  origin,
  description
from
  azuredevops_group
where
  not membership_state;

select
  principal_name,
  display_name,
  membership_state,
  domain,
  origin,
  description
from
  azuredevops_group
where
  not membership_state;

List empty groups

Discover the segments that are classified as empty groups in the Azure DevOps platform. This can be useful to identify and clean up unused or redundant groups, enhancing the efficiency of your resource management.

select
  principal_name,
  display_name,
  membership_state,
  domain,
  origin,
  description
from
  azuredevops_group
where
  memberships = '[]';

select
  principal_name,
  display_name,
  membership_state,
  domain,
  origin,
  description
from
  azuredevops_group
where
  memberships = '[]';

Get parent group detail of a particular group

Determine the parent group details associated with a specific group in Azure DevOps. This can be useful for understanding group hierarchies and membership states, especially when managing user access and permissions.

select
  principal_name,
  display_name,
  membership_state,
  domain,
  origin,
  description
from
  azuredevops_group
where
  descriptor in (
    select
      m ->> 'containerDescriptor'
    from
      azuredevops_group,
      jsonb_array_elements(memberships) as m
    where
      display_name = 'Build Administrators'
  );

select
  principal_name,
  display_name,
  membership_state,
  domain,
  origin,
  description
from
  azuredevops_group
where
  descriptor in (
    select
      json_extract(m.value, '$.containerDescriptor')
    from
      azuredevops_group,
      json_each(memberships) as m
    where
      display_name = 'Build Administrators'
  );

Schema for azuredevops_group

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
description	text		A short phrase to help human readers disambiguate groups with similar names.
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.
domain	text		This represents the name of the container of origin for a graph member. (For MSA this is Windows Live ID, for AD the name of the domain, for AAD the tenantID of the directory, for VSTS groups the ScopeId, etc).
legacy_descriptor	text		The legacy descriptor is here in case you need to access old version IMS using identity descriptor.
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.
mail_address	text		The email address of record for a given graph member. This may be different than the principal name.
membership_state	boolean		When true, the membership is active.
memberships	jsonb		Get all the memberships where this descriptor is a member in the relationship.
organization	text	=, !=, ~~, ~~, !~~, !~~	The name of the organization.
origin	text		The type of source provider for the origin identifier (ex:AD, AAD, MSA).
origin_id	text		The unique identifier from the system of origin. Typically a sid, object id or Guid. Linking and unlinking operations can cause this value to change for a user because the user is not backed by a different provider and has a different unique id in the new provider.
principal_name	text		This is the PrincipalName of this graph member from the source provider. The source provider may change this field over time and it is not guaranteed to be immutable for the life of the graph member by VSTS.
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.
subject_kind	text		This field identifies the type of the graph subject (ex: Group, Scope, User).
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_group