Table: azuredevops_project - Query Azure DevOps Projects using SQL

Azure DevOps is a Microsoft product that provides version control, reporting, requirements management, project management, automated builds, lab management, 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_project table provides insights into projects within Azure DevOps. As a project manager or developer, explore project-specific details through this table, including project name, description, visibility, version control, and process template. Utilize it to uncover information about projects, such as those with public visibility, the version control used, and the process template associated with each project.

Examples

Basic info

Explore which Azure DevOps projects are currently active, their visibility status, and when they were last updated. This information can help assess the current state of your projects and identify any that may require attention.

select
  id,
  name,
  state,
  visibility,
  abbreviation,
  last_update_time
from
  azuredevops_project;

select
  id,
  name,
  state,
  visibility,
  abbreviation,
  last_update_time
from
  azuredevops_project;

List public projects

Discover the segments that are public in your Azure DevOps projects, allowing you to assess the elements within your setup that are visible to all users. This can help you maintain appropriate access controls and security measures.

select
  id,
  name,
  state,
  visibility,
  abbreviation,
  last_update_time
from
  azuredevops_project
where
  visibility = 'public';

select
  id,
  name,
  state,
  visibility,
  abbreviation,
  last_update_time
from
  azuredevops_project
where
  visibility = 'public';

List projects which are in the `createPending` state

Discover the segments that are pending creation within your projects. This can aid in understanding project progress and managing resources effectively.

select
  id,
  name,
  state,
  visibility,
  abbreviation,
  last_update_time
from
  azuredevops_project
where
  state = 'createPending';

select
  id,
  name,
  state,
  visibility,
  abbreviation,
  last_update_time
from
  azuredevops_project
where
  state = 'createPending';

Show project capabilities

Explore the capabilities of your projects to understand the version control and process template settings. This can help you manage and optimize your project settings in Azure DevOps.

select
  id,
  name,
  jsonb_pretty(capabilities -> 'versioncontrol') as version_control,
  jsonb_pretty(capabilities -> 'processTemplate') as process_template
from
  azuredevops_project;

select
  id,
  name,
  capabilities as version_control,
  capabilities as process_template
from
  azuredevops_project;

Get project default team details

Gain insights into the default team details associated with various projects to better understand team structure and project management within Azure DevOps.

select
  id,
  name,
  default_team ->> 'id' as default_team_id,
  default_team ->> 'name' as default_team_name,
  default_team ->> 'url' as default_team_url
from
  azuredevops_project;

select
  id,
  name,
  json_extract(default_team, '$.id') as default_team_id,
  json_extract(default_team, '$.name') as default_team_name,
  json_extract(default_team, '$.url') as default_team_url
from
  azuredevops_project;

List project properties

Explore the various properties of your projects in Azure DevOps. This is useful for gaining insights into project details like their state and visibility settings.

select
  id,
  name,
  state,
  visibility,
  jsonb_pretty(properties) as properties
from
  azuredevops_project;

select
  id,
  name,
  state,
  visibility,
  properties
from
  azuredevops_project;

Schema for azuredevops_project

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
abbreviation	text		Project abbreviation.
capabilities	jsonb		Set of capabilities this project has (such as process template & version control).
default_team	jsonb		The shallow ref to the default team.
description	text		The project's description (if any).
id	text	=	Project identifier.
last_update_time	timestamp with time zone		Project last update time.
links	jsonb		The links to other objects related to this object.
name	text		Project name.
organization	text	=, !=, ~~, ~~, !~~, !~~	The name of the organization.
properties	jsonb		Get a collection of team project properties.
revision	bigint		Project revision.
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.
state	text	=	Project state.
title	text		Title of the resource.
url	text		Url to the full version of the object.
visibility	text		Project visibility.

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_project