turbot/azuredevops
steampipe plugin install azuredevops

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

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form, e.g. connection_name.
descriptiontextA short phrase to help human readers disambiguate groups with similar names.
descriptortext=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_nametextThis is the non-unique display name of the graph subject. To change this field, you must alter its value in the source provider.
domaintextThis 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_descriptortextThe legacy descriptor is here in case you need to access old version IMS using identity descriptor.
linksjsonbThis 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_addresstextThe email address of record for a given graph member. This may be different than the principal name.
membership_statebooleanWhen true, the membership is active.
membershipsjsonbGet all the memberships where this descriptor is a member in the relationship.
origintextThe type of source provider for the origin identifier (ex:AD, AAD, MSA).
origin_idtextThe 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_nametextThis 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.
subject_kindtextThis field identifies the type of the graph subject (ex: Group, Scope, User).
titletextTitle of the resource.
urltextThis 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