This page provides a guide to Establishing a Connection to Azure DevOps in CData Cloud, as well as information on the available resources, and a reference to the available connection properties.

Connecting to Azure DevOps

Establishing a Connection shows how to authenticate to Azure DevOps and configure any necessary connection properties to create a database in CData Cloud

Accessing Data from CData Cloud Services

Accessing data from Azure DevOps through the available standard services and CData Cloud administration is documented in further details in the CData Cloud Documentation.

Connect to Azure DevOps by selecting the corresponding icon in the Database tab. Required properties are listed under Settings. The Advanced tab lists connection properties that are not typically required.

Connecting to Azure DevOps

To connect to your Azure DevOps account, navigate to Profile > Organizations to obtain the name of your organization in the account. Set the Organization property to this value.

Note: Some table names exist in multiple catalogs and schemas. When querying a table, you should specify the catalog and schema in either the Catalog and Schema connection properties or the fully qualified table name.

Authenticating to Azure DevOps

Azure DevOps supports both Basic and Azure AD (OAuth-based) authentication.

Basic

When you connect to your Azure DevOps via Basic authentication, you provide both the Organization and a PersonalAccessToken.

To generate a personal access token, log in to your Azure DevOps Organization account and navigate to Profile > Personal Access Tokens > New Token. The generated token will be displayed.

Entra ID (Azure AD)

Note: Microsoft has rebranded Azure AD as Entra ID. In topics that require the user to interact with the Entra ID Admin site, we use the same names Microsoft does. However, there are still CData connection properties whose names or values reference "Azure AD".

Microsoft Entra ID is a multi-tenant, cloud-based identity and access management platform. It supports OAuth-based authentication flows that enable the driver to access Azure DevOps endpoints securely.

Authentication to Entra ID via a web application always requires that you first create and register a custom OAuth application. This enables your application to define its own redirect URI, manage credential scope, and comply with organization-specific security policies.

For full instructions on how to create and register a custom OAuth application, see Creating an Entra ID (Azure AD) Application.

After setting AuthScheme to AzureAD, the steps to authenticate vary, depending on the environment. For details on how to connect from desktop applications, web-based workflows, or headless systems, see the following sections.

Creating an Entra ID (Azure AD) Application

Note: Microsoft has rebranded Azure AD as Entra ID. In topics that require the user to interact with the Entra ID Admin site, we use the same names Microsoft does. However, there are still CData connection properties whose names or values reference "Azure AD".

Azure DevOps supports OAuth-based authentication using Microsoft Entra ID. If you will connect via a web application and want to authenticate via Entra ID, you must first register a custom OAuth application in the Entra Admin Center, as described below.

Registering the Application

To register an OAuth application in Microsoft Entra ID, follow these steps:

1. Go to https://portal.azure.com.
2. In the left-hand navigation pane, select Microsoft Entra ID > App registrations.
3. Click New registration.
4. Enter a name for the application.
5. Specify the types of accounts this application should support:
   • For private-use applications, select Accounts in this organization directory only.
   • For distributed applications, select one of the multi-tenant options.

   Note: If you select Accounts in this organizational directory only, when you connect with CData Cloud, you must set AzureTenant to the tenant's ID (either GUID or verified domain). Otherwise, authentication will fail.

6. Set Select a platform to Web, and set the redirect URI to http://localhost:33333 (default), or use another URI appropriate for your deployment. When using a custom redirect URI set a CallbackURL connection property; in those cases, set it to match this URI exactly.
7. Click Register. The application management screen opens. Record these values for later use:
   • Application (client) ID is used for OAuthClientId
   • Directory (tenant) ID is used for AzureTenant
8. Go to Certificates & Secrets. Click New Client Secret, set the desired expiration, and save the generated value. This value will only be shown once — record it to use with OAuthClientSecret.

9. Select the Azure DevOps API and then select the permissions your app will seek.
10. To confirm, click Add permissions.

Fine Tuning Data Access

You can use the following properties to gain more control over the data returned from Azure DevOps:

• Catalog: Specifies the catalog to be used.
  • If you want to query data for a specific project, set Catalog to either Project_projectId or Project_projectName. For example, to query data in a project named dev with the ID 2ee729d6-804d-4ece-84af-d5befa94abe8, you can set Catalog to either Project_dev or Project_2ee729d6-804d-4ece-84af-d5befa94abe8. To get a list of project IDs and names, execute a SELECT query against the Projects table.
  • If you want to query information that is independent of a specific project, set Catalog to CData.
• Schema: Specifies the schema to be used.
  • If Catalog is set to CData, the only schema available is Information.
  • If Catalog is set to a project catalog, you can set Schema to either Analytics, Project, or one of the Repository schemas. If you want to query data for a specific repository, set Schema to Repository_repositoryId. For example, to query data in a repository with the ID 3ee729d6-804d-4ece-84af-d5befa94abe8, you can set Schema to Repository_3ee729d6-804d-4ece-84af-d5befa94abe8. To get a list of repository IDs and names, set Schema to Project and execute a SELECT query to the Repositories table.

Customizing the SSL Configuration

By default, the Cloud attempts to negotiate TLS with the server. The server certificate is validated against the default system trusted certificate store. You can override how the certificate gets validated using the SSLServerCert connection property.

To specify another certificate, see the SSLServerCert connection property.

Connecting Through a Firewall or Proxy

HTTP Proxies

To authenticate to an HTTP proxy, set the following:

• ProxyServer: the hostname or IP address of the proxy server that you want to route HTTP traffic through.
• ProxyPort: the TCP port that the proxy server is running on.
• ProxyAuthScheme: the authentication method the Cloud uses when authenticating to the proxy server.
• ProxyUser: the username of a user account registered with the proxy server.
• ProxyPassword: the password associated with the ProxyUser.

Other Proxies

Set the following properties:

• To use a proxy-based firewall, set FirewallType, FirewallServer, and FirewallPort.
• To tunnel the connection, set FirewallType to TUNNEL.
• To authenticate, specify FirewallUser and FirewallPassword.
• To authenticate to a SOCKS proxy, additionally set FirewallType to SOCKS5.

Overview

The Cloud allows you to access Azure DevOps data at multiple levels, from organization-wide information down to specific project and repository details.

Once connected, the Cloud organizes data into two catalog types. The static CData catalog operates at the organization level and contains one schema called Information, which provides data across your entire Azure DevOps organization. The Cloud also creates dynamic Project catalogs for each specific project in your organization. Each Project catalog contains two static schemas and additional dynamic schemas. The Analytics schema provides data from the Analytics service, while the Project schema contains general information about that specific Azure DevOps project. Additionally, each Project catalog automatically generates Repository schemas for every repository within that project, allowing you to access repository-specific information. An overview of the types of catalogs and schemas that can be expected after a connection has been established is given here:

• CData
  • Information
• Project_1
  • Analytics
  • Project
  • Repository_c36a682e-db74-4bc1-b0c3-8929402ce826
  • Repository_61b94efc-fe12-4179-825c-5ab3d0fee244
• Project_2
  • Analytics
  • Project
  • Repository_3dfd899f-c4c2-419a-a33d-23c59582922a

CData catalog

This contains information that is related to the entire Azure DevOps organization rather than being tied to a specific project. It can be accessed by setting the catalog to CData.

Information schema

As the only schema in the CData catalog, this also contains information that is related to the entire Azure DevOps organization. It can be accessed by setting the schema to Information.

This schema is useful if you are trying to query information on projects. For example, if you are trying to compile a list of project names that you can use to build the Project catalog names, query the Projects table.

SELECT Name FROM [CData].[Information].Projects

This schema is also useful if you need to retrieve data that is shared across all projects, like Agent Pools.

SELECT * FROM [CData].[Information].AgentPools

Project catalogs

Most data in Azure DevOps is related to a project. In the Cloud, each project is modeled in its own catalog, so the amount and names of the Project catalogs depend on how many projects are in your Azure DevOps organization.

The names of these catalogs must be in either the format 'Project_projectId' or 'Project_projectName'. For example, to query data in a project named 'dev' with the Id '2ee729d6-804d-4ece-84af-d5befa94abe8', you can set the catalog to either Project_dev or Project_2ee729d6-804d-4ece-84af-d5befa94abe8.

Project schema

The Project schema contains information on the project specified in the catalog name. It can be accessed by setting the schema to Project.

Information such as Test Suites, Teams, Builds, and other items related to a project can be found here. For example, the following query retrieves all test suites for the project named 'dev'.

SELECT * FROM [Project_dev].[Project].TestSuites

It can also be used to retrieve a list of repository Ids, which can be used to construct the Repository schema names. For example, the following query retrieves all repository Ids in the project named 'dev'.

SELECT Id FROM [Project_dev].[Project].Repositories

It also contains information found in a project's repositories, sliced across all the repositories in the project. This allows you to query data across all repositories, but it is less performant than the Repository schemas.

SELECT * FROM [Project_dev].[Project].PullRequests

Repository schemas

Each repository is modeled in its own schema, which will be different depending on how many repositories are in a specified project.

The names of these schemas must be in the format 'Repository_repositoryId'. For example, to query data in a repository with the Id '3ee729d6-804d-4ece-84af-d5befa94abe8', you can set the schema to Repository_3ee729d6-804d-4ece-84af-d5befa94abe8.

The Repository schema is useful when you are trying to query data in a single repository, such as pull requests, commits, and Git branches. While these tables are also available in the Project schema, it is more performant to use one of the Repository schemas as this does not require the data to be sliced across all repositories. For example, if you are trying to query pull requests in repository 'd36a682e-db74-4bc1-b0c3-8929402ce829', it would be more performant to use the repository schema, as shown below.

SELECT * FROM [Project_dev].[Repository_d36a682e-db74-4bc1-b0c3-8929402ce829].PullRequests

Analytics schema

This is unique in that while all other schemas connect to REST endpoints, the Analytics schema connects to the OData Analytics service. It can be accessed by setting the schema to Analytics.

It is useful for querying the analytics data for a project, such as analytics for teams or work items, as shown below.

SELECT count(TeamId) as noOfTeams FROM [Project_dev].[Analytics].Teams

SELECT sum(CompletedWork) as SumOfCompletedWork, sum(RemainingWork) as SumOfRemainingWork FROM [Project_dev].[Analytics].WorkItems

When Catalog is set to 'CData', the following data is accessible: Information Data Model.

Overview

This section shows the available API objects and provides more information on executing SQL to Azure DevOps APIs. Note that this schema can only be accessed when Catalog is set to 'CData' and Schema is set to 'Information'.

Key Features

• The Cloud models Azure DevOps entities like users, agent pools, and projects as tables and views, allowing you to write SQL to query Azure DevOps data.
• Stored procedures allow you to execute operations to Azure DevOps
• Live connectivity to these objects means any changes to your Azure DevOps account are immediately reflected when using the Cloud.

Tables

Tables describes the available tables. The provider models the data in Azure DevOps into a list of tables that can be queried using standard SQL statements.

Views

Views describes the available views. Unlike tables, views are read-only.

Stored Procedures

Stored Procedures are SQL scripts that extend beyond standard CRUD operations. They allow you to execute operations to Azure DevOps, such as updating a user, cloning a test case, and creating a pull request.

The Cloud models the data in Azure DevOps as a list of tables in a relational database that can be queried using standard SQL statements.

CData Cloud - Azure DevOps Tables

Name	Description
AgentPools	Retrieves a list of agent pools.
BuildDefinitions	Retrieves a list of build definitions, sliced across all projects.
GroupMembers	Get direct members of a group.
Pipelines	Retrieves a list of pipelines
Projects	Get all projects in the organization that the authenticated user has access to and details of the specific project.
Repositories	Generated schema file..
TestPlans	Get a list of test plans and details of specific test plan.
Users	Retrieves a list of users. This table will not retrieve results for the On-premise edition.
WorkItems	Retrieves a list of work items. This table includes custom fields which are automatically discovered when 'IncludeCustomFields' is enabled.

Retrieves a list of agent pools.

Table Specific Information

Select

The Cloud uses the Azure DevOps API to process WHERE clause conditions built with the following columns and operators:

• Id supports the '=,in' operators.
• PoolType supports the '=' operator.
• Action supports the '=' operator.
• Properties supports the 'in' operator.

The rest of the filter is executed client-side in the Cloud.

For example:

	SELECT * FROM AgentPools WHERE Id IN (1, 2, 3)
	SELECT * FROM AgentPools WHERE Id = 9
	SELECT * FROM AgentPools WHERE PoolType = 'deployment'
	SELECT * FROM AgentPools WHERE Action = 'manage'

Insert

The following are examples of inserting into an AgentPools table:

INSERT INTO AgentPools (Name) VALUES ('PoolA')
INSERT INTO AgentPools (IsHosted, CreatedByDisplayName, AgentCloudId, Name) VALUES (false, 'Cdata', 1, 'Cdata_Ecity')

Update

The following is an example of updating an AgentPools table:

UPDATE AgentPools SET Name = 'Data1' WHERE Id = 1

Delete

The following is an example of deleting data in an AgentPools table:

DELETE FROM AgentPools WHERE Id = '1'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		Id of the agent pool.
AgentCloudId	Integer	False		The ID of the associated agent cloud.
AutoProvision	Boolean	False		Whether or not a queue should be automatically provisioned for each project collection.
AutoSize	Boolean	False		Whether or not the pool should autosize itself based on the agent cloud provider settings.
AutoUpdate	Boolean	False		Whether or not a pool should be automatically updated.
CreatedByDescriptor	String	False		The descriptor is the primary way to reference the creator while the system is running.
CreatedByDisplayName	String	False		This is the non-unique display name of the creator.
CreatedById	String	False		Id of the creator.
CreatedByUrl	String	False		Full http link to the creator.
CreatedOn	Datetime	False		The date/time of the pool creation.
IsHosted	Boolean	False		Indicates whether or not this pool is managed by the service.
IsLegacy	Boolean	False		Determines whether the pool is legacy.
Name	String	False		The name of the agent pool.
OwnerDescriptor	String	False		The descriptor is the primary way to reference the owner while the system is running.
OwnerDisplayName	String	False		This is the non-unique display name of the owner.
OwnerId	String	False		Id of the owner.
OwnerUrl	String	False		Full Http Link to the owner.
PoolType	String	False		The type of the pool. The allowed values are automation, deployment.
Properties	String	False		Represents a property bag as a collection of key-value pairs.
Scope	String	False		The scope of the pool.
Size	Integer	False		The current size of the pool.
TargetSize	Integer	False		Target parallelism.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements, and offer a more granular control over the tuples that are returned from the data source. Unless otherwise specified, only the = operator is permitted when filtering on pseudocolumns.

Name	Type	Description
Action	String	Filter by whether the calling user has use or manage permissions. The allowed values are manage, none, use.

Retrieves a list of build definitions, sliced across all projects.

Table Specific Information

Select

The Cloud uses the Azure DevOps API to process WHERE clause conditions built with the following columns and operators:

• Id supports the '=,in' operators.
• ProjectId supports the '=' operator.
• Name supports the '=' operator.
• Path supports the '=' operator.
• ProcessType supports the '=' operator.
• ProcessYamlFileName supports the '=' operator.
• Properties supports the '=,in' operators.
• RepositoryId supports the '=' operator.
• RepositoryType supports the '=' operator.
• RevisionNum supports the '=' operator and filters the Revision column, but only when the Id is also specified.
• BuildDate supports the '<,<=,>,>=' operators.
• MinMetricsTime supports the '=' operator.
• IncludeLatestBuilds supports the '=' operator.
• TaskId supports the '=' operator.
• IncludeAllProperties supports the '=' operator.

The rest of the filter is executed client-side in the Cloud.

For example:

    SELECT * FROM BuildDefinitions WHERE ProjectId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf'
	SELECT * FROM BuildDefinitions WHERE ProjectId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf' AND Id IN (3, 4, 5)
	SELECT * FROM BuildDefinitions WHERE ProjectId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf' AND Path = '\"'
	SELECT * FROM BuildDefinitions WHERE Id = 298 AND RevisionNum = 1

Insert

The following is an example of inserting into BuildDefinitions table:

INSERT INTO BuildDefinitions (Name, ProjectId, RepositoryType, ProcessYamlFilename, RepositoryId, Tags) VALUES (cdata, 'b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937', TfsGit, 'data.txt', 'e50698d4-bb6e-400f-a1a0-5f4d17517d9e', '1, 2, 3')

Update

The following is an example of updating a BuildDefinitions table:

UPDATE BuildDefinitions SET Name = 'Shubham1id', Revision = 1, RepositoryId = 'e50698d4-bb6e-400f-a1a0-5f4d17517d9e', RepositoryType = 'TfsGit', ProcessYamlFilename = 'data.txt' WHERE ProjectId = 'b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937' AND Id = 4

Delete

The following is an example of deleting data in a BuildDefinitions table:

DELETE FROM BuildDefinitions WHERE ProjectId = 'b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937' AND Id = 4

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		Id of the build definition.
Links	String	True		Aggregate of the reference links.
AuthoredByDisplayName	String	False		This is the non-unique display name of the user.
AuthoredById	String	False		Id of the user.
AuthoredByUrl	String	False		The URL Of the user.
BadgeEnabled	Boolean	False		Indicates whether the badge is enabled.
CreatedDate	Datetime	False		The date this version of the definition was created.
DraftOfCreatedDate	Integer	False		The date this version of the definition was created.
DraftOfId	Integer	False		The Id of the referenced definition.
DraftOfName	String	False		The name of the referenced definition.
DraftOfPath	String	False		The folder path of the definition.
DraftOfProjectId	String	False		Id of the Project of the referenced Definition.
DraftOfQueueStatus	String	False		A value that indicates whether builds can be queued against this definition.
DraftOfRevision	Integer	False		The definition revision number.
DraftOfType	String	False		The type of the definition.
DraftOfUri	String	False		The Definition's URI.
DraftOfUrl	String	False		The REST URL of the definition.
JobAuthorizationScope	String	False		The job authorization scope for builds queued against this definition. Only available if the filter IncludeAllProperties=true is set.
JobCancelTimeoutInMinutes	Integer	False		The job cancel timeout (in minutes) for builds cancelled by user for this definition. Only available if the filter IncludeAllProperties=true is set.
JobTimeoutInMinutes	Integer	False		The job execution timeout (in minutes) for builds queued against this definition. Only available if the filter IncludeAllProperties=true is set.
LatestBuildId	Integer	False		Id of the latest build.
LatestCompletedBuildId	Integer	False		Id of the latest completed build.
Name	String	False		The name of the referenced definition.
Path	String	False		The folder path of the definition.
ProcessType	Integer	False		The process type. Only available if the filter IncludeAllProperties=true is set.
ProcessYamlFilename	String	False		The process YAML file name. Only available if the filter IncludeAllProperties=true is set.
ProjectId	String	False	Projects.Id	Project identifier.
Properties	String	False		Properties of the build definition. Only available if the filter IncludeAllProperties=true is set.
Quality	String	False		The quality of the definition document (draft, etc.).
QueueLinksSelfHref	String	True		Queue self reference link.
QueueId	Integer	False		The ID of the queue.
QueueName	String	False		The name of the queue.
QueuePoolId	Integer	False		The pool Id.
QueuePoolIsHosted	Boolean	False		A value indicating whether or not this pool is managed by the service.
QueuePoolName	String	False		The pool name.
QueueUrl	String	False		The full http link to the resource.
QueueStatus	String	False		A value that indicates whether builds can be queued against this definition.
RepositoryCheckoutSubmodules	Boolean	False		Indicates whether to checkout submodules. Only available if the filter IncludeAllProperties=true is set.
RepositoryClean	String	False		Indicates whether to clean the target folder when getting code from the repository.
RepositoryId	String	False		The ID of the repository. Only available if the filter IncludeAllProperties=true is set.
RepositoryType	String	False		The type of the repository. Only available if the filter IncludeAllProperties=true is set.
Revision	Integer	False		The definition revision number.
Tags	String	False		The tags associated with this definition. Only available if the filter IncludeAllProperties=true is set.
Triggers	String	False		The build triggers. Only available if the filter IncludeAllProperties=true is set.
Type	String	False		The type of the definition.
Uri	String	False		The definition's URI.
Url	String	False		The REST URL of the definition.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements, and offer a more granular control over the tuples that are returned from the data source. Unless otherwise specified, only the = operator is permitted when filtering on pseudocolumns.

Name	Type	Description
RevisionNum	Integer	The definition revision number, tied to the Revision. This filter is ignored if the Id is not specified.
BuildDate	Datetime	If specified, filters to definitions that have builds before or after this date.
MinMetricsTime	Datetime	If specified, indicates the date from which metrics should be included.
IncludeLatestBuilds	Boolean	Indicates whether latest builds should be included.
TaskId	String	If specified, filters to definitions that use the specified task.
IncludeAllProperties	Boolean	Indicates whether the full definitions should be returned.

Get direct members of a group.

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	String	False		Unique identifier of the group member.
GroupId [KEY]	String	False	Groups.OriginId	OriginId of the group.
DateCreated	Datetime	True		Date the user was added to the collection.
LastAccessedDate	Datetime	True		Date the user last accessed the collection.
AccountLicenseType	String	True		Type of Account License.
AssignmentSource	String	True		Assignment Source of the License.
GitHubLicenseType	String	True		GitHub license type.
LicenseDisplayName	String	True		Display name of the License.
LicensingSource	String	True		Licensing Source.
MsdnLicenseType	String	True		Type of MSDN License.
AccessLevelStatus	String	True		User status in the account.
AccessLevelStatusMessage	String	True		Status message.
UserDescriptor	String	True		The primary way to reference the graph subject while the system is running.
UserDirectoryAlias	String	True		The short, generally unique name for the user in the backing directory.
UserDisplayName	String	True		The non-unique display name of the graph subject.
UserDomain	String	True		The name of the container of origin for a graph member.
UserMailAddress	String	True		The email address of record for a given graph member.
UserMetaType	String	True		The meta type of the user in the origin, such as 'member', 'guest', etc.
UserOrigin	String	True		The type of source provider for the origin identifier (ex:AD, AAD, MSA).
UserOriginId	String	True		The unique identifier from the system of origin. Typically a sid, object id or Guid.
UserPrincipalName	String	True		PrincipalName of this graph member from the source provider.
UserSubjectKind	String	True		This field identifies the type of the graph subject (ex: Group, Scope, User).
UserUrl	String	True		This url is the full route to the source resource of this graph subject.
Extensions	String	True		Extensions.
GroupAssignments	String	True		GroupEntitlements that this user belongs to.
ProjectEntitlements	String	True		Relation between a project and the member's effective permissions in that project.

Retrieves a list of pipelines

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Int	True		Id of the pipeline.
Folder	String	False		Folder in which the pipeline is located.
Name	String	False		Pipeline name.
Revision	Int	True		Revision number.
URL	String	True		URL of the pipeline.
Links	String	True		Aggregate of the reference links.
WebURL	String	True		Web Url.
ConfigurationPath	String	False		Path to the pipeline's configuration file. This must link to a YAML file within the repository. Only available when the Id is specified.
ConfigurationRepositoryId	String	False		The pipeline's configuration's repository's id. Only available when the Id is specified.
ConfigurationRepositoryType	String	False		The pipeline's configuration's repository's type. Only available when the Id is specified.
ConfigurationType	String	False		The pipeline's configuration type. Only available when the Id is specified.
ProjectId [KEY]	String	False	Projects.Id	Id of the project.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements, and offer a more granular control over the tuples that are returned from the data source. Unless otherwise specified, only the = operator is permitted when filtering on pseudocolumns.

Name	Type	Description
PipelineVersion	Int	The pipeline version, tied to the revision number. Only available when the Id is specified.

Get all projects in the organization that the authenticated user has access to and details of the specific project.

Table Specific Information

Select

The Cloud uses the Azure DevOps API to process WHERE clause conditions built with the following columns and operators:

• Id supports the '=' operator.
• State supports the '=' operator. If the Id is also specified, this filter must be processed client-side.

The rest of the filter is executed client-side in the Cloud.

For example:

	
SELECT * FROM Projects WHERE Id = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf'
SELECT * FROM Projects WHERE State = 'new'

Insert

The following is an example of inserting into the Projects table:

INSERT INTO Projects (Name, description, visibility, CapabilitiesVersionControlType, CapabilitiesProcessTemplateTypeId) VALUES ('cdata','demo project', 'private', 'Git', '6b724908-ef14-45cf-84f8-768b5384da45')

Update

The following is an example of updating the Projects table:

UPDATE Projects SET name='Cdata' where Id='b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937'

Delete

The following is an example of deleting from the Projects table:

DELETE FROM Projects WHERE Id = 'b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		Unique identifier of the project.
CapabilitiesProcessTemplateName	String	False		Process template capabilities this project has.
CapabilitiesProcessTemplateTypeId	String	False		Process template capabilities this project has.
CapabilitiesVersionControlType	String	False		Version control capabilities this project has.
CapabilitiesVersionControlGitEnabled	Boolean	False		Version control capabilities this project has.
CapabilitiesVersionControlTfvcEnabled	Boolean	False		Version control capabilities this project has.
DefaultTeamId	String	False		Team (identity) GUID.
DefaultTeamName	String	False		The name of the default team.
DefaultTeamUrl	String	False		The URL of the team.
DefaultTeamImageUrl	String	False		URL to default team identity image.
Description	String	False		The description of the project.
LastUpdateTime	Datetime	False		The timestamp at which the project was last updated.
Links	String	True		Aggregate of the reference links.
Name	String	False		The name of the project.
Revision	Integer	False		The revision of the project.
State	String	False		The current state of the project.
Url	String	False		URL to the full version of the object.
Visibility	String	False		Indicates whom the project is visible to.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements, and offer a more granular control over the tuples that are returned from the data source. Unless otherwise specified, only the = operator is permitted when filtering on pseudocolumns.

Name	Type	Description
GetDefaultTeamImageUrl	Boolean	If set, returns of the default team image URL.
IncludeCapabilities	Boolean	Include capabilities (such as source control) in the team project result (default: false).

Generated schema file..

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		Id of the repository.
Links	String	True		Aggregate of the reference links.
DefaultBranch	String	True		The default branch.
IsFork	Boolean	True		True if the repository was created as a fork.
Name	String	False		The name of the repository.
ParentRepositoryId	String	False		Id of the parent repository.
ParentRepositoryIsFork	Boolean	False		True if the repository was created as a fork.
ParentRepositoryName	String	False		The name of the parent repository.
ParentRepositoryProjectId	String	False		The project ID of the parent repository.
ParentRepositoryRemoteUrl	String	False		The remote URL of the parent repository.
ParentRepositorySshUrl	String	False		The SSH URL of the parent repository.
ParentRepositoryUrl	String	False		The URl of the parent repository.
ProjectId	String	False	Projects.Id	Id of the project.
ProjectLastUpdateTime	Datetime	True		Datetime when the project was last updated.
RemoteUrl	String	True		The remote URL of the repository.
Size	String	True		The size of the repository.
SshUrl	String	True		The SSH URL of the repository.
Url	String	True		The URL of the repository.
ValidRemoteUrls	String	True		The collection of valid remote URL's.
WebUrl	String	True		The web URL of the Repository.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements, and offer a more granular control over the tuples that are returned from the data source. Unless otherwise specified, only the = operator is permitted when filtering on pseudocolumns.

Name	Type	Description
IncludeAllUrls	Boolean	True to include all remote URLs.
IncludeHidden	Boolean	True to include hidden repositories.
IncludeLinks	Boolean	True to include reference links.
IncludeParent	Boolean	True to include parent repository.

Get a list of test plans and details of specific test plan.

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		Id of the test plan.
AreaPath	String	False		Area of the test plan.
BuildDefinitionId	Integer	False	BuildDefinitions.Id	Id of the Build Definition that generates a build associated with this test plan.
BuildDefinitionName	String	False		Name of the Build Definition.
BuildId	Integer	False		Build to be tested.
Description	String	False		Description of the test plan.
EndDate	Datetime	False		End date for the test plan.
Iteration	String	False		Iteration path of the test plan.
Links	String	True		Aggregate of the reference links.
Name	String	False		Name of the test plan.
OwnerDisplayName	String	False		The non-unique display name of the owner.
OwnerUrl	String	False		The URL of the owner.
OwnerId	String	False		The Id of the owner.
PreviousBuildId	Integer	True		Previous build Id associated with the test plan.
ProjectId [KEY]	String	False	Projects.Id	Id of the Project that contains the test plan.
ProjectName	String	True		Name of the Project.
ProjectLastUpdateTime	Date	True		Datetime when the project was last updated.
ReleaseEnvironmentDefinitionId	Integer	False		Release Environment to be used to deploy the build and run automated tests from this test plan.
Revision	Integer	True		Revision of the test plan.
RootSuiteId	Integer	True		Id of the Root Suite of the test plan.
RootSuiteName	String	True		Name of the Root Suite of the test plan.
StartDate	Datetime	False		Start date for the test plan.
State	String	False		State of the test plan.
SyncOutcomeAcrossSuites	Boolean	False		Value to configure how same tests across test suites under a test plan need to behave.
UpdatedByDisplayName	String	True		The non-unique display name of the user who last updated this test plan.
UpdatedByUrl	String	True		The URL of the user.
UpdatedById	String	True		The Id of the user.
UpdatedDate	Datetime	True		Updated date of the test plan.
ItemUrl	String	True		UI Url of the item.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements, and offer a more granular control over the tuples that are returned from the data source. Unless otherwise specified, only the = operator is permitted when filtering on pseudocolumns.

Name	Type	Description
IncludePlanDetails	Boolean	Get all properties of the test plan.
ActivePlans	Boolean	Get just the active plans.

Retrieves a list of users. This table will not retrieve results for the On-premise edition.

Table Specific Information

Select

The Cloud uses the Azure DevOps API to process WHERE clause conditions built with the following columns and operators:

• Id supports the '=' operator.

The rest of the filter is executed client-side in the Cloud.

For example:

SELECT * FROM Users WHERE Id = 'c774bab2-7c43-65da-8ae4-be3ca4519257'

Insert

When performing an Insert, the following fields are required: UserPrincipalName, UserOriginID, AccessLevelAccountLicenseType

The following is an example of inserting into the Users table:

INSERT INTO Users (UserDisplayName, UserPrincipalName, UserOriginID, AccessLevelAccountLicenseType, UserSubjectKind) VALUES ('Anirudh', '[email protected]', '000300003732A094', 'express', 'user')

Update

Updates are not supported for this table. However, they can be performed through the UpdateUser stored procedure.

Delete

Due to the fact that there is no way to distinguish between the API response for a successful and a failed DELETE for this table, the affected row count is always -1.

The following is an example of deleting from the Users table:

DELETE FROM Users WHERE Id = '7342ddfe-abc9-4884-9fbf-773be61e2c92'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		Id of the User.
AccessLevelAccountLicenseType	String	False		Type of Account License (e.g. Express, Stakeholder etc.).
AccessLevelAssignmentSource	String	False		Assignment Source of the License (e.g. Group, Unknown etc.).
AccessLevelLicenseDisplayName	String	False		Display name of the license.
AccessLevelLicensingSource	String	False		Licensing Source (e.g. Account. MSDN etc.).
AccessLevelMSDNLicenseType	String	False		Type of MSDN License (e.g. Visual Studio Professional, Visual Studio Enterprise etc.).
AccessLevelStatus	String	False		User status in the account.
AccessLevelStatusMessage	String	False		Status message.
DateCreated	Datetime	True		Date the user was added to the collection.
LastAccessedDate	Datetime	True		Date the user last accessed the collection.
UserDescriptor	String	False		The descriptor is the primary way to reference the user while the system is running.
UserDirectoryAlias	String	False		The short, generally unique name for the user in the backing directory.
UserDisplayName	String	False		This is the non-unique display name of the graph subject.
UserDomain	String	False		This represents the name of the container of origin for a graph member.
UserMailAddress	String	False		The email address of record for a given graph member.
UserMetaType	String	False		The meta type of the user in the origin, such as 'member', 'guest', etc.
UserOrigin	String	False		The type of source provider for the origin identifier (ex:AD, AAD, MSA).
UserOriginId	String	False		The unique identifier from the system of origin.
UserPrincipalName	String	False		This is the PrincipalName of this graph member from the source provider.
UserSubjectKind	String	False		This field identifies the type of the graph subject.
UserUrl	String	False		This url is the full route to the source resource of this graph subject.

Retrieves a list of work items. This table includes custom fields which are automatically discovered when 'IncludeCustomFields' is enabled.

Table Specific Information

Select

The Cloud uses the Azure DevOps API to process WHERE clause conditions built with the following columns and operators:

• Id supports the '=', 'IN' operators.

The rest of the filter is executed client-side in the Cloud.

For example:

SELECT * FROM WorkItems WHERE Id = 1

Insert

Inserts are not supported for this table. However, they can be performed through the CreateWorkItem stored procedure.

Update

Updates are not supported for this table. However, they can be performed through the UpdateWorkItem stored procedure.

Delete

The following is an example of deleting from the WorkItems table:

DELETE FROM WorkItems WHERE Id = 2

Note that some work items are of type TestCase or TestPlan, leading to the item being listed both there and in WorkItems. These work items must be deleted from the TestPlan or TestCase tables rather than the WorkItems table.

GetDeleted

The ChangedDate column is filterable while retrieving deleted WorkItems:

GETDELETED FROM WorkItems WHERE ChangedDate >= '2022-01-01'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		Id of the work item.
Type	String	True		Type of the work item.
State	String	True		Current state of the work item.
CreatedDate	Datetime	True		Creation date of the work item.
CreatedById	String	True		User ID of work item creator.
CreatedByDisplayName	String	True		Display name of work item creator.
CreatedByUrl	String	True		Profile link of work item creator.
ChangedDate	Datetime	True		Date of last change to the work item.
ChangedById	String	True		User ID of most recent work item editor.
ChangedByDisplayName	String	True		Display name of most recent work item editor.
ChangedByUrl	String	True		Profile link of most recent work item editor.
AssignedToId	String	True		User ID of current work item assignee.
AssignedToDisplayName	String	True		Display name of current work item assignee.
AssignedToUrl	String	True		Profile link of current work item assignee.
Links	String	True		Aggregate of the reference links.
Rev	Integer	True		Revision number of the work item.
Url	String	True		Full HTTP link URL .

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements, and offer a more granular control over the tuples that are returned from the data source. Unless otherwise specified, only the = operator is permitted when filtering on pseudocolumns.

Name	Type	Description
AsOf	Datetime	AsOf UTC date time string.
ErrorPolicy	String	The flag to control error policy in a bulk get work items request. The allowed values are fail, omit.
Expand	String	The expand parameters for work item attributes. The allowed values are all, fields, links, none, relations.

Views are similar to tables in the way that data is represented; however, views are read-only.

Queries can be executed against a view as if it were a normal table.

CData Cloud - Azure DevOps Views

Name	Description
AccessControlLists	Return a list of access control lists for the specified security namespace.
AuditLogEntries	Retrieves all audit log entries. This table includes custom fields which are automatically discovered when 'IncludeCustomFields' is enabled.
ClassificationNodesAreas	Lists classification nodes of StructureType Area for a given list of parent nodes ids. If no parent node ids are specified, the children of the root nodes will be displayed.
ClassificationNodesIterations	Lists classification nodes of StructureType Iteration for a given list of parent nodes ids. If no parent node ids are specified, the children of the root nodes will be displayed.
GroupMemberships	Get the members (MemberDescriptor) of a group (ContainerDescriptor).
Groups	Gets a list of all groups in the organization or account.
Identities	Resolve legacy identity information for use with older APIs such as the Security APIs.
ProjectProperties	Retrieves a collection of project properties, sliced across all projects.
SecurityNamespaceActions	Lists the actions that a Security Namespace is responsible for securing.
SecurityNamespaces	List security namespaces.
UserMemberships	Get the groups (ContainerDescriptor) of which a user (MemberDescriptor) is a member.
WorkItemIds	Retrieves a list of work items.
WorkItemsHistory	Retrieves a work item's history as a list.
WorkItemUpdatesHistory	Retrieves a work item's updates history as a list. The WorkItemId can be filtered server-side.

Return a list of access control lists for the specified security namespace.

Columns

Name	Type	References	Description
NamespaceId	String	SecurityNamespaces.Id	Id of the namespace.
InheritPermissions	Boolean		True if the given token inherits permissions from parents.
Token	String		The token that this AccessControlList is for.
AcesDictionary	String		Storage of permissions keyed on the identity the permission is for.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements, and offer a more granular control over the tuples that are returned from the data source. Unless otherwise specified, only the = operator is permitted when filtering on pseudocolumns.

Name	Type	Description
Descriptors	String		An optional filter containing a list of identity descriptors separated by ',' whose ACEs should be retrieved. If not specified, entire ACLs will be returned.
IncludeExtendedInfo	Boolean		If true, populate the extended information properties for the access control entries contained in the returned lists.
Recurse	String		If true and this is a hierarchical namespace, return child ACLs of the specified token.

Retrieves all audit log entries. This table includes custom fields which are automatically discovered when 'IncludeCustomFields' is enabled.

Table Specific Information

Select

The Cloud uses the Azure DevOps API to process WHERE clause conditions built with the following columns and operators:

• BatchSize supports the '=' operator.
• DownloadWindow supports the '>,>=,<,<=' operators.
• SkipAggregation supports the '=' operator.

The rest of the filter is executed client-side in the Cloud.

For example:

	
	SELECT * FROM AuditLogEntries WHERE BatchSize = 5
	SELECT * FROM AuditLogEntries WHERE DownloadWindow > '2020-04-06 05:50:00' AND DownloadWindow < '2020-04-06T06:50:00.000+00:00'

Columns

Name	Type	References	Description
Id [KEY]	String		Id of the audit log entry.
ActionId	String		The action if for the event, i.e Git.CreateRepo, Project.RenameProject.
ActivityId	String		Id of the activity.
ActorCUID	String		The actor's CUID.
ActorDisplayName	String		DisplayName of the user who initiated the action.
ActorImageUrl	String		URL of actor's profile image.
ActorUserId	String		The actor's user Id.
Area	String		Area of Azure DevOps the action occurred.
AuthenticationMechanism	String		Type of authentication used by the actor.
Category	String		Type of action executed.
CategoryDisplayName	String		DisplayName of the category.
CorrelationId	String		This allows related audit entries to be grouped together. Generally this occurs when a single action causes a cascade of audit entries. For example, project creation.
Details	String		Decorated details.
IpAddress	String		IP Address where the event was originated.
ScopeDisplayName	String		Display Name of the scope.
ScopeId	String		The organization or project Id.
ScopeType	String		The type of the scope, organization or project.
Timestamp	Datetime		The time when the event occurred in UTC.
UserAgent	String		The user agent from the request.
Data	String		External data such as CUIDs, item names, etc.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements, and offer a more granular control over the tuples that are returned from the data source. Unless otherwise specified, only the = operator is permitted when filtering on pseudocolumns.

Name	Type	Description
BatchSize	Integer		Max number of results to return.
DownloadWindow	Datetime		Start and end time of download window.
SkipAggregation	Boolean		Skips aggregating events and leaves them as individual entries instead.

Lists classification nodes of StructureType Area for a given list of parent nodes ids. If no parent node ids are specified, the children of the root nodes will be displayed.

Columns

Name	Type	References	Description
ParentId	Integer		ID of the parent classification node.
Id [KEY]	Integer		ID of the classification node.
Identifier	String		GUID of the classification node.
Name	String		Name of the classification node.
StructureType	String		Node structure type.
HasChildren	Boolean		Indicates if the classification node has any child nodes.
Attributes	String		Dictionary that has node attributes like start or finish date for iteration nodes.
Path	String		Path of the classification node.
Url	String		Url of the classification node.
ProjectId [KEY]	String		The Id of the project to which this node belongs.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements, and offer a more granular control over the tuples that are returned from the data source. Unless otherwise specified, only the = operator is permitted when filtering on pseudocolumns.

Name	Type	Description
Depth	Integer		Depth of nodes to fetch. By default only root nodes are fetched.

Lists classification nodes of StructureType Iteration for a given list of parent nodes ids. If no parent node ids are specified, the children of the root nodes will be displayed.

Columns

Name	Type	References	Description
ParentId	Integer		ID of the parent classification node.
Id [KEY]	Integer		ID of the classification node.
Identifier	String		GUID of the classification node.
Name	String		Name of the classification node.
StructureType	String		Node structure type.
HasChildren	Boolean		Indicates if the classification node has any child nodes.
Attributes	String		Dictionary that has node attributes like start or finish date for iteration nodes.
Path	String		Path of the classification node.
Url	String		Url of the classification node.
ProjectId [KEY]	String		The Id of the project to which this node belongs.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements, and offer a more granular control over the tuples that are returned from the data source. Unless otherwise specified, only the = operator is permitted when filtering on pseudocolumns.

Name	Type	Description
Depth	Integer		Depth of nodes to fetch. By default only root nodes are fetched.

Get the members (MemberDescriptor) of a group (ContainerDescriptor).

Columns

Name	Type	References	Description
ContainerDescriptor [KEY]	String	Groups.Descriptor	A descriptor to the container in the relationship. This is equivalent to the GroupDescriptor.
MemberDescriptor [KEY]	String	Users.UserDescriptor	Subject descriptor for which to fetch all direct memberships. This is equivalent to the UserDescriptor.

Gets a list of all groups in the organization or account.

Columns

Name	Type	References	Description
Descriptor [KEY]	String		The primary way to reference the graph subject while the system is running. Uniquely identifies the same graph subject across both Accounts and Organizations.
Description	String		A short phrase to help human readers disambiguate groups with similar names.
DisplayName	String		Non-unique display name of the graph subject.
Domain	String		Name of the container of origin for a graph member.
MailAddress	String		Email address of record for a given graph member.
Origin	String		Type of source provider for the origin identifier.
OriginId	String		The unique identifier from the system of origin.
PrincipalName	String		PrincipalName of this graph member from the source provider.
SubjectKind	String		Identifies the type of the graph subject.
Url	String		Full route to the source resource of this graph subject.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements, and offer a more granular control over the tuples that are returned from the data source. Unless otherwise specified, only the = operator is permitted when filtering on pseudocolumns.

Name	Type	Description
ScopeDescriptor	String		Specify a non-default scope (collection, project) to search for groups.
SubjectTypes	String		A list of user subject subtypes to reduce the retrieved results.

Resolve legacy identity information for use with older APIs such as the Security APIs.

View Specific Information

Select

The Cloud uses the Azure DevOps API to process WHERE clause conditions built with the following columns and operators:

• Id supports the '=,in' operators.
• Descriptor supports the '=,in' operators.
• SubjectDescriptor supports the '=,in' operators.
• SearchFilter supports the '=' operator. Note that the FilterValue must also be specified.
• FilterValue supports the '=' operator. Note that the SearchFilter must also be specified.

The rest of the filter is executed client-side in the Cloud.

Note that the API requires a filter, so if no filter is specified by the user, then the driver will automatically add the filter 'WHERE Id IN (SELECT Id FROM Users)'.

For example:

	
SELECT * FROM Identities WHERE Id IN ('016a5631-0d32-6644-ab69-b1baf02fdb5c','1356ed53-6784-661a-b5d1-7c080ec0c928')
SELECT * FROM Identities WHERE SubjectDescriptor = 'vssgp.Uy0xKTktMTU1MTM3NDI0RS0zMjA2MTU1Njc2LTE2Njk1MzM1MDUtMzE1NTcwNTg2Mi0yODc4NzY2ODE4LTAtMC0wLTAtMQ'
SELECT * FROM Identities WHERE SearchFilter ='General' AND FilterValue='[email protected]'

Columns

Name	Type	References	Description
Id [KEY]	String		Identity Identifier. Also called Storage Key, or VSID.
Descriptor	String		A wrapper for the identity type.
SubjectDescriptor	String		Subject descriptor of a Graph entity.
ProviderDisplayName	String		The display name for the identity as specified by the source identity provider.
IsActive	Boolean		True if the identity has a membership in any Azure Devops group in the organization.
IsContainer	Boolean		True if the identity is a group.
MemberIds	String		Id of the members of the identity (groups only).
MemberOf	String		A wrapper for the identity type (Windows SID, Passport) along with a unique identifier such as the SID or PUID.
Members	String		A wrapper for the identity type (Windows SID, Passport) along with a unique identifier such as the SID or PUID.
MetaTypeId	Integer		Meta Type Id.
ResourceVersion	Integer		Resource Version.
Properties	String		Key-value pairs for the property bag.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements, and offer a more granular control over the tuples that are returned from the data source. Unless otherwise specified, only the = operator is permitted when filtering on pseudocolumns.

Name	Type	Description
SearchFilter	String		The type of search to perform. Must be used with FilterValue. Values can be AccountName (domain\alias), DisplayName, MailAddress, General (display name, account name, or unique name), or LocalGroupName (only search Azure Devops groups). The allowed values are AccountName, DisplayName, MailAddress, General, LocalGroupName.
FilterValue	String		The search value, as specified by the searchFilter. Must be used with SearchFilter.
QueryMembership	String		The membership information to include with the identities. Values can be None for no membership data or Direct to include the groups that the identity is a member of and the identities that are a member of this identity (groups only). The allowed values are none, direct, expanded, expandedDown, expandedUp.

Retrieves a collection of project properties, sliced across all projects.

Table Specific Information

Select

The Cloud uses the Azure DevOps API to process WHERE clause conditions built with the following columns and operators:

• ProjectId supports the '=' operator.
• Name supports the '=,in' operators.

The rest of the filter is executed client-side in the Cloud.

For example:

	
	SELECT * FROM ProjectProperties WHERE ProjectId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf'
	SELECT * FROM ProjectProperties WHERE ProjectId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf' AND Name IN ('System.Process Template', 'System.CurrentProcessTemplateId')

Columns

Name	Type	References	Description
ProjectId	String	Projects.Id	Unique Id of the project.
Name	String		The name of the property.
Value	String		The value of the property.

Lists the actions that a Security Namespace is responsible for securing.

Columns

Name	Type	References	Description
NamespaceId [KEY]	String	SecurityNamespaces.Id	The unique Id of the namespace, outside of Actions.
ActionNamespaceId	String		The namespace that this action belongs to.
Bit [KEY]	Integer		The bit mask integer for this action. Must be a power of 2.
DisplayName	String		The non-localized name for this action.
Name	String		If the security tokens on which this namespace operates need to be split on certain character lengths, that length is specified here. If not, this value is -1.

List security namespaces.

Columns

Name	Type	References	Description
Id [KEY]	String		The unique Id of the namespace.
DataspaceCategory	String		Describes where the security information for this SecurityNamespace should be stored.
DisplayName	String		Localized name of the namespace.
ElementLength	Integer		If the security tokens on which this namespace operates need to be split on certain character lengths, that length is specified here. If not, this value is -1.
ExtensionType	String		Type of the extension that should be loaded from the plugins directory for extending this security namespace.
IsRemotable	Boolean		If true, the security namespace is remotable, allowing another service to proxy the namespace.
Name	String		Non-localized name of the namespace.
ReadPermission	Integer		Permission bits needed by a user in order to read security data on the Security Namespace.
SeparatorValue	String		If the security tokens on which this namespace operates need to be split on certain characters, that character is specified here. If not, this value is null.
StructureValue	Integer		Used to send information about the structure of the security namespace over the web service.
SystemBitMask	Integer		The bits reserved by system store.
UseTokenTranslator	Boolean		If true, the security service will expect an ISecurityDataspaceTokenTranslator plugin to exist for this namespace.
WritePermission	Integer		Permission bits needed by a user in order to modify security data on the Security Namespace.

Get the groups (ContainerDescriptor) of which a user (MemberDescriptor) is a member.

Columns

Name	Type	References	Description
ContainerDescriptor [KEY]	String	Groups.Descriptor	A descriptor to the container in the relationship. This is equivalent to the GroupDescriptor.
MemberDescriptor [KEY]	String	Users.UserDescriptor	Subject descriptor for which to fetch all direct memberships. This is equivalent to the UserDescriptor.

Retrieves a list of work items.

Columns

Name	Type	References	Description
Id [KEY]	Integer		Id of the work item.
Url	String		Full HTTP link URL .

Retrieves a work item's history as a list.

Table Specific Information

Select

The Cloud uses the Azure DevOps API to process WHERE clause conditions built with the following columns and operators:

• Revision supports the '=' operator.
• WorkItemId supports the '=' operator.

The rest of the filter is executed client-side in the Cloud.

For example, the following query will be filtered server-side:

SELECT * FROM WorkItemsHistory WHERE WorkItemId = 1

Columns

Name	Type	References	Description
Revision [KEY]	String		The WorkItem Revision.
RevisedById	String		Revised By Id.
WorkItemId [KEY]	Integer	WorkItemIds.Id	The WorkItem Id.
Name	String		Revised By Name.
Value	String		Work Item Value.
LinksAvatarHref	String		Revised By Links href.
Descriptor	String		Revised By Descriptor.
DisplayName	String		Revised By Display Name.
RevisedDate	String		Revised Date.
Url	String		URL.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements, and offer a more granular control over the tuples that are returned from the data source. Unless otherwise specified, only the = operator is permitted when filtering on pseudocolumns.

Name	Type	Description
AsOf	Datetime		AsOf UTC date time string.
ErrorPolicy	String		The flag to control error policy in a bulk get work items request. The allowed values are fail, omit.
Expand	String		The expand parameters for work item attributes. The allowed values are all, fields, links, none, relations.

Retrieves a work item's updates history as a list. The WorkItemId can be filtered server-side.

Columns

Name	Type	References	Description
Id [KEY]	Integer		Id
WorkItemId [KEY]	Integer	WorkItemIds.Id	Id of Workitem
PriorityNewValue	Integer		Field Value for the work item updates.
StateChangeDateNewValue	Datetime		Field Value for the work item updates.
ValueAreaNewValue	String		Field Value for the work item updates.
AreaIdNewValue	Integer		Field Value for the work item updates.
AreaLevel1NewValue	String		Field Value for the work item updates.
AreaPathNewValue	String		Field Value for the work item updates.
AuthorizedAsDescriptor	String		Field Value for the work item updates.
AuthorizedAsDisplayName	String		Field Value for the work item updates.
AuthorizedAsId	String		Field Value for the work item updates.
AuthorizedAsurl	String		Field Value for the work item updates.
AuthorizedDateNewValue	Datetime		Field Value for the work item updates.
ChangedByDescriptor	String		Field Value for the work item updates.
ChangedByDisplayName	String		Field Value for the work item updates.
ChangedById	String		Field Value for the work item updates.
ChangedByUrl	String		Field Value for the work item updates.
ChangedDateNewValue	Datetime		Field Value for the work item updates.
CommentCountNewValue	Integer		Field Value for the work item updates.
CreatedByDescriptor	String		Field Value for the work item updates.
CreatedByDisplayName	String		Field Value for the work item updates.
CreatedById	String		Field Value for the work item updates.
CreatedByUrl	String		Field Value for the work item updates.
CreatedDateNewValue	Datetime		Field Value for the work item updates.
NodeNameNewValue	String		Field Value for the work item updates.
PersonIdNewValue	Integer		Field Value for the work item updates.
ReasonNewValue	String		Field Value for the work item updates.
RevNewValue	Integer		Field Value for the work item updates.
RevisedDate.newValue	Datetime		Field Value for the work item updates.
StateNewValue	String		Field Value for the work item updates.
TeamProjectNewValue	String		Field Value for the work item updates.
TitleNewValue	String		Field Value for the work item updates.
WatermarkNewValue	Integer		Field Value for the work item updates.
WorkItemTypeNewValue	String		Field Value for the work item updates.
Revision	Integer		Revision
RevisedByDescriptor	String		Field Value for the work item updates.
RevisedByDisplayName	String		Field Value for the work item updates.
RevisedById	String		Field Value for the work item updates.
RevisedByName	String		Field Value for the work item updates.
RevisedByUrl	String		Field Value for the work item updates.
RevisedDate	Datetime		Field Value for the work item updates.
Url	String		Field Value for the work item updates.
Relations	String		Relations in work items updates history

Stored procedures are function-like interfaces that extend the functionality of the Cloud beyond simple SELECT/INSERT/UPDATE/DELETE operations with Azure DevOps.

Stored procedures accept a list of parameters, perform their intended function, and then return any relevant response data from Azure DevOps, along with an indication of whether the procedure succeeded or failed.

CData Cloud - Azure DevOps Stored Procedures

Name	Description
AddBuildTag	Add tag to a build.
CloneTestCase	Clones a test case.
CloneTestPlan	Clones a test plan.
CloneTestSuite	Clones a test suite.
CreatePullRequest	Create a pull request.
CreatePullRequestAttachment	Create Pull Request Attachment.
CreateWorkItem	Create a work item.
DeleteBuildTag	Delete tag from a build.
DeletePullRequestAttachment	Delete Pull Request Attachment.
DeleteTestCase	Deletes the test case
GetDescriptor	Resolve a storage key to a descriptor.
GetPullRequestCommits	Get the commits for the specified pull request.
PushChanges	Pushes changes to a repository in your Azure DevOps instance.
RunPipeline	Runs a pipeline with the specified configuration.
SetProjectProperties	Create, update, and delete team project properties.
UpdatePullRequest	Update a pull request.
UpdateUser	Update a user.
UpdateWorkItem	Update a work item.

Add tag to a build.

Input

Name	Type	Required	Description
ProjectId	String	True	Id of the project.
BuildId	String	True	Id of the build.
Tag	String	True	Tag to add.

Result Set Columns

Name	Type	Description
Status	String	Stored procedure execution status.

Clones a test case.

Input

Name	Type	Required	Description
ProjectId	String	False	Id of the Project.
IncludeAttachments	Boolean	False	If set to true, include the attachments. The default value is false.
IncludeLinks	Boolean	False	If set to true, include the links. The default value is false.
RelatedLinkComment	String	False	Comment on the link that will link the new clone test case to the original.
DestinationTestPlanId	Integer	True	Id of the destination test plan.
DestinationTestSuiteId	Integer	True	Id of the destination test suite.
SourceTestPlanId	Integer	True	Id of the destination test plan.
SourceTestSuiteId	Integer	True	Id of the source test suite.
TestCaseId	Integer	True	Comma-separated list of Test Case Ids to clone.

Result Set Columns

Name	Type	Description
CloneOperationId	Integer	Stored procedure execution status.
CloneOperationState	String	Stored procedure execution status.

Clones a test plan.

Input

Name	Type	Required	Description
ProjectId	String	False	Id of the Project.
DeepClone	Boolean	False	Clones all the associated test cases as well.
CloneRequirements	Boolean	False	If set to true, requirements will be cloned. The default value is false.
CopyAllSuites	Boolean	False	Copy all suites from a source plan.
CopyAncestorHierarchy	Boolean	False	Copy ancestor hierarchy.
DestinationWorkItemType	String	False	Name of the workitem type of the clone.
OverrideParameters	String	False	Key value pairs where the key value is overridden by the value.
RelatedLinkComment	String	False	Comment on the link that will link the new clone test case to the original.
DestinationPlanAreaPath	String	False	Area of the Test Plan.
DestinationPlanBuildId	String	False	Build to be tested.
DestinationPlanDescription	String	False	Description of the test plan.
DestinationPlanEndDate	String	False	End date for the test plan.
DestinationPlanIteration	String	False	Iteration path of the test plan.
DestinationPlanName	String	True	Name of the test plan.
DestinationPlanOwnerId	String	False	User Id of the owner of the test plan.
DestinationPlanProject	String	False	Destination project name.
DestinationPlanStartDate	String	False	Start date for the test plan.
DestinationPlanState	String	False	State of the test plan.
DestinationPlanSync	Boolean	False	Value to configure how same tests across test suites under a test plan need to behave.
SourceTestPlanId	Integer	True	Id of the source test plan.
SourceTestPlanSuiteId	Integer	False	Comma-separated list of Test Suite Ids to clone inside source Test Plan.

Result Set Columns

Name	Type	Description
CloneOperationId	Integer	Stored procedure execution status.
CloneOperationState	String	Stored procedure execution status.

Clones a test suite.

Input

Name	Type	Required	Description
ProjectId	String	False	Id of the Project.
DeepClone	Boolean	False	Clones all the associated test cases as well.
CloneRequirements	Boolean	False	If set to true, requirements will be cloned. The default value is false.
CopyAllSuites	Boolean	False	Copy all suites from a source plan.
CopyAncestorHierarchy	Boolean	False	Copy ancestor hierarchy.
DestinationWorkItemType	String	False	Name of the workitem type of the clone.
OverrideParameters	String	False	Key value pairs where the key value is overridden by the value.
RelatedLinkComment	String	False	Comment on the link that will link the new clone test case to the original.
DestinationTestSuiteId	Integer	True	Id of the destination test suite.
DestinationProjectName	String	False	Destination project name.
SourceTestSuiteId	Integer	True	Id of the source test suite.

Result Set Columns

Name	Type	Description
CloneOperationId	Integer	Stored procedure execution status.
CloneOperationState	String	Stored procedure execution status.

Create a pull request.

Input

Name	Type	Required	Description
ProjectId	String	True	Id of the project.
RepositoryId	String	True	Id of the repository.
Title	String	True	Title of the pull request.
Description	String	False	The description of the pull request.
SourceRefName	String	True	The name of the source branch of the pull request.
TargetRefName	String	True	The name of the target branch of the pull request.
CompletionOptions	String	False	Options which affect how the pull request will be merged when it is completed.
IsDraft	Boolean	False	Draft / WIP pull request.
Labels	String	False	The labels associated with the pull request.
MergeOptions	String	False	Options used when the pull request merge runs.
Reviewers	String	False	A list of reviewers on the pull request.
WorkItemRefs	String	False	Any work item references associated with this pull request.

Result Set Columns

Name	Type	Description
Status	String	Stored procedure execution status.
PullRequestId	Integer	Id of the created pull request.

Create Pull Request Attachment.

Input

Name	Type	Required	Description
ProjectId	String	False	Id of the project.
RepositoryId	String	True	Id of the repository.
PullRequestId	Integer	True	Id of the pull request.
FileName	String	True	Name of the attachment.
FileLocation	String	False	Location of the file. Cannot include the file name.

Result Set Columns

Name	Type	Description
Status	String	Stored procedure execution status.

Create a work item.

Input

Name	Type	Required	Description
ProjectId	String	True	Id of the Project.
Type	String	True	The work item type of the work item to create.
From	String	False	The path to copy from for the Move/Copy operation.
Op	String	False	The patch operation. Possible values: add, copy, move, remove, replace, test.
Path	String	False	The path for the operation.
Value	String	False	The value for the operation.
BulkArgs	String	False	Set this to a temporary table to specify multiple values for multiple actions. Note that if this input is specified, the inputs From, Op, Path, and Value must not be set.

Result Set Columns

Name	Type	Description
Status	String	Stored procedure execution status.
WorkItemId	String	Newly-created work item id.

Delete tag from a build.

Input

Name	Type	Required	Description
ProjectId	String	True	Id of the project.
BuildId	String	True	Id of the build.
Tag	String	True	Tag to remove.

Result Set Columns

Name	Type	Description
Status	String	Stored procedure execution status.

Delete Pull Request Attachment.

Input

Name	Type	Required	Description
ProjectId	String	False	Id of the project.
RepositoryId	String	True	Id of the repository.
PullRequestId	Integer	True	Id of the pull request.
FileName	String	True	Name of the attachment.

Result Set Columns

Name	Type	Description
Status	String	Stored procedure execution status.

Deletes the test case

Input

Name	Type	Required	Description
ProjectId	String	True	Id of the project.
TestCaseId	Integer	True	Id of the test case.

Result Set Columns

Name	Type	Description
Status	String	Stored procedure execution status

Resolve a storage key to a descriptor.

Input

Name	Type	Required	Description
StorageKey	String	True	Storage key of the subject (user, group, scope, etc.) to resolve.

Result Set Columns

Name	Type	Description
Value	String	Descriptor value.

Get the commits for the specified pull request.

Input

Name	Type	Required	Description
ProjectId	String	True	Id or name of project.
RepositoryId	String	True	Id or name of repository. Must be set to repository id if ProjectId not set.
PullRequestId	String	True	Id of the Pull Request

Result Set Columns

Name	Type	Description
CommitId	String	Id of generated Commit.
AuthorName	String	Name of file author.
AuthorEmail	String	Email address of file author.
AuthoredDate	Datetime	Creation date of affected file.
CommitterName	String	Name of commit author.
CommitterEmail	String	Email address of commit author.
CommittedDate	Datetime	Date of commit creation.
Comment	String	Comment for the commit
CommentTruncated	String	Whether the comment is truncated or not.
Url	String	URL of generated commit object.

Pushes changes to a repository in your Azure DevOps instance.

Input

Name	Type	Required	Description
ProjectId	String	False	Id or name of project.
RepositoryId	String	True	Id or name of repository. Must be set to repository id if ProjectId not set.
BranchRefName	String	True	Fully resolved name of git branch.
OldObjectId	String	True	Current Object ID of branch.
CommitComment	String	True	Comment to be sent with commit.
ChangesAggregate	String	True	Aggregate object which holds details of each change being made in the commit.

Result Set Columns

Name	Type	Description
NewObjectId	String	Object Id of repository after commit has completed.
CommitId	String	Id of generated Commit.
CommitTreeId	String	Id of commit tree for generated commit.
AuthorName	String	Name of file author.
AuthorEmail	String	Email address of file author.
AuthoredDate	Datetime	Creation date of affected file.
CommitterName	String	Name of commit author.
CommitterEmail	String	Email address of commit author.
CommittedDate	Datetime	Date of commit creation.
CommitUrl	String	URL of generated commit object.
RepositoryId	String	Id of affected repository.
RepositoryName	String	Name of affected repository.
PushId	String	Id of push to repository.
PushDate	String	Date of push to repository.
PushURL	String	URL of push to repository.

Runs a pipeline with the specified configuration.

Input

Name	Type	Required	Description
ProjectId	String	True	Id or name of the project.
PipelineId	Integer	True	Id of the pipeline.
PipelineVersion	Integer	False	The version of the pipeline to run.
Resources	String	False	The resources the run requires.
Variables	String	False	The list of variables and values for use during pipeline execution.
StagesToSkip	String	False	Array of stage names to skip.
TemplateParameters	String	False	Key-Value map of parameter values for use during pipeline execution.

Result Set Columns

Name	Type	Description
State	String	Current state of pipeline execution on server.
RunId	String	Id of generated Run object.
RunName	String	Name of generated Run object.
RunHref	String	Link to generated Run object.
CreatedDate	Datetime	Creation date of generated Run object.

Create, update, and delete team project properties.

Input

Name	Type	Required	Description
ProjectId	String	True	Id of the Project.
From	String	False	The path to copy from for the Move/Copy operation.
Op	String	False	The patch operation. Possible values: add, copy, move, remove, replace, test.
Path	String	False	The path for the operation.
Value	String	False	The value for the operation.
BulkArgs	String	False	Set this to a temporary table to specify multiple values for multiple actions. Note that if this input is specified, the inputs From, Op, Path, and Value must not be set.

Result Set Columns

Name	Type	Description
Status	String	Stored procedure execution status.

Update a pull request.

Input

Name	Type	Required	Description
ProjectId	String	True	Id of the project.
RepositoryId	String	True	Id of the repository.
PullRequestId	String	True	Id of the pull request.
Title	String	False	Title of the pull request.
Description	String	False	The description of the pull request.
Status	String	False	Status of the pull request.
TargetRefName	String	False	The name of the target branch of the pull request. Only available when the PR retargeting feature is enabled.
CompletionOptions	String	False	Options which affect how the pull request will be merged when it is completed.
MergeOptions	String	False	Options used when the pull request merge runs.
AutoCompleteSetById	String	False	Id of the user who enabled Autocomplete.

Result Set Columns

Name	Type	Description
Status	String	Stored procedure execution status.
PullRequestId	Integer	Id of the created pull request.

Update a user.

Input

Name	Type	Required	Description
UserId	String	True	Id of the User.
From	String	False	The path to copy from for the Move/Copy operation.
Op	String	False	The patch operation. Possible values: add, copy, move, remove, replace, test.
Path	String	False	The path for the operation.
Value	String	False	The value for the operation.
BulkArgs	String	False	Set this to a temporary table to specify multiple values for multiple actions. Note that if this input is specified, the inputs From, Op, Path, and Value must not be set.

Result Set Columns

Name	Type	Description
Status	String	Stored procedure execution status.

Update a work item.

Input

Name	Type	Required	Description
ProjectId	String	True	Id of the Project.
WorkItemId	String	True	Id of the Work Item.
From	String	False	The path to copy from for the Move/Copy operation.
Op	String	False	The patch operation. Possible values: add, copy, move, remove, replace, test.
Path	String	False	The path for the operation.
Value	String	False	The value for the operation.
BulkArgs	String	False	Set this to a temporary table to specify multiple values for multiple actions. Note that if this input is specified, the inputs From, Op, Path, and Value must not be set.

Result Set Columns

Name	Type	Description
Status	String	Stored procedure execution status.

When Catalog is set to a project, the following data is accessible: Project Data Model, Repository Data Model, and Analytics Data Model.

Overview

This section shows the available API objects and provides more information on executing SQL to Azure DevOps APIs. Note that this schema can only be accessed when Catalog is set to a project and Schema is set to 'Project'.

Key Features

• The Cloud models Azure DevOps entities like builds, environments, and feeds as tables and views, allowing you to write SQL to query Azure DevOps data.
• Stored procedures allow you to execute operations to Azure DevOps
• Live connectivity to these objects means any changes to your Azure DevOps account are immediately reflected when using the Cloud.

Tables

Tables describes the available tables. The provider models the data in Azure DevOps into a list of tables that can be queried using standard SQL statements.

Views

Views describes the available views. Unlike tables, views are read-only. Dynamic views beginning with 'Query_' and 'Backlog_' are also supported.

Stored Procedures

Stored Procedures are SQL scripts that extend beyond standard CRUD operations. They allow you to execute operations to Azure DevOps, such as cloning a test case.

The Cloud models the data in Azure DevOps as a list of tables in a relational database that can be queried using standard SQL statements.

CData Cloud - Azure DevOps Tables

Name	Description
BuildDefinitionDrafts	Retrieves a list of drafts associated with the specific definition.
BuildDefinitions	Retrieves a list of build definitions.
Builds	Retrieves a list of builds.
Dashboards	Retrieves a list of dashboards and details for a specific dashboard.
DeploymentGroups	Retrieves a list of all deployment groups.
Environments	Retrieves environments.
Feeds	Retrieves all feeds in an account.
FeedViews	Retrieves all views for the specific feed.
GitBranches	Retrieves a collection of git branch, sliced across all repositories.
Pipelines	Retrieves a list of pipelines
PullRequestReviewers	Retrieves a list of reviewers for the specific pull request, sliced across all repositories.
Pushes	Retrieves pushes associated with a repository, sliced across all repositories.
Queries	Retrieves the root queries and their children.
ReleaseApprovals	Retrieves a list of approvals..
ReleaseDefinitionArtifacts	Retrieves a list of release definition artifacts.
ReleaseDefinitions	Retrieves a list of release definitions.
ReleaseEnvironments	Retrieves a list of releases.
Releases	Retrieves a list of releases.
Repositories	Generated schema file..
TaskGroups	Retrieves a list of task groups.
TeamIterations	Retrieve a team's iteration.
Teams	Retrieves a list of all teams and details of specified team.
TeamSettings	Retrieves settings for a team.
TestConfigurations	Retrieves a list test configurations.
TestPlans	Get a list of test plans and details of specific test plan.
TestResults	Retrieves test results for a test run.
TestRuns	Retrieves a list of test runs.
TestSessions	Retrieves a list of test sessions.
TestSuites	Retrieves all test suites.
TestVariables	Retrieves a list of test variables.
VariableGroups	Retrieves a list of variable groups.
Widgets	Retrieves a list of dashboard widgets and details for a specific widget.
WikiPages	Retrieves metadata or content of the wiki page for the provided path.
Wikis	Retrieves all wikis in a project or collection.
WorkItemComments	Retrieves a list of work item comments
WorkItems	Retrieves a list of work items. This table includes custom fields which are automatically discovered when 'IncludeCustomFields' is enabled.

Retrieves a list of drafts associated with the specific definition.

Table Specific Information

Select

The Cloud uses the Azure DevOps API to process WHERE clause conditions built with the following columns and operators:

• DefinitionId supports the '=' operator.
• ProjectId supports the '=' operator.

The rest of the filter is executed client-side in the Cloud.

NOTE: DefinitionId is required in order to query BuildDefinitionDrafts.

For example:

    SELECT * FROM BuildDefinitionDrafts WHERE ProjectId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf' AND DefinitionId = 2

Update

The following is an example of updating a BuildDefinitionDrafts table:

UPDATE BuildDefinitionDrafts SET Name = 'Shubham2', revision = 1, RepositoryId = 'e50698d4-bb6e-400f-a1a0-5f4d17517d9e', ProcessYamlFilename = 'data.txt', RepositoryType = 'TfsGit' WHERE ProjectId = 'b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937' AND DefinitionId = '1'

Delete

The following is an example of deleting data in a BuildDefinitionDrafts table:

DELETE FROM BuildDefinitionDrafts WHERE ProjectId = 'b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937' AND Definitionid = '1'

Columns

Name	Type	ReadOnly	References	Description
ProjectId	String	True		Id of the project.
DefinitionId	Integer	False	BuildDefinitions.Id	Id of the build definition.
CreatedDate	Integer	False		The date this version of the definition was created.
Id [KEY]	Integer	True		The Id of the referenced definition.
Name	String	False		The name of the referenced definition.
Path	String	False		The folder path of the definition.
QueueStatus	String	False		A value that indicates whether builds can be queued against this definition.
Revision	Integer	False		The definition revision number.
Type	String	False		The type of the definition.
Uri	String	False		The definition's URI.
Url	String	False		The REST URL of the definition.
RepositoryId	String	False	Repositories.Id	The ID of the repository.
RepositoryType	String	False		The type of the repository.
ProcessType	Integer	False		The process type.
ProcessYamlFilename	String	False		The process YAML file name.

Retrieves a list of build definitions.

Table Specific Information

Select

The Cloud uses the Azure DevOps API to process WHERE clause conditions built with the following columns and operators:

• Id supports the '=,in' operators.
• ProjectId supports the '=' operator.
• Name supports the '=' operator.
• Path supports the '=' operator.
• ProcessType supports the '=' operator.
• ProcessYamlFileName supports the '=' operator.
• Properties supports the '=,in' operators.
• RepositoryId supports the '=' operator.
• RepositoryType supports the '=' operator.
• RevisionNum supports the '=' operator and filters the Revision column, but only when the Id is also specified.
• BuildDate supports the '<,<=,>,>=' operators.
• MinMetricsTime supports the '=' operator.
• IncludeLatestBuilds supports the '=' operator.
• TaskId supports the '=' operator.
• IncludeAllProperties supports the '=' operator.

The rest of the filter is executed client-side in the Cloud.

For example:

    SELECT * FROM BuildDefinitions WHERE ProjectId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf'
	SELECT * FROM BuildDefinitions WHERE ProjectId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf' AND Id IN (3, 4, 5)
	SELECT * FROM BuildDefinitions WHERE ProjectId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf' AND Path = '\"'
	SELECT * FROM BuildDefinitions WHERE Id = 298 AND RevisionNum = 1

Insert

The following is an example of inserting into BuildDefinitions table:

INSERT INTO BuildDefinitions (Name, ProjectId, RepositoryType, ProcessYamlFilename, RepositoryId, Tags) VALUES (cdata, 'b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937', TfsGit, 'data.txt', 'e50698d4-bb6e-400f-a1a0-5f4d17517d9e', '1, 2, 3')

Update

The following is an example of updating a BuildDefinitions table:

UPDATE BuildDefinitions SET Name = 'Shubham1id', Revision = 1, RepositoryId = 'e50698d4-bb6e-400f-a1a0-5f4d17517d9e', RepositoryType = 'TfsGit', ProcessYamlFilename = 'data.txt' WHERE ProjectId = 'b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937' AND Id = 4

Delete

The following is an example of deleting data in a BuildDefinitions table:

DELETE FROM BuildDefinitions WHERE ProjectId = 'b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937' AND Id = 4

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		Id of the build definition.
Links	String	True		Aggregate of the reference links.
AuthoredByDisplayName	String	False		This is the non-unique display name of the user.
AuthoredById	String	False		Id of the user.
AuthoredByUrl	String	False		The URL Of the user.
BadgeEnabled	Boolean	False		Indicates whether the badge is enabled.
CreatedDate	Datetime	False		The date this version of the definition was created.
DraftOfCreatedDate	Integer	False		The date this version of the definition was created.
DraftOfId	Integer	False		The Id of the referenced definition.
DraftOfName	String	False		The name of the referenced definition.
DraftOfPath	String	False		The folder path of the definition.
DraftOfProjectId	String	False		Id of the Project of the referenced Definition.
DraftOfQueueStatus	String	False		A value that indicates whether builds can be queued against this definition.
DraftOfRevision	Integer	False		The definition revision number.
DraftOfType	String	False		The type of the definition.
DraftOfUri	String	False		The Definition's URI.
DraftOfUrl	String	False		The REST URL of the definition.
JobAuthorizationScope	String	False		The job authorization scope for builds queued against this definition. Only available if the filter IncludeAllProperties=true is set.
JobCancelTimeoutInMinutes	Integer	False		The job cancel timeout (in minutes) for builds cancelled by user for this definition. Only available if the filter IncludeAllProperties=true is set.
JobTimeoutInMinutes	Integer	False		The job execution timeout (in minutes) for builds queued against this definition. Only available if the filter IncludeAllProperties=true is set.
LatestBuildId	Integer	False	Builds.Id	Id of the latest build.
LatestCompletedBuildId	Integer	False		Id of the latest completed build.
Name	String	False		The name of the referenced definition.
Path	String	False		The folder path of the definition.
ProcessType	Integer	False		The process type. Only available if the filter IncludeAllProperties=true is set.
ProcessYamlFilename	String	False		The process YAML file name. Only available if the filter IncludeAllProperties=true is set.
ProjectId	String	False		Project identifier.
Properties	String	False		Properties of the build definition. Only available if the filter IncludeAllProperties=true is set.
Quality	String	False		The quality of the definition document (draft, etc.).
QueueLinksSelfHref	String	True		Queue self reference link.
QueueId	Integer	False		The ID of the queue.
QueueName	String	False		The name of the queue.
QueuePoolId	Integer	False		The pool Id.
QueuePoolIsHosted	Boolean	False		A value indicating whether or not this pool is managed by the service.
QueuePoolName	String	False		The pool name.
QueueUrl	String	False		The full http link to the resource.
QueueStatus	String	False		A value that indicates whether builds can be queued against this definition.
RepositoryCheckoutSubmodules	Boolean	False		Indicates whether to checkout submodules. Only available if the filter IncludeAllProperties=true is set.
RepositoryClean	String	False		Indicates whether to clean the target folder when getting code from the repository.
RepositoryId	String	False	Repositories.Id	The ID of the repository. Only available if the filter IncludeAllProperties=true is set.
RepositoryType	String	False		The type of the repository. Only available if the filter IncludeAllProperties=true is set.
Revision	Integer	False		The definition revision number.
Tags	String	False		The tags associated with this definition. Only available if the filter IncludeAllProperties=true is set.
Triggers	String	False		The build triggers. Only available if the filter IncludeAllProperties=true is set.
Type	String	False		The type of the definition.
Uri	String	False		The definition's URI.
Url	String	False		The REST URL of the definition.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements, and offer a more granular control over the tuples that are returned from the data source. Unless otherwise specified, only the = operator is permitted when filtering on pseudocolumns.

Name	Type	Description
RevisionNum	Integer	The definition revision number, tied to the Revision. This filter is ignored if the Id is not specified.
BuildDate	Datetime	If specified, filters to definitions that have builds before or after this date.
MinMetricsTime	Datetime	If specified, indicates the date from which metrics should be included.
IncludeLatestBuilds	Boolean	Indicates whether latest builds should be included.
TaskId	String	If specified, filters to definitions that use the specified task.
IncludeAllProperties	Boolean	Indicates whether the full definitions should be returned.

Retrieves a list of builds.

Table Specific Information

Select

The Cloud uses the Azure DevOps API to process WHERE clause conditions built with the following columns and operators:

• Id supports the '=,in' operators.
• ProjectId supports the '=' operator.
• BuildNumber supports the '=' operator.
• DefinitionId supports the '=,in' operators.
• FinishTime supports the '>,>=,<,<=' operators.
• QueueTime supports the '>,>=,<,<=' operators.
• Reason supports the '=' operator.
• RepositoryId supports the '=' operator.
• RepositoryType supports the '=' operator.
• Result supports the '=' operator.
• SourceBranch supports the '=' operator.
• StartTime supports the '<,<=,>,>=' operators.
• Status supports the '=' operator.
• Tags supports the '=,in' operators.
• DeletedFilter supports the '=' operator.
• Properties supports the '=,in' operators.
• QueueId supports the '=' operator.
• RequestedForId supports the '=' operator.
• MaxBuildsPerDefinition supports the '=' operator.

The rest of the filter is executed client-side in the Cloud.

For example:

	SELECT * FROM Builds WHERE ProjectId = '66eb7414-f622-4eff-88da-3ad681f19073'
	SELECT * FROM Builds WHERE ProjectId = '66eb7414-f622-4eff-88da-3ad681f19073' AND BuildNumber = '20200401.1'
	SELECT * FROM Builds WHERE ProjectId = '66eb7414-f622-4eff-88da-3ad681f19073' ORDER BY StartTime ASC
	SELECT * FROM Builds WHERE ProjectId = '66eb7414-f622-4eff-88da-3ad681f19073' AND Id IN (2, 3, 4)
	SELECT * FROM Builds WHERE ProjectId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf' AND FinishTime > '2020-04-06 05:50:00' AND FinishTime < '2020-04-06 06:50:00'

Update

The following is an example of updating a Builds table:

UPDATE Builds SET Reason = 'cdata2' WHERE ProjectId = 'b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937' AND Id = '1'

Delete

The following is an example of deleting data in a Builds table:

DELETE FROM Builds WHERE ProjectId = 'b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937' AND Id = '1'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		Id of the Build.
AgentSpecificationIdentifier	String	False		The agent specification for the build.
BuildNumber	String	False		The build number/name of the build.
BuildNumberRevision	Integer	False		The build number revision.
ControllerCreatedDate	Datetime	False		The date the controller was created. This is only set if definition type is XAML.
ControllerDescription	String	False		The description of the controller. This is only set if definition type is XAML.
ControllerEnabled	Boolean	False		Indicates whether the controller is enabled. This is only set if definition type is XAML.
ControllerId	Integer	False		Id of the build controller. This is set only if definition type is XAML.
ControllerName	String	False		Name of the controller. This is set only if definition type is XAML.
ControllerStatus	String	False		Status of the controller. This is set only if definition type is XAML.
ControllerUpdatedDate	Datetime	False		The date the controller was last updated. This is only set if definition type is XAML.
ControllerUri	String	False		The controller URI. This is only set if definition type is XAML.
ControllerUrl	String	False		Full Http Link to the resource. This is set only if definition type is XAML.
DefinitionId	Integer	False	BuildDefinitions.Id	The Id of the definition associated with the build.
Deleted	Boolean	False		Indicates whether the build has been deleted.
DeletedByDisplayName	String	False		This is the non-unique display name of the user.
DeletedById	String	False		The Id of the user.
DeletedDate	Datetime	False		The date the build was deleted.
DeletedReason	String	False		The description of how the build was deleted.
FinishTime	Datetime	False		The time that the build was completed.
KeepForever	Boolean	True		Indicates whether the build should be skipped by retention policies.
LastChangedByDisplayName	String	False		This is the non-unique display name of the user.
LastChangedById	String	False		The Id of the user.
LastChangedDate	Datetime	False		The date the build was last changed.
Links	String	True		Aggregate of the reference links.
LogsId	Integer	False		The Id of the log.
LogsType	String	False		The type of the log location.
LogsUrl	String	False		A full link to the log resource.
OrchestrationPlanId	String	False		The ID of the plan.
OrchestrationPlanType	Integer	False		The type of the plan.
Parameters	String	False		The parameters for the build.
Priority	String	False		The build's priority.
ProjectId	String	True		Project identifier. Can be either the id or name.
Properties	String	False		The class represents a property bag as a collection of key-value pairs.
Quality	String	False		The quality of the XAML build (good, bad, etc.).
QueueId	Integer	False		The Id of the queue.
QueueName	String	False		The name of the queue.
QueuePoolId	Integer	False		The pool Id.
QueuePoolIsHosted	Boolean	False		A value indicating whether or not this pool is managed by the service.
QueuePoolName	String	False		The pool name.
QueueOptions	String	False		Additional options for queueing the build.
QueuePosition	Integer	False		The current position of the build in the queue.
QueueTime	Datetime	False		The time that the build was queued.
Reason	String	False		The reason that the build was create. The allowed values are all, batchedCI, buildCompletion, checkInShelveset, individualCI, manual, none, pullRequest, schedule, scheduleForced, triggered, userCreated, validateShelveset.
RepositoryId	String	False	Repositories.Id	The Id of the repository.
RepositoryType	String	False		Type of the repository.
RequestedByDisplayName	String	False		This is the non-unique display name of the user.
RequestedById	String	False		The Id of the user.
RequestedForDisplayName	String	False		This is the non-unique display name of the user.
RequestedForId	String	False		The Id of the user.
Result	String	False		The build result. The allowed values are canceled, failed, none, partiallySucceeded, succeeded.
RetainedByRelease	Boolean	False		Indicates whether the build is retained by a release.
SourceBranch	String	False		The source branch.
SourceVersion	String	False		The source version.
SourceSha	String	False		The SHA checksum of the action which triggered the build.
StartTime	Datetime	False		The time that the build was started.
Status	String	False		The status of the build. The allowed values are all, cancelling, completed, inProgress, none, notStarted, postponed.
Tags	String	False		The tags associated with this build.
TriggerMessage	String	False		Commit message of the action which triggered the build.
TriggerRepository	String	False		Repository Id of the commit which triggered the build.
TriggeredByBuildId	Integer	False		The build that triggered this build via a Build completion trigger.
Uri	String	False		The URI of the build.
Url	String	False		The REST URL of the build.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements, and offer a more granular control over the tuples that are returned from the data source. Unless otherwise specified, only the = operator is permitted when filtering on pseudocolumns.

Name	Type	Description
DeletedFilter	String	Indicates whether to exclude, include, or only return deleted builds. The allowed values are excludeDeleted, includeDeleted, onlyDeleted.
MaxBuildsPerDefinition	Integer	The maximum number of builds to return per definition.