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, descriptionfrom azuredevops_group;
select principal_name, display_name, membership_state, domain, origin, descriptionfrom 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, descriptionfrom azuredevops_groupwhere not membership_state;
select principal_name, display_name, membership_state, domain, origin, descriptionfrom azuredevops_groupwhere 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, descriptionfrom azuredevops_groupwhere memberships = '[]';
select principal_name, display_name, membership_state, domain, origin, descriptionfrom azuredevops_groupwhere 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, descriptionfrom azuredevops_groupwhere 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, descriptionfrom azuredevops_groupwhere 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