Table: azuredevops_serviceendpoint - Query Azure DevOps Service Endpoints using SQL

A Service Endpoint in Azure DevOps is a connection point to an external service. It is used to abstract the underlying connectivity to an external service, providing a stable interface for Azure DevOps while allowing the underlying connection details to change. Service Endpoints support services like Jenkins, Azure Resource Manager, and others.

Service endpoints are a way for Azure DevOps to connect to external systems or services. They're a bundle of properties securely stored by Azure DevOps, which includes but isn't limited to the following properties:

Service name
Description
Server URL
Certificates or tokens
User names and passwords

Table Usage Guide

The azuredevops_serviceendpoint table provides insights into Service Endpoints within Azure DevOps. As a DevOps engineer, you can use this table to explore details about Service Endpoints, including their types, authorization details, and associated project details. This table is beneficial for understanding the connections between your Azure DevOps environment and external services.

Examples

Basic info

Explore the readiness and sharing status of Azure DevOps service endpoints to understand their accessibility and ownership. This can help in identifying any potential issues or risks related to endpoint accessibility and management.

select
  id,
  name,
  is_ready,
  is_shared,
  owner,
  type
from
  azuredevops_serviceendpoint;

select
  id,
  name,
  is_ready,
  is_shared,
  owner,
  type
from
  azuredevops_serviceendpoint;

List service endpoints which are not ready

Explore which Azure DevOps service endpoints are not ready for use. This can be beneficial in identifying potential issues in your deployment pipeline.

select
  id,
  name,
  is_ready,
  is_shared,
  owner,
  type
from
  azuredevops_serviceendpoint
where
  not is_ready;

select
  id,
  name,
  is_ready,
  is_shared,
  owner,
  type
from
  azuredevops_serviceendpoint
where
  is_ready = 0;

List shared service endpoints

Analyze the settings to understand which service endpoints in Azure DevOps are shared, providing insights into how resources are being utilized across different teams or projects. This can aid in resource optimization and improve collaboration efficiency.

select
  id,
  name,
  is_ready,
  is_shared,
  owner,
  type
from
  azuredevops_serviceendpoint
where
  is_shared;

select
  id,
  name,
  is_ready,
  is_shared,
  owner,
  type
from
  azuredevops_serviceendpoint
where
  is_shared = 1;

List service endpoints owned by library

Discover the segments that are owned by a specific entity in Azure DevOps. This is useful for understanding who has control over certain resources and can aid in resource management.

select
  id,
  name,
  is_ready,
  is_shared,
  owner,
  type
from
  azuredevops_serviceendpoint
where
  owner = 'Library';

select
  id,
  name,
  is_ready,
  is_shared,
  owner,
  type
from
  azuredevops_serviceendpoint
where
  owner = 'Library';

Get creator details of the service endpoints

Explore the creators of service endpoints, gaining insights into their unique identifiers and display names to understand who has set up each endpoint. This can be useful for auditing or tracking changes in your Azure DevOps environment.

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_serviceendpoint;

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_serviceendpoint;

Schema for azuredevops_serviceendpoint

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form, e.g. connection_name.
administrators_group	jsonb		Gets the identity reference for the administrators group of the service endpoint.
authorization	jsonb		Gets the authorization data for talking to the endpoint.
created_by	jsonb		Gets the identity reference for the user who created the Service endpoint.
data	jsonb		The service endpoint data.
description	text		Gets the description of endpoint.
id	text	=	Gets the identifier of this endpoint.
is_ready	boolean		EndPoint state indicator.
is_shared	boolean		Indicates whether service endpoint is shared with other projects or not.
name	text		Gets the friendly name of the endpoint.
operation_status	jsonb		Error message during creation/deletion of endpoint.
owner	text	=	Owner of the endpoint. Supported values are library and agentcloud.
project_id	text	=	ID of the project this service endpoint belongs to.
readers_group	jsonb		Gets the identity reference for the readers group of the service endpoint.
service_endpoint_project_references	jsonb		All other project references where the service endpoint is shared.
title	text		Title of the resource.
type	text	=	Gets the type of the endpoint.
url	text		Gets the url of the endpoint.

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_serviceendpoint