Table: azuredevops_build_definition - Query Azure DevOps Build Definitions using SQL

Azure DevOps Build Definitions is a feature within Azure DevOps that allows you to define the steps required to build your code. These definitions include tasks, triggers, and repository information. It provides a structured way to automate the process of transforming your code into a finished product.

Table Usage Guide

The azuredevops_build_definition table provides insights into build definitions within Azure DevOps. As a DevOps engineer, explore build definition details through this table, including tasks, triggers, and associated repository information. Utilize it to uncover information about build pipelines, such as their configuration, the tasks they perform, and the triggers that initiate them.

Examples

Basic info

Analyze the settings to understand the quality and creation date of different build definitions in your Azure DevOps project. This can help you assess the elements within your project and make necessary improvements.

select
  id,
  name,
  quality,
  project_id,
  repository_id,
  created_date
from
  azuredevops_build_definition;

select
  id,
  name,
  quality,
  project_id,
  repository_id,
  created_date
from
  azuredevops_build_definition;

List build definitions where badge is enabled

Explore the build definitions in Azure DevOps that have the badge feature enabled. This can be useful for understanding which projects are actively promoting their build status.

select
  id,
  name,
  quality,
  project_id,
  repository_id,
  created_date
from
  azuredevops_build_definition
where
  badge_enabled;

select
  id,
  name,
  quality,
  project_id,
  repository_id,
  created_date
from
  azuredevops_build_definition
where
  badge_enabled = 1;

List build definitions where builds can be queued

Explore which build definitions in Azure DevOps are set up to allow queuing. This is useful for identifying areas where build processes can be scheduled and managed effectively.

select
  id,
  name,
  quality,
  project_id,
  repository_id,
  created_date
from
  azuredevops_build_definition
where
  queue_status = 'enabled';

select
  id,
  name,
  quality,
  project_id,
  repository_id,
  created_date
from
  azuredevops_build_definition
where
  queue_status = 'enabled';

List build definitions of a particular project

Discover the build definitions associated with a specific project. This can be useful for auditing project configurations and understanding the quality of different builds within the project.

select
  id,
  name,
  quality,
  project_id,
  repository_id,
  created_date
from
  azuredevops_build_definition
where
  project ->> 'name' = 'private_project';

select
  id,
  name,
  quality,
  project_id,
  repository_id,
  created_date
from
  azuredevops_build_definition
where
  json_extract(project, '$.name') = 'private_project';

List build definitions without repository

Discover the segments that have build definitions without an associated repository in Azure DevOps. This is useful to identify potential misconfigurations or orphaned build definitions that may need attention.

select
  id,
  name,
  quality,
  project_id,
  repository_id,
  created_date
from
  azuredevops_build_definition
where
  repository_id is null;

select
  id,
  name,
  quality,
  project_id,
  repository_id,
  created_date
from
  azuredevops_build_definition
where
  repository_id is null;

Get the author details of a particular build definition

Explore the authorship information related to a specific build definition in Azure DevOps to understand who created or modified it, which can be crucial for tracking changes and maintaining accountability.

select
  authored_by ->> 'id' as author_id,
  authored_by ->> 'displayName' as display_name,
  authored_by ->> 'uniqueName' as unique_name,
  authored_by ->> 'descriptor' as descriptor,
  authored_by ->> 'url' as url,
  authored_by ->> 'imageUrl' as image_url
from
  azuredevops_build_definition
where
  name = 'private_project';

select
  json_extract(authored_by, '$.id') as author_id,
  json_extract(authored_by, '$.displayName') as display_name,
  json_extract(authored_by, '$.uniqueName') as unique_name,
  json_extract(authored_by, '$.descriptor') as descriptor,
  json_extract(authored_by, '$.url') as url,
  json_extract(authored_by, '$.imageUrl') as image_url
from
  azuredevops_build_definition
where
  name = 'private_project';

Schema for azuredevops_build_definition

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form, e.g. connection_name.
authored_by	jsonb		The author of the definition.
badge_enabled	boolean		Indicates whether badges are enabled for this definition.
build_number_format	text		The build number format.
comment	text		A save-time comment for the definition.
created_date	timestamp with time zone		The date this version of the definition was created.
demands	jsonb		A list of demands that represents the agent capabilities required by this build.
description	text		The description.
draft_of	jsonb		A reference to the definition that this definition is a draft of, if this is a draft definition.
drafts	jsonb		The list of drafts associated with this definition, if this is not a draft definition.
drop_location	text		The drop location for the definition.
id	bigint	=	The ID of the referenced definition.
job_authorization_scope	text		The job authorization scope for builds queued against this definition.
job_cancel_timeout_in_minutes	bigint		The job cancel timeout (in minutes) for builds cancelled by user for this definition.
job_timeout_in_minutes	bigint		The job execution timeout (in minutes) for builds queued against this definition.
latest_build	jsonb		Data representation of a build.
latest_completed_build	jsonb		Data representation of a latest completed build.
links	jsonb		The class to represent a collection of REST reference links.
metrics	jsonb		Represents metadata about builds in the system.
name	text	=	The name of the referenced definition.
options	jsonb		Represents the application of an optional behavior to a build definition.
path	text	=	The folder path of the definition.
process	jsonb		The build process.
process_parameters	jsonb		The process parameters for this definition.
project	jsonb		A reference to the project.
project_id	text	=	ID of the project.
properties	jsonb		The class represents a property bag as a collection of key-value pairs. Values of all primitive types (any type with a TypeCode != TypeCode.Object) except for DBNull are accepted. Values of type Byte[], Int32, Double, DateType and String preserve their type, other primitives are returned as a String. Byte[] expected as base64 encoded string.
quality	text		The quality of the definition document (draft, etc.).
queue	jsonb		The default queue for builds run against this definition.
queue_status	text		A value that indicates whether builds can be queued against this definition.
repository	jsonb		The repository.
repository_id	text	=	ID of the repository.
repository_type	text	=	Type of the repository.
retention_rules	jsonb		Represents a retention policy for a build definition.
revision	bigint		The definition revision number.
tags	jsonb		The build definition tags.
title	text		Title of the resource.
triggers	jsonb		Represents a trigger for a build definition.
type	text		The type of the definition.
uri	text		The definition's URI.
url	text		The REST URL of the definition.
variable_groups	jsonb		Represents a variable group.
variables	jsonb		Represents a variable used by a build definition.

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_definition