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_forever
from
  azuredevops_release;

select
  id,
  name,
  status,
  project_id,
  created_on,
  keep_forever
from
  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_forever
from
  azuredevops_release
where
  keep_forever;

select
  id,
  name,
  status,
  project_id,
  created_on,
  keep_forever
from
  azuredevops_release
where
  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_forever
from
  azuredevops_release
where
  status = 'abandoned';

select
  id,
  name,
  status,
  project_id,
  created_on,
  keep_forever
from
  azuredevops_release
where
  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_forever
from
  azuredevops_release
where
  reason = 'manual';

select
  id,
  name,
  status,
  project_id,
  created_on,
  keep_forever
from
  azuredevops_release
where
  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_url
from
  azuredevops_release
where
  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_url
from
  azuredevops_release
where
  name = 'Release-1';

Schema for azuredevops_release

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
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.
organization	text	=, !=, ~~, ~~, !~~, !~~	The name of the organization.
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.
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.
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