Table: azuredevops_release - Query Azure DevOps Releases using SQL
Azure DevOps is a suite of development tools, services, and features that enables teams to plan, develop, test, and deliver software more efficiently. The Releases in Azure DevOps provide a consistent and reliable delivery pipeline, managing the stages of deployment and tracking the status of each. It offers the ability to automate deployments, monitor the health of the pipeline, and roll back if necessary.
Table Usage Guide
The azuredevops_release
table provides insights into the release pipelines within Azure DevOps. As a DevOps engineer, you can explore details about each release, including the stages, deployment status, and associated metadata. Use this table to manage and monitor your software delivery pipeline, ensuring a reliable and efficient deployment process.
Examples
Basic info
Explore which projects have been created in Azure DevOps and their current status, allowing you to understand the lifecycle and longevity of each project for better management.
select id, name, status, project_id, created_on, keep_foreverfrom azuredevops_release;
select id, name, status, project_id, created_on, keep_foreverfrom azuredevops_release;
List releases which should be skipped by retention policies
Assess the elements within your Azure DevOps releases that are earmarked to be kept indefinitely, allowing you to identify potential areas for data management and storage optimization. This is particularly useful in managing retention policies and ensuring efficient use of resources.
select id, name, status, project_id, created_on, keep_foreverfrom azuredevops_releasewhere keep_forever;
select id, name, status, project_id, created_on, keep_foreverfrom azuredevops_releasewhere keep_forever = 1;
List abandoned releases
Identify instances where releases have been abandoned in Azure DevOps. This aids in understanding project progress and identifying potential bottlenecks or areas for improvement.
select id, name, status, project_id, created_on, keep_foreverfrom azuredevops_releasewhere status = 'abandoned';
select id, name, status, project_id, created_on, keep_foreverfrom azuredevops_releasewhere status = 'abandoned';
List manual releases
Explore which project releases in Azure DevOps have been manually initiated. This can help in assessing the frequency of manual interventions and their impact on the overall project timeline.
select id, name, status, project_id, created_on, keep_foreverfrom azuredevops_releasewhere reason = 'manual';
select id, name, status, project_id, created_on, keep_foreverfrom azuredevops_releasewhere reason = 'manual';
Get creator details of a particular release
Explore which individual created a specific release in the Azure DevOps platform. This is useful for accountability and tracking changes within the project management lifecycle.
select name, created_by ->> 'id' as id, created_by ->> 'displayName' as display_name, created_by ->> 'uniqueName' as unique_name, created_by ->> 'descriptor' as descriptor, created_by ->> 'url' as url, created_by ->> 'imageUrl' as image_urlfrom azuredevops_releasewhere name = 'Release-1';
select name, json_extract(created_by, '$.id') as id, json_extract(created_by, '$.displayName') as display_name, json_extract(created_by, '$.uniqueName') as unique_name, json_extract(created_by, '$.descriptor') as descriptor, json_extract(created_by, '$.url') as url, json_extract(created_by, '$.imageUrl') as image_urlfrom azuredevops_releasewhere name = 'Release-1';
Schema for azuredevops_release
Name | Type | Operators | Description |
---|---|---|---|
_ctx | jsonb | Steampipe context in JSON form, e.g. connection_name. | |
artifacts | jsonb | Gets the list of artifacts. | |
comment | text | Gets comment. | |
created_by | jsonb | Gets the identity who created. | |
created_for | jsonb | Gets the identity for whom release was created. | |
created_on | timestamp with time zone | Gets date on which it got created. | |
definition_snapshot_revision | bigint | Gets revision number of definition snapshot. | |
description | text | Gets description of release. | |
environments | jsonb | Gets list of environments. | |
id | bigint | = | Gets the unique identifier of this release. |
keep_forever | boolean | Whether to exclude the release from retention policies. | |
links | jsonb | The class to represent a collection of REST reference links. | |
logs_container_url | text | Gets logs container url. | |
modified_by | jsonb | Gets the identity who modified. | |
modified_on | timestamp with time zone | Gets date on which it got modified. | |
name | text | = | The release name. |
pool_name | text | Gets pool name. | |
project_id | text | = | ID of the project this release belongs to. |
project_reference | jsonb | Gets project reference. | |
properties | jsonb | The release properties. | |
reason | text | Gets reason of release. | |
release_definition | jsonb | Gets releaseDefinitionReference which specifies the reference of the release definition to which this release is associated. | |
release_definition_revision | bigint | Gets the release definition revision. | |
release_name_format | text | Gets release name format. | |
status | text | = | The release status. |
tags | jsonb | Gets list of tags. | |
title | text | Title of the resource. | |
triggering_artifact_alias | text | Gets triggering artifact alias. | |
variable_groups | jsonb | Gets the list of variable groups. | |
variables | jsonb | Gets or sets the dictionary of variables. |
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_release