Table: azuredevops_build - Query Azure DevOps Builds using SQL

Azure DevOps is a Microsoft product that provides version control, reporting, requirements management, project management, automated builds, testing and release management capabilities. It covers the entire application lifecycle and enables DevOps capabilities. Azure DevOps can be used for a variety of application types.

Table Usage Guide

The azuredevops_build table provides insights into the builds within Azure DevOps. As a DevOps engineer, explore build-specific details through this table, including build status, the build process, and associated metadata. Utilize it to uncover information about builds, such as those with failed status, the details of the build process, and the verification of build metadata.

Examples

Basic info

Explore the various builds in your Azure DevOps project, identifying their quality, status, and priority. This can help you understand which builds are kept indefinitely and potentially streamline your project management efforts.

select
  id,
  build_number,
  quality,
  project_id,
  status,
  keep_forever,
  priority
from
  azuredevops_build;

select
  id,
  build_number,
  quality,
  project_id,
  status,
  keep_forever,
  priority
from
  azuredevops_build;

List postponed builds

Explore which build projects in Azure DevOps have been postponed. This is useful for prioritizing and managing development workflow.

select
  id,
  build_number,
  quality,
  project_id,
  status,
  keep_forever,
  priority
from
  azuredevops_build
where
  status = 'postponed';

select
  id,
  build_number,
  quality,
  project_id,
  status,
  keep_forever,
  priority
from
  azuredevops_build
where
  status = 'postponed';

List high priority builds

Determine the areas in which high priority builds are being used within your Azure DevOps projects. This can help prioritize resources and track project progress more effectively.

select
  id,
  build_number,
  quality,
  project_id,
  status,
  keep_forever,
  priority
from
  azuredevops_build
where
  priority = 'high';

select
  id,
  build_number,
  quality,
  project_id,
  status,
  keep_forever,
  priority
from
  azuredevops_build
where
  priority = 'high';

List builds which should be skipped by retention policies

Assess the elements within your Azure DevOps projects to pinpoint specific builds that have been marked to bypass retention policies. This can be beneficial for understanding which builds are being retained indefinitely, aiding in project management and resource allocation.

select
  id,
  build_number,
  quality,
  project_id,
  status,
  keep_forever,
  priority
from
  azuredevops_build
where
  keep_forever;

select
  id,
  build_number,
  quality,
  project_id,
  status,
  keep_forever,
  priority
from
  azuredevops_build
where
  keep_forever = 1;

List builds without repository

Analyze the settings to understand which Azure DevOps builds lack an associated repository. This can be useful for identifying potential configuration issues or orphaned builds.

select
  id,
  build_number,
  quality,
  project_id,
  status,
  keep_forever,
  priority
from
  azuredevops_build
where
  repository_id is null;

select
  id,
  build_number,
  quality,
  project_id,
  status,
  keep_forever,
  priority
from
  azuredevops_build
where
  repository_id is null;

List deleted builds

Explore which builds have been deleted in your Azure DevOps project. This can be useful for auditing purposes or for understanding the lifecycle of your builds.

select
  id,
  build_number,
  quality,
  project_id,
  status,
  keep_forever,
  priority
from
  azuredevops_build
where
  deleted;

select
  id,
  build_number,
  quality,
  project_id,
  status,
  keep_forever,
  priority
from
  azuredevops_build
where
  deleted = 1;

List builds associated with a particular project

Assess the elements within a specific project to identify the associated builds and their details, such as their quality, status, and priority. This can help in project management and in making decisions about resource allocation and task prioritization.

select
  id,
  build_number,
  quality,
  project_id,
  status,
  keep_forever,
  priority
from
  azuredevops_build
where
  project ->> 'name' = 'private_project';

select
  id,
  build_number,
  quality,
  project_id,
  status,
  keep_forever,
  priority
from
  azuredevops_build
where
  json_extract(project, '$.name') = 'private_project';

Schema for azuredevops_build

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form, e.g. connection_name.
agent_specification	jsonb		The agent specification for the build.
build_number	text	=	The build number/name of the build.
build_number_revision	bigint		The build number revision.
controller	jsonb		The build controller. This is only set if the definition type is Xaml.
definition	jsonb		The definition associated with the build.
deleted	boolean		Indicates whether the build has been deleted.
deleted_by	jsonb		The identity of the process or person that deleted the build.
deleted_date	timestamp with time zone		The date the build was deleted.
deleted_reason	text		The description of how the build was deleted.
demands	jsonb		A list of demands that represents the agent capabilities required by this build.
finish_time	timestamp with time zone		The time that the build was completed.
id	bigint	=	The ID of the build.
keep_forever	boolean		Indicates whether the build should be skipped by retention policies.
last_changed_by	jsonb		The identity representing the process or person that last changed the build.
last_changed_date	timestamp with time zone		The date the build was last changed.
links	jsonb		The class to represent a collection of REST reference links.
logs	jsonb		Information about the build logs.
orchestration_plan	jsonb		The orchestration plan for the build.
parameters	text		The parameters for the build.
plans	jsonb		Orchestration plans associated with the build (build, cleanup).
priority	text		The build's priority.
project	jsonb		The team project.
project_id	text	=	ID of the project this build belongs to.
properties	jsonb		The build properties.
quality	text		The quality of the xaml build (good, bad, etc.).
queue	jsonb		The queue. This is only set if the definition type is Build.
queue_options	text		Additional options for queueing the build.
queue_position	bigint		The current position of the build in the queue.
queue_time	timestamp with time zone		The time that the build was queued.
reason	text	=	The reason that the build was created.
repository	jsonb		The repository.
repository_id	text	=	ID of the repository.
repository_type	text	=	Type of the repository.
requested_by	jsonb		The identity that queued the build.
requested_for	jsonb		The identity on whose behalf the build was queued.
result	text	=	The build result.
retained_by_release	boolean		Indicates whether the build is retained by a release.
source_branch	text		The source branch.
source_version	text		The source version.
start_time	timestamp with time zone		The time that the build was started.
status	text	=	The status of the build.
tags	jsonb		The build tags.
title	text		Title of the resource.
trigger_info	jsonb		Sourceprovider-specific information about what triggered the build.
triggered_by_build	jsonb		The build that triggered this build via a Build completion trigger.
uri	text		The URI of the build.
url	text		The REST URL of the build.
validation_results	jsonb		Represents the result of validating a build request.

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_build