このページでは、CData Cloud でのAzure DevOps への接続の確立 のガイド、利用可能なリソースに関する情報、および使用可能な接続プロパティのリファレンスについて説明します。

Azure DevOps への接続

接続の確立 は、CData Cloud にデータベースを作成するためのAzure DevOps への認証方法と必要な接続プロパティの設定方法について示します。

CData Cloud サービスからデータにアクセス

利用可能な標準サービスを経由してAzure DevOps からデータにアクセスする方法と、CData Cloud の管理については、CData Cloud ドキュメント で詳しく説明します。

Database タブで対応するアイコンを選択して、Azure DevOps に接続します。必須プロパティはSettings にリストされています。Advanced タブには、通常は必要ない接続プロパティが表示されます。

Azure DevOps への接続

Azure DevOps アカウントに接続するには、Profile -> Organizations に移動し、アカウントの組織名を取得します。Organization プロパティをこの値に設定します。

Note： 複数のカタログやスキーマに存在するテーブル名もあります。テーブルをクエリする際は、Catalog およびSchema 接続プロパティ、または完全修飾テーブル名のいずれかでカタログとスキーマを指定する必要があります。

Azure DevOps への認証

Azure DevOps は、Basic 認証とAzure AD（OAuth ベース）認証の両方をサポートします。

Basic

Basic 認証でAzure DevOps に接続する場合、Organization とPersonalAccessToken の両方を指定します。

パーソナルアクセストークンを生成するには、Azure DevOps 組織アカウントにログインし、Profile -> Personal Access Tokens -> New Token に移動します。生成されたトークンが表示されます。

Entra ID（Azure AD）

Note：Microsoft はAzure AD をEntra ID にリブランドしました。ユーザーがEntra ID 管理サイトを操作する必要があるトピックでは、Microsoft が使用している名称と同じものを使用します。ただし、名前または値が"Azure AD" を参照しているCData 接続プロパティは、依然として存在します。

Microsoft Entra ID は、マルチテナント型のクラウドベースのID およびアクセス管理プラットフォームです。 OAuth ベースの認証フローに対応しており、ドライバーによるAzure DevOps エンドポイントへのセキュアなアクセスを実現します。

Web アプリケーションを介したEntra ID への認証には、必ずはじめにカスタムOAuth アプリケーションを作成して登録する必要があります。 これにより、アプリケーションは独自のリダイレクトURI を定義し、クレデンシャルのスコープを管理し、組織固有のセキュリティポリシーに準拠することができるようになります。

カスタムOAuth アプリケーションの作成および登録方法の詳細については、Entra ID（Azure AD）アプリケーションの作成 を参照してください。

AuthScheme をAzureAD に設定した後の認証手順は、環境によって異なります。 デスクトップアプリケーション、Web ベースのワークフロー、またはヘッドレスシステムから接続する方法の詳細については、以下のセクションを参照してください。

Entra ID（Azure AD）アプリケーションの作成

Note：Microsoft はAzure AD をEntra ID にリブランドしました。ユーザーがEntra ID 管理サイトを操作する必要があるトピックでは、Microsoft が使用している名称と同じものを使用します。ただし、名前または値が"Azure AD" を参照しているCData 接続プロパティは、依然として存在します。

Azure DevOps は、Microsoft Entra ID を使用したOAuth ベースの認証をサポートします。 Web アプリケーション経由で接続してEntra ID で認証したい場合は、以下に説明するように、まずEntra 管理センターでカスタムOAuth アプリケーションを登録する必要があります。

アプリケーションの登録

Microsoft Entra ID でOAuth アプリケーションを登録するには、次の手順に従います。

1. https://portal.azure.com に移動します。
2. 左側のナビゲーションペインでMicrosoft Entra ID ->アプリの登録を選択します。
3. 新規登録をクリックします。
4. アプリケーションの名前を入力します。
5. このアプリケーションがサポートすべきアカウントの種類を指定します。
   • 個人使用のアプリケーションの場合は、この組織ディレクトリ内のアカウントのみを選択します。
   • 配布アプリケーションの場合は、マルチテナントオプションのいずれかを選択します。

   Note：この組織ディレクトリ内のアカウントのみを選択する場合は、 CData Cloud への接続の際にAzureTenant をテナントのId（GUID または検証済みドメイン）に設定する必要があります。 そうでない場合、認証は失敗します。

6. Select a platform をWeb に設定し、リダイレクトURI をhttp://localhost:33333（デフォルト）、またはデプロイに適した別のURI に設定してください。 カスタムのリダイレクトURI を使用する場合は、CallbackURL 接続プロパティを設定してください。その際、このURI と正確に一致するように設定する必要があります。
7. 登録をクリックします。アプリケーション管理画面が開きます。 後で使用するために、表示される値を控えておきます。
   • アプリケーション（クライアント）ID は、OAuthClientId に使用されます。
   • ディレクトリ（テナント）ID は、AzureTenant に使用されます。
8. 証明書とシークレットに移動します。新しいクライアントシークレットをクリックし、希望する有効期限を設定して、生成された値を保存します。 この値は一度しか表示されないため、OAuthClientSecret で使用するために記録しておいてください。

9. Select the Azure DevOps API and then select the permissions your app will seek.
10. 確認のため、アクセス許可の追加をクリックします。

データアクセスのファインチューニング

次のプロパティを使って、Azure DevOps から返されるデータをより詳細に制御できます。

• Catalog：使用するカタログを指定します。
  • 特定のプロジェクトのデータをクエリする場合は、Catalog をProject_projectId またはProject_projectName に設定します。例えば、ID 2ee729d6-804d-4ece-84af-d5befa94abe8 のdev という名前のプロジェクトのデータをクエリするには、Catalog をProject_dev またはProject_2ee729d6-804d-4ece-84af-d5befa94abe8 に設定します。プロジェクトID と名前の一覧を取得するには、Projects テーブルに対してSELECT クエリを実行します。
  • 特定のプロジェクトに依存しない情報をクエリする場合は、Catalog をCData に設定します。
• Schema：使用するスキーマを指定します。
  • Catalog がCData に設定されている場合、利用可能なスキーマはInformation のみです。
  • Catalog がプロジェクトのカタログに設定されている場合、Schema をAnalytics、Project、Repository スキーマのいずれかに設定できます。特定のリポジトリのデータをクエリする場合は、Schema をRepository_repositoryId に設定します。例えば、ID 3ee729d6-804d-4ece-84af-d5befa94abe8 のリポジトリのデータをクエリするには、Schema をRepository_3ee729d6-804d-4ece-84af-d5befa94abe8 に設定します。リポジトリID と名前の一覧を取得するには、Schema をProject に設定し、Repositories テーブルに対してSELECT クエリを実行します。

SSL 設定のカスタマイズ

デフォルトでは、Cloud はサーバーとのTLS のネゴシエーションを試みます。サーバー証明書は、デフォルトのシステム信頼済み証明書ストアで検証されます。SSLServerCert 接続プロパティを使用して、証明書の検証方法をオーバーライドできます。

別の証明書を指定するには、SSLServerCert 接続プロパティを参照してください。

Firewall またはProxy 経由の接続

HTTP プロキシ

HTTP プロキシへの認証には、以下のように設定します。

• ProxyServer：HTTP トラフィックをルートするプロキシサーバーのホストネームもしくはIP アドレス。
• ProxyPort：プロキシサーバーが起動しているTCP ポート。
• ProxyAuthScheme：プロキシサーバーに対して認証する際にCloud が使用する認証方法。
• ProxyUser：プロキシサーバーに登録されているユーザーアカウントのユーザー名。
• ProxyPassword：ProxyUser に紐付けられたパスワード。

その他のプロキシ

次のプロパティを設定します。

• プロキシベースのファイヤーウォールを使用するには、FirewallType、FirewallServer、およびFirewallPort を設定します。
• 接続をトンネルするには、FirewallType をTUNNEL に設定します。
• 認証するには、FirewallUser とFirewallPassword を設定します。
• SOCKS プロキシへの認証には、さらにFirewallType を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 データモデル.

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

テーブル 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

ビュー describes the available views. Unlike tables, views are read-only.

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.

Cloud はAzure DevOps のデータを、標準のSQL ステートメントを使用してクエリできるリレーショナルデータベースのテーブルのリストとしてモデル化します。

CData Cloud - Azure DevOps テーブル

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. 使用できる値は次のとおりです。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

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。 特に指定がない限り、擬似カラムでフィルタリングを行う場合は = 演算子のみ使用できます。

Name	Type	Description
Action	String	Filter by whether the calling user has use or manage permissions. 使用できる値は次のとおりです。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

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。 特に指定がない限り、擬似カラムでフィルタリングを行う場合は = 演算子のみ使用できます。

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

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。 特に指定がない限り、擬似カラムでフィルタリングを行う場合は = 演算子のみ使用できます。

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

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。 特に指定がない限り、擬似カラムでフィルタリングを行う場合は = 演算子のみ使用できます。

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

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。 特に指定がない限り、擬似カラムでフィルタリングを行う場合は = 演算子のみ使用できます。

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

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。 特に指定がない限り、擬似カラムでフィルタリングを行う場合は = 演算子のみ使用できます。

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

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。 特に指定がない限り、擬似カラムでフィルタリングを行う場合は = 演算子のみ使用できます。

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. 使用できる値は次のとおりです。fail, omit
Expand	String	The expand parameters for work item attributes. 使用できる値は次のとおりです。all, fields, links, none, relations

ビューは、データを示すという点でテーブルに似ていますが、ビューは読み取り専用です。

クエリは、ビューに対して通常のテーブルと同様に実行することができます。

CData Cloud - Azure DevOps ビュー

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

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。 特に指定がない限り、擬似カラムでフィルタリングを行う場合は = 演算子のみ使用できます。

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

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。 特に指定がない限り、擬似カラムでフィルタリングを行う場合は = 演算子のみ使用できます。

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

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。 特に指定がない限り、擬似カラムでフィルタリングを行う場合は = 演算子のみ使用できます。

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

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。 特に指定がない限り、擬似カラムでフィルタリングを行う場合は = 演算子のみ使用できます。

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

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。 特に指定がない限り、擬似カラムでフィルタリングを行う場合は = 演算子のみ使用できます。

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

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。 特に指定がない限り、擬似カラムでフィルタリングを行う場合は = 演算子のみ使用できます。

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). 使用できる値は次のとおりです。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). 使用できる値は次のとおりです。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

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。 特に指定がない限り、擬似カラムでフィルタリングを行う場合は = 演算子のみ使用できます。

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. 使用できる値は次のとおりです。fail, omit
Expand	String		The expand parameters for work item attributes. 使用できる値は次のとおりです。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

ストアドプロシージャはファンクションライクなインターフェースで、Azure DevOps の単純なSELECT/INSERT/UPDATE/DELETE 処理にとどまらずCloud の機能を拡張します。

ストアドプロシージャは、パラメータのリストを受け取り、目的の機能を実行し、プロシージャが成功したか失敗したかを示すとともにAzure DevOps から関連するレスポンスデータを返します。

CData Cloud - Azure DevOps ストアドプロシージャ

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. デフォルト値はfalseです。
IncludeLinks	Boolean	False	If set to true, include the links. デフォルト値は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. デフォルト値は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. デフォルト値は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 データモデル, Repository データモデル, and Analytics データモデル.

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

テーブル 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

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

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.

Cloud はAzure DevOps のデータを、標準のSQL ステートメントを使用してクエリできるリレーショナルデータベースのテーブルのリストとしてモデル化します。

CData Cloud - Azure DevOps テーブル

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

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。 特に指定がない限り、擬似カラムでフィルタリングを行う場合は = 演算子のみ使用できます。

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. 使用できる値は次のとおりです。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. 使用できる値は次のとおりです。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. 使用できる値は次のとおりです。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

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。 特に指定がない限り、擬似カラムでフィルタリングを行う場合は = 演算子のみ使用できます。

Name	Type	Description
DeletedFilter	String	Indicates whether to exclude, include, or only return deleted builds. 使用できる値は次のとおりです。excludeDeleted, includeDeleted, onlyDeleted
MaxBuildsPerDefinition	Integer	The maximum number of builds to return per definition.