Establishing a Connection

Adding a Connection to Azure DevOps

To add a connection to Azure DevOps:

In the application console, navigate to the Connections page.
At the Add Connections panel, select the icon for the connection you want to add.
If the Azure DevOps icon is not available, click the Add More icon to download and install the Azure DevOps connector from the CData site.

For required properties, see the Settings tab.

For connection properties that are not typically required, see the Advanced tab.

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.

Azure AD

Azure AD is Microsoft’s multi-tenant, cloud-based directory and identity management service. It is user-based authentication that requires that you set AuthScheme to AzureAD and set Organization to the name of your Azure DevOps Organization.

Authentication to Azure AD over a Web application always requires the creation of a custom OAuth application .

For details about creating a custom OAuth application, see Creating an Azure AD Application.

Fine-Tuning Data Access

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.

Advanced Features

This section details a selection of advanced features of the Azure DevOps Sync App.

User Defined Views

The Sync App supports the use of user defined views, virtual tables whose contents are decided by a pre-configured user defined query. These views are useful when you cannot directly control queries being issued to the drivers. For an overview of creating and configuring custom views, see User Defined Views .

SSL Configuration

Use SSL Configuration to adjust how Sync App handles TLS/SSL certificate negotiations. You can choose from various certificate formats;. For further information, see the SSLServerCert property under "Connection String Options" .

Firewall and Proxy

Configure the Sync App for compliance with Firewall and Proxy, including Windows proxies and HTTP proxies. You can also set up tunnel connections.

Query Processing

The Sync App offloads as much of the SELECT statement processing as possible to Azure DevOps and then processes the rest of the query in memory (client-side).

For further information, see Query Processing.

Logging

For an overview of configuration settings that can be used to refine CData logging, see Logging. Only two connection properties are required for basic logging, but there are numerous features that support more refined logging, which enables you to use the LogModules connection property to specify subsets of information to be logged.

SSL Configuration

Customizing the SSL Configuration

By default, the Sync App 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.

Firewall and Proxy

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 Sync App 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.

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 Sync App models Azure DevOps entities like documents, folders, and groups as relational 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 Sync App.

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. Views are statically defined to model Projects, Tasks, Teams, etc. Dynamic views, such as queries exposed as views, and views for looking up specific combinations of project_team work items are supported.

Stored Procedures

Stored Procedures are function-like interfaces to Azure DevOps. Stored procedures allow you to execute operations to Azure DevOps, including downloading documents and moving envelopes.

Tables

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

Azure DevOps Connector for CData Sync Tables


Name	Description
AgentPools	Retrieves a list of agent pools.
BuildDefinitions	Retrieves a list of build definitions, sliced across all projects.
Projects	Get all projects in the organization that the authenticated user has access to and details of the specific project.
Users	Retrieves a list of users. This table will not retrieve results for the On-premise edition.

AgentPools

Retrieves a list of agent pools.

Table Specific Information

Select

The Sync App 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 Sync App.

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.

BuildDefinitions

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

Table Specific Information

Select

The Sync App 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 Sync App.

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.

Projects

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

Table Specific Information

Select

The Sync App 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 Sync App.

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).

Users

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

Table Specific Information

Select

The Sync App 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 Sync App.

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.

Views

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.

Azure DevOps Connector for CData Sync Views


Name	Description
AuditLogEntries	Retrieves all audit log entries. This table includes custom fields which are automatically discovered when 'IncludeCustomFields' is enabled.
ProjectProperties	Retrieves a collection of project properties, sliced across all projects.
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.

AuditLogEntries

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

Table Specific Information

Select

The Sync App 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 Sync App.

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.

ProjectProperties

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

Table Specific Information

Select

The Sync App 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 Sync App.

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.

WorkItemIds

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 .

WorkItemsHistory

Retrieves a work item's history as a list.

Table Specific Information

Select

The Sync App 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 Sync App.

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.

WorkItemUpdatesHistory

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

Project 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 Sync App models Azure DevOps entities like documents, folders, and groups as relational 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 Sync App.

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. Views are statically defined to model Projects, Tasks, Teams, etc. Dynamic views, such as queries exposed as views, and views for looking up specific combinations of project_team work items are supported.

Stored Procedures

Stored Procedures are function-like interfaces to Azure DevOps. Stored procedures allow you to execute operations to Azure DevOps, including downloading documents and moving envelopes.

Tables

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

Azure DevOps Connector for CData Sync 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.
WorkItems	Retrieves a list of work items. This table includes custom fields which are automatically discovered when 'IncludeCustomFields' is enabled.

BuildDefinitionDrafts

Retrieves a list of drafts associated with the specific definition.

Table Specific Information

Select

The Sync App 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 Sync App.

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.

BuildDefinitions

Retrieves a list of build definitions.

Table Specific Information

Select

The Sync App 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 Sync App.

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.

Builds

Retrieves a list of builds.

Table Specific Information

Select

The Sync App 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 Sync App.

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.

Dashboards

Retrieves a list of dashboards and details for a specific dashboard.

Table Specific Information

Select

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

Id supports the '=' operator.
ProjectId supports the '=' operator.
TeamId supports the '=' operator.

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

For example:

SELECT * FROM Dashboards WHERE ProjectId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf'

SELECT * FROM Dashboards WHERE ProjectId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf' AND TeamId = '27369296-c53e-4f21-9cac-1f0d62c87e40'

Insert

The following are examples of inserting into the Dashboards table:

INSERT INTO DashBoards (ProjectId, Name, Description) VALUES ('b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937', 'MyBoard', 'demo dashboard')

INSERT INTO DashBoards (ProjectId, TeamId, Name, Description) VALUES ('b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937', '619e870e-5242-4218-bedd-c52d8c003591', 'hello@123y', 'demo dashboard team')

Update

The following are examples of updating the Dashboards table:

UPDATE DashBoards SET Name = 'abc' WHERE ProjectId = 'b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937' AND Id = 'd112a62e-5fa1-42eb-abcc-2272cdceefe0'

UPDATE DashBoards SET Name = 'abc' WHERE ProjectId = 'b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937' AND Id = 'd112a62e-5fa1-42eb-abcc-2272cdceefe0' AND TeamId = '619e870e-5242-4218-bedd-c52d8c003591'

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 are examples of deleting data from the Dashboards table:

DELETE FROM DashBoards WHERE ProjectId = 'b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937' AND Id = 'd112a62e-5fa1-42eb-abcc-2272cdceefe0'

DELETE FROM DashBoards WHERE ProjectId = 'b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937' AND Id = 'd112a62e-5fa1-42eb-abcc-2272cdceefe0' AND TeamId = '619e870e-5242-4218-bedd-c52d8c003591'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		Unique Id of the dashboard.
ProjectId	String	True	Teams.Id	The Id of the project to which this dashboard belongs.
TeamId	String	True		The Id of the team to which this dashboard belongs.
Description	String	False		Description of the dashboard.
ETag	String	False		Server defined version tracking value, used for edit collision detection.
Name	String	False		Name of the dashboard.
OwnerId	String	False		ID of the owner for a dashboard.
GroupId	String	True		ID of the group for a dashboard. For team-scoped dashboards this is the unique identifier for the team associated with the dashboard. For project-scoped dashboards this property is empty.
Position	Integer	False		Position of the dashboard, within a dashboard group.
RefreshInterval	Integer	False		Interval for client to automatically refresh the dashboard. Expressed in minutes.
Url	String	False		The full HTTP link to the dashboard.
LastAccessedDate	String	True		Date when the dashboard was last accessed.
ModifiedDate	String	True		Date when the dashboard was last modified.

DeploymentGroups

Retrieves a list of all deployment groups.

Table Specific Information

Select

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

ProjectId supports the '=' operator.
Id supports the '=' operator.
Name supports the '=' operator.
Action supports the '=' operator.
Expand supports the '=' operator.

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

For example:

    SELECT * FROM DeploymentGroups WHERE ProjectId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf'
	SELECT * FROM DeploymentGroups WHERE ProjectId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf' AND Id = 29
	SELECT * FROM DeploymentGroups WHERE ProjectId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf' AND Action = 'manage'
	SELECT * FROM DeploymentGroups WHERE ProjectId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf' AND Expand = 'tags'

Insert

The following is an example of inserting into a DeploymentGroups table:

INSERT INTO DeploymentGroups (ProjectId, Name) VALUES ('c831d3b4-a289-462f', 'TestName')

Update

The following is an example of updating a DeploymentGroups table:

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

Delete

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

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

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		Id of the deployment group.
Description	String	False		Description of the deployment group.
MachineCount	Integer	True		Number of deployment targets in the deployment group.
MachineTags	String	True		List of unique tags across all deployment targets in the deployment group.
Name	String	False		Name of the deployment group.
PoolId	Integer	False		Id of the agent pool.
PoolIsHosted	Boolean	True		A value indicating whether or not this pool is managed by the service.
PoolIsLegacy	Boolean	True		Determines whether the pool is legacy.
PoolName	String	True		Name of the pool.
PoolType	String	True		The type of the pool.
PoolScope	String	True		The scope of the pool.
PoolSize	Integer	True		The current size of the pool.
ProjectId	String	True		Id of the project.
ProjectName	String	True		Name of the project.
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
Action	String	Get the deployment group only if this action can be performed on it. The allowed values are manage, none, use.
Expand	String	Include these additional details in the returned object. The allowed values are none, tags.

Environments

Retrieves environments.

Table Specific Information

Select

The Sync App 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 Sync App.

For example:

SELECT * FROM Environments WHERE Id = 11

Insert

The Name, Description, and ProjectId can be inserted.

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

INSERT INTO Environments (Name, Description, ProjectId) VALUES ('env2', 'inserted environment', '62d9f6e9-17ef-4cbf-833a-eb713c874df1')

Update

The Name and Description can be updated.

The following is an example of updating the Environments table:

UPDATE Environments SET Name='updatedEnv', Description='updated environment' WHERE Id = 11

Delete

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

DELETE FROM Environments where Id = 11

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		Id of the environment.
ProjectId	String	False		Id of the project.
Name	String	False		Name of the environment.
Description	String	False		Environment description.
CreatedOn	Datetime	True		The date the build was last changed.
LastModifiedOn	Datetime	True		The date the build was last changed.
CreatedById	String	True		Id of the user who created the environment.
CreatedByName	String	True		Name of the user who created the environment.
LastModifiedById	String	True		Id of the user who last modified the environment.
LastModifiedByName	String	True		Name of the user who last modified the environment.

Feeds

Retrieves all feeds in an account.

Table Specific Information

Select

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

ProjectId supports the '=' operator.
Id supports the '=' operator.
Role supports the '=' operator.

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

For example:

	SELECT * FROM Feeds WHERE ProjectId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf'

Insert

The following is an example of inserting into an Earnings table:

INSERT INTO Feeds (Id, BadgesEnabled, ProjectId, IsReadOnly, Name, Description) VALUES ('2c7f4f88-e64c-412e-b514-8c6b0dde5ecc', false, 'b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937', false, 'hellqw', 'demo dashboards')

Update

The following is an example of updating a Feeds table:

UPDATE Feeds SET Name = 'abc' WHERE ProjectId = 'b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937' AND ID = '2dfe0d39-1ee0-4501-9924-2e6b186a7435'

Delete

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

DELETE FROM Feeds WHERE ProjectId = 'b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937' AND ID = '2dfe0d39-1ee0-4501-9924-2e6b186a7435'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		Unique Id of the feed.
BadgesEnabled	Boolean	False		If set, this feed supports generation of package badges.
Links	String	True		Aggregate of the reference links.
Capabilities	String	False		Supported capabilities of a feed.
DefaultViewId	String	False		The view that the feed administrator has indicated is the default experience for readers.
DeletedDate	Datetime	False		The date that this feed was deleted.
Description	String	False		A description for the feed. Descriptions must not exceed 255 characters.
FullyQualifiedId	String	False		This will either be the feed GUID or the feed GUID and view GUID depending on how the feed was accessed.
FullyQualifiedName	String	False		Full name of the view, in feed@view format.
HideDeletedPackageVersions	Boolean	False		If set, the feed will hide all deleted/unpublished versions.
IsReadOnly	Boolean	False		If set, all packages in the feed are immutable.
Name	String	False		A name for the feed.
ProjectId	String	True		Id of the project.
ProjectName	String	False		Name of the project.
UpstreamEnabled	Boolean	False		This should always be true. Setting to false will override all sources in UpstreamSources.
UpstreamEnabledChangedDate	String	False		If set, time that the UpstreamEnabled property was changed. Will be null if UpstreamEnabled was never changed after Feed creation.
Url	String	False		The URL of the base feed in GUID form.
ViewId	String	False		View Id.
ViewName	String	False		View name.

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

Role

String

Filter by this role.

The allowed values are administrator, collaborator, contributor, custom, none, reader.

FeedViews

Retrieves all views for the specific feed.

Table Specific Information

Select

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

Id supports the '=' operator.
FeedId supports the '=' operator.
ProjectId supports the '=' operator.

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

NOTE: FeedId is required in order to query FeedViews.

For example:

    SELECT * FROM FeedViews WHERE FeedId = 'e14f9853-4830-4f04-9561-c551254a32c9'
	SELECT * FROM FeedViews WHERE FeedId = 'e14f9853-4830-4f04-9561-c551254a32c9' AND Id = 'a7e5d881-fde1-46d8-8852-7433bf49fcd3'

Insert

The following is an example of inserting into an Earnings Table:

INSERT INTO FeedViews (ProjectId, FeedId, Name, Type) VALUES ('c831d3b4-a289-462f', 'b680c89a-fda0-4689', 'TestName', 'release')

Update

The following is an example of updating a FeedViews table:

UPDATE FeedViews Name = 'abc' WHERE ProjectId = 'b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937' AND feedid = '2dfe0d39-1ee0-4501-9924-2e6b186a7435' AND Id = '738ccfca-cef3-4d53-98f8-4136c2e446cf'

Delete

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

DELETE FROM FeedViews WHERE ProjectId = 'b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937' AND FeedId = '2dfe0d39-1ee0-4501-9924-2e6b186a7435' AND Id = '738ccfca-cef3-4d53-98f8-4136c2e446cf'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		Id of the view.
ProjectId	String	True		Id of the project.
FeedId	String	True	Feeds.Id	Id of the feed.
Links	String	True		Aggregate of the reference links.
Name	String	False		Name of the view.
Type	String	False		Type of view.
Url	String	False		Url of the view.
Visibility	String	False		Visibility status of the view.

GitBranches

Retrieves a collection of git branch, sliced across all repositories.

Table Specific Information

Select

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

ProjectId supports the '=' operator.
RepositoryId supports the '=' operator.
IncludeLinks supports the '=' operator.
MyBranches supports the '=' operator.
IncludeStatuses supports the '=' operator.
LatestStatusesOnly supports the '=' operator.

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

NOTE: RepositoryId is required in order to query GitBranches.

For example:

	SELECT * FROM GitBranches WHERE RepositoryId = '02b4a62d-2f5f-4d69-8420-29257dcc8051'
	SELECT * FROM GitBranches WHERE RepositoryId = '02b4a62d-2f5f-4d69-8420-29257dcc8051' AND IncludeLinks = true

Update

The following is an example of updating a GitBranches table:

UPDATE GitBranches SET isLocked = true WHERE name = 'abc' AND ProjectId = 'b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937' AND RepositoryId = 'e50698d4-bb6e-400f-a1a0-5f4d17517d9e'

Columns


Name	Type	ReadOnly	References	Description
ObjectId [KEY]	String	True		Path for the branch.
ProjectId	String	False		Id of the project this branch belongs to.
RepositoryId	String	False	Repositories.Id	Id of the repositories.
Name	String	False		Name of the branch.
CreatorDisplayName	String	True		The non-unique display name of the user who created this branch.
CreatorUrl	String	True		The URL of the user who created this branch.
CreatorLinksAvatarHref	String	True		Avatar reference link of the creator.
CreatorId	String	True		Id of the creator.
CreatorDescriptor	String	True		Descriptor of the creator.
Links	String	True		Aggregate of the reference links.
Statuses	String	True		Contains the metadata of a service/extension posting a status.
Url	String	True		Full HTTP resource link of the branch.
isLocked	Boolean	False		Represents a boolean value if the branch is locked or not.

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
IncludeLinks	Boolean	Specifies if referenceLinks should be included in the result.
IncludeStatuses	Boolean	Includes up to the first 1000 commit statuses for each ref.
MyBranches	Boolean	Includes only branches that the user owns, the branches the user favorites, and the default branch.
LatestStatusesOnly	Boolean	rue to include only the tip commit status for each ref.

Pipelines

Retrieves a list of pipelines

Table Specific Information

Select

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

Id supports the '=' operator.
ProjectId supports the '=' operator.
PipelineVersion supports the '=' operator.

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

For example:

	SELECT * FROM Pipelines WHERE ProjectId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf'
	SELECT * FROM Pipelines WHERE ProjectId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf' AND Id = 123

Insert

The following is an example of inserting into Pipelines table:

INSERT INTO Pipelines (ProjectId, Name, Folder, ConfigurationPath, ConfigurationRepositoryId, ConfigurationRepositoryType, ConfigurationType) values ('a0gd2e71-533c-4f96-9e5b-063740ee660b','test-pipeline','\testfolder','build-deploy.yml','cebheae8-6036-438d-bc23-d456c4a213b4', 'azureReposGit','yaml')

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.
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	String	True		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.

PullRequestReviewers

Retrieves a list of reviewers for the specific pull request, sliced across all repositories.

Table Specific Information

Select

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

RepositoryId supports the '=' operator.
PullRequestId supports the '=' operator.

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

For example:

	SELECT * FROM PullRequestReviewers WHERE ProjectId = '66eb7414-f622-4eff-88da-3ad681f19073' AND RepositoryId = '229ec1a1-609f-4545-af5a-85f00ce7428b' AND PullRequestId = 2
	SELECT * FROM PullRequestReviewers WHERE RepositoryId = '229ec1a1-609f-4545-af5a-85f00ce7428b' AND PullRequestId = 2

Insert

The following is an example of inserting into a PullRequestReviewers table:

INSERT INTO PullRequestReviewers (ProjectId, RepositoryId, PullRequestId, Id, Vote) VALUES ('c831d3b4-a289-462f', 'b20311e2-b5e4-444f', 2, '0c51c6d1-49b7-661b', 5)

Update

The following is an example of updating a PullRequestReviewers table:

UPDATE PullRequestReviewers SET DisplayName = 'cdata1', hasDeclined = false WHERE ProjectId = '1db52c22-a4e9-4ddc-ba82-5c0ae281dfd2' AND RepositoryId = '6b9dab15-dfe0-4488-a2b1-c5fe2a34b2cb' AND PullRequestId = 1 AND Id = '6a10066b-ee05-40c0-a207-b9fcbac568be'

Delete

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

DELETE FROM PullRequestReviewers WHERE ProjectId = '1db52c22-a4e9-4ddc-ba82-5c0ae281dfd2' AND RepositoryId = '6b9dab15-dfe0-4488-a2b1-c5fe2a34b2cb' AND PullRequestId = 1 AND Id = '6a10066b-ee05-40c0-a207-b9fcbac568be'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		Id of the reviewer
ProjectId	String	True		Id of the project.
RepositoryId [KEY]	String	True		Id of the repository.
PullRequestId	Integer	True	PullRequests.Id	Id of the pullrequest.
DisplayName	String	False		Display name of the reviewer.
ReviewerUrl	String	False		URL to retrieve information about the reviewer.
Url	String	False		This url is the full route to the source resource of the reviewer.
Vote	Integer	False		Vote on a pull request: 10 - approved, 5 - approved with suggestions, 0 - no vote, -5 - waiting for author, -10 - rejected.
isFlagged	Boolean	False		Whether a pull request is flagged.
hasDeclined	Boolean	False		Whether a pull request has been declined.

Pushes

Retrieves pushes associated with a repository, sliced across all repositories.

Table Specific Information

Select

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

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

NOTE: RepositoryId is required in order to query Pushes.

PushId supports the '=' operator.
ProjectId supports the '=' operator.
RepositoryId supports the '=' operator.
PushedById supports the '=' operator.
Date supports the '>=,<' operators.
BranchName supports the '=' operator.

For example:

	SELECT * FROM Pushes WHERE RepositoryId = '229ec1a1-609f-4545-af5a-85f00ce7428b'
	SELECT * FROM Pushes WHERE RepositoryId = '229ec1a1-609f-4545-af5a-85f00ce7428b' AND PushId = 16 AND Date >= '2000-01-01'

Insert

The following are examples of inserting into a Pushes table. For example:

INSERT INTO RefUpdates#TEMP (NewObjectId, Name) VALUES ('b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937', 'abcd')
INSERT INTO Commits#TEMP (Comment, AuthorName) VALUES ('b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937', 'xyz')
INSERT INTO Pushes (ProjectId, RepositoryId, Commits, RefUpdates) VALUES ('b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937', '84609754-3aa8-4b91-8a56-8ccb48f6d017', Commits#TEMP, RefUpdates#TEMP)

Columns


Name	Type	ReadOnly	References	Description
PushId [KEY]	Integer	True		Id of the push.
ProjectId	String	True		Id of the project.
Date	Datetime	True		The date of the push.
PushedByDisplayName	String	False		The display name of the user.
PushedById	String	False		The Id of the user.
PushedByUrl	String	False		The URL of the user.
RepositoryDefaultBranch	String	False		The default of the repository.
RepositoryId	String	True	Repositories.Id	The Id of the repository.
RepositoryName	String	False		Name of the repository.
RepositoryProjectId	String	False		The Project Id.
RepositoryProjectName	String	False		The Project name.
RepositoryProjectState	String	False		The Project state.
RepositoryProjectUrl	String	False		The Project URL.
RepositoryRemoteUrl	String	False		The Remote URL of the repository.
RepositoryUrl	String	False		The URL of the repository.
Url	String	False		The URL of the push.

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
BranchName	String	Branch name.
RefUpdates	String	Branch name.
Commits	String	List of inputs for the specific task group.

Queries

Retrieves the root queries and their children.

Table Specific Information

Select

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

Id supports the '=' operator.
ProjectId supports the '=' operator.
Depth supports the '=' operator.
Expand supports the '=' operator.
IncludeDeleted supports the '=' operator.

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

For example:

	
	SELECT * FROM Queries WHERE ProjectId = '66eb7414-f622-4eff-88da-3ad681f19073'
	SELECT * FROM QueryClauses WHERE ProjectId = '66eb7414-f622-4eff-88da-3ad681f19073' AND Id = '40314330-b454-41fd-9514-e6be6096bd0b'
	SELECT * FROM QueryClauses WHERE ProjectId = '66eb7414-f622-4eff-88da-3ad681f19073' AND Id = '40314330-b454-41fd-9514-e6be6096bd0b' AND Expand = 'wiql'
	SELECT * FROM QueryClauses WHERE ProjectId = '66eb7414-f622-4eff-88da-3ad681f19073' AND Id = '40314330-b454-41fd-9514-e6be6096bd0b' AND Depth = 2

Insert

The following are examples of inserting into a Queries table:

INSERT INTO Queries (ProjectId, Id, Name, IsFolder) VALUES ('619e870e-5242-4218-bedd-c52d8c003591', '2c2ad877-b460-4a6a-a323-a1c000035e2f', cdata11211, false)

Using aggregate columns:

INSERT INTO QueryColumns#TEMP (Name, referenceName) VALUES (test1, Cdata1)
INSERT INTO QueryColumns#TEMP (Name, referenceName) VALUES (test2, cdata11)
INSERT INTO Queries (ProjectId, ParentQueryId, Name, IsFolder, QueryColumns) VALUES ('b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937', 'fa454167-0ba2-4fdf-8a27-7946ed80636d', 'Creating a new query object', true, QueryColumns#TEMP)

Update

The following is an example of updating a Queries table:

UPDATE Queries SET Name = 'cdata1' WHERE ProjectId = '1db52c22-a4e9-4ddc-ba82-5c0ae281dfd2' AND Id = 'fa454167-0ba2-4fdf-8a27-7946ed80636d'

Delete

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

DELETE FROM Queries WHERE ProjectId = '1db52c22-a4e9-4ddc-ba82-5c0ae281dfd2' AND Id = 'fa454167-0ba2-4fdf-8a27-7946ed80636d'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		Id of the query.
ProjectId	String	True		Id of the project.
CreatedById	String	False		The Id of the user.
CreatedByName	String	False		The name of the user.
CreatedByUrl	String	False		The URL of the user.
CreatedDate	Datetime	False		When the query item was created.
FilterOptions	String	False		The link query mode.
HasChildren	Boolean	False		If this is a query folder, indicates if it contains any children.
IsDeleted	Boolean	False		Indicates if this query item is deleted.
IsFolder	Boolean	False		Indicates if this is a query folder or a query.
IsInvalidSyntax	Boolean	False		Indicates if the WIQL of this query is invalid.
IsPublic	Boolean	False		Indicates if this query item is public or private.
LastExecutedById	String	False		The Id of the user.
LastExecutedByName	String	False		The unique name of the user.
LastExecutedByUrl	String	False		The URL of the user.
LastExecutedDate	Datetime	False		When the query was last run.
LastModifiedById	String	False		The Id of the user.
LastModifiedByName	String	False		The unique name of the user.
LastModifiedByUrl	String	False		The URL of the user.
LastModifiedDate	Datetime	False		When the query item was last modified.
Links	String	True		Aggregate of the reference links.
Name	String	False		The name of the query item.
Path	String	False		The path of the query item.
QueryRecursionOption	String	False		The recursion option for use in a tree query.
QueryType	String	False		The type of query.
Url	String	False		The URL of the query Item.
Wiql	String	False		The WIQL text of the query.

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	In the folder of queries, return the children queries or folders at this depth. The allowed values are 1, 2.
Expand	Boolean	Include the query string (WIQL). The allowed values are minimal, none, wiql.
IncludeDeleted	Boolean	Include deleted queries and folders.
ParentQueryId	String	The id of Parent Query item
QueryColumns	String	The id of Parent Query item

ReleaseApprovals

Retrieves a list of approvals..

Table Specific Information

Select

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

ProjectId supports the '=' operator.
ApprovalType supports the '=' operator.
ReleaseId supports the '=,in' operators.
Status supports the '<,<=,>,>=' operators.
AssignedTo supports the '=' operator.
IncludeMyGroupApprovals supports the '=' operator.

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

For example:

	SELECT * FROM ReleaseApprovals WHERE ProjectId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf' AND Status > 'pending'

Update

The following is an example of updating a ReleaseApprovals table:

UPDATE ReleaseApprovals SET Status = 'approved', Comments = 'Good to go!' WHERE ProjectId = 'b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937' AND Id = '1'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		Id of the approval.
ProjectId	String	True		Id of the project.
ApprovalType	String	False		The type of approval. The allowed values are all, postDeploy, preDeploy, undefined.
ApprovedByDisplayName	String	False		The display name of the user who approved.
ApprovedById	String	False		The Id of the user who approved.
ApprovedByUrl	String	False		The URL of the user who approved.
ApproverDisplayName	String	False		The display name of the user who should approve.
ApproverId	String	False		The Id of the user who should approve.
ApproverUrl	String	False		The URL of the user who should approve.
Attempt	Integer	False		This specifies as which deployment attempt it belongs.
Comments	String	False		Comments for approval.
CreatedOn	Datetime	False		The date on which it was created.
IsAutomated	Boolean	False		Indicates whether approval is automated or not.
IsNotificationOn	Boolean	True		Indicates whether notification is on or not.
ModifiedOn	Datetime	False		The date on which it got modified.
Rank	Integer	False		Specifies the order of the approval.
ReleaseId	Integer	False		Id of the release.
ReleaseName	String	False		Name of the release.
ReleaseUrl	String	False		URL of the release.
ReleaseDefinitionId	Integer	False		Id of the release definition.
ReleaseDefinitionName	String	False		Name of the release definition.
ReleaseDefinitionUrl	String	False		URL of the release definition.
ReleaseEnvironmentId	Integer	False		Id of the release environment.
ReleaseEnvironmentName	String	False		Name of the release environment.
ReleaseEnvironmentUrl	String	False		URL of the release environment.
Revision	Integer	False		The revision number.
Status	String	False		The status of the approval.
TrialNumber	Integer	True		The trial number.
Url	String	False		The URL to access the approval.

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
AssignedTo	String	Approvals assigned to this user.
IncludeMyGroupApprovals	Boolean	Include my group approvals.

ReleaseDefinitionArtifacts

Retrieves a list of release definition artifacts.

Table Specific Information

Select

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

ProjectId supports the '=' operator.
DefinitionId supports the '=,in' operators.

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

NOTE: DefinitionId is required in order to query ReleaseDefinitionArtifacts.

For example:

	SELECT * FROM ReleaseDefinitionArtifacts WHERE ProjectId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf' AND DefinitionId IN (1, 2, 3)

Columns


Name	Type	ReadOnly	References	Description
DefinitionId [KEY]	Integer	True	ReleaseDefinitions.Id	Id of the release.
ProjectId	String	True		Id of the project.
Alias	String	False		Artifact alias.
DefinitionReference	String	False		Definition reference of the artifact.
IsPrimary	Boolean	False		Indicates whether artifact is primary or not.
IsRetained	Boolean	False		Indicates whether artifact is retained by release or not.
SourceId	String	True		Id of the source.
Type	String	False		Type of the artifact.

ReleaseDefinitions

Retrieves a list of release definitions.

Table Specific Information

Select

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

Id supports the '=,in' operator.
ProjectId supports the '=' operator.
IsDeleted supports the '=' operator.
Path supports the '=' operator.
Properties supports the '=,in' operators.
Tags supports the '=,in' operators.
ArtifactSourceId supports the '=' operator.
ArtifactType supports the '=' operator.
Expand supports the '=' operator.
IsExactNameMatch supports the '=' operator.
SearchText supports the '=' operator.
SearchTextContainsFolderName supports the '=' operator.

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

For example:

	SELECT * FROM ReleaseDefinitions WHERE ProjectId = '1e313382-5f07-43be-b5ae-1dcfa51ffaf4'
	SELECT * FROM ReleaseDefinitions WHERE ProjectId = '1e313382-5f07-43be-b5ae-1dcfa51ffaf4' AND Id = 1
	SELECT * FROM ReleaseDefinitions WHERE ProjectId = '1e313382-5f07-43be-b5ae-1dcfa51ffaf4' AND Tags IN ('Tag1', 'Tag2')
	SELECT * FROM ReleaseDefinitions WHERE ProjectId = '66eb7414-f622-4eff-88da-3ad681f19073' AND Expand = 'triggers'

Insert

The following are examples of inserting into ReleaseDefinitions table. For example:

INSERT INTO ReleaseDefinitionArtifacts#TEMP (Alias, IsPrimary) VALUES ('cdata1', false)
INSERT INTO ReleaseDefinitionArtifacts#TEMP (Alias, IsPrimary) VALUES ('cdata2', true)
INSERT INTO ReleaseDefinitions (Revision, Source, ProjectId, Name, Comment, Description, ReleaseDefinitionArtifacts) VALUES (1, undefined, 'b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937', cdata, 'demo request to create release definitions', 'HelloCdata1', ReleaseDefinitionArtifacts#TEMP)

Update

The following is an example of updating a ReleaseDefinitions table:

UPDATE ReleaseDefinitions SET Name = 'cdata2' WHERE ProjectId = 'b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937'

Delete

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

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

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		Id of the release definition.
Links	String	True		Aggregate of the reference links.
Comment	String	False		Comment on the release definition.
CreatedByDisplayName	String	False		The non unique display name of the user who created this release definition.
CreatedById	String	False		The Id of the user.
CreatedByUrl	String	False		The URL of the user.
CreatedOn	Datetime	False		The date on which it got created.
Description	String	False		The description of the release definition.
IsDeleted	Boolean	False		Whether release definition is deleted.
LastReleaseCreatedByDisplayName	String	False		The non-unique display name of the user who created last release.
LastReleaseCreatedById	String	False		The Id of the user who created last release.
LastReleaseCreatedByUrl	String	False		The URL of the user who created last release.
LastReleaseCreatedOn	Datetime	False		The date on which the last release was created.
LastReleaseDescription	String	False		The description of the last release.
LastReleaseId	Integer	False		The Id of the last release.
LastReleaseModifiedById	String	False		The Id of the user who modified the last release.
LastReleaseName	String	False		The name of the last release.
LastReleaseReason	String	False		The reason of the last release.
LastReleaseReleaseDefinitionId	Integer	False		The Id of the release definition of the last release.
LastReleaseWebAccessUri	String	False		The web access URI of the last release.
ModifiedByDisplayName	String	False		The non-unique display name of the user who modified this release definition.
ModifiedById	String	False		The Id of the user who modified this release definition.
ModifiedByUrl	String	False		The URL of the user who modified this release definition.
ModifiedOn	Datetime	False		The date on which it got modified.
Name	String	False		The name of the release definition.
Path	String	False		The Path of the release definition.
ProjectId	String	False		Id of the Project.
ProjectName	String	False		Name of the Project.
Properties	String	False		The list of properties associated with this definition.
ReleaseNameFormat	String	False		The release name format.
Revision	Integer	False		The revision number.
Source	String	False		The source of the release definition.
Tags	String	False		The list of tags.
Triggers	String	False		The list of triggers.
Url	String	False		REST API URL to access the release definition.
VariableGroups	String	False		The list of variable groups.
Variables	String	False		Release Definition Variables.

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
ArtifactSourceId	String	Release definitions with given artifactSourceId will be returned.
ArtifactType	String	Release definitions with given artifactType will be returned.
Expand	String	The properties that should be expanded in the list of Release definitions. The allowed values are tags, lastRelease, triggers, none.
IsExactNameMatch	Boolean	'true' to gets the release definitions with exact match as specified in searchText.
SearchText	String	Get release definitions with names containing searchText.
SearchTextContainsFolderName	Boolean	'true' to get the release definitions under the folder with name as specified in searchText.
ReleaseDefinitionArtifacts	String	List of artifacts for release definition object.

ReleaseEnvironments

Retrieves a list of releases.

Table Specific Information

SELECT Query Examples

SELECT * FROM ReleaseEnvironments
SELECT * FROM ReleaseEnvironments WHERE ReleaseId = 13 AND id = 18

UPDATE Query Examples

UPDATE ReleaseEnvironments SET Status = 'inProgress' WHERE ReleaseId = 10 AND id = 12

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		Unique identifier for the release environment.
ReleaseId [KEY]	Integer	True	Releases.Id	Unique identifier for the release associated with this environment.
Name	String	True		Descriptive name of the release environment.
OwnerId	String	True		Identifier of the user or entity that owns the environment.
CreatedOn	Datetime	True		Timestamp indicating when the release environment was created.
ModifiedOn	Datetime	True		Timestamp indicating when the release environment was last modified.
DefinitionEnvironmentId	Integer	True		Identifier for the associated definition environment.
AutoLinkWorkItems	Boolean	True		Indicates whether work items should be automatically linked to deployments.
BadgeEnabled	Boolean	True		Indicates whether a badge displaying deployment status is enabled.
EmailNotificationType	String	True		Specifies the type of email notifications to be sent for deployments.
EmailRecipients	String	True		List of email recipients for deployment notifications.
EnableAccessToken	Boolean	True		Indicates whether an access token is enabled for the environment.
PublishDeploymentStatus	Boolean	True		Indicates whether deployment status should be published.
PullRequestDeploymentEnabled	Boolean	True		Indicates whether deployment via pull requests is enabled.
SkipArtifactsDownload	Boolean	True		Indicates whether artifact downloads should be skipped during deployment.
TimeoutInMinutes	Integer	True		Specifies the maximum allowed deployment duration in minutes.
PostApprovalAutoTrigger	Boolean	True		Indicates whether an approval can be skipped if the same approver approved the previous stage.
PostApprovalEnforceIdentityReval	Boolean	True		Specifies whether the identity of the approver must be revalidated before completing approval.
PostApprovalExecutionOrder	String	True		Defines the execution order for approvals.
PostApprovalCreatorCanBeApprover	Boolean	True		Indicates whether the user initiating a release or deployment can also be an approver.
PostApprovalRequiredApproverCount	Integer	True		Specifies the number of required approvals for the release to proceed. '0' means all approvals are required.
PostApprovalTimeoutInMinutes	Integer	True		Specifies the timeout duration for approvals in minutes. Default is 30 days, maximum is 365 days. '0' uses the default timeout.
PostDeploymentGatesSnapshotId	Integer	True		Identifier for the snapshot of post-deployment gates.
PreApprovalAutoTrigger	Boolean	True		Indicates whether an approval can be skipped if the same approver approved the previous stage.
PreApprovalEnforceIdentityReval	Boolean	True		Specifies whether the identity of the approver must be revalidated before completing approval.
PreApprovalExecutionOrder	String	True		Defines the execution order for approvals.
PreApprovalCreatorCanBeApprover	Boolean	True		Indicates whether the user initiating a release or deployment can also be an approver.
PreApprovalRequiredApproverCount	Integer	True		Specifies the number of required approvals for the release to proceed. '0' means all approvals are required.
PreApprovalTimeoutInMinutes	Integer	True		Specifies the timeout duration for approvals in minutes. Default is 30 days, maximum is 365 days. '0' uses the default timeout.
PreDeploymentGatesSnapshotId	Integer	True		Identifier for the snapshot of pre-deployment gates.
Rank	Integer	True		Specifies the ranking or order of this environment in the release pipeline.
ReleaseCreatedById	String	True		Identifier of the user who created the release.
ReleaseDefinitionId	Integer	True		Identifier of the release definition associated with this environment.
Status	String	False		Current status of the environment in the release process.
TimeToDeploy	Double	True		Time taken to deploy the environment.
TriggerReason	String	True		Specifies the reason that triggered the release.
Variables	String	False		Contains environment-specific variables in an aggregated format.
ProjectId	String	True		Unique identifier or name of the project associated with this release.

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
Comment	String	User-provided comments related to the release environment.
ScheduledDeploymentTime	String	Scheduled time for the deployment to occur.

Releases

Retrieves a list of releases.

Table Specific Information

Select

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

Id supports the '=,in' operators.
CreatedOn supports the '>,>=,<,<=' operators.
ProjectId supports the '=' operator.
ReleaseDefinitionId supports the '=' operator.
Status supports the '=' operator.
Tags supports the '=,in' operators.
Properties supports the '=,in' operators.
IsDeleted supports the '=' operator.
SourceBranch supports the '=' operator.
ArtifactVersionId supports the '=' operator.
ArtifactTypeId supports the '=' operator.
EnvironmentStatus supports the '=' operator.
DefinitionEnvironmentId supports the '=' operator.
SearchText supports the '=' operator.
SourceId supports the '=' operator.
Path supports the '=' operator.
TopGateRecoards supports the '=' operator.
Expand supports the '=' operator.

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

For example:

	SELECT * FROM Releases WHERE ProjectId = '66eb7414-f622-4eff-88da-3ad681f19073'
	SELECT * FROM Releases WHERE ProjectId = '66eb7414-f622-4eff-88da-3ad681f19073' AND Id = 1
    SELECT * FROM Releases WHERE ProjectId = '66eb7414-f622-4eff-88da-3ad681f19073' AND CreatedOn >= '2000-01-01'
	SELECT * FROM Releases WHERE ProjectId = '66eb7414-f622-4eff-88da-3ad681f19073' AND Expand = 'Variables'

Insert

The following is an example of inserting into Releases table. For example:

INSERT INTO ReleaseArtifacts#TEMP (Alias, BuildVersionId, BuildVersionName) VALUES ('cdata1', 1, 'cdata')
INSERT INTO ReleaseArtifacts#TEMP (Alias, BuildVersionId, BuildVersionName) VALUES ('cdata2', 2, 'cdata33')
INSERT INTO Releases (ProjectId, Reason, Description, ReleaseArtifacts) VALUES ('b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937', 'creating release object', 'HelloCdata1', releaseArtifacts#TEMP)

Update

The following is an example of updating a Releases table:

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

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		Id of the release.
Links	String	True		Aggregate of the reference links.
Comment	String	True		Release comment.
CreatedByDisplayName	String	True		The display name of the user who created this release.
CreatedById	String	True		The Id of the user who created this release.
CreatedByUrl	String	True		The URL of the user who created this release.
CreatedOn	Datetime	True		The date on which it was created.
DefinitionSnapshotRevision	Integer	True		Revision number of definition snapshot.
Description	String	False		Description of release.
KeepForever	Boolean	True		Whether to exclude the release from retention policies.
LogsContainerUrl	String	True		Logs container url.
ModifiedByDisplayName	String	True		The display name of the user who modified this release.
ModifiedById	String	True		The id of the user who modified this release.
ModifiedByUrl	String	True		The URL of the user who modified this release.
ModifiedOn	Datetime	True		The date on which it got modified.
Name	String	False		Release name.
PoolName	String	True		Pool name.
ProjectId	String	True		Id of the project.
ProjectName	String	True		Name of the project.
Properties	String	False		Release properties.
Reason	String	False		Reason of release.
ReleaseDefinitionId	Integer	False	ReleaseDefinitions.Id	Id of the release definition.
ReleaseDefinitionName	String	True		Name of the release definition.
ReleaseDefinitionUrl	String	True		URL of the release definition.
ReleaseDefinitionRevision	Integer	True		The release definition revision.
ReleaseNameFormat	String	True		The release name format.
Status	String	True		Release status. The allowed values are abandoned, active, draft, undefined.
Tags	String	True		List of tags.
TriggeringArtifactAlias	String	True		Triggering artifact alias.
Url	String	True		The URL of the release.
Variables	String	False		The dictionary of variables.

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
IsDeleted	Boolean	Gets the soft deleted releases, if true.
SourceBranch	String	Releases with given sourceBranchFilter will be returned.
ArtifactVersionId	String	Releases with given artifactVersionId will be returned.
ArtifactTypeId	String	Releases with given artifactTypeId will be returned.
EnvironmentStatus	Integer	Environment status filter.
DefinitionEnvironmentId	Integer	Id of the definition environment.
SearchText	String	Releases with names containing searchText.
SourceId	String	Unique identifier of the artifact used.
Path	String	Releases under this folder path will be returned.
TopGateRecords	Integer	Number of release gate records to get.
Expand	String	The property that should be expanded in the list of releases. The allowed values are none, tags, variables.
ReleaseArtifacts	String	The property that should be expanded in the list of releases.

Repositories

Generated schema file..

Table Specific Information

Select

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

Id supports the '=' operator.
ProjectId supports the '=' operator.
IncludeAllUrls supports the '=' operator.
IncludeHidden supports the '=' operator.
IncludeLinks supports the '=' operator.
IncludeParent supports the '=' operator.

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

For example:

	SELECT * FROM Repositories WHERE Id = '02b4a62d-2f5f-4d69-8420-29257dcc8051'
	SELECT * FROM Repositories WHERE ProjectId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf'
	SELECT * FROM Repositories WHERE IncludeAllUrls = true
	SELECT * FROM Repositories WHERE IncludeLinks = true

Insert

The following is an example of inserting into a Repositories table:

INSERT INTO Repositories (ProjectId, Name) VALUES ('c831d3b4-a289-462f', 'TestRepository')

Update

The following is an example of updating a Repositories table:

UPDATE Repositories SET Name = 'cdata2' WHERE Id = 'dbf5e1ff-9192-4f94-ba21-735a4c289c72' AND ProjectId = 'b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937'

Delete

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

DELETE FROM Repositories WHERE Id = 'dbf5e1ff-9192-4f94-ba21-735a4c289c72' AND ProjectId = 'b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937'

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	True		Id of the project.
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.

TaskGroups

Retrieves a list of task groups.

Table Specific Information

Select

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

Id supports the '=' operator.
ProjectId supports the '=' operator.
Deleted supports the '=' operator.
Mine supports the '=' operator.
Expanded supports the '=' operator.
TaskId supports the '=' operator.

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

For example:

	SELECT * FROM TaskGroups WHERE ProjectId = '66eb7414-f622-4eff-88da-3ad681f19073'

Insert

The following are examples of inserting into a TaskGroups table:

INSERT INTO TaskGroups (ProjectId, Name) VALUES ('c831d3b4-a289-462f', 'TestTaskGroup')

Using aggregate columns:

INSERT INTO TaskGroupinputs#TEMP (Name, Aliases) VALUES (test1, Cdata1)
INSERT INTO TaskGroupinputs#TEMP (Name, Aliases) VALUES (test, Cdata)
INSERT INTO TaskGroups (ProjectId, Name, TaskGroupinputs) VALUES ('1db52c22-a4e9-4ddc-ba82-5c0ae281dfd2', 'demoTaskGroup', TaskGroupinputs#TEMP)

Update

The following is an example of updating a TaskGroups table:

UPDATE TaskGroups SET Name = 'cdata2' WHERE Id = '7afcae8b-7c47-47c3-b801-2443129a205f' AND ProjectId = '1db52c22-a4e9-4ddc-ba82-5c0ae281dfd2'

Delete

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

DELETE FROM TaskGroups WHERE Id = '7afcae8b-7c47-47c3-b801-2443129a205f' AND ProjectId = '1db52c22-a4e9-4ddc-ba82-5c0ae281dfd2'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		Id of the task group.
ProjectId	String	True		Id of the project.
Author	String	False		Author of the group.
Category	String	False		Category of the task group.
Comment	String	True		Comment.
ContentsUploaded	Boolean	True		Indicates whether content is uploaded or not.
ContributionIdentifier	String	True		Identifier of the the contribution.
ContributionVersion	String	True		Version of the contribution.
CreatedByDisplayName	String	True		The display name of the user .
CreatedById	String	True		The Id of the user who created this task group.
CreatedOn	Datetime	True		The timestamp at which the group was created.
DefinitionType	String	True		Type of the definition.
Deleted	Boolean	True		Indicates whether this is a deleted task group.
Demands	String	True		Task group demands.
Deprecated	Boolean	True		Indicates whether this is deprecated group.
Description	String	False		Description of the task group.
Disabled	Boolean	True		Indicates whether this task group is disabled or not.
Execution	String	True		Execution details of the tasks.
FriendlyName	String	False		Friendly name of the task group.
Groups	String	True		Groups definition.
HelpMarkDown	String	True		Help mark down.
HelpUrl	String	True		The help URL.
HostType	String	True		The host type.
IconUrl	String	False		The URL of the icon.
InstanceNameFormat	String	False		Format of the instance name.
MinimumAgentVersion	String	True		Minimum version of the task agent.
ModifiedByDisplayName	String	True		The non-unique display name of the user who modified this task group.
ModifiedById	String	True		The Id of the user.
ModifiedOn	Datetime	True		The timestamp at which this task group was modified.
Name	String	False		Name of the task group.
OutputVariables	String	True		Details of the task output variables.
Owner	String	True		Owner of the task group.
PackageLocation	String	True		Package location of the task group.
PackageType	String	True		Type of the package.
ParentDefinitionId	String	False		Parent task group id.
PostJobExecution	String	True		Post job execution details.
PreJobExecution	String	True		Pre job execution details.
Preview	Boolean	True		Indicates whether its a preview or not.
ReleaseNotes	String	True		Release notes.
Revision	Integer	True		Revision of the task group.
RunsOn	String	False		Runs On.
Satisfies	String	True		Satisfies.
ServerOwned	Boolean	True		Server owned.
ShowEnvironmentVariables	Boolean	True		Indicates whether to show the environment variables or not.
SourceLocation	String	True		Location of the source.
VersionIsTest	Boolean	False		Indicates whether its a test version.
VersionMajor	Integer	False		Major version .
VersionMinor	Integer	False		Minor version.
VersionPatch	Integer	False		Patch version.
Visibility	String	True		Task group visibility.

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
Expanded	Boolean	Returns task groups recursively, if set to true.
TaskId	String	GUID of the taskId to filter.
TaskGroupInputs	String	List of inputs for the specific task group.

TeamIterations

Retrieve a team's iteration.

Table Specific Information

Select

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

Id supports the '=' operator.
ProjectId supports the '=' operator.
TeamId supports the '=' operator.
AttributesTimeFrame supports the '=' operator.

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

For example:

	SELECT * FROM TeamIterations WHERE ProjectId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf'
	SELECT * FROM TeamIterations WHERE ProjectId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf' AND Id = '2bc932aa-21bd-4d2f-860d-43c843b46431'
	SELECT * FROM TeamIterations WHERE ProjectId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf' AND AttributesTimeFrame = 'current'

Insert

The following are examples of inserting into TeamIterations table:

INSERT INTO TeamIterations (ProjectId, TeamId, Name) VALUES ('b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937', '619e870e-5242-4218-bedd-c52d8c003591', 'hello@122')

This example shows how to add a separate Iterations table:

INSERT INTO TeamIterations (ProjectId, TeamId, Id) VALUES ('c831d3b4-a289-462f', '7f1d8582-a070-4d2b', 'afaad11d-8025-4c31')

Update

The following is an example of updating a TeamIterations table:

UPDATE TeamIterations SET Name = 'cdata2' WHERE ProjectId = 'b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937' AND TeamId = '619e870e-5242-4218-bedd-c52d8c003591'

Delete

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

DELETE FROM TeamIterations WHERE ProjectId = 'b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937' AND TeamId = '619e870e-5242-4218-bedd-c52d8c003591'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	False		Id of the iteration.
ProjectId	String	False		Id of the project.
TeamId	String	False	Teams.Id	Id of the team.
AttributesFinishDate	Datetime	False		Finish date of the iteration.
AttributesStartDate	Datetime	False		Start date of the iteration.
AttributesTimeFrame	String	False		Time frame of the iteration, such as past, current or future.
Links	String	True		Aggregate of the reference links.
Name	String	False		Name of the iteration.
Path	String	False		Relative path of the iteration.
Url	String	False		Full http link to the resource.

Teams

Retrieves a list of all teams and details of specified team.

Table Specific Information

Select

The Sync App 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 Sync App.

For example:

SELECT * FROM Teams WHERE ProjectId = '837ccd31-8159-4db3' AND Id = '7f1d8582-a070-4d2b'

Insert

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

INSERT INTO Teams (ProjectId, Name) VALUES ('b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937', 'TestTeam')

Update

The following is an example of updating the Teams table:

UPDATE Teams SET Name='cdata2' WHERE ProjectId='b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937' AND Id='619e870e-5242-4218-bedd-c52d8c003591'

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 Teams table:

DELETE FROM Teams WHERE ProjectId='b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937' AND Id ='619e870e-5242-4218-bedd-c52d8c003591'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		Unique Identifier of the team.
Description	String	False		The description of the team.
IdentityCustomDisplayName	String	True		The custom display name fro the identity.
IdentityId	String	True		Id of the identity.
IdentityIsActive	Boolean	True		Indicates whether the identity is active.
IdentityIsContainer	Boolean	True		Indicates whether the identity is a container.
IdentityMasterId	String	True		Master Id.
IdentityMetaTypeId	Integer	True		Meta Type Id.
IdentityProviderDisplayName	String	True		The display name for the identity as specified by the source identity provider.
IdentityResourceVersion	Integer	True		Resource version.
IdentitySubjectDescriptor	String	True		Subject descriptor.
IdentityUrl	String	True		Identity REST API URL to this team.
Name	String	False		The name of the team.
ProjectId	String	True		The Unique Identifier of the project this team belongs to.
ProjectName	String	True		The name of the project this team belongs to.
Url	String	True		Team REST API 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
ExpandIdentity	Boolean	A value indicating whether or not to expand Identity information in the result WebApiTeam object.
Mine	Boolean	Return all teams requesting user is member. Otherwise return all teams user has read access.

TeamSettings

Retrieves settings for a team.

Table Specific Information

Select

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

ProjectId supports the '=' operator.
TeamId supports the '=' operator.

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

For example:

	SELECT * FROM TeamSettings WHERE ProjectId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf'
	SELECT * FROM TeamSettings WHERE ProjectId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf' AND TeamId = '27369296-c53e-4f21-9cac-1f0d62c87e40'

Update

The following is an example of updating a TeamSettings table:

UPDATE TeamSettings SET DefaultIterationName = 'cdata2' WHERE ProjectId = 'b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937' AND TeamId = '619e870e-5242-4218-bedd-c52d8c003591'

Columns


Name	Type	ReadOnly	References	Description
ProjectId	String	False		Id of the project.
TeamId	String	False	Teams.Id	Id of the team.
BacklogIterationId	String	False		Id of the backlog iteration.
BacklogIterationName	String	False		Name of the backlog iteration.
BacklogIterationPath	String	False		Relative path of the backlog iteration.
BacklogIterationUrl	String	False		Full http link of the backlog iteration.
BacklogIterationAttributesFinishDate	String	False		Finish date of the iteration.
BacklogIterationAttributesStartDate	String	False		Start date of the iteration.
BacklogIterationAttributesTimeFrame	String	False		Time frame of the iteration, such as past, current or future.
IsFeatureCategoryVisible	Boolean	False		Indicates if the Feature category is visible on this team's backlog
IsEpicCategoryVisible	Boolean	False		Indicates if the Epic category is visible on this team's backlog
IsRequirementCategoryVisible	Boolean	False		Indicates if the Requirement category is visible on this team's backlog
BugsBehavior	String	False		Bug Behavior.
DefaultIterationId	String	False		Id of the default iteration.
DefaultIterationName	String	False		Name of the default iteration.
DefaultIterationPath	String	False		Relative path of the default iteration.
DefaultIterationUrl	String	False		Full http link of the default iteration.
DefaultIterationAttributesFinishDate	String	False		Finish date of the iteration.
DefaultIterationAttributesStartDate	String	False		Start date of the iteration.
DefaultIterationAttributesTimeFrame	String	False		Time frame of the iteration, such as past, current or future.
DefaultIterationMacro	String	False		Default iteration macro.
Links	String	True		Aggregate of the reference links.
Url	String	True		Full http link to the resource.
WorkingDays	String	False		Days that the team is working.

TestConfigurations

Retrieves a list test configurations.

Table Specific Information

Select

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

Id supports the '=' operator.
ProjectId supports the '=' operator.

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

For example:

	
	SELECT * FROM TestConfigurations WHERE ProjectId = '1e313382-5f07-43be-b5ae-1dcfa51ffaf4'
	SELECT * FROM TestConfigurations WHERE ProjectId = '1e313382-5f07-43be-b5ae-1dcfa51ffaf4' AND Id = 7

Insert

The following is an example of inserting into a TestConfigurations table:

INSERT INTO TestConfigurations (ProjectId, Name) VALUES ('b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937', 'cdata')

Update

The following is an example of updating a TestConfigurations table:

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

Delete

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

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

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		Id of the Test Configuration.
ProjectId	String	True		Id of the Project.
ProjectName	String	True		Name of the Project.
Description	String	False		Description of the test configuration.
IsDefault	Boolean	False		Is the configuration a default for the test plans.
Name	String	False		Name of the configuration.
State	String	False		State of the configuration.
Values	String	False		Dictionary of Test Variable, Selected Value.

TestPlans

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

Table Specific Information

Select

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

Id supports the '=' operator.
ProjectId supports the '=' operator.
OwnerId supports the '=' operator.
IncludePlanDetails supports the '=' operator.
ActivePlans supports the '=' operator.

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

For example:

	SELECT * FROM TestPlans WHERE ProjectId = '1e313382-5f07-43be-b5ae-1dcfa51ffaf4'
	SELECT * FROM TestPlans WHERE ProjectId = '1e313382-5f07-43be-b5ae-1dcfa51ffaf4' AND OwnerId = '4dbc0cec-c473-652b-972f-f42587b4494d' AND IncludePlanDetails = true

Insert

The following is an example of inserting into a TestPlans table:

INSERT INTO TestPlans (ProjectId, Name) VALUES ('b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937', 'cdata')

Update

The following is an example of updating a TestPlans table:

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

Delete

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

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

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	Builds.Id	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	String	True		Id of the Project that contains the test plan.
ProjectName	String	True		Name of the Project.
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.

TestResults

Retrieves test results for a test run.

Table Specific Information

Select

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

Id supports the '=' operator.
TestRunId supports the '=' operator.
Outcome supports the '=' operator.

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

NOTE: TestRunId is required in order to query TestResults.

For example:

	SELECT * FROM TestResults WHERE ProjectId = '1e313382-5f07-43be-b5ae-1dcfa51ffaf4' AND TestRunId = 6

Insert

The following are examples of inserting into a TestResults table:

INSERT INTO TestResults#TEMP (ProjectId, TestRunId, Comment, OwnerName) VALUES ('b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937', 1, 'b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937', 'abcd')
INSERT INTO TestResults#TEMP (ProjectId, TestRunId, Comment, OwnerName) VALUES ('b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937', 2, 'b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937', 'xyz')
INSERT INTO TestResults (ProjectId, TestRunId, Comment, OwnerName) SELECT ProjectId, TestRunId, Comment, OwnerName FROM TestResults#TEMP
INSERT INTO TestResults (ProjectId, TestRunId, TestCaseTitle, AutomatedTestName, Priority, Outcome) VALUES ('c831d3b4-a289-462f', 1, 'NewTestCase', 'TestProject.TestElement.TestClass.NewTestCase', 1, 'Passed')

Update

The following are examples of updating a TestResults table:

INSERT INTO TestResults#TEMP (ProjectId, TestRunId, Id, Comment, OwnerName) VALUES ('b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937', 1, '1', 'hello@1234', 'abcd')
INSERT INTO TestResults#TEMP (ProjectId, TestRunId, Id, Comment, OwnerName) VALUES ('b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937', 1, '2', 'hello@123t', 'xyz')
UPDATE TestResults (ProjectId, TestRunId, Id, Comment, OwnerName) SELECT ProjectId, TestRunId, Id, Comment, OwnerName FROM TestResults#TEMP

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	False		Id of the Test Result.
AfnStripId	Integer	False		Test Attachment Id of action recording.
AreaId	String	False		Id of the Area Path Of Test.
AreaName	String	False		Name of the Area Path of Test.
AreaUrl	String	False		URL of the Area Path of Test.
AssociatedBugs	String	False		Reference to bugs linked to test result.
AutomatedTestId	String	False		ID representing test method in a dll.
AutomatedTestName	String	False		Fully qualified name of test executed.
AutomatedTestStorage	String	False		Container to which test belongs.
AutomatedTestType	String	False		Type of automated test.
AutomatedTestTypeId	String	False		TypeId of automated test.
BuildId	String	False	Builds.Id	Id of the Build associated with this test result.
BuildName	String	False		Name of the Build.
BuildUrl	String	False		URL of the Build.
Comment	String	False		Comment in a test result with maxSize= 1000 chars.
CompletedDate	Datetime	False		Time when test execution completed.
ComputerName	String	False		Machine name where test executed.
ConfigurationId	String	False		Id of the Test Configuration.
ConfigurationName	String	False		Name of the Test Configuration.
ConfigurationUrl	String	False		Url of the Test Configuration.
CreatedDate	Datetime	False		Timestamp when test result created.
DurationInMs	Integer	False		Duration of test execution in milliseconds.
ErrorMessage	String	False		Error message in test execution.
FailingSinceBuildSystem	String	False		Build System.
FailingSinceBuildDefinitionId	Integer	False		Build Definition Id since tests are failing.
FailingSinceBuildId	Integer	False		Build Id since tests are failing.
FailingSinceBuildNumber	String	False		Build Number.
FailingSinceDate	Datetime	False		Time since failing.
FailingSinceReleaseId	Integer	False		Release reference since failing.
FailureType	String	False		Failure type of test result.
LastUpdatedByDisplayName	String	False		The non-unique display name of the user who last updated this test result.
LastUpdatedById	String	False		The Id of the user who last updated this test result.
LastUpdatedByUrl	String	False		The URL of the user.
LastUpdatedDate	Datetime	False		Last updated datetime of test result.
Outcome	String	False		Test outcome of test result.
OwnerId	String	False		The Id of the owner of the test.
OwnerName	String	False		The name of the owner.
OwnerUrl	String	False		The URL of the Owner.
Priority	Integer	False		Priority of test executed.
ProjectId	String	True		Id of the Project.
ProjectName	String	False		Name of the Project.
ProjectUrl	String	False		URL of the Project.
ReleaseId	Integer	False		Id of the release associated with this result.
ReleaseName	String	False		Name of the release associated with this result.
ReleaseUrl	String	False		Url of the release associated with this result.
ResetCount	Integer	False		ResetCount.
ResolutionState	String	False		Resolution state of test result.
ResolutionStateId	Integer	False		ID of resolution state.
ResultGroupType	String	False		Hierarchy type of the result, default value of None means its leaf node.
Revision	Integer	False		Revision number of test resul.
RunByDisplayName	String	False		The non-unique display name of the user who executed the test.
RunById	String	False		The Id of the user who executed the test.
RunByUrl	String	False		The URL of the user who executed the test.
StackTrace	String	False		Stacktrace with maxSize= 1000 chars.
StartedDate	Datetime	False		Time when test execution started.
State	String	False		State of test result.
TestCaseId	String	False	TestCases.Id	Id of the Test case executed.
TestCaseName	String	False		Name of the Test case executed.
TestCaseUrl	String	False		Url of the Test case executed.
TestCaseReferenceId	Integer	False		Reference ID of test used by test result.
TestCaseRevision	Integer	False		TestCaseRevision Number.
TestCaseTitle	String	False		Name of test.
TestPlanId	String	False	TestPlans.Id	Id of The Test Plan test case work item is part of.
TestPlanName	String	False		Name of the Test Plan.
TestPlanUrl	String	False		Url of the Test Plan.
TestPointId	String	False	TestPoints.Id	Id of the Test Point Executed.
TestPointName	String	False		Name of the Test Point Executed.
TestPointUrl	String	False		Url of the Test Point Executed.
TestRunId	String	True	TestRuns.Id	Id of the Test Run.
TestRunName	String	False		Name of the Test Run.
TestRunUrl	String	False		Url of the Test Run.
TestSuiteId	String	False	TestSuites.Id	Id of the Test Suite test case workitem is part of.
TestSuiteName	String	False		Name of the Test Suite.
TestSuiteUrl	String	False		Url of the Test Suite.
Url	String	False		Url of the Test Result.

TestRuns

Retrieves a list of test runs.

Table Specific Information

Select

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

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

ProjectId supports the '=' operator.
IsAutomated supports the '=' operator.
OwnerId supports the '=' operator.
PlanId supports the '=' operator.
RunId supports the '=' operator.
BuildUri supports the '=' operator.
IncludeRunDetails supports the '=' operator.
TmiRunId supports the '=' operator.

For example:

	SELECT * FROM TestRuns WHERE ProjectId = '1e313382-5f07-43be-b5ae-1dcfa51ffaf4'	
	SELECT * FROM TestRuns WHERE ProjectId = '1e313382-5f07-43be-b5ae-1dcfa51ffaf4' AND Id = 6	
	SELECT * FROM TestRuns WHERE ProjectId = '1e313382-5f07-43be-b5ae-1dcfa51ffaf4' AND IncludeRunDetails = true

Insert

The following is an example of inserting into an Earnings table:

INSERT INTO TestRuns (ProjectId, Name) VALUES ('b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937', 'Shubham')

Update

The following is an example of updating a TestRuns table:

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

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		Id of the Test Run.
BuildId	String	False	Builds.Id	Id of the build associated with this test run.
BuildName	String	False		Name of the build associated with this test run.
BuildUrl	String	False		Url of the build associated with this test run.
Comment	String	False		Comments entered by those analyzing the run.
CompletedDate	Datetime	False		Completed date time of the run.
Controller	String	False		Test Run Controller.
CreatedDate	Datetime	True		Test Run CreatedDate.
DropLocation	String	False		Drop Location for the test run.
DueDate	Datetime	False		Due date and time for test run.
ErrorMessage	String	False		Error message associated with the run.
IncompleteTests	Integer	True		Number of Incomplete Tests.
IsAutomated	Boolean	False		True if test run is automated, false otherwise.
Iteration	String	False		The iteration to which the run belongs.
LastUpdatedByDisplayName	String	True		The non-unique display name of the user who last updated this test run.
LastUpdatedById	String	True		The Id of the user who last updated this test run.
LastUpdatedByUrl	String	True		The Url of the user who last updated this test run.
LastUpdatedDate	Datetime	True		Last updated date and time.
Name	String	False		Name of the Test run.
NotApplicableTests	Integer	True		Number of Not Applicable Tests.
OwnerDisplayName	String	False		The non-unique display name of the owner.
OwnerId	String	False		The Id of the owner.
OwnerUrl	String	False		The URL of the owner.
PassedTests	Integer	True		Number of passed tests in the run.
Phase	String	True		Phase/State for the test run
PlanId	String	False	TestPlans.Id	Id of the test plan associated with this test run.
PlanName	String	False		Name of the test plan.
PlanUrl	String	False		URL of the test plan.
PostProcessState	String	True		Post Process State.
ProjectId	String	True		Id of the Project associated with this test run.
ProjectName	String	True		Name of the Project.
ProjectUrl	String	True		URL of the Project.
ReleaseId	String	True	Releases.Id	Id of the Release.
ReleaseEnvironmentUri	String	True		Release Environment URI for test run.
ReleaseUri	String	False		Release Uri for test run.
Revision	Integer	True		Test run Revision.
StartedDate	Datetime	False		Start date time of the run.
State	String	False		The state of the run.
SubState	String	True		Test run Substate.
Tags	String	False		Tags attached with this test run.
TestEnvironmentId	String	False		Id of the Test Environment associated with this test run.
TestEnvironmentName	String	True		Name of the Test Environment associated with this test run.
TestMessageLogId	Integer	True		Test Message Log Id.
TestSettingsId	String	False		Id of the Test Settings.
TestSettingsName	String	False		Name of the Test Settings.
TestSettingsUrl	String	False		Url of the Test Settings.
TotalTests	Integer	True		Total tests in the run.
UnanalyzedTests	Integer	True		Number of failed tests in the run.
Url	String	True		Url of the test run.
WebAccessUrl	String	True		Web Access Url for test run.

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
BuildUri	String	URI of the build that the runs used.
IncludeRunDetails	Boolean	If true, include all the properties of the runs.
TmiRunId	String	Tmi Run Id.

TestSessions

Retrieves a list of test sessions.

Table Specific Information

Select

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

ProjectId supports the '=' operator.
TeamId supports the '=' operator.
Source supports the '=' operator.
AllSessions supports the '=' operator.
IncludeAllProperties supports the '=' operator.
IncludeOnlyCompletedSessions supports the '=' operator.
Period supports the '=' operator.

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

For example:

	SELECT * FROM TestSessions WHERE ProjectId = '1e313382-5f07-43be-b5ae-1dcfa51ffaf4'

Insert

The following are examples of inserting into a TestSessions table:

INSERT INTO TestSessions (ProjectId, TeamId, Title) VALUES ('b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937', '619e870e-5242-4218-bedd-c52d8c003591', 'Cdata')
INSERT INTO TestSessions (ProjectId, TeamId, Title, AreaName) VALUES ('c831d3b4-a289-462f', '7f1d8582-a070-4d2b', 'Sample TestSession', 'Sample-Test-TFVC')

Update

The following is an example of updating a TestSessions table:

UPDATE TestSessions SET Comment = 'cdata2' WHERE Id = '1' AND ProjectId = 'b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937' AND TeamId = '619e870e-5242-4218-bedd-c52d8c003591'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		Id of the Test Session.
TeamId	String	True	Teams.Id	Id of the Team.
AreaId	String	False		Id of the Area Path of the test session.
AreaName	String	False		Name of the Area path of the test session.
AreaUrl	String	False		Url of the Area path of the test session.
Comment	String	False		Comments in the test session.
EndDate	Datetime	False		Duration of the session.
LastUpdatedByDisplayName	String	False		The non-unique display name of the user who last updated this session.
LastUpdatedById	String	False		The Id of the user who last updated this session.
LastUpdatedByUrl	String	False		The URL of the user who last updated this session.
LastUpdatedDate	Datetime	False		Last updated date.
OwnerDisplayName	String	False		The non unique display name of the owner of the test session.
OwnerId	String	False		The Id of the owner of the test session.
OwnerUrl	String	False		The URL of the owner of the test session.
ProjectId	String	True		Id of the Project.
ProjectName	String	False		Name of the Project.
ProjectUrl	String	False		Url of the Project.
PropertyBag	String	False		Generic store for test session data.
Revision	Integer	False		Revision of the test session.
Source	String	False		Source of the test session. The allowed values are feedbackDesktop, feedbackWeb, sessionInsightsForAll, unknown, xtDesktop, xtDesktop2, xtWeb.
StartDate	Datetime	False		Start date of the test session.
State	String	False		State of the test session.
Title	String	False		Title of the test session.
Url	String	False		Url of Test Session Resource.

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
AllSessions	Boolean	If false, returns test sessions for current user. Otherwise, it returns test sessions for all users.
IncludeAllProperties	Boolean	If true, it returns all properties of the test sessions.
IncludeOnlyCompletedSessions	Boolean	If true, it returns test sessions in completed state.
Period	Integer	Period in days from now, for which test sessions are fetched.

TestSuites

Retrieves all test suites.

Table Specific Information

Select

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

Id supports the '=' operator.
ProjectId supports the '=' operator.
PlanId supports the '=' operator.
Expand supports the '=' operator.
TreeView supports the '=' operator.

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

For example:

	SELECT * FROM TestSuites WHERE ProjectId = '1e313382-5f07-43be-b5ae-1dcfa51ffaf4' AND PlanId = 296
	SELECT * FROM TestSuites WHERE ProjectId = '1e313382-5f07-43be-b5ae-1dcfa51ffaf4' AND PlanId = 296 AND Expand = 'children'
	SELECT * FROM TestSuites WHERE ProjectId = '1e313382-5f07-43be-b5ae-1dcfa51ffaf4' AND PlanId = 296 AND TreeView = true

Insert

The following are examples of inserting into TestSuites table:

INSERT INTO TestSuites (ProjectId, PlanId, Name) VALUES ('b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937', 1, 'Shubham')
INSERT INTO TestSuites (ProjectId, PlanId, Name, SuiteType, ParentSuiteId, InheritDefaultConfigurations) VALUES ('c831d3b4-a289-462f', 1, 'Sample TestSuite', 'Sample-Test-TFVC', 85, true)

Update

The following is an example of updating a TestSuites table:

UPDATE TestSuites SET Name = 'cdata2' WHERE Id = '1' AND ProjectId = 'b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937' AND PlanId = '1'

Delete

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

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

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		Id of the test suite.
Links	String	True		Aggregate of the reference links.
Children	String	True		Child test suites of current test suite.
DefaultConfigurations	String	False		Test suite default configurations.
DefaultTesters	String	False		Test suite default testers.
HasChildren	Boolean	True		Boolean value dictating if child test suites are present.
InheritDefaultConfigurations	Boolean	False		Default configuration was inherited or not.
LastError	String	True		Last error for test suite.
LastPopulatedDate	Datetime	True		Last populated date.
LastUpdatedByLinksAvatarHref	String	True		Avatar reference link of the user who last updated this test suite.
LastUpdatedByDescriptor	String	True		The descriptor is the primary way to reference the user who last updated this test suite while the system is running.
LastUpdatedByDisplayName	String	True		The non unique display name of the user who last updated this test suite.
LastUpdatedById	String	True		The Id of the user who last updated this test suite.
LastUpdatedByUrl	String	True		The unique name of the user who last updated this test suite.
LastUpdatedDate	Datetime	True		The date at which the suite was last updated.
Name	String	False		Name of the test suite.
ParentSuiteId	Integer	False		Id of the parent test suite.
ParentSuiteName	String	False		Name of the parent test suite.
PlanId	Integer	True	TestPlans.Id	Id of the test plan to which this test suite belongs.
PlanName	String	True		Name of the test plan.
ProjectId	String	True		Id of the project.
ProjectName	String	True		Name of the project.
QueryString	String	False		Test suite query string, for dynamic suites.
RequirementId	Integer	False		Test suite requirement id.
Revision	Integer	True		Test suite revision.
SuiteType	String	False		Test suite type.
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
Expand	String	Include the children suites. The allowed values are children, defaultTesters, none.
TreeView	Boolean	If the suites returned should be in a tree structure.

TestVariables

Retrieves a list of test variables.

Table Specific Information

Select

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

Id supports the '=' operator.
ProjectId supports the '=' operator.

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

For example:

	SELECT * FROM TestVariables WHERE ProjectId = '66eb7414-f622-4eff-88da-3ad681f19073'

Insert

The following is an example of inserting into a TestVariables table:

INSERT INTO TestVariables (ProjectId, Name, Description) VALUES ('c831d3b4-a289-462f', 'SampleTestVariable', 'A sample test variable')

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		Id of the test variable.
Description	String	False		Description of the variable.
ProjectId	String	True		Id of the project.
ProjectName	String	True		Name of the project. this field will be populated with a value only when the Id is specified.
Name	String	False		Name of the test variable.
Values	String	False		List of allowed variables.

VariableGroups

Retrieves a list of variable groups.

Table Specific Information

Select

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

ProjectId supports the '=' operator.
Id supports the '=,in' operators.
Name supports the '=' operator.
Action supports the '=' operator.

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

For example:

	SELECT * FROM VariableGroups WHERE ProjectId = '1e313382-5f07-43be-b5ae-1dcfa51ffaf4'
	SELECT * FROM VariableGroups WHERE ProjectId = '1e313382-5f07-43be-b5ae-1dcfa51ffaf4' AND Id IN (1, 2, 3)

Insert

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

INSERT INTO VariablesAggregate#TEMP ([key1.value]) VALUES ('value1')
INSERT INTO VariableGroups (ProjectId, Name, Type, Variables) VALUES ('b154d8f3-bfd9-4bfb', 'TestVarGroup', 'Vsts', 'VariablesAggregate#TEMP')

Update

The following is an example of updating a VariableGroups table:

UPDATE VariableGroups SET Name = 'cdata2', Variables = '{\"name\" : \"cdata\"}' WHERE Id = 2 AND ProjectId = 'b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937'

Delete

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

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

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		Id of the variable group.
ProjectId	String	True		Id of the project.
CreatedByDisplayName	String	True		The display name of the user who created this variable group.
CreatedById	String	True		The Id of the user who created this variable group.
CreatedByUrl	String	True		The URL od the user who created this variable group.
CreatedOn	Datetime	True		The time when variable group was created.
Description	String	False		Description of the variable group.
IsShared	Boolean	True		Indicates whether variable group is shared with other projects or not.
ModifiedByDisplayName	String	True		The display name of the user who modified this variable group.
ModifiedById	String	True		The Id of the user who modified this variable group.
ModifiedByUrl	String	True		The URL of the user who modified this variable group.
ModifiedOn	Datetime	True		The time when variable group was modified.
Name	String	False		Name of the variable group.
ProviderData	String	False		Provider data.
Type	String	False		Type of the variable group.
Variables	String	False		Variables contained in the variable group.
VariableGroupProjectRefs	String	False		Variable group project references.

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

Specifies the action which can be performed on the variable groups.

The allowed values are manage, none, use.

Widgets

Retrieves a list of dashboard widgets and details for a specific widget.

Table Specific Information

Select

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

Id supports the '=' operator.
DashboardId supports the '=' operator.
TeamId supports the '=' operator.

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

For example:

SELECT * FROM Widgets WHERE DashboardId = 'eee8499b-dbf1-4f81-8d13-e5613f24a81d'

SELECT * FROM Widgets WHERE DashboardId = '4b9cc7c1-d5c4-4647-a11c-38045b2ca2a5' AND TeamId = '1530e163-5321-4d48-81b5-f10a18d1c9b5'

Insert

The following are examples of inserting into the Widgets table:

INSERT INTO Widgets (Name, DashboardId, RowPosition, ColumnPosition, RowSpanSize, ColumnSpanSize, ContributionId) VALUES ('widget1', 'eee8499b-dbf1-4f81-8d13-e5613f24a81d', 10, 10, 1, 2, 'ms.vss-dashboards-web.Microsoft.VisualStudioOnline.Dashboards.BuildHistogramWidget')

INSERT INTO Widgets (Name, DashboardId, Settings, RowPosition, ColumnPosition, RowSpanSize, ColumnSpanSize, ContributionId) VALUES ('settingstest', '18fbcc4b-1309-45be-bf1a-eeb0730bf5d5', '{"buildDefinition":{"name":"devops-driver-test","id":289,"type":2,"uri":"vstfs:///Build/Definition/289","projectId":"62d9f6e9-17ef-4cbf-833a-eb713c874df1"},"fullBranchName":null}', 10, 10, 1, 2, 'ms.vss-dashboards-web.Microsoft.VisualStudioOnline.Dashboards.BuildHistogramWidget')

INSERT INTO Widgets (Name, DashboardId, TeamId, RowPosition, ColumnPosition, RowSpanSize, ColumnSpanSize, ContributionId) VALUES ('widget2', '4b9cc7c1-d5c4-4647-a11c-38045b2ca2a5', '1530e163-5321-4d48-81b5-f10a18d1c9b5', 10, 10, 1, 2, 'ms.vss-dashboards-web.Microsoft.VisualStudioOnline.Dashboards.BuildHistogramWidget')

Update

Note that the Name, RowSpanSize, ColumnSpanSize, ETag, DashboardETag, and ContributionId are required for updating Widgets. After a successful update, the ETag and DashboardETag will be increased by one, which must be taken into account when performing successive updates.

The following are examples of updating the Widgets table:

UPDATE Widgets SET Name='updatedWidget', RowSpanSize=2, ColumnSpanSize=2, ETag='2', DashboardETag='5', ContributionId='ms.vss-dashboards-web.Microsoft.VisualStudioOnline.Dashboards.MarkdownWidget' WHERE Id='7991b969-fde0-4cc6-b203-8858cf0e7a3c' AND DashboardId='18fbcc4b-1309-45be-bf1a-eeb0730bf5d5'

UPDATE Widgets SET Name='updatedWidget', RowSpanSize=2, ColumnSpanSize=2, ETag='2', DashboardETag='5', ContributionId='ms.vss-dashboards-web.Microsoft.VisualStudioOnline.Dashboards.MarkdownWidget' WHERE Id='7991b969-fde0-4cc6-b203-8858cf0e7a3c' AND DashboardId='18fbcc4b-1309-45be-bf1a-eeb0730bf5d5' AND TeamId = '1530e163-5321-4d48-81b5-f10a18d1c9b5'

Delete

The following are examples of deleting data from the Widgets table:

DELETE FROM Widgets WHERE Id='bfad6fd8-9f4f-4a53-aefc-5dadf11a37ec'

DELETE FROM Widgets WHERE Id='bfad6fd8-9f4f-4a53-aefc-5dadf11a37ec' AND TeamId = '1530e163-5321-4d48-81b5-f10a18d1c9b5'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		Unique Id of the widget.
ProjectId	String	False		The Id of the project to which this widget belongs.
TeamId	String	False	Teams.Id	The Id of the team to which this widget belongs.
DashboardId	String	False	Dashboards.Id	The Id of the dashboard to which this widget belongs.
ETag	String	False		Server defined version tracking value, used for edit collision detection.
Name	String	False		Name of the widget.
ArtifactId	String	False		Unique identifier of a feature artifact. Used for pinning or unpinning a specific artifact.
ConfigContributionId	String	False		ID of the configuration contribution.
ConfigContributionRelativeId	String	False		Relative ID of the configuration contribution.
ContentUri	String	False		Content Uri.
ContributionId	String	False		ID of the underlying contribution defining the supplied Widget Configuration.
DashboardETag	String	False		Dashboard-level eTag. Only available when a Widget Id is specified.
IsEnabled	Boolean	False		Whether the widget is enabled.
IsNameConfigurable	Boolean	False		Whether the widget name is configurable.
LoadingImageUrl	String	False		The loading image Url.
RowPosition	Integer	False		Row position of the widget, within a dashboard group.
ColumnPosition	Integer	False		Column position of the widget, within a dashboard group.
Settings	String	False		Settings of the widget.
MajorVersion	Integer	False		Major version for an artifact when you make incompatible API changes.
MinorVersion	Integer	False		Minor version for an artifact when you add functionality in a backwards-compatible manner.
PatchVersion	Integer	False		Patch version for an artifact when you make backwards-compatible bug fixes.
RowSpanSize	Integer	False		Width of the widget, expressed in dashboard grid columns.
ColumnSpanSize	Integer	False		Height of the widget, expressed in dashboard grid columns.
TypeId	String	False		Type Id of the widget.
Url	String	False		The full HTTP link to the widget. Only available when a Widget Id is specified.

WikiPages

Retrieves metadata or content of the wiki page for the provided path.

Table Specific Information

Select

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

ProjectId supports the '=' operator.
WikiId supports the '=' operator.
Path supports the '=' operator.
IncludeContent supports the '=' operator.
RecursionLevel supports the '=' operator.
VersionOptions supports the '=' operator.
Version supports the '=' operator.
VersionType supports the '=' operator.

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

NOTE: WikiId is required in order to query WikiPages.

For example:

	SELECT * FROM WikiPages WHERE ProjectId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf' AND WikiId = '9d910096-122d-432e-b64a-8ef4d06d2905'
	SELECT * FROM WikiPages WHERE ProjectId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf' AND WikiId = '9d910096-122d-432e-b64a-8ef4d06d2905' AND RecursionLevel = 'full'
	SELECT * FROM WikiPages WHERE ProjectId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf' AND WikiId = '9d910096-122d-432e-b64a-8ef4d06d2905' AND Version = 'wikiMaster'

Insert

The following is an example of inserting a WikiPages table:

INSERT INTO WikiPages(WikiId, Content, ProjectId, Path) VALUES ('e7c569e7-3ff0-432c-93f0-084c09d578b5', 'Content for testing', 'b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937', 'main')

Update

The following is an example of updating a WikiPages table:

UPDATE WikiPages SET Content = 'cd' WHERE Path = 'main' AND WikiId = 'e7c569e7-3ff0-432c-93f0-084c09d578b5' AND ProjectId = 'b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937'

Delete

The following is an example of deleting data in the WikiPages table:

DELETE FROM WikiPages WHERE WikiId = 'e7c569e7-3ff0-432c-93f0-084c09d578b5' AND ProjectId = 'b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937'

Columns


Name	Type	ReadOnly	References	Description
Id	Integer	True		Permanent Id of the wiki page.
ProjectId	String	True		Id of the project for which this wiki was created.
WikiId [KEY]	String	True	Wikis.Id	Id of the wiki to which this page belongs to.
Content	String	False		Content of the wiki page.
GitItemPath	String	True		Path of the git item corresponding to the wiki page stored in the backing Git repository.
IsParentPage	Boolean	True		True if this page has subpages under its path.
Order	Integer	True		Order of the wiki page, relative to other pages in the same hierarchy level.
Path [KEY]	String	False		Path of the wiki page.
RemoteUrl	String	True		Remote web url to the wiki page.
SubPages	String	True		Sub Pages of the wiki page.
Url	String	True		REST url for this wiki page.

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
IncludeContent	Boolean	True to include the content of the page in the response for JSON content type.
RecursionLevel	String	Recursion level for subpages retrieval. Defaults to None. The allowed values are full, none, oneLevel, oneLevelPlusNestedEmptyFolders.
VersionOptions	String	Version options - specify additional modifiers to version. The allowed values are firstParent, none, previousChange.
Version	String	Version string identifier (name of tag/branch, SHA1 of commit).
VersionType	String	Version type (branch, tag, or commit). Determines how Id is interpreted. The allowed values are branch, commit, tag.

Wikis

Retrieves all wikis in a project or collection.

Table Specific Information

Select

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

Id supports the '=' operator.
ProjectId supports the '=' operator.

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

For example:

SELECT * FROM Wikis WHERE Id = '35df8f05-c66c-4a97-953d-a2a6d47a6198'
SELECT * FROM Wikis WHERE ProjectId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf'

Insert

Note that when inserting a Wiki of type ProjectWiki, the MappedPath, RepositoryId, and Version are not required.

The following are examples of inserting into the Wikis table:

INSERT INTO Wikis (ProjectId, Name) VALUES ('b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937', 'TestWiki')

INSERT INTO Wikis (ProjectId, Name, Type, MappedPath, RepositoryId, Version) VALUES ('b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937', 'MyCodeWiki', 'CodeWiki', '/', 'd36a682e-db74-4bc1-b0c3-8929402ce829', '{"version":"main"}')

Update

The following is an example of updating the Wikis table:

UPDATE Wikis SET Name = 'cd' WHERE Id = 'e7c569e7-3ff0-432c-93f0-084c09d578b5' AND ProjectId = 'b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937'

Delete

The following is an example of deleting data from the Wikis table:

DELETE FROM Wikis WHERE Id = 'e7c569e7-3ff0-432c-93f0-084c09d578b5' AND ProjectId = 'b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		Unique ID of the wiki.
MappedPath	String	False		Folder path inside repository which is shown as wiki.
Name	String	False		The name of the wiki.
ProjectId	String	False		ID of the project in which the wiki is to be created.
Properties	String	True		Properties of the wiki.
RemoteUrl	String	True		Remote web url to the wiki.
RepositoryId	String	False	Repositories.Id	ID of the git repository that backs up the wiki. Not required for ProjectWiki type.
Type	String	False		Type of the wiki.
Url	String	True		REST url for this wiki.

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
Version	String	Version aggregate of the wiki.

WorkItems

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

Table Specific Information

Select

The Sync App 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 Sync App.

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 ProjectId and ChangedDate columns are filterable while retrieving deleted WorkItems:

GETDELETED FROM WorkItems WHERE Projectid = 'bl54d8f3-bfd9-4bfb-90ae-2e6c8cda8937' AND ChangedDate >= '2022-01-01'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		Id of the work item.
ProjectId	String	True		Id of the project.
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

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.

Azure DevOps Connector for CData Sync Views


Name	Description
BacklogColumnFields	Retrieves column fields for the specific backlog level.
BacklogPanelFields	Retrieves panel fields for the specific backlog level.
Backlogs	Retrieves all backlog levels and details of the specific backlog level.
BacklogWorkItems	Retrieves a list of work items within a backlog level.
BoardColumns	Retrieve columns on a board.
BoardRows	Retrieve rows on a board.
Boards	Retrieve boards for the specific project and details of the specified board.
BuildChanges	Retrieves the changes associated with the build.
BuildDefinitionMetrics	Retrieves metadata for the specific build.
BuildDemands	Retrieves a list of demands that represents the agent capabilities required by the build.
BuildLogs	Retrieve the logs for a build.
BuildPlans	Retrieves the list of orchestration plans associated with the build.
BuildValidationResults	Retrieves the list of results of validating the build request.
BuildWorkItems	Retrieves a list of work items associated with a build.
CommitChanges	Retrieve changes for a particular commit, sliced across all repositories.
CommitGitStatus	Retrieve git status for the specific commit, sliced across all repositories.
Commits	Retrieve git commits for a project, sliced across all repositories.
CommitWorkItems	Retrieve work items for the specific commit, sliced across all repositories.
DeploymentGroupMachines	Retrieves all machines for the specific deployment group.
FeedPermissions	Retrieves the permissions for the specific feed.
FeedUpstreamSources	Retrieves a list of upstream sources for the specific feed.
GitStats	Retrieve statistics about all branches within a repository, sliced across all repositories.
IterationWorkItems	Retrieve work items for the specific iteration.
ProjectProperties	Retrieves a collection of project properties.
PullRequestAttachments	Retrieves a list of attachments for the specific pull request, sliced across all repositories.
PullRequests	Retrieves a list of pull requests, sliced across all repositories.
PullRequestWorkItems	Retrieves a list of work items associated with a pull request, sliced across all repositories.
PushRefUpdates	Retrieve Ref Updates for the specific push, sliced across all repositories.
QueryClauses	Retrieves clauses for the specific query.
QueryColumns	Retrieves all columns for the specific query.
ReleaseArtifacts	Retrieves a list of release artifacts.
ReleaseChanges	Retrieves a list of releases.
ReleaseDeployments	Retrieves a list of deployments.
TaskGroupInputs	Retrieves a list of inputs for the specific task group.
TaskGroupSourceDefinitions	Retrieves a list of source definitions for the specific task group.
Tasks	Retrieves tasks in a task group.
TeamMembers	Retrieves a list of members for a specific team.
TestAttachments	Retrieves a list of test result or run Attachments.
TestCasePointAssignments	Retrieves point assignments for the specific test case.
TestCases	Retrieves a list of all test cases.
TestPoints	Retrieves a list of test points.
TestResultIterationDetails	Retrieves iteration details for the test result.
TestRunStatistics	Retrieves test run statistics, used when we want to get summary of a run by outcome.
TestSubResults	Retrieves sub results for the test result.
TfvcBranches	Retrieves a collection of branch roots -- first-level children, branches with no parents.
TfvcChangesets	Retrieves Tfvc Changesets.
WikiVersions	Retrieves all wiki versions for the specific wiki.
WorkItemIds	Retrieves a list of work items, for use with other tables in the Project schema.
WorkItemRelations	Retrieves relationships between work items.
WorkItemRevisionFields	Retrieves a list of work item revision fields
WorkItemRevisions	Retrieves a list of work item revisions. This table includes custom fields which are automatically discovered when 'IncludeCustomFields' is enabled.
WorkItemsFields	Retrieves a list of work items fields
WorkItemUpdatesHistory	Retrieves a list of work items updates history. The WorkItemId can be filtered server-side.

BacklogColumnFields

Retrieves column fields for the specific backlog level.

Table Specific Information

Select

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

ProjectId supports the '=' operator.
TeamId supports the '=' operator.
BacklogId supports the '=' operator.

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

NOTE: Specifying the TeamId and BacklogId can improve the performance when querying BacklogColumnFields.

For example:

    SELECT * FROM BacklogColumnFields WHERE ProjectId = '03e4b7af-3bff-49d0' AND TeamId = '60efe1db-5742-4fe1' AND BacklogId = 'Microsoft.EpicCategory'

Columns


Name	Type	References	Description
ProjectId [KEY]	String		Id of the project in which the backlog was created.
TeamId [KEY]	String	Teams.Id	Id of the team for which the backlog was created.
BacklogId [KEY]	String	Backlogs.Id	Id of the backlog these column fields belong to.
ColumnFieldName [KEY]	String		The name of the column field.
ColumnFieldReferenceName	String		The reference name of the column field.
ColumnFieldUrl	String		The REST URL of the column field.
Width	Integer		The width of the column.

BacklogPanelFields

Retrieves panel fields for the specific backlog level.

Table Specific Information

Select

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

ProjectId supports the '=' operator.
TeamId supports the '=' operator.
BacklogId supports the '=' operator.

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

NOTE: Specifying the TeamId and BacklogId can improve the performance when querying BacklogPanelFields.

For example:

    SELECT * FROM BacklogPanelFields WHERE ProjectId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf' AND TeamId = '27369296-c53e-4f21-9cac-1f0d62c87e40' AND BacklogId = 'Microsoft.EpicCategory'
    SELECT * FROM BacklogPanelFields WHERE ProjectId = '03e4b7af-3bff-49d0' AND TeamId = '60efe1db-5742-4fe1' AND BacklogId = 'Microsoft.EpicCategory'

Columns


Name	Type	References	Description
ProjectId [KEY]	String		Id of the project in which the backlog was created.
TeamId [KEY]	String	Teams.Id	Id of the team for which the backlog was created.
BacklogId [KEY]	String	Backlogs.Id	Id of the backlog these column fields belong to.
Name [KEY]	String		The name of the field.
ReferenceName	String		The reference name of the field.
Url	String		The REST URL of the field.

Backlogs

Retrieves all backlog levels and details of the specific backlog level.

Table Specific Information

Select

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

Id supports the '=' operator.
ProjectId supports the '=' operator.
TeamId supports the '=' operator.

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

NOTE: TeamId is required in order to query BacklogPanelFields.

For example:

    SELECT * FROM Backlogs WHERE ProjectId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf' AND TeamId = '27369296-c53e-4f21-9cac-1f0d62c87e40'
	SELECT * FROM Backlogs WHERE ProjectId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf' AND TeamId = '27369296-c53e-4f21-9cac-1f0d62c87e40' AND Id = 'Microsoft.EpicCategory'

Columns


Name	Type	References	Description
Id [KEY]	String		Unique Id of the backlog.
ProjectId	String		Id of the project in which this backlog was created.
TeamId	String	Teams.Id	Id of the team for which this backlog was created.
Color	String		The color of the backlog level.
DefaultWorkItemTypeName	String		The name of the field.
DefaultWorkItemTypeReferenceName	String		The reference name of the field.
DefaultWorkItemTypeUrl	String		The REST URL of the field.
IsHidden	Boolean		Indicates whether the backlog level is hidden.
Name	String		The name of the backlog.
Rank	Integer		Backlog rank (task backlog is 0).
Type	String		The type of this backlog level.
WorkItemCountLimit	Integer		Max number of work items to show in the given backlog.

BacklogWorkItems

Retrieves a list of work items within a backlog level.

Table Specific Information

Select

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

ProjectId supports the '=' operator.
TeamId supports the '=' operator.
BacklogId supports the '=' operator.

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

NOTE: Specifying the TeamId and BacklogId can improve the performance when querying BacklogWorkItems.

For example:

    SELECT * FROM BacklogWorkItems WHERE ProjectId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf' AND TeamId = '27369296-c53e-4f21-9cac-1f0d62c87e40' AND BacklogId = 'Microsoft.EpicCategory'

Columns


Name	Type	References	Description
ProjectId	String		Id of the project in which the backlog was created.
TeamId	String	Teams.Id	Id of the team for which the backlog was created.
BacklogId	String	Backlogs.Id	Id of the backlog.
Rel	String		The type of link.
SourceId	Integer		Source work item ID.
SourceUrl	String		REST API URL of the source.
TargetId	Integer		Target work item ID.
TargetUrl	String		REST API URL of the target.

BoardColumns

Retrieve columns on a board.

Table Specific Information

Select

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

ProjectId supports the '=' operator.
TeamId supports the '=' operator.
BoardId supports the '=' operator.

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

For example:

    SELECT * FROM BoardColumns WHERE ProjectId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf' AND BoardId = 'a1c17364-7447-47e6-9862-b10b78c3f09b'
	SELECT * FROM BoardColumns WHERE ProjectId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf' AND TeamId = '27369296-c53e-4f21-9cac-1f0d62c87e40' AND BoardId = 'a1c17364-7447-47e6-9862-b10b78c3f09b'

Columns


Name	Type	References	Description
Id [KEY]	String		Id of the board column.
ProjectId	String		Id of the project in which this was created.
TeamId	String	Teams.Id	Id of the team this board belongs to.
BoardId [KEY]	String	Boards.Id	Id of the board this column belongs to.
ColumnType	String		The type of the column.
Description	String		The description of the column.
IsSplit	Boolean		Indicates if the column is split.
ItemLimit	Integer		The limit of the items.
Name	String		The name of the column.
StateMappings	String		State mappings.

BoardRows

Retrieve rows on a board.

Table Specific Information

Select

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

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

ProjectId supports the '=' operator.
TeamId supports the '=' operator.
BoardId supports the '=' operator.

For example:

    SELECT * FROM BoardRows WHERE ProjectId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf' AND BoardId = 'a1c17364-7447-47e6-9862-b10b78c3f09b'
	SELECT * FROM BoardRows WHERE ProjectId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf' AND TeamId = '27369296-c53e-4f21-9cac-1f0d62c87e40' AND BoardId = 'a1c17364-7447-47e6-9862-b10b78c3f09b'

Columns


Name	Type	References	Description
Id [KEY]	String		Id of the board row.
ProjectId	String		Id of the project in which this board row was created.
TeamId	String	Teams.Id	Id of the team this board row belongs to.
BoardId [KEY]	String	Boards.Id	Id of the board this row belongs to.
Name	String		Name of the board row.

Boards

Retrieve boards for the specific project and details of the specified board.

Table Specific Information

Select

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

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

Id supports the '=' operator.
ProjectId supports the '=' operator.
TeamId supports the '=' operator.

For example:

	
	SELECT * FROM Boards WHERE ProjectId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf'
	SELECT * FROM Boards WHERE ProjectId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf' AND Id = 'a1c17364-7447-47e6-9862-b10b78c3f09b'
	SELECT * FROM Boards WHERE ProjectId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf' AND TeamId = '27369296-c53e-4f21-9cac-1f0d62c87e40'
	SELECT * FROM Boards WHERE ProjectId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf' AND TeamId = '27369296-c53e-4f21-9cac-1f0d62c87e40' AND Id = 'a1c17364-7447-47e6-9862-b10b78c3f09b'

Columns


Name	Type	References	Description
Id [KEY]	String		Id of the board
ProjectId	String		Id of the Project in which this board was created
TeamId	String	Teams.Id	Id of the Team to which this board belongs to
AllowedMappings	String		Allowed mappings. This field will be populated with a value only when the Id is specified.
CanEdit	Boolean		Indicates if the board can be edited. This field will be populated with a value only when the Id is specified.
FieldsColumnFieldReferenceName	String		Reference name for the column field. this field will be populated with a value only when the Id is specified.
FieldsColumnFieldUrl	String		Full Http link for the column field. this field will be populated with a value only when the Id is specified.
FieldsDoneFieldReferenceName	String		Reference name for the done field. this field will be populated with a value only when the Id is specified.
FieldsDoneFieldUrl	String		Full Http link for the done field. this field will be populated with a value only when the Id is specified.
FieldsRowFieldReferenceName	String		Reference name for the row field. this field will be populated with a value only when the Id is specified.
FieldsRowFieldUrl	String		Full Http link for the row field. this field will be populated with a value only when the Id is specified.
IsValid	Boolean		Indicates whether this board is valid or not. This field will be populated with a value only when the Id is specified.
Links	String		Aggregate of the reference links.
Name	String		The name of the board
Revision	Integer		The revision of the board. This field will be populated with a value only when the Id is specified.
Url	String		The full http link to the board

BuildChanges

Retrieves the changes associated with the build.

Table Specific Information

Select

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

ProjectId supports the '=' operator.
BuildId supports the '=' operator.
IncludeSourceChange supports the '=' operator.

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

NOTE: To improve performance, a single BuildId should be specified in the WHERE clause of the query.

For example:

	SELECT * FROM BuildChanges WHERE ProjectId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf' AND BuildId = 3	
	SELECT * FROM BuildChanges WHERE ProjectId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf' AND BuildId = 3 AND IncludeSourceChange = true

Columns


Name	Type	References	Description
Id [KEY]	String		Id of the build change.
ProjectId	String		Id of the project.
BuildId [KEY]	String	Builds.Id	Id of the builds.
AuthorDisplayName	String		This is the non-unique display name of the author.
AuthorId	String		Id of the author.
Location	String		The location of the full representation of the resource.
Message	String		The description of the change.
MessageTruncated	Boolean		Indicates whether the message was truncated.
Pusher	String		The person or process that pushed the change.
Timestamp	Datetime		The timestamp for the change.
Type	String		The type of change. 'commit', 'changeset', 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
IncludeSourceChange	Boolean		Indicates whether to include source change.

BuildDefinitionMetrics

Retrieves metadata for the specific build.

Table Specific Information

Select

The Sync App 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 Sync App.

NOTE: DefinitionId is required in order to query BuildDefinitionMetrics.

For example:

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

Columns


Name	Type	References	Description
ProjectId	String		Id of the project.
DefinitionId	Integer	BuildDefinitions.Id	Id of the build definition.
Date	Datetime		The date for the scope.
IntValue	Integer		The value.
Name	String		The name of the metric.
Scope	String		The scope.

BuildDemands

Retrieves a list of demands that represents the agent capabilities required by the build.

Table Specific Information

Select

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

ProjectId supports the '=' operator.
BuildId supports the '=' operator.

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

NOTE: To improve performance, a single BuildId should be specified in the WHERE clause of the query.

For example:

    SELECT * FROM BuildDemands WHERE ProjectId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf' AND BuildId = 6

Columns


Name	Type	References	Description
ProjectId	String		Id of the project.
BuildId [KEY]	Integer	Builds.Id	Id of the build.
Name [KEY]	String		The name of the capability referenced by the demand.
Value	String		The demanded value.

BuildLogs

Retrieve the logs for a build.

Table Specific Information

Select

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

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

ProjectId supports the '=' operator.
BuildId supports the '=' operator.

NOTE: To improve performance, a single BuildId should be specified in the WHERE clause of the query.

For example:

    SELECT * FROM BuildLogs WHERE ProjectId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf' AND BuildId = 3

Columns


Name	Type	References	Description
Id [KEY]	Integer		The ID of the log.
ProjectId	String		Id of the project.
BuildId [KEY]	Integer	Builds.Id	Id of the build for which this log was created.
CreatedOn	Datetime		The date and time the log was created.
LastChangedOn	Datetime		The date and time the log was last changed.
LineCount	Integer		The number of lines in the log.
Type	String		The type of the log location.
Url	String		A full link to the log resource.

BuildPlans

Retrieves the list of orchestration plans associated with the build.

Table Specific Information

Select

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

ProjectId supports the '=' operator.
BuildId supports the '=' operator.

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

NOTE: BuildId is required in order to query BuildPlans.

For example:

    SELECT * FROM BuildPlans WHERE ProjectId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf' AND BuildId = 6

Columns


Name	Type	References	Description
ProjectId	String		Id of the project.
BuildId	Integer	Builds.Id	Id of the build for which this log was created.
OrchestrationType	Integer		The type of the plan.
PlanId	String		The Id of the plan.

BuildValidationResults

Retrieves the list of results of validating the build request.

Table Specific Information

Select

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

ProjectId supports the '=' operator.
BuildId supports the '=' operator.

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

NOTE: BuildId is required in order to query BuildValidationResults.

For example:

	SELECT * FROM BuildValidationResults WHERE ProjectId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf' AND BuildId = 6

Columns


Name	Type	References	Description
ProjectId	String		Id of the project.
BuildId	Integer	Builds.Id	Id of the build.
Message	String		The message associated with this result.
Result	String		The validation result.

BuildWorkItems

Retrieves a list of work items associated with a build.

Table Specific Information

Select

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

ProjectId supports the '=' operator.
BuildId supports the '=,>=,>,<=,<' operators.

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

NOTE: BuildId is required in order to query BuildWorkItems.

For example:

    SELECT * FROM BuildChanges WHERE ProjectId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf' AND BuildId = 3
    SELECT * FROM BuildWorkItems WHERE ProjectId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf' AND BuildId > 3 AND BuildId < 6

Columns


Name	Type	References	Description
Id [KEY]	String		Id of the work item.
Url	String		URL of the work item.
ProjectId	String		Id of the project.
BuildId	Integer	Builds.Id	Id of the builds.

CommitChanges

Retrieve changes for a particular commit, sliced across all repositories.

Table Specific Information

Select

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

ProjectId supports the '=' operator.
RepositoryId supports the '=' operator.
CommitId supports the '=' operator.

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

For example:

    SELECT * FROM CommitChanges WHERE RepositoryId = '02b4a62d-2f5f-4d69-8420-29257dcc8051' AND CommitId = '01832416d11f521e2e8fa1dc3acd9aebd93c773f'
	SELECT * FROM CommitChanges WHERE ProjectId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf' AND RepositoryId = '02b4a62d-2f5f-4d69-8420-29257dcc8051' AND CommitId = '01832416d11f521e2e8fa1dc3acd9aebd93c773f'

Columns


Name	Type	References	Description
CommitId	String	Commits.Id	Id of the commit.
ProjectId	String		Id of the project.
RepositoryId	String		Id of the repository.
ChangeType	String		The type of change that was made to the item.
ItemGitObjectType	String		Git object type.
ItemObjectId	String		Change object Id.
ItemIsFolder	Boolean		Indicates whether its a folder.
ItemPath	String		Path of the change.
ItemUrl	String		URL of the commit change.

CommitGitStatus

Retrieve git status for the specific commit, sliced across all repositories.

Table Specific Information

Select

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

CommitId supports the '=' operator.
RepositoryId supports the '=' operator.

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

For example:

	SELECT * FROM CommitGitStatus WHERE RepositoryId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf' AND CommitId = '01832416d11f521e2e8fa1dc3acd9aebd93c773f'

Columns


Name	Type	References	Description
CommitId	String	Commits.Id	Id of the commit.
ProjectId	String		Id of the project.
RepositoryId	String		Id of the repository.
ContextGenre	String		Genre of the status. Typically name of the service/tool generating the status, can be empty.
ContextName	String		Name identifier of the status.
CreatedByDisplayName	String		The non-unique display name of the user who created the status.
CreatedById	String		The Id of the user who created the status.
CreationDate	Datetime		Creation date and time of the status.
Description	String		Status description. Typically describes current state of the status.
Id	Integer		Id of the status.
State	String		State of the status.
TargetUrl	String		URL with status details.
UpdatedDate	Datetime		Last updated date and time of the status.

Commits

Retrieve git commits for a project, sliced across all repositories.

Table Specific Information

Select

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

Id supports the '=' operator.
ProjectId supports the '=' operator.
RepositoryId supports the '=' operator.
AuthorName supports the '=' operator.
CommitterName supports the '=' operator.
PushId supports the '=' operator.
ExcludeDeletes supports the '=' operator.
HistoryMode supports the '=' operator.
IncludePushData supports the '=' operator.
IncludeLinks supports the '=' operator.
ItemPath supports the '=' operator.
VersionType supports the '=' operator.
Version supports the '=' operator.
VersionOptions supports the '=' operator.
CompareVersionType supports the '=' operator.
CompareVersion supports the '=' operator.
CompareVersionOptions supports the '=' operator.
FromCommitId supports the '=' operator.
ToCommitId supports the '=' operator.

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

NOTE: RepositoryId is required in order to query Commits.

For example:

	SELECT * FROM Commits WHERE RepositoryId = '02b4a62d-2f5f-4d69-8420-29257dcc8051'
	SELECT * FROM Commits WHERE RepositoryId = '02b4a62d-2f5f-4d69-8420-29257dcc8051' AND Id = '01832416d11f521e2e8fa1dc3acd9aebd93c773f'
	SELECT * FROM Commits WHERE RepositoryId = '02b4a62d-2f5f-4d69-8420-29257dcc8051' AND IncludePushData = true

Columns


Name	Type	References	Description
Id [KEY]	String		Id of the commit.
ProjectId	String		Id of the project.
RepositoryId	String	Repositories.Id	Id of the repository.
AuthorDate	Datetime		Date of the Git operation.
AuthorEmail	String		Email address of the user performing the Git operation.
AuthorName	String		Name of the user performing the Git operation.
ChangeCountsAdd	String		Counts of the types of changes (edits, deletes, etc.) included with the commit.
ChangeCountsEdit	String		Counts of the types of changes (edits, deletes, etc.) included with the commit.
ChangeCountsDelete	String		Counts of the types of changes (edits, deletes, etc.) included with the commit.
Comment	String		Comment or message of the commit.
CommentTruncated	Boolean		Indicates if the comment is truncated from the full Git commit comment message.
CommitterDate	Datetime		Date of the Git operation.
CommitterEmail	String		Email address of the user performing the Git operation.
CommitterName	String		Name of the user performing the Git operation.
Links	String		Aggregate of the reference links.
LinkedWorkItems	String		List of linked WorkItem Ids.
Parents	String		An enumeration of the parent commit IDs for this commit.
PushDate	Datetime		Date of the commit push.
PushedByDisplayName	String		This is the non-unique display name of the user.
PushedById	String		Id of the user.
PushedByUrl	String		The URL of the user resource.
PushId	Integer		The Id of the commit push.
RemoteUrl	String		Remote URL path to the commit.
Url	String		REST URL for this resource.

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
FromCommitId	String		A lower bound for filtering commits alphabetically.
ToCommitId	String		An upper bound for filtering commits alphabetically.
ExcludeDeletes	Boolean		Only applies when an itemPath is specified. This determines whether to exclude delete entries of the specified path.
HistoryMode	String		What Git history mode should be used. This only applies to the search criteria when Ids = null and an itemPath is specified. The allowed values are firstParent, fullHistory, fullHistorySimplifyMerges, simplifiedHistory.
IncludePushData	Boolean		Whether to include the push information.
IncludeLinks	Boolean		Whether to include the links.
ItemPath	String		Path of item to search under.
VersionType	String		Version type (branch, tag, or commit). Determines how Id is interpreted.
Version	String		Version string identifier (name of tag/branch, SHA1 of commit).
VersionOptions	String		Version options - Specify additional modifiers to version (e.g Previous).
CompareVersionType	String		Version type (branch, tag, or commit). Determines how Id is interpreted.
CompareVersion	String		Version string identifier (name of tag/branch, SHA1 of commit).
CompareVersionOptions	String		Version options - Specify additional modifiers to version (e.g Previous).

CommitWorkItems

Retrieve work items for the specific commit, sliced across all repositories.

Table Specific Information

Select

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

ProjectId supports the '=' operator.
RepositoryId supports the '=' operator.
CommitId supports the '=' operator.

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

NOTE: RepositoryId and CommitId are required in order to query CommitWorkItems.

For example:

	SELECT * FROM CommitWorkItems WHERE RepositoryId = '02b4a62d-2f5f-4d69-8420-29257dcc8051' AND CommitId = '01832416d11f521e2e8fa1dc3acd9aebd93c773f'

Columns


Name	Type	References	Description
Id [KEY]	String		Id of the work item.
ProjectId	String		Id of the project.
RepositoryId	String	Repositories.Id	Id of the repository.
CommitId	String	Commits.Id	Id of the commit.
Url	String		URL of the work item.

DeploymentGroupMachines

Retrieves all machines for the specific deployment group.

Table Specific Information

Select

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

ProjectId supports the '=' operator.
DeploymentGroupId supports the '=' operator.

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

NOTE: DeploymentGroupId is required in order to query DeploymentGroupMachines.

For example:

    SELECT * FROM DeploymentGroupMachines WHERE ProjectId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf' AND DeploymentGroupId = 29

Columns


Name	Type	References	Description
Id [KEY]	Integer		Id of the deployment group machine.
ProjectId	String		Id of the project.
DeploymentGroupId	Integer	DeploymentGroups.Id	Id of the deployment group.
AgentLinksSelfHref	String		Agent self reference link.
AgentLinksWebHref	String		Agent web reference link.
AgentAccessPoint	String		This agent's access point.
AgentAuthorizationClientId	String		Client identifier for this agent.
AgentAuthorizationPublicKeyExponent	String		The exponent for the public key.
AgentAuthorizationPublicKeyModulus	String		The modulus for the public key.
AgentCreatedOn	Datetime		Date on which this agent was created.
AgentEnabled	Boolean		Whether or not this agent should run jobs.
AgentId	Integer		Identifier of the agent.
AgentMaxParallelism	Integer		Maximum job parallelism allowed for this agent.
AgentName	String		Name of the agent.
AgentOsDescription	String		Agent OS.
AgentProvisioningState	String		Provisioning state of this agent.
AgentStatus	String		Whether or not the agent is online.
AgentStatusChangedOn	Datetime		Date on which the last connectivity status change occurred.
AgentVersion	String		Agent version.
PropertiesCount	Integer		The count of properties in the collection.
PropertiesItem	String		The item in the properties collection.
PropertiesKeys	String		The set of keys in the collection.
PropertiesValues	String		The set of values in the collection.
Tags	String		Tags of the deployment target.

FeedPermissions

Retrieves the permissions for the specific feed.

Table Specific Information

Select

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

ProjectId supports the '=' operator.
FeedId supports the '=' operator.
ExcludeInheritedPermissions supports the '=' operator.
IncludeIds supports the '=' operator.

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

NOTE: FeedId is required in order to query FeedPermissions.

For example:

    SELECT * FROM FeedPermissions WHERE FeedId = 'e14f9853-4830-4f04-9561-c551254a32c9'

Columns


Name	Type	References	Description
FeedId	String	Feeds.Id	Id of the feed.
ProjectId	String		Id of the project.
DisplayName	String		Display name for the identity.
IdentityDescriptorIdentifier	String		The unique identifier for this identity.
IdentityDescriptorType	String		Type of descriptor.
IdentityId	String		Id of the identity associated with this role.
IsInheritedRole	Boolean		Indicates whether the role is inherited.
Role	String		The role for this identity on a feed.

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
ExcludeInheritedPermissions	Boolean		True to only return explicitly set permissions on the feed. Default is false.
IncludeIds	Boolean		True to include user Ids in the response. Default is false.

FeedUpstreamSources

Retrieves a list of upstream sources for the specific feed.

Table Specific Information

Select

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

FeedId supports the '=' operator.
ProjectId supports the '=' operator.
IncludeDeletedUpstreams supports the '=' operator.

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

NOTE: FeedId is required in order to query FeedUpstreamSources.

For example:

	SELECT * FROM FeedUpstreamSources WHERE FeedId = 'e14f9853-4830-4f04-9561-c551254a32c9'
	SELECT * FROM FeedUpstreamSources WHERE FeedId = 'e14f9853-4830-4f04-9561-c551254a32c9' AND IncludeDeletedUpstreams = true

Columns


Name	Type	References	Description
Id [KEY]	String		Id of the feed upstream source.
FeedId	String	Feeds.Id	Id of the feed.
ProjectId	String		Id of the project.
DeletedDate	Datetime		UTC date that this upstream was deleted.
DisplayLocation	String		Locator for connecting to the upstream source in a user friendly format, that may potentially change over time.
Location	String		Consistent locator for connecting to the upstream source.
Name	String		Display name.
Protocol	String		Package type associated with the upstream source.
Status	String		Status of the Upstream source.
UpstreamSourceType	String		Source type, such as Public or Internal.

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
IncludeDeletedUpstreams	Boolean		Include upstreams that have been deleted in the response.

GitStats

Retrieve statistics about all branches within a repository, sliced across all repositories.

Table Specific Information

Select

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

ProjectId supports the '=' operator.
RepositoryId supports the '=' operator.
Name supports the '=' operator.
VersionOptions supports the '=' operator.
Version supports the '=' operator.
VersionType supports the '=' operator.

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

NOTE: RepositoryId is required in order to query GitStats.

For example:

    SELECT * FROM GitStats WHERE RepositoryId = '02b4a62d-2f5f-4d69-8420-29257dcc8051'
	SELECT * FROM GitStats WHERE RepositoryId = '02b4a62d-2f5f-4d69-8420-29257dcc8051' AND VersionOptions = 'none'
	SELECT * FROM GitStats WHERE RepositoryId = '02b4a62d-2f5f-4d69-8420-29257dcc8051' AND Name = 'master'

Columns


Name	Type	References	Description
Name [KEY]	String		Name of the branch.
ProjectId	String		Id of the project.
RepositoryId	String	Repositories.Id	Id of the repository.
AheadCount	Integer		Number of commits ahead.
BehindCount	Integer		Number of commits behind.
CommitId	String		ID (SHA-1) of the commit.
IsBaseVersion	Boolean		Indicates whether this is base version.

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
VersionOptions	String		Version options - Specify additional modifiers to version (e.g Previous). The allowed values are firstParent, none, previousChange.
Version	String		Version string identifier (name of tag/branch, SHA1 of commit).
VersionType	String		Version type (branch, tag, or commit). Determines how Id is interpreted. The allowed values are branch, commit, tag.

IterationWorkItems

Retrieve work items for the specific iteration.

Table Specific Information

Select

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

ProjectId supports the '=' operator.
TeamId supports the '=' operator.
IterationId supports the '=' operator.

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

NOTE: IterationId is required in order to query IterationWorkItems.

For example:

	SELECT * FROM IterationWorkItems WHERE ProjectId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf' AND IterationId = '2bc932aa-21bd-4d2f-860d-43c843b46431'

Columns


Name	Type	References	Description
ProjectId	String		Id of the project.
TeamId	String	Teams.Id	Id of the team.
IterationId	String	TeamIterations.Id	Id of the test iteration.
Rel	String		The type of link.
SourceId	Integer		The source work item Id.
SourceUrl	String		The source work item URL.
TargetId	Integer		The target work item Id.
TargetUrl	String		The target work item URL.

ProjectProperties

Retrieves a collection of project properties.

Table Specific Information

Select

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

Name supports the '=,in' operators.

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

For example:

	
SELECT * FROM ProjectProperties WHERE Name IN ('System.Process Template', 'System.CurrentProcessTemplateId')

Insert

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

Update

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

Delete

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

Columns


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

PullRequestAttachments

Retrieves a list of attachments for the specific pull request, sliced across all repositories.

Table Specific Information

Select

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

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

PullRequestId supports the '=' operator.
RepositoryId supports the '=' operator.

For example:

	SELECT * FROM PullRequestAttachments WHERE ProjectId = '1e313382-5f07-43be-b5ae-1dcfa51ffaf4' AND RepositoryId = '123e04e0-6c4c-4675-8636-af6b0bc29d43' AND PullRequestId = 4

Columns


Name	Type	References	Description
Id	Integer		Id of the attachment.
ProjectId	String		Id of the project.
RepositoryId	String		Id of the repository.
PullRequestId	Integer	PullRequests.Id	Id of the pull request.
AuthorDisplayName	String		The non-unique display name of the author.
AuthorId	String		Id of the author.
AuthorUrl	String		The URL of the author.
ContentHash	String		Content hash of on-disk representation of file content. Its calculated by the server by using SHA1 hash function.
CreatedDate	Datetime		The time the attachment was uploaded.
Description	String		The description of the attachment.
DisplayName	String		The display name of the attachment.
Properties	String		Properties of the attachments.
Url	String		The URL to download the content of the attachment.

PullRequests

Retrieves a list of pull requests, sliced across all repositories.

Table Specific Information

Select

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

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

NOTE: ProjectId or RepositoryId or Id is required in order to query PullRequests.

Id supports the '=' operator.
ProjectId supports the '=' operator.
RepositoryId supports the '=' operator.
CreatedById supports the '=' operator.
SourceRefName supports the '=' operator.
Status supports the '=' operator.
TargetRefName supports the '=' operator.
IncludeLinks supports the '=' operator.
ReviewerId supports the '=' operator.
SourceRepositoryId supports the '=' operator.
TargetRepositoryId supports the '=' operator.

For example:

	SELECT * FROM PullRequests WHERE ProjectId = '1e313382-5f07-43be-b5ae-1dcfa51ffaf4'
	SELECT * FROM PullRequests WHERE RepositoryId = '02b4a62d-2f5f-4d69-8420-29257dcc8051'
	SELECT * FROM PullRequests WHERE ProjectId = '66eb7414-f622-4eff-88da-3ad681f19073' AND IncludeLinks = true
	SELECT * FROM PullRequests WHERE Id = 1
	SELECT * FROM PullRequests WHERE RepositoryId = '229ec1a1-609f-4545-af5a-85f00ce7428b' AND TargetRefName = 'refs/heads/master'

Columns


Name	Type	References	Description
Id [KEY]	Integer		Id of the pull request.
ProjectId	String		Id of the project.
ArtifactId	String		A string which uniquely identifies this pull request.
AutoCompleteSetByDisplayName	String		This is the non-unique display name of the resource.
AutoCompleteSetById	String		Id of the resource.
AutoCompleteSetByUrl	String		URL of the resource.
ClosedByDisplayName	String		This is the non-unique name of the user who closed this pull request.
ClosedById	String		Id of the User.
ClosedByUrl	String		URL of the user.
ClosedDate	Datetime		The date when the pull request was closed (completed, abandoned, or merged externally).
CodeReviewId	Integer		The code review ID of the pull request. Used internally.
CompletionOptionsBypassPolicy	Boolean		If true, policies will be explicitly bypassed while the pull request is completed.
CompletionOptionsBypassReason	String		If policies are bypassed, this reason is stored as to why bypass was used.
CompletionOptionsDeleteSourceBranch	Boolean		If true, the source branch of the pull request will be deleted after completion.
CompletionOptionsMergeCommitMessage	String		If set, this will be used as the commit message of the merge commit.
CompletionOptionsMergeStrategy	String		Specify the strategy used to merge the pull request during completion.
CompletionOptionsTransitionWorkItems	Boolean		If true, we will attempt to transition any work items linked to the pull request into the next logical state.
CompletionOptionsTriggeredByAutoComplete	Boolean		If true, the current completion attempt was triggered via auto-complete.
CompletionQueueTime	String		The most recent date at which the pull request entered the queue to be completed. Used internally.
CreatedByDisplayName	String		This is the non-unique name of the user who created this pull request.
CreatedById	String		Id of the user.
CreatedByUrl	String		URL of the user.
CreationDate	Datetime		The date when the pull request was created.
Description	String		The description of the pull request.
ForkSourceCreatorDisplayName	String		The non-unique display name of the user who created this source.
ForkSourceCreatorId	String		Id of the user.
ForkSourceIsLocked	Boolean		Indicates whether the fork source is locked or not.
ForkSourceIsLockedByDisplayName	String		The non0unique display name of the user who locked this fork source.
ForkSourceIsLockedById	String		The Id of the user.
ForkSourceName	String		Name of the fork source.
ForkSourceObjectId	String		Object Id of the fork source.
ForkSourcePeeledObjectId	String		Peeled Object Id of the fork source.
ForkSourceRepositoryId	String		Repository Id of the fork.
ForkSourceUrl	String		Url of the fork source.
IsDraft	Boolean		Draft / WIP pull request.
Labels	String		The labels associated with the pull request.
LastMergeCommitId	String		Id (SHA-1) of the last merged commit.
LastMergeCommitUrl	String		REST URL for the last merged commit.
LastMergeSourceCommitId	String		Id (SHA-1) of the last merged source commit.
LastMergeSourceCommitUrl	String		REST URL for the last merged source commit.
LastMergeTargetCommitId	String		Id (SHA-1) of the last merged target commit.
LastMergeTargetCommitUrl	String		REST URL for the last merged source commit.
Links	String		Aggregate of the reference links.
MergeFailureMessage	String		If set, pull request merge failed for this reason.
MergeFailureType	String		The type of failure (if any) of the pull request merge.
MergeId	String		The Id of the job used to run the pull request merge.
MergeOptionsDetectRenameFalsePositives	Boolean		The options which are used when a pull request merge is created.
MergeOptionsDisableRenames	Boolean		If true, rename detection will not be performed during the merge.
MergeStatus	String		The current status of the pull request merge.
RemoteUrl	String		Remote URL of the pull request.
RepositoryId	String	Repositories.Id	Id of the repository.
SourceRefName	String		The name of the source branch of the pull request.
Status	String		The status of the pull request.
SupportsIterations	Boolean		If true, this pull request supports multiple iterations.
TargetRefName	String		The name of the target branch of the pull request.
Title	String		The title of the pull request.
Url	String		The URL of the pull request.

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
IncludeLinks	Boolean		Whether to include the _links field on the shallow references.
ReviewerId	String		If set, search for pull requests that have this identity as a reviewer.
SourceRepositoryId	String		If set, search for pull requests whose source branch is in this repository.
TargetRepositoryId	String		If set, search for pull requests whose target branch is in this repository.

PullRequestWorkItems

Retrieves a list of work items associated with a pull request, sliced across all repositories.

Table Specific Information

Select

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

RepositoryId supports the '=' operator.
PullRequestId supports the '=' operator.

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

For example:

	SELECT * FROM PullRequestWorkItems WHERE RepositoryId = '229ec1a1-609f-4545-af5a-85f00ce7428b' AND PullRequestId = 2

Columns


Name	Type	References	Description
Id [KEY]	String		Id of the work item.
Url	String		URL of the work item.
ProjectId	String		Id of the project.
RepositoryId	String		Id of the repository.
PullRequestId	Integer	PullRequests.Id	Id of the pull request.

PushRefUpdates

Retrieve Ref Updates for the specific push, sliced across all repositories.

Table Specific Information

Select

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

PushId supports the '=' operator.
RepositoryId supports the '=' operator.

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

For example:

	SELECT * FROM PushRefUpdates WHERE RepositoryId = '229ec1a1-609f-4545-af5a-85f00ce7428b' AND PushId = 16

Columns


Name	Type	References	Description
ProjectId	String		Id of the project.
PushId	Integer	Pushes.Id	Id of the push.
Name	String		Name of the ref update.
NewObjectId	String		New object Id.
OldObjectId	String		Old object Id.
RepositoryId	String		Id of the repository.
IsLocked	Boolean		Represents a boolean value if the branch is locked or not.

QueryClauses

Retrieves clauses for the specific query.

Table Specific Information

Select

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

QueryId supports the '=' operator.
ProjectId supports the '=' operator.
ClauseType supports the '=' operator.

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

NOTE: QueryId is required in order to query QueryClauses.

For example:

	SELECT * FROM QueryClauses WHERE ProjectId = '66eb7414-f622-4eff-88da-3ad681f19073' AND QueryId = '40314330-b454-41fd-9514-e6be6096bd0b'
	SELECT * FROM QueryClauses WHERE ProjectId = '66eb7414-f622-4eff-88da-3ad681f19073' AND QueryId = '40314330-b454-41fd-9514-e6be6096bd0b' AND ClauseType = 'sourceClauses'

Columns


Name	Type	References	Description
ProjectId	String		Id of the project.
QueryId	String	Queries.Id	Id of the query.
FieldName	String		Friendly name of the field.
FieldReferenceName	String		Reference name of the field.
LogicalOperator	String		Logical operator separating the condition clause.
OperatorName	String		Friendly name of the operation.
OperatorReferenceName	String		Reference name of the operation.
Value	String		Right side of the condition when a field to value comparison.
ClauseType	String		Type of the clause to retrieve. The allowed values are clauses, sourceClauses, targetClauses, linkClauses.

QueryColumns

Retrieves all columns for the specific query.

Table Specific Information

Select

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

QueryId supports the '=' operator.
ProjectId supports the '=' operator.

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

NOTE: QueryId is required in order to query QueryColumns.

For example:

	SELECT * FROM QueryColumns WHERE ProjectId = '66eb7414-f622-4eff-88da-3ad681f19073' AND QueryId = '40314330-b454-41fd-9514-e6be6096bd0b'

Columns


Name	Type	References	Description
ProjectId	String		Id of the project.
QueryId	String	Queries.Id	Id of the query.
Name	String		Friendly name of the column.
ReferenceName	String		Reference name of the column.
Url	String		The Url of the query column.

ReleaseArtifacts

Retrieves a list of release artifacts.

Table Specific Information

Select

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

ProjectId supports the '=' operator.
ReleaseId supports the '=,in' operators.

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

NOTE: ReleaseId is required in order to query ReleaseArtifacts.

For example:

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

Columns


Name	Type	References	Description
ReleaseId [KEY]	Integer	Releases.Id	Id of the release.
ProjectId	String		Id of the project.
Alias	String		Artifact alias.
DefinitionReference	String		Definition reference of the artifact.
IsPrimary	Boolean		Indicates whether artifact is primary or not.
IsRetained	Boolean		Indicates whether artifact is retained by release or not.
SourceId	String		Id of the source.
Type	String		Type of the artifact.
BuildVersionId	String		Sets the build id.
BuildVersionCommitMessage	String		commit message for the artifact.
BuildVersionName	String		Sets the build number.

ReleaseChanges

Retrieves a list of releases.

Columns


Name	Type	References	Description
Id [KEY]	String		Id of the release change.
ReleaseId	String	Releases.Id	Id of the release.
AuthorAvatarLink	String		Author reference link.
AuthorDisplayName	String		The display name of the author of the release change.
AuthorId	String		The Id of the author of the release change.
ChangeType	String		The type of release change.
Location	String		Location in the repository of the commit.
Message	String		Commit message of release change.
PushedByDisplayName	String		The display name of the user who pushed the release change commit.
PushedById	String		The Id of the user who pushed the release change commit.
Timestamp	Datetime		The timestamp of the release change.
ProjectId	String		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
BaseReleaseId	String		Base release to which the current release will be compared.

ReleaseDeployments

Retrieves a list of deployments.

Table Specific Information

Select

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

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

ProjectId supports the '=' operator.
DefinitionEnvironmentId supports the '=' operator.
DeploymentStatus supports the '=' operator.
LastModifiedOn supports the '<,<=,>,>=' operators.
OperationStatus supports the '=' operator.
RequestedById supports the '=' operator.
ReleaseDefinitionId supports the '=' operator.
RequestedForId supports the '=' operator.
StartedOn supports the '>,>=,<,<=' operators.
LastestAttemptsOnly supports the '=' operator.
SourceBranch supports the '=' operator.

For example:

	SELECT * FROM ReleaseDeployments WHERE ProjectId = '66eb7414-f622-4eff-88da-3ad681f19073'
	SELECT * FROM ReleaseDeployments WHERE ProjectId = '66eb7414-f622-4eff-88da-3ad681f19073' AND StartedOn > '2020-04-01 12:18:51'

Columns


Name	Type	References	Description
Id [KEY]	Integer		Id of the deployment.
Attempt	Integer		Attempt number.
CompletedOn	String		The date on which deployment is complete.
Conditions	String		The list of condition associated with deployment.
DefinitionEnvironmentId	Integer		Release definition environment Id.
DeploymentStatus	String		Status of the deployment. The allowed values are all, failed, inProgress, notDeployed, partiallySucceeded, succeeded, undefined.
LastModifiedByDisplayName	String		The display name of the user who last modified this deployment.
LastModifiedById	String		The Id of the user who last modified this deployment.
LastModifiedByUrl	String		The URL of the user who last modified this deployment.
LastModifiedOn	Datetime		The date on which deployment is last modified.
OperationStatus	String		Operation status of deployment. The allowed values are all, approved, canceled, cancelling, deferred, evaluatingGates, gateFailed, manualInterventionPending, pending, phaseCanceled, phaseFailed, phaseInProgress, phasePartiallySucceeded, phaseSucceeded, queued, queuedForAgent, queuedForPipeline, rejected, scheduled, undefined.
PostDeployApprovals	String		List of PostDeployApprovals.
PreDeployApprovals	String		List of PreDeployApprovals.
ProjectId	String		Id of the project.
ProjectName	String		Name of the project.
QueuedOn	Date		The date on which deployment is queued.
Reason	String		Reason of deployment.
ReleaseId	Integer		The Id of the release.
ReleaseDefinitionId	Integer		The Id of the release definition.
ReleaseDefinitionName	String		The Name of the release definition.
ReleaseDefinitionPath	String		The Path of the release definition.
ReleaseEnvironmentId	Integer		The Id of the release environment.
ReleaseEnvironmentName	String		The Name of the release environment.
ReleaseEnvironmentUrl	String		The URL of the release environment.
RequestedByDisplayName	String		The Display name of the user who requested.
RequestedById	String		The Id of the user who requested.
RequestedByUrl	String		The URL of the user who requested.
RequestedForDisplayName	String		The display name of the user for whom deployment is requested.
RequestedForId	String		The Id of the user for whom deployment is requested.
RequestedForUrl	String		The URL of the user for whom deployment is requested.
ScheduledDeploymentTime	Date		The date on which deployment is scheduled.
StartedOn	Datetime		The date on which deployment is started.

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
LatestAttemptsOnly	Boolean		Includes latest attempts only.
SourceBranch	String		Source branch.

TaskGroupInputs

Retrieves a list of inputs for the specific task group.

Table Specific Information

Select

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

TaskGroupId supports the '=' operator.
ProjectId supports the '=' operator.

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

NOTE: TaskGroupId is required in order to query TaskGroupInputs.

For example:

	SELECT * FROM TaskGroupInputs WHERE ProjectId = '66eb7414-f622-4eff-88da-3ad681f19073' AND TaskGroupId = 7

Columns


Name	Type	References	Description
ProjectId	String		Id of the project.
TaskGroupId [KEY]	String	TaskGroups.Id	Id of the taskgroup.
Aliases	String		Aliases.
DefaultValue	String		Default value of the task group input.
GroupName	String		Task group name.
HelpMarkDown	String		Help mark down.
Label	String		Label of the input.
Name	String		Name of the input.
Options	String		Options of the task group input.
Properties	String		Properties of the task group input.
Required	Boolean		Indicated whether this input is required.
Type	String		Type of the input.
ValidationExpression	String		Validation expression of the input.
ValidationMessage	String		Validation message of the input.
VisibleRule	String		Visible rule of the input.

TaskGroupSourceDefinitions

Retrieves a list of source definitions for the specific task group.

Table Specific Information

Select

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

TaskGroupId supports the '=' operator.
ProjectId supports the '=' operator.

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

NOTE: TaskGroupId is required in order to query TaskGroupSourceDefinitions.

For example:

	SELECT * FROM TaskGroupSourceDefinitions WHERE ProjectId = '66eb7414-f622-4eff-88da-3ad681f19073' AND TaskGroupId = 7

Columns


Name	Type	References	Description
ProjectId	String		Id of the project.
TaskGroupId [KEY]	Integer	TaskGroups.Id	Id of the taskgroup.
AuthKey	String		Auth key of the source definition.
Endpoint	String		Source definition endpoint.
Selector	String		Source definition selector.
Target	String		Source definition target.

Tasks

Retrieves tasks in a task group.

Table Specific Information

Select

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

TaskGroupId supports the '=' operator.
ProjectId supports the '=' operator.

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

NOTE: TaskGroupId is required in order to query TaskGroupSourceDefinitions.

For example:

	SELECT * FROM Tasks WHERE ProjectId = '66eb7414-f622-4eff-88da-3ad681f19073' AND TaskGroupId = 7

Columns


Name	Type	References	Description
ProjectId	String		Id of the project.
TaskGroupId [KEY]	String	TaskGroups.Id	Id of the task group.
AlwaysRun	Boolean		Indicates whether to run the task always.
Condition	String		Condition for the task.
ContinueOnError	Boolean		Indicates whether to continue on error or not.
DisplayName	String		The display name of the task.
Enabled	Boolean		Indicates whether task is enabled or not.
Environment	String		Dictionary of environment variables.
Inputs	String		Dictionary of inputs.
TaskDefinitionType	String		The definition type.
TaskId	String		The unique identifier of task.
TaskVersionSpec	String		The version specification of the task.
TimeoutInMinutes	Integer		The maximum time in minutes, that a task is allowed to execute on agent before being cancelled by server.

TeamMembers

Retrieves a list of members for a specific team.

Table Specific Information

Select

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

TeamId supports the '=' operator.

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

NOTE: TeamId is required in order to query TeamMembers.

For example:

	SELECT * FROM TeamMembers WHERE ProjectId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf' AND TeamId = '27369296-c53e-4f21-9cac-1f0d62c87e40'

Columns


Name	Type	References	Description
ProjectId	String		The Project Identifier to which this team belongs to.
TeamId	String	Teams.Id	The Team Identifier to which this member belongs to.
IdentityDescriptor	String		The descriptor is the primary way to reference the graph subject while the system is running.
IdentityDisplayName	String		This is the non-unique display name of the member.
IdentityId	String		Unique Id of the member.
IdentityUrl	String		This url is the full route to the source resource of this graph subject.
IsTeamAdmin	Boolean		Indicates if this member is admin of the team.

TestAttachments

Retrieves a list of test result or run Attachments.

Table Specific Information

Select

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

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

NOTE: TestRunId is required in order to query TestAttachments.

Id supports the '=,in' operators.
TestRunId supports the '=' operator.
TestResultId supports the '=' operator.

For example:

	SELECT * FROM TestAttachments WHERE ProjectId = '1e313382-5f07-43be-b5ae-1dcfa51ffaf4' AND TestRunId = 6 AND Id IN (1, 2, 3)
	SELECT * FROM TestAttachments WHERE ProjectId = '1e313382-5f07-43be-b5ae-1dcfa51ffaf4' AND TestRunId = 6 AND TestResultId = 100000

Columns


Name	Type	References	Description
Id [KEY]	Integer		Id of the test attachment.
ProjectId	String		Id of the project.
TestRunId [KEY]	Integer	TestRuns.Id	Id of the test run.
TestResultId	Integer	TestResults.Id	Id of the test result.
AttachmentType	String		Attachment type.
Comment	String		Comment associated with attachment.
CreatedDate	Datetime		Attachment created date.
FileName	String		The File name of the attachment.
Size	Integer		Attachment size.
Url	String		Attachment URL.

TestCasePointAssignments

Retrieves point assignments for the specific test case.

Table Specific Information

Select

The Sync App 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.
TestPlanId supports the '=' operator.
TestSuiteId supports the '=' operator.
ConfigurationIds supports the 'in' operator.

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

NOTE: TestPlanId, TestSuiteId, and TestCaseId are required in order to query TestCasePointAssignments.

For example:

SELECT * FROM TestCasePointAssignments WHERE ProjectId = '03e4b7af-3bff-49d0' AND TestPlanId = 1 AND TestSuiteId = 2 AND TestCaseId = 1

Columns


Name	Type	References	Description
Id	Integer		Id of the test case point.
ProjectId	String		Id of the Project.
TestPlanId	Integer	TestPlans.Id	Id of the test plan.
TestSuiteId	Integer	TestSuites.Id	Id of the test suite.
TestCaseId	String	TestCases.Id	Id of the test case.
ConfigurationId	Integer		Id of the Configuration Assigned to the test point.
ConfigurationName	String		Name of the Configuration Assigned to the test point.
TesterLinksAvatarHref	String		Reference links.
TesterDisplayName	String		The non-unique display name of the tester.
TesterId	String		The Id of the tester.
TesterUrl	String		The URL of the tester.

TestCases

Retrieves a list of all test cases.

Table Specific Information

Select

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

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

NOTE: TestPlanId and TestSuiteId is required in order to query TestCases.

Id supports the '=,in' operators.
ProjectId supports the '=' operator.
TestPlanId supports the '=' operator.
TestSuiteId supports the '=' operator.
ConfigurationIds supports the 'in' operator.

For example:

	SELECT * FROM TestCases WHERE ProjectId = '1e313382-5f07-43be-b5ae-1dcfa51ffaf4' AND TestPlanId = 296 AND TestSuiteId = 298
	SELECT * FROM TestCases WHERE ProjectId = '1e313382-5f07-43be-b5ae-1dcfa51ffaf4' AND TestPlanId = 296 AND TestSuiteId = 298 AND ConfigurationIds IN (7, 10)
	SELECT * FROM TestCases WHERE ProjectId = '03e4b7af-3bff-49d0' AND TestPlanId = 1 AND TestSuiteId = 2

Delete

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

Columns


Name	Type	References	Description
Id [KEY]	Integer		Work item id.
LinksSelfHref	String		Self reference link.
LinksConfigurationHref	String		Configuration reference link.
LinksSourcePlanHref	String		Source plan reference link.
LinksSourceProjectHref	String		Source project reference link.
LinksSourceSuiteHref	String		Source suite reference link.
LinksTestPointsHref	String		Test points reference link.
Order	Integer		Order of the test case in the suite.
ProjectId	String		Id of the project.
ProjectLastUpdateTime	Date		Last updated time of the project.
ProjectName	String		Name of the project.
ProjectState	String		State of the project.
ProjectVisibility	String		Visibility of the project.
TestPlanId	Integer	TestPlans.Id	Id of the test plan.
TestPlanName	String		Name of the test plan.
TestSuiteId	Integer	TestSuites.Id	Id of the test suite.
TestSuiteName	String		Name of the test suite.
WorkItemName	String		Work item name.
WorkItemFields	String		Work item fields.
ItemUrl	String		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
ConfigurationIds	String		Fetch Test Cases which contains all the configuration Ids specified.

TestPoints

Retrieves a list of test points.

Table Specific Information

Select

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

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

NOTE: TestPlanId and TestSuiteId is required in order to query TestPoints.

Id supports the '=,in' operators.
ProjectId supports the '=' operator.
TestRunId supports the '=' operator.
TestCaseId supports the '=' operator.
ConfigurationId supports the '=' operator.
IncludePointDetails supports the '=' operator.

For example:

	SELECT * FROM TestPoints WHERE ProjectId = '1e313382-5f07-43be-b5ae-1dcfa51ffaf4' AND TestPlanId = 296 AND TestSuiteId = 298
	SELECT * FROM TestPoints WHERE ProjectId = '1e313382-5f07-43be-b5ae-1dcfa51ffaf4' AND TestPlanId = 296 AND TestSuiteId = 298 AND Id = 1
	SELECT * FROM TestPoints WHERE ProjectId = '1e313382-5f07-43be-b5ae-1dcfa51ffaf4' AND TestPlanId = 296 AND TestSuiteId = 298 AND IncludePointDetails = false

Columns


Name	Type	References	Description
Id [KEY]	Integer		Id of the Test Point.
ProjectId	String		Id of the Project.
AssignedToDisplayName	String		The non-unique name of the user to whom its assigned.
AssignedToId	String		The Id of the user.
Automated	Boolean		Is the Test Point for Automated Test Case or Manual.
Comment	String		Comment associated to the Test Point.
ConfigurationId	String		Id of the Configuration associated to the Test Point.
ConfigurationName	String		Name of the Configuration associated to the Test Point.
FailureType	String		Failure type of test point.
LastResetToActive	String		Last Reset to Active Time Stamp for the Test Point.
LastResolutionStateId	Integer		Last resolution state id of test point.
LastResultId	String		Id of the last result of the test point.
LastResultName	String		Name of the last result of the test point.
LastResultUrl	String		Url of the last result of the test point.
LastResultDetailsDateCompleted	String		Completed date of last result.
LastResultDetailsDuration	Integer		Duration of last result.
LastResultDetailsRunById	String		Id of the user who run this last result.
LastResultState	String		Last result state of test point.
LastRunBuildNumber	String		Last run build number of test point.
LastTestRunId	String		Id of the Last test run of test point.
LastTestRunName	String		Name of the last test run of test point.
LastTestRunUrl	String		Url of the last test run of test point.
LastUpdatedByDisplayName	String		The non-unique display name of the user who last updated this test point.
LastUpdatedById	String		Id of the user who last updated this test point.
LastUpdatedByUrl	String		The full REST API Resource Url.
LastUpdatedDate	Datetime		Last updated date of test point.
Outcome	String		Outcome of Test Point.
Revision	Integer		Revision Number.
State	String		State of test point.
TestCaseId	String	TestCases.Id	Id of the test case associated to test point.
TestCaseUrl	String		Url of the test case associated to test point.
TestCaseWebUrl	String		WebUrl of the test case associated to test point.
TestPlanId	String	TestPlans.Id	Id of the test plan of test point.
TestPlanName	String		Name of the Test Plan of test point.
TestPlanUrl	String		Url of the Test Plan of test point.
TestSuiteId	String	TestSuites.Id	Id of the Suite of test point.
TestSuiteName	String		Name of the Suite of test point.
TestSuiteUrl	String		Url of the Suite of test point.
Url	String		Test Point URL.
WorkItemProperties	String		Work item properties of test point.

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
IncludePointDetails	String		If set to false, returns only necessary information.

TestResultIterationDetails

Retrieves iteration details for the test result.

Table Specific Information

Select

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

ProjectId supports the '=' operator.
TestRunId supports the '=' operator.
TestResultId supports the '=' operator.
IncludeActionResults supports the '=' operator.

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

NOTE: TestRunId and TestResultId are required in order to query TestResultIterationDetails.

For example:

	SELECT * FROM TestResultIterationDetails WHERE ProjectId = '1e313382-5f07-43be-b5ae-1dcfa51ffaf4' AND TestRunId = 6 AND TestResultId = 100001

Columns


Name	Type	References	Description
Id [KEY]	Integer		ID of test iteration result.
ProjectId	String		Id of the project.
TestRunId	Integer	TestRuns.Id	Id of the test run.
TestResultId	Integer	TestResults.Id	Id of the test result.
ActionResults	String		Test step results in an iteration.
Comment	String		Comment in test iteration result.
CompletedDate	Datetime		Time when execution completed.
DurationInMs	Integer		Duration of execution.
ErrorMessage	String		Error message in test iteration result execution.
Outcome	String		Test outcome if test iteration result.
Parameters	String		Test parameters in an iteration.
StartedDate	Datetime		Time when execution started.
Url	String		Url to test iteration result.

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
IncludeActionResults	Boolean		Indicates whether to include result details for each action performed in the test iteration.

TestRunStatistics

Retrieves test run statistics, used when we want to get summary of a run by outcome.

Table Specific Information

Select

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

RunId supports the '=' operator.
ProjectId supports the '=' operator.

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

NOTE: RunId is required in order to query TestRunStatistics.

For example:

	SELECT * FROM TestRunStatistics WHERE ProjectId = '1e313382-5f07-43be-b5ae-1dcfa51ffaf4' AND RunId = 6

Columns


Name	Type	References	Description
RunId	String	TestRuns.Id	Id of the Test Run.
ProjectId	String		Id of the Project.
Count	Integer		Test result count of the given outcome.
Outcome	String		Test Result outcome.
ResolutionStateId	Integer		Test Resolution State Id.
ResolutionStateName	String		Test Resolution State Name.
ResolutionStateProjectId	String		Test Resolution State Project Id.
State	String		State of the Test Run.

TestSubResults

Retrieves sub results for the test result.

Table Specific Information

Select

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

ProjectId supports the '=' operator.
TestRunId supports the '=' operator.
TestResultId supports the '=' operator.

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

NOTE: TestRunId and TestResultId are required in order to query TestResultIterationDetails.

For example:

	SELECT * FROM TestSubResults WHERE ProjectId = '1e313382-5f07-43be-b5ae-1dcfa51ffaf4' AND TestRunId = 6 AND TestResultId = 100001

Columns


Name	Type	References	Description
Id	Integer		ID of test sub result.
ProjectId	String		Id of the Project.
TestRunId	Integer	TestRuns.Id	Id of the Test Run.
TestResultId	Integer	TestResults.Id	Id of the Test Result.
Comment	String		Comment in test sub result.
CompletedDate	Datetime		Time when execution completed.
ComputerName	String		Machine where test executed.
ConfigurationId	String		Id of the Test Configuration.
ConfigurationName	String		Name of the Test Configuration.
ConfigurationUrl	String		Url of the Test Configuration.
DisplayName	String		Name of sub result.
DurationInMs	Integer		Duration of execution.
ErrorMessage	String		Error message in test iteration result execution.
LastUpdatedDate	Datetime		Last updated datetime of test result.
Outcome	String		Test outcome if test iteration result.
ParentId	Integer		Immediate parent ID of sub result.
ResultGroupType	String		Hierarchy type of the result, default value of None means its leaf node.
SequenceId	Integer		Index number of sub result.
StackTrace	String		Stacktrace with maxSize= 1000 chars.
StartedDate	Datetime		Time when test execution started.
Url	String		Url to sub result.

TfvcBranches

Retrieves a collection of branch roots -- first-level children, branches with no parents.

Table Specific Information

Select

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

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

Path supports the '=' operator.
ProjectId supports the '=' operator.
IncludeParent supports the '=' operator.
IncludeChildren supports the '=' operator.
IncludeDeleted supports the '=' operator.
IncludeLinks supports the '=' operator.

For example:

	SELECT * FROM TfvcBranches WHERE Path = '$/example/example-repo'

Columns


Name	Type	References	Description
Path	String		Path for the branch.
ProjectId	String		Id of the project this branch belongs to.
Children	String		List of children for the branch.
CreatedDate	Datetime		Creation date of the branch.
Description	String		Description of the branch.
IsDeleted	Boolean		Indicates whether the branch is deleted or not.
Links	String		A collection of REST reference links.
Mappings	String		List of branch mappings.
OwnerDisplayName	String		The non-unique display name of the owner.
OwnerId	String		The Id of the owner.
OwnerUrl	String		The Full Http url of the owner.
ParentPath	String		Path of the branch's parent.
RelatedBranches	String		List of paths of the related branches.
Url	String		URL to retrieve the branch.

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
IncludeParent	Boolean		Return the parent branch, if there is one.
IncludeChildren	Boolean		Return the child branches for each root branch.
IncludeDeleted	Boolean		Return deleted branches.
IncludeLinks	Boolean		Return links.

TfvcChangesets

Retrieves Tfvc Changesets.

Table Specific Information

Select

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

ProjectId supports the '=' operator.
ChangesetId supports the '=' operator.
Author supports the '=' operator.
FromDate supports the '=' operator.
ToDate supports the '=' operator.
FromId supports the '=' operator.
ToId supports the '=' operator.

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

For example:

	SELECT * FROM TfvcChangesets WHERE ProjectId = '837ccd31'

    SELECT * FROM TfvcChangesets WHERE ChangesetId = '837ccd31'

    SELECT * FROM TfvcChangesets WHERE ToDate = '07/03/2021 12:00:00'

Columns


Name	Type	References	Description
ChangesetId	Integer		Changeset Id.
Url	String		URL to retrieve the item.
Links	String		A collection of REST reference links.
AuthorId	String		The Id of the author.
AuthorDisplayName	String		The non-unique display name of the author.
AuthorUrl	String		The Full HTTP URL of the author.
CheckedInById	String		The id of the user who has checked in.
CheckedInByDisplayName	String		The non-unique display name of the user who has checked in.
CheckedInByUrl	String		The Full HTTP URL of the user who has checked in.
CreatedDate	Datetime		Creation date of the changeset.
Comment	String		Comment for the changeset.
CommentTruncated	Boolean		Indicates if the Comment result is truncated or not.
ProjectId	String		Id of the project this changeset belongs 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
MaxCommentLength	Integer		Include details about associated work items in the response.
Author	String		Alias or display name of user who made the changes.
FollowRenames	Boolean		Whether or not to follow renames for the given item being queried.
FromId	Integer		If provided, only include changesets after this changesetID.
IncludeLinks	Boolean		Whether to include the _links field on the shallow references.
ItemPath	String		Path of item to search under.
ToId	Integer		If provided, a version descriptor for the latest change list to include.

WikiVersions

Retrieves all wiki versions for the specific wiki.

Table Specific Information

Select

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

WikiId supports the '=' operator.
ProjectId supports the '=' operator.

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

NOTE: WikiId is required in order to query WikiVersions.

For example:

	SELECT * FROM WikiVersions WHERE WikiId = '9d910096-122d-432e-b64a-8ef4d06d2905'
	SELECT * FROM WikiVersions WHERE ProjectId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf' AND WikiId = '9d910096-122d-432e-b64a-8ef4d06d2905'

Columns


Name	Type	References	Description
WikiId	String	Wikis.Id	Id of the wiki.
ProjectId	String		Id of the project.
Version	String		Version string identifier (name of tag/branch, SHA1 of commit).
VersionOptions	String		Version options - Specify additional modifiers to version (e.g Previous).
VersionType	String		Version type (branch, tag, or commit). Determines how Id is interpreted.

WorkItemIds

Retrieves a list of work items, for use with other tables in the Project schema.

Table Specific Information

Select

The Sync App uses the Azure DevOps API to process WHERE clause conditions built with the following column and operator:

Id supports the '=' operator.

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

For example:

SELECT * FROM WorkItemIds WHERE Id = 1

Columns


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

WorkItemRelations

Retrieves relationships between work items.

Columns


Name	Type	References	Description
Id	Integer		Id of the work item.
LinkedItemUrl	String		URL of the linked object.
RelationType	String		Relation type.
ProjectId	String		Id of the project.
RelationName	String		Name of the relation.
Comment	String		Comment on the relation.
IsLocked	Boolean		Whether the relation is locked or not.
ActionID	Integer		ID of action which created link.
AuthorizedDate	Datetime		Authorization date of action which created link.
ResourceCreatedDate	Datetime		Creation date of linked resource.
ResourceModifiedDate	Datetime		Modification date of linked resource.
RevisedDate	Datetime		Last revision date of link.

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.

WorkItemRevisionFields

Retrieves a list of work item revision fields

Table Specific Information

Select

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

Id supports the '=' operator.
Revision supports the '=' operator.
FieldName supports the '=', 'IN' operators.

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

For example:

SELECT * FROM WorkItemRevisionFields WHERE Id = 1

Columns


Name	Type	References	Description
Id [KEY]	Integer		Id of the work item.
Revision [KEY]	Integer		Revision of the work item.
FieldName	String		Field Key for the work item revision.
FieldValue	String		Field Value for the work item revision.
ProjectId	String		Id of the project this changeset belongs 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
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.

WorkItemRevisions

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

Table Specific Information

Select

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

Id supports the '=' operator.
Revision supports the '=' operator.

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

For example:

SELECT * FROM WorkItemRevisions WHERE Id = 1

Columns


Name	Type	References	Description
Id [KEY]	Integer		Id of the work item.
Revision [KEY]	Integer		Revision of the work item.
ProjectId	String		Id of the project this changeset belongs 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
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.

WorkItemsFields

Retrieves a list of work items fields

Columns


Name	Type	References	Description
Id [KEY]	Integer		Id of the work item.
ProjectId	String		Id of the project.
FieldName [KEY]	String		Field Key for the work item.
FieldValue	String		Field Value for the work 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
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.

WorkItemUpdatesHistory

Retrieves a list of work items updates history. 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
ProjectId	String		Id of the project.

Repository 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 a repository.

Key Features

The Sync App models Azure DevOps entities like documents, folders, and groups as relational 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 Sync App.

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. Views are statically defined to model Projects, Tasks, Teams, etc. Dynamic views, such as queries exposed as views, and views for looking up specific combinations of project_team work items are supported.

Stored Procedures

Stored Procedures are function-like interfaces to Azure DevOps. Stored procedures allow you to execute operations to Azure DevOps, including downloading documents and moving envelopes.

Tables

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

Azure DevOps Connector for CData Sync Tables


Name	Description
GitBranches	Retrieves a collection of git branch.
PullRequestReviewers	Retrieves a list of reviewers for the specific pull request
Pushes	Retrieves pushes associated with the specified repository.

GitBranches

Retrieves a collection of git branch.

Table Specific Information

Select

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

ProjectId supports the '=' operator.
RepositoryId supports the '=' operator.
IncludeLinks supports the '=' operator.
MyBranches supports the '=' operator.
IncludeStatuses supports the '=' operator.
LatestStatusesOnly supports the '=' operator.

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

NOTE: RepositoryId is required in order to query GitBranches.

For example:

	SELECT * FROM GitBranches WHERE RepositoryId = '02b4a62d-2f5f-4d69-8420-29257dcc8051'
	SELECT * FROM GitBranches WHERE RepositoryId = '02b4a62d-2f5f-4d69-8420-29257dcc8051' AND IncludeLinks = true

Update

The following is an example of updating a GitBranches table:

UPDATE GitBranches SET isLocked = true WHERE name = 'abc' AND ProjectId = 'b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937' AND RepositoryId = 'e50698d4-bb6e-400f-a1a0-5f4d17517d9e'

Columns


Name	Type	ReadOnly	References	Description
ObjectId [KEY]	String	True		Path for the branch.
ProjectId	String	False		Id of the project this branch belongs to.
RepositoryId	String	False		Id of the repositories.
Name	String	False		Name of the branch.
CreatorDisplayName	String	True		The non-unique display name of the user who created this branch.
CreatorUrl	String	True		The URL of the user who created this branch.
CreatorLinksAvatarHref	String	True		Avatar reference link of the creator.
CreatorId	String	True		Id of the creator.
CreatorDescriptor	String	True		Descriptor of the creator.
Links	String	True		Aggregate of the reference links.
Statuses	String	True		Contains the metadata of a service/extension posting a status.
Url	String	True		Full HTTP resource link of the branch.
isLocked	Boolean	False		Represents a boolean value if the branch is locked or not.

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
IncludeLinks	Boolean	Specifies if referenceLinks should be included in the result.
IncludeStatuses	Boolean	Includes up to the first 1000 commit statuses for each ref.
MyBranches	Boolean	Includes only branches that the user owns, the branches the user favorites, and the default branch.
LatestStatusesOnly	Boolean	rue to include only the tip commit status for each ref.

PullRequestReviewers

Retrieves a list of reviewers for the specific pull request

Table Specific Information

Select

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

ProjectId supports the '=' operator.
RepositoryId supports the '=' operator.
PullRequestId supports the '=' operator.

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

NOTE: RepositoryId and PullRequestId are required in order to query PullRequestReviewers.

For example:

	SELECT * FROM PullRequestReviewers WHERE ProjectId = '66eb7414-f622-4eff-88da-3ad681f19073' AND RepositoryId = '229ec1a1-609f-4545-af5a-85f00ce7428b' AND PullRequestId = 2
	SELECT * FROM PullRequestReviewers WHERE RepositoryId = '229ec1a1-609f-4545-af5a-85f00ce7428b' AND PullRequestId = 2

Insert

The following is an example of inserting into a PullRequestReviewers table:

INSERT INTO PullRequestReviewers (ProjectId, RepositoryId, PullRequestId, Id, Vote) VALUES ('c831d3b4-a289-462f', 'b20311e2-b5e4-444f', 2, '0c51c6d1-49b7-661b', 5)

Update

The following is an example of updating a PullRequestReviewers table:

UPDATE PullRequestReviewers SET DisplayName = 'cdata1', hasDeclined = false WHERE ProjectId = '1db52c22-a4e9-4ddc-ba82-5c0ae281dfd2' AND RepositoryId = '6b9dab15-dfe0-4488-a2b1-c5fe2a34b2cb' AND PullRequestId = 1 AND Id = '6a10066b-ee05-40c0-a207-b9fcbac568be'

Delete

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

DELETE FROM PullRequestReviewers WHERE ProjectId = '1db52c22-a4e9-4ddc-ba82-5c0ae281dfd2' AND RepositoryId = '6b9dab15-dfe0-4488-a2b1-c5fe2a34b2cb' AND PullRequestId = 1 AND Id = '6a10066b-ee05-40c0-a207-b9fcbac568be'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		Id of the reviewer
ProjectId	String	True		Id of the project.
RepositoryId	String	True		Id of the repository.
PullRequestId [KEY]	Integer	True	PullRequests.Id	Id of the pullrequest.
DisplayName	String	False		Display name of the reviewer.
ReviewerUrl	String	False		URL to retrieve information about the reviewer.
Url	String	False		This url is the full route to the source resource of the reviewer.
Vote	Integer	False		Vote on a pull request: 10 - approved, 5 - approved with suggestions, 0 - no vote, -5 - waiting for author, -10 - rejected.
isFlagged	Boolean	False		Whether a pull request is flagged.
hasDeclined	Boolean	False		Whether a pull request has been declined.

Pushes

Retrieves pushes associated with the specified repository.

Table Specific Information

Select

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

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

NOTE: RepositoryId is required in order to query Pushes.

PushId supports the '=' operator.
ProjectId supports the '=' operator.
RepositoryId supports the '=' operator.
PushedById supports the '=' operator.
Date supports the '>=,<' operators.
BranchName supports the '=' operator.

For example:

	SELECT * FROM Pushes WHERE RepositoryId = '229ec1a1-609f-4545-af5a-85f00ce7428b'
	SELECT * FROM Pushes WHERE RepositoryId = '229ec1a1-609f-4545-af5a-85f00ce7428b' AND PushId = 16 AND Date >= '2000-01-01'

Insert

The following are examples of inserting into a Pushes table. For example:

INSERT INTO RefUpdates#TEMP (NewObjectId, Name) VALUES ('b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937', 'abcd')
INSERT INTO Commits#TEMP (Comment, AuthorName) VALUES ('b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937', 'xyz')
INSERT INTO Pushes (ProjectId, RepositoryId, Commits, RefUpdates) VALUES ('b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937', '84609754-3aa8-4b91-8a56-8ccb48f6d017', Commits#TEMP, RefUpdates#TEMP)

Columns


Name	Type	ReadOnly	References	Description
PushId [KEY]	Integer	True		Id of the push.
ProjectId	String	True		Id of the project.
Date	Datetime	True		The date of the push.
PushedByDisplayName	String	False		The display name of the user.
PushedById	String	False		The Id of the user.
PushedByUrl	String	False		The URL of the user.
RepositoryDefaultBranch	String	False		The default of the repository.
RepositoryId	String	True		The Id of the repository.
RepositoryName	String	False		Name of the repository.
RepositoryProjectId	String	False		The Project Id.
RepositoryProjectName	String	False		The Project name.
RepositoryProjectState	String	False		The Project state.
RepositoryProjectUrl	String	False		The Project URL.
RepositoryRemoteUrl	String	False		The Remote URL of the repository.
RepositoryUrl	String	False		The URL of the repository.
Url	String	False		The URL of the push.

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
BranchName	String	Branch name.
RefUpdates	String	Branch name.
Commits	String	List of inputs for the specific task group.

Views

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.

Azure DevOps Connector for CData Sync Views


Name	Description
CommitChanges	Retrieve changes for a particular commit.
CommitGitStatus	Retrieve git status for the specific commit.
Commits	Retrieve git commits for a project.
CommitWorkItems	Retrieve work items for the specific commit.
GitStats	Retrieve statistics about all branches within a repository.
PullRequestAttachments	Retrieves a list of attachments for the specific pull request.
PullRequests	Retrieves a list of pull requests.
PullRequestWorkItems	Retrieves a list of work items associated with a pull request.
PushRefUpdates	Retrieve Ref Updates for the specific push.

CommitChanges

Retrieve changes for a particular commit.

Table Specific Information

Select

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

ProjectId supports the '=' operator.
RepositoryId supports the '=' operator.
CommitId supports the '=' operator.

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

NOTE: RepositoryId and CommitId are required in order to query CommitChanges.

For example:

    SELECT * FROM CommitChanges WHERE RepositoryId = '02b4a62d-2f5f-4d69-8420-29257dcc8051' AND CommitId = '01832416d11f521e2e8fa1dc3acd9aebd93c773f'
	SELECT * FROM CommitChanges WHERE ProjectId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf' AND RepositoryId = '02b4a62d-2f5f-4d69-8420-29257dcc8051' AND CommitId = '01832416d11f521e2e8fa1dc3acd9aebd93c773f'

Columns


Name	Type	References	Description
CommitId	String	Commits.Id	Id of the commit.
ProjectId	String		Id of the project.
RepositoryId	String		Id of the repository.
ChangeType	String		The type of change that was made to the item.
ItemGitObjectType	String		Git object type.
ItemObjectId	String		Change object Id.
ItemIsFolder	Boolean		Indicates whether its a folder.
ItemPath	String		Path of the change.
ItemUrl	String		URL of the commit change.

CommitGitStatus

Retrieve git status for the specific commit.

Table Specific Information

Select

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

CommitId supports the '=' operator.
ProjectId supports the '=' operator.
RepositoryId supports the '=' operator.

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

For example:

	SELECT * FROM CommitGitStatus WHERE RepositoryId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf' AND CommitId = '01832416d11f521e2e8fa1dc3acd9aebd93c773f'

Columns


Name	Type	References	Description
CommitId	String	Commits.Id	Id of the commit.
ProjectId	String		Id of the project.
RepositoryId	String		Id of the repository.
ContextGenre	String		Genre of the status. Typically name of the service/tool generating the status, can be empty.
ContextName	String		Name identifier of the status.
CreatedByDisplayName	String		The non-unique display name of the user who created the status.
CreatedById	String		The Id of the user who created the status.
CreationDate	Datetime		Creation date and time of the status.
Description	String		Status description. Typically describes current state of the status.
Id	Integer		Id of the status.
State	String		State of the status.
TargetUrl	String		URL with status details.
UpdatedDate	Datetime		Last updated date and time of the status.

Commits

Retrieve git commits for a project.

Table Specific Information

Select

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

Id supports the '=' operator.
ProjectId supports the '=' operator.
RepositoryId supports the '=' operator.
AuthorName supports the '=' operator.
CommitterName supports the '=' operator.
PushId supports the '=' operator.
ExcludeDeletes supports the '=' operator.
HistoryMode supports the '=' operator.
IncludePushData supports the '=' operator.
IncludeLinks supports the '=' operator.
ItemPath supports the '=' operator.
VersionType supports the '=' operator.
Version supports the '=' operator.
VersionOptions supports the '=' operator.
CompareVersionType supports the '=' operator.
CompareVersion supports the '=' operator.
CompareVersionOptions supports the '=' operator.
FromCommitId supports the '=' operator.
ToCommitId supports the '=' operator.

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

NOTE: RepositoryId is required in order to query Commits.

For example:

	SELECT * FROM Commits WHERE RepositoryId = '02b4a62d-2f5f-4d69-8420-29257dcc8051'
	SELECT * FROM Commits WHERE RepositoryId = '02b4a62d-2f5f-4d69-8420-29257dcc8051' AND Id = '01832416d11f521e2e8fa1dc3acd9aebd93c773f'
	SELECT * FROM Commits WHERE RepositoryId = '02b4a62d-2f5f-4d69-8420-29257dcc8051' AND IncludePushData = true

Columns


Name	Type	References	Description
Id [KEY]	String		Id of the commit.
ProjectId	String		Id of the project.
RepositoryId	String		Id of the repository.
AuthorDate	Datetime		Date of the Git operation.
AuthorEmail	String		Email address of the user performing the Git operation.
AuthorName	String		Name of the user performing the Git operation.
ChangeCountsAdd	String		Counts of the types of changes (edits, deletes, etc.) included with the commit.
ChangeCountsEdit	String		Counts of the types of changes (edits, deletes, etc.) included with the commit.
ChangeCountsDelete	String		Counts of the types of changes (edits, deletes, etc.) included with the commit.
Comment	String		Comment or message of the commit.
CommentTruncated	Boolean		Indicates if the comment is truncated from the full Git commit comment message.
CommitterDate	Datetime		Date of the Git operation.
CommitterEmail	String		Email address of the user performing the Git operation.
CommitterName	String		Name of the user performing the Git operation.
Links	String		Aggregate of the reference links.
LinkedWorkItems	String		List of linked WorkItem Ids.
Parents	String		An enumeration of the parent commit IDs for this commit.
PushDate	Datetime		Date of the commit push.
PushedByDisplayName	String		This is the non-unique display name of the user.
PushedById	String		Id of the user.
PushedByUrl	String		The URL of the user resource.
PushId	Integer		The Id of the commit push.
RemoteUrl	String		Remote URL path to the commit.
Url	String		REST URL for this resource.

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
FromCommitId	String		A lower bound for filtering commits alphabetically.
ToCommitId	String		An upper bound for filtering commits alphabetically.
ExcludeDeletes	Boolean		Only applies when an itemPath is specified. This determines whether to exclude delete entries of the specified path.
HistoryMode	String		What Git history mode should be used. This only applies to the search criteria when Ids = null and an itemPath is specified. The allowed values are firstParent, fullHistory, fullHistorySimplifyMerges, simplifiedHistory.
IncludePushData	Boolean		Whether to include the push information.
IncludeLinks	Boolean		Whether to include the links.
ItemPath	String		Path of item to search under.
VersionType	String		Version type (branch, tag, or commit). Determines how Id is interpreted.
Version	String		Version string identifier (name of tag/branch, SHA1 of commit).
VersionOptions	String		Version options - Specify additional modifiers to version (e.g Previous).
CompareVersionType	String		Version type (branch, tag, or commit). Determines how Id is interpreted.
CompareVersion	String		Version string identifier (name of tag/branch, SHA1 of commit).
CompareVersionOptions	String		Version options - Specify additional modifiers to version (e.g Previous).

CommitWorkItems

Retrieve work items for the specific commit.

Table Specific Information

Select

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

ProjectId supports the '=' operator.
RepositoryId supports the '=' operator.
CommitId supports the '=' operator.

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

NOTE: RepositoryId and CommitId are required in order to query CommitWorkItems.

For example:

	SELECT * FROM CommitWorkItems WHERE RepositoryId = '02b4a62d-2f5f-4d69-8420-29257dcc8051' AND CommitId = '01832416d11f521e2e8fa1dc3acd9aebd93c773f'

Columns


Name	Type	References	Description
Id [KEY]	String		Id of the work item.
ProjectId	String		Id of the project.
RepositoryId	String		Id of the repository.
CommitId	String	Commits.Id	Id of the commit.
Url	String		URL of the work item.

GitStats

Retrieve statistics about all branches within a repository.

Table Specific Information

Select

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

ProjectId supports the '=' operator.
RepositoryId supports the '=' operator.
Name supports the '=' operator.
VersionOptions supports the '=' operator.
Version supports the '=' operator.
VersionType supports the '=' operator.

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

NOTE: RepositoryId is required in order to query GitStats.

For example:

    SELECT * FROM GitStats WHERE RepositoryId = '02b4a62d-2f5f-4d69-8420-29257dcc8051'
	SELECT * FROM GitStats WHERE RepositoryId = '02b4a62d-2f5f-4d69-8420-29257dcc8051' AND VersionOptions = 'none'
	SELECT * FROM GitStats WHERE RepositoryId = '02b4a62d-2f5f-4d69-8420-29257dcc8051' AND Name = 'master'

Columns


Name	Type	References	Description
Name [KEY]	String		Name of the branch.
ProjectId	String		Id of the project.
RepositoryId	String		Id of the repository.
AheadCount	Integer		Number of commits ahead.
BehindCount	Integer		Number of commits behind.
CommitId	String		ID (SHA-1) of the commit.
IsBaseVersion	Boolean		Indicates whether this is base version.

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
VersionOptions	String		Version options - Specify additional modifiers to version (e.g Previous). The allowed values are firstParent, none, previousChange.
Version	String		Version string identifier (name of tag/branch, SHA1 of commit).
VersionType	String		Version type (branch, tag, or commit). Determines how Id is interpreted. The allowed values are branch, commit, tag.

PullRequestAttachments

Retrieves a list of attachments for the specific pull request.

Table Specific Information

Select

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

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

NOTE: RepositoryId and PullRequestId is required in order to query PullRequestAttachments.

PullRequestId supports the '=' operator.
ProjectId supports the '=' operator.
RepositoryId supports the '=' operator.

For example:

	SELECT * FROM PullRequestAttachments WHERE ProjectId = '1e313382-5f07-43be-b5ae-1dcfa51ffaf4' AND RepositoryId = '123e04e0-6c4c-4675-8636-af6b0bc29d43' AND PullRequestId = 4

Columns


Name	Type	References	Description
Id	Integer		Id of the attachment.
ProjectId	String		Id of the project.
RepositoryId	String		Id of the repository.
PullRequestId	Integer	PullRequests.Id	Id of the pull request.
AuthorDisplayName	String		The non-unique display name of the author.
AuthorId	String		Id of the author.
AuthorUrl	String		The URL of the author.
ContentHash	String		Content hash of on-disk representation of file content. Its calculated by the server by using SHA1 hash function.
CreatedDate	Datetime		The time the attachment was uploaded.
Description	String		The description of the attachment.
DisplayName	String		The display name of the attachment.
Properties	String		Properties of the attachments.
Url	String		The URL to download the content of the attachment.

PullRequests

Retrieves a list of pull requests.

Table Specific Information

Select

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

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

NOTE: ProjectId or RepositoryId or Id is required in order to query PullRequests.

Id supports the '=' operator.
ProjectId supports the '=' operator.
RepositoryId supports the '=' operator.
CreatedById supports the '=' operator.
SourceRefName supports the '=' operator.
Status supports the '=' operator.
TargetRefName supports the '=' operator.
IncludeLinks supports the '=' operator.
ReviewerId supports the '=' operator.
SourceRepositoryId supports the '=' operator.
TargetRepositoryId supports the '=' operator.

For example:

	SELECT * FROM PullRequests WHERE ProjectId = '1e313382-5f07-43be-b5ae-1dcfa51ffaf4'
	SELECT * FROM PullRequests WHERE RepositoryId = '02b4a62d-2f5f-4d69-8420-29257dcc8051'
	SELECT * FROM PullRequests WHERE ProjectId = '66eb7414-f622-4eff-88da-3ad681f19073' AND IncludeLinks = true
	SELECT * FROM PullRequests WHERE Id = 1
	SELECT * FROM PullRequests WHERE RepositoryId = '229ec1a1-609f-4545-af5a-85f00ce7428b' AND TargetRefName = 'refs/heads/master'

Insert

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

Update

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

Columns


Name	Type	References	Description
Id [KEY]	Integer		Id of the pull request.
ProjectId	String		Id of the project.
ArtifactId	String		A string which uniquely identifies this pull request.
AutoCompleteSetByDisplayName	String		This is the non-unique display name of the resource.
AutoCompleteSetById	String		Id of the resource.
AutoCompleteSetByUrl	String		URL of the resource.
ClosedByDisplayName	String		This is the non-unique name of the user who closed this pull request.
ClosedById	String		Id of the User.
ClosedByUrl	String		URL of the user.
ClosedDate	Datetime		The date when the pull request was closed (completed, abandoned, or merged externally).
CodeReviewId	Integer		The code review ID of the pull request. Used internally.
CompletionOptionsBypassPolicy	Boolean		If true, policies will be explicitly bypassed while the pull request is completed.
CompletionOptionsBypassReason	String		If policies are bypassed, this reason is stored as to why bypass was used.
CompletionOptionsDeleteSourceBranch	Boolean		If true, the source branch of the pull request will be deleted after completion.
CompletionOptionsMergeCommitMessage	String		If set, this will be used as the commit message of the merge commit.
CompletionOptionsMergeStrategy	String		Specify the strategy used to merge the pull request during completion.
CompletionOptionsTransitionWorkItems	Boolean		If true, we will attempt to transition any work items linked to the pull request into the next logical state.
CompletionOptionsTriggeredByAutoComplete	Boolean		If true, the current completion attempt was triggered via auto-complete.
CompletionQueueTime	String		The most recent date at which the pull request entered the queue to be completed. Used internally.
CreatedByDisplayName	String		This is the non-unique name of the user who created this pull request.
CreatedById	String		Id of the user.
CreatedByUrl	String		URL of the user.
CreationDate	Datetime		The date when the pull request was created.
Description	String		The description of the pull request.
ForkSourceCreatorDisplayName	String		The non-unique display name of the user who created this source.
ForkSourceCreatorId	String		Id of the user.
ForkSourceIsLocked	Boolean		Indicates whether the fork source is locked or not.
ForkSourceIsLockedByDisplayName	String		The non0unique display name of the user who locked this fork source.
ForkSourceIsLockedById	String		The Id of the user.
ForkSourceName	String		Name of the fork source.
ForkSourceObjectId	String		Object Id of the fork source.
ForkSourcePeeledObjectId	String		Peeled Object Id of the fork source.
ForkSourceRepositoryId	String		Repository Id of the fork.
ForkSourceUrl	String		Url of the fork source.
IsDraft	Boolean		Draft / WIP pull request.
Labels	String		The labels associated with the pull request.
LastMergeCommitId	String		Id (SHA-1) of the last merged commit.
LastMergeCommitUrl	String		REST URL for the last merged commit.
LastMergeSourceCommitId	String		Id (SHA-1) of the last merged source commit.
LastMergeSourceCommitUrl	String		REST URL for the last merged source commit.
LastMergeTargetCommitId	String		Id (SHA-1) of the last merged target commit.
LastMergeTargetCommitUrl	String		REST URL for the last merged source commit.
Links	String		Aggregate of the reference links.
MergeFailureMessage	String		If set, pull request merge failed for this reason.
MergeFailureType	String		The type of failure (if any) of the pull request merge.
MergeId	String		The Id of the job used to run the pull request merge.
MergeOptionsDetectRenameFalsePositives	Boolean		The options which are used when a pull request merge is created.
MergeOptionsDisableRenames	Boolean		If true, rename detection will not be performed during the merge.
MergeStatus	String		The current status of the pull request merge.
RemoteUrl	String		Remote URL of the pull request.
RepositoryId	String		Id of the repository.
SourceRefName	String		The name of the source branch of the pull request.
Status	String		The status of the pull request.
SupportsIterations	Boolean		If true, this pull request supports multiple iterations.
TargetRefName	String		The name of the target branch of the pull request.
Title	String		The title of the pull request.
Url	String		The URL of the pull request.

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
IncludeLinks	Boolean		Whether to include the _links field on the shallow references.
ReviewerId	String		If set, search for pull requests that have this identity as a reviewer.
SourceRepositoryId	String		If set, search for pull requests whose source branch is in this repository.
TargetRepositoryId	String		If set, search for pull requests whose target branch is in this repository.

PullRequestWorkItems

Retrieves a list of work items associated with a pull request.

Table Specific Information

Select

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

ProjectId supports the '=' operator.
RepositoryId supports the '=' operator.
PullRequestId supports the '=' operator.

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

NOTE: RepositoryId and PullRequestId are required in order to query PullRequestWorkItems.

For example:

	SELECT * FROM PullRequestWorkItems WHERE RepositoryId = '229ec1a1-609f-4545-af5a-85f00ce7428b' AND PullRequestId = 2

Columns


Name	Type	References	Description
Id [KEY]	String		Id of the work item.
Url	String		URL of the work item.
ProjectId	String		Id of the project.
RepositoryId	String		Id of the repository.
PullRequestId	Integer	PullRequests.Id	Id of the pull request.

PushRefUpdates

Retrieve Ref Updates for the specific push.

Table Specific Information

Select

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

PushId supports the '=' operator.
ProjectId supports the '=' operator.
RepositoryId supports the '=' operator.

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

NOTE: RepositoryId and PushId are required in order to query PushRefUpdates.

For example:

	SELECT * FROM PushRefUpdates WHERE RepositoryId = '229ec1a1-609f-4545-af5a-85f00ce7428b' AND PushId = 16

Columns


Name	Type	References	Description
ProjectId	String		Id of the project.
PushId	Integer	Pushes.Id	Id of the push.
Name	String		Name of the ref update.
NewObjectId	String		New object Id.
OldObjectId	String		Old object Id.
RepositoryId	String		Id of the repository.
IsLocked	Boolean		Represents a boolean value if the branch is locked or not.

Analytics Data Model

This section documents the Views and Stored Procedures available to connect to the Azure DevOps Analytics APIs.

Note that this schema can only be accessed when Catalog is set to a project, and Schema is set to Analytics.

Views

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.

Azure DevOps Connector for CData Sync Views


Name	Description
Areas	List Areas
BoardLocations	List BoardLocations
Dates	List Dates
Iterations	List Iterations
Projects	List Projects
Tags	List Tags
Teams	List Teams
Users	List Users
WorkItemBoardSnapshot	List WorkItemBoardSnapshot
WorkItemIds	Retrieves a list of work items.
WorkItemLinks	List WorkItemLinks
WorkItemRevisions	List WorkItemRevisions
WorkItems	List WorkItems
WorkItemSnapshot	List WorkItemSnapshot
WorkItemTypeFields	List WorkItemTypeFields

Areas

List Areas

Columns

References Description String [KEY] String String String String String String String String String String String String String String String String String String Integer Integer String />


Name	Type style="white-space:nowrap">ParentReference
AreaSK
AreaId
AreaLevel1
AreaLevel10
AreaLevel11
AreaLevel12
AreaLevel13
AreaLevel14
AreaLevel2
AreaLevel3
AreaLevel4
AreaLevel5
AreaLevel6
AreaLevel7
AreaLevel8
AreaLevel9
AreaName
AreaPath
Depth
Number
ProjectSK

BoardLocations

List BoardLocations

Columns


Name	Type	References	Description
ParentReference	String
BoardLocationSK [KEY]	Integer
BacklogType	String
BoardCategoryReferenceName	String
BoardId	String
BoardLevel	Integer
BoardName	String
ChangedDate	Datetime
ColumnId	String
ColumnItemLimit	Integer
ColumnName	String
ColumnOrder	Integer
Done	String
IsBoardVisible	Boolean
IsColumnSplit	Boolean
IsCurrent	Boolean
IsDefaultLane	Boolean
IsDone	Boolean
LaneId	String
LaneName	String
LaneOrder	Integer
ProjectSK	String
RevisedDate	Datetime
TeamSK	String

Dates

List Dates

Columns


Name	Type	References	Description
ParentReference	String
DateSK [KEY]	Integer
Date	Datetime
DayName	String
DayOfMonth	Integer
DayOfWeek	Integer
DayOfYear	Integer
DayShortName	String
IsLastDayOfPeriod	String
Month	String
MonthName	String
MonthOfYear	Integer
MonthShortName	String
WeekEndingDate	Datetime
WeekStartingDate	Datetime
Year	Integer
YearMonth	Integer

Iterations

List Iterations

Columns


Name	Type	References	Description
ParentReference	String
IterationSK [KEY]	String
Depth	Integer
EndDate	Datetime
IsEnded	Boolean
IterationId	String
IterationLevel1	String
IterationLevel10	String
IterationLevel11	String
IterationLevel12	String
IterationLevel13	String
IterationLevel14	String
IterationLevel2	String
IterationLevel3	String
IterationLevel4	String
IterationLevel5	String
IterationLevel6	String
IterationLevel7	String
IterationLevel8	String
IterationLevel9	String
IterationName	String
IterationPath	String
Number	Integer
ProjectSK	String
StartDate	Datetime

Projects

List Projects

Table Specific Information

Select

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

ProjectSK supports the '=,in' operator.

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

For example:

	SELECT * FROM Projects WHERE ProjectSK = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf'
	SELECT * FROM Projects WHERE ProjectSK IN ('837ccd31-8159-4db3-b8ce-de0c36d2a0bf', '837ccd31-8159-4db3-b8ce-de0c36d2a0hg')

Columns


Name	Type	References	Description
ParentReference	String
ProjectSK [KEY]	String
ProjectId	String
ProjectName	String
ProjectVisibility	String

Teams

List Teams

Table Specific Information

Select

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

TeamSK supports the '=,in' operator.

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

For example:

	SELECT * FROM Teams WHERE TeamSK = '66eb7414-f622-4eff-88da-3ad681f19073'
	SELECT * FROM Teams WHERE TeamSK IN ('4dbc0cec-c473-652b-972f-f42587b4494d', '6ddc3cee-c232-634b-342f-f84325b4494d')

Columns


Name	Type	References	Description
ParentReference	String
TeamSK [KEY]	String
ProjectSK	String
TeamId	String
TeamName	String

Users

List Users

Table Specific Information

Select

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

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

UserSK supports the '=,in' operators.

For example:

	
	SELECT * FROM Users WHERE UserSK = '4dbc0cec-c473-652b-972f-f42587b4494d'
	SELECT * FROM Users WHERE UserSK IN ('4dbc0cec-c473-652b-972f-f42587b4494d', '6ddc3cee-c232-634b-342f-f84325b4494d')

Columns


Name	Type	References	Description
ParentReference	String
UserSK [KEY]	String
UserEmail	String
UserId	String
UserName	String

WorkItemBoardSnapshot

List WorkItemBoardSnapshot

Columns


Name	Type	References	Description
ParentReference	String
BoardLocationSK [KEY]	Integer
DateSK	Integer
WorkItemId	Integer
ActivatedByUserSK	String
ActivatedDate	Datetime
ActivatedDateSK	Integer
Activity	String
AreaSK	String
AssignedToUserSK	String
AutomatedTestId	String
AutomatedTestName	String
AutomatedTestStorage	String
AutomatedTestType	String
AutomationStatus	String
BacklogType	String
BoardCategoryReferenceName	String
BoardId	String
BoardLevel	Integer
BoardName	String
BusinessValue	Integer
ChangedByUserSK	String
ChangedDate	Datetime
ChangedDateSK	Integer
ClosedByUserSK	String
ClosedDate	Datetime
ClosedDateSK	Integer
ColumnId	String
ColumnItemLimit	Integer
ColumnName	String
ColumnOrder	Integer
CommentCount	Integer
CompletedDate	Datetime
CompletedDateSK	Integer
CompletedWork	Double
Count	Double
CreatedByUserSK	String
CreatedDate	Datetime
CreatedDateSK	Integer
CycleTimeDays	Datetime
Done	String
DueDate	Datetime
Effort	Double
FinishDate	Datetime
InProgressDateSK	Integer
IntegrationBuild	String
IsBoardVisible	Boolean
IsColumnSplit	Boolean
IsCurrent	Boolean
IsDefaultLane	Boolean
IsDone	Boolean
IsLastDayOfPeriod	String
Issue	String
IterationSK	String
LaneId	String
LaneName	String
LaneOrder	Integer
LeadTimeDays	Double
Microsoft_VSTS_CodeReview_AcceptedBySK	String
Microsoft_VSTS_CodeReview_AcceptedDate	Datetime
Microsoft_VSTS_CodeReview_ClosedStatus	String
Microsoft_VSTS_CodeReview_ClosedStatusCode	Double
Microsoft_VSTS_CodeReview_ClosingComment	String
Microsoft_VSTS_CodeReview_Context	String
Microsoft_VSTS_CodeReview_ContextCode	Double
Microsoft_VSTS_CodeReview_ContextOwner	String
Microsoft_VSTS_CodeReview_ContextType	String
Microsoft_VSTS_Common_ReviewedBySK	String
Microsoft_VSTS_Common_StateCode	Double
Microsoft_VSTS_Feedback_ApplicationType	String
Microsoft_VSTS_TCM_TestSuiteType	String
Microsoft_VSTS_TCM_TestSuiteTypeId	Double
OriginalEstimate	Double
ParentWorkItemId	Integer
Priority	Integer
ProjectSK	String
Rating	String
Reason	String
RemainingWork	Double
ResolvedByUserSK	String
ResolvedDate	Datetime
ResolvedDateSK	Integer
ResolvedReason	String
Revision	Integer
Risk	String
Severity	String
StackRank	Double
StartDate	Datetime
State	String
StateCategory	String
StateChangeDate	Datetime
StateChangeDateSK	Integer
StoryPoints	Double
TagNames	String
TargetDate	Datetime
TeamSK	String
TimeCriticality	Double
Title	String
ValueArea	String
Watermark	Integer
WorkItemRevisionSK	Integer
WorkItemType	String

WorkItemIds

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.
ProjectId	String		Id of the project.

WorkItemLinks

List WorkItemLinks

Columns


Name	Type	References	Description
ParentReference	String
WorkItemLinkSK [KEY]	Integer
AnalyticsUpdatedDate	Datetime
Comment	String
CreatedDate	Datetime
DeletedDate	Datetime
LinkTypeId	Integer
LinkTypeIsAcyclic	Boolean
LinkTypeIsDirectional	Boolean
LinkTypeName	String
LinkTypeReferenceName	String
ProjectSK	String
SourceWorkItemId	Integer
TargetWorkItemId	Integer

WorkItemRevisions

List WorkItemRevisions

Columns


Name	Type	References	Description
ParentReference	String
Revision [KEY]	Integer
WorkItemId	Integer
ActivatedByUserSK	String
ActivatedDate	Datetime
ActivatedDateSK	Integer
Activity	String
AnalyticsUpdatedDate	Datetime
AreaSK	String
AssignedToUserSK	String
AutomatedTestId	String
AutomatedTestName	String
AutomatedTestStorage	String
AutomatedTestType	String
AutomationStatus	String
BusinessValue	Integer
ChangedByUserSK	String
ChangedDate	Datetime
ChangedDateSK	Integer
ClosedByUserSK	String
ClosedDate	Datetime
ClosedDateSK	Integer
CommentCount	Integer
CompletedDate	Datetime
CompletedDateSK	Integer
CompletedWork	Double
Count	Double
CreatedByUserSK	String
CreatedDate	Datetime
CreatedDateSK	Integer
CycleTimeDays	Double
DateSK	Integer
DueDate	Datetime
Effort	Double
FinishDate	Datetime
FoundIn	String
InProgressDate	Datetime
InProgressDateSK	Integer
IntegrationBuild	Datetime
IsCurrent	Boolean
IsLastRevisionOfDay	Boolean
IsLastRevisionOfPeriod	String
Issue	String
IterationSK	String
LeadTimeDays	Double
Microsoft_VSTS_CodeReview_AcceptedBySK	String
Microsoft_VSTS_CodeReview_AcceptedDate	Datetime
Microsoft_VSTS_CodeReview_ClosedStatus	String
Microsoft_VSTS_CodeReview_ClosedStatusCode	Double
Microsoft_VSTS_CodeReview_ClosingComment	String
Microsoft_VSTS_CodeReview_Context	String
Microsoft_VSTS_CodeReview_ContextCode	Double
Microsoft_VSTS_CodeReview_ContextOwner	String
Microsoft_VSTS_CodeReview_ContextType	String
Microsoft_VSTS_Common_ReviewedBySK	String
Microsoft_VSTS_Common_StateCode	Double
Microsoft_VSTS_Feedback_ApplicationType	String
Microsoft_VSTS_TCM_TestSuiteType	String
Microsoft_VSTS_TCM_TestSuiteTypeId	Double
OriginalEstimate	Double
ParentWorkItemId	Integer
Priority	Integer
ProjectSK	String
Rating	String
Reason	String
RemainingWork	Double
ResolvedByUserSK	String
ResolvedDate	Datetime
ResolvedDateSK	Integer
ResolvedReason	String
RevisedDate	Datetime
RevisedDateSK	Integer
Risk	String
Severity	String
StackRank	Double
StartDate	Datetime
State	String
StateCategory	String
StateChangeDate	Datetime
StateChangeDateSK	Integer
StoryPoints	Double
TagNames	String
TargetDate	Datetime
TimeCriticality	Double
Title	String
ValueArea	String
Watermark	Integer
WorkItemRevisionSK	Integer
WorkItemType	String

WorkItems

List WorkItems

Table Specific Information

Select

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

WorkItemId supports the '=' operator.

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

For example:

    SELECT * FROM WorkItems WHERE WorkItemId = 1
	SELECT * FROM WorkItems WHERE WorkItemId IN (1, 2, 3)

Columns


Name	Type	References	Description
ParentReference	String
WorkItemId [KEY]	Integer
ActivatedByUserSK	String
ActivatedDate	Datetime
ActivatedDateSK	Integer
Activity	String
AnalyticsUpdatedDate	Datetime
AreaSK	String
AssignedToUserSK	String
AutomatedTestId	String
AutomatedTestName	String
AutomatedTestStorage	String
AutomatedTestType	String
AutomationStatus	String
BusinessValue	Integer
ChangedByUserSK	String
ChangedDate	Datetime
ChangedDateSK	Integer
ClosedByUserSK	String
ClosedDate	Datetime
ClosedDateSK	Integer
CommentCount	Integer
CompletedDate	Datetime
CompletedDateSK	Integer
CompletedWork	Double
Count	Double
CreatedByUserSK	String
CreatedDate	Datetime
CreatedDateSK	Integer
CycleTimeDays	Double
DueDate	Datetime
Effort	Double
FinishDate	Datetime
FoundIn	String
InProgressDate	Datetime
InProgressDateSK	Integer
IntegrationBuild	String
Issue	String
IterationSK	String
LeadTimeDays	Double
Microsoft_VSTS_CodeReview_AcceptedBySK	String
Microsoft_VSTS_CodeReview_AcceptedDate	Datetime
Microsoft_VSTS_CodeReview_ClosedStatus	String
Microsoft_VSTS_CodeReview_ClosedStatusCode	Double
Microsoft_VSTS_CodeReview_ClosingComment	String
Microsoft_VSTS_CodeReview_Context	String
Microsoft_VSTS_CodeReview_ContextCode	Double
Microsoft_VSTS_CodeReview_ContextOwner	String
Microsoft_VSTS_CodeReview_ContextType	String
Microsoft_VSTS_Common_ReviewedBySK	String
Microsoft_VSTS_Common_StateCode	Double
Microsoft_VSTS_Feedback_ApplicationType	String
Microsoft_VSTS_TCM_TestSuiteType	String
Microsoft_VSTS_TCM_TestSuiteTypeId	Double
OriginalEstimate	Double
ParentWorkItemId	Integer
Priority	Integer
ProjectSK	String
Rating	String
Reason	String
RemainingWork	Double
ResolvedByUserSK	String
ResolvedDate	Datetime
ResolvedDateSK	Integer
ResolvedReason	String
Revision	Integer
Risk	String
Severity	String
StackRank	Double
StartDate	Datetime
State	String
StateCategory	String
StateChangeDate	Datetime
StateChangeDateSK	Integer
StoryPoints	Double
TagNames	String
TargetDate	Datetime
TimeCriticality	Double
Title	String
ValueArea	String
Watermark	Integer
WorkItemRevisionSK	Integer
WorkItemType	String

WorkItemSnapshot

List WorkItemSnapshot

Columns


Name	Type	References	Description
ParentReference	String
DateSK [KEY]	Integer
WorkItemId	Integer
ActivatedByUserSK	String
ActivatedDate	Datetime
ActivatedDateSK	Integer
Activity	String
AreaSK	String
AssignedToUserSK	String
AutomatedTestId	String
AutomatedTestName	String
AutomatedTestStorage	String
AutomatedTestType	String
AutomationStatus	String
BusinessValue	Integer
ChangedByUserSK	String
ChangedDate	Datetime
ChangedDateSK	Integer
ClosedByUserSK	String
ClosedDate	Datetime
ClosedDateSK	Integer
CommentCount	Integer
CompletedDate	Datetime
CompletedDateSK	Integer
CompletedWork	Double
Count	Double
CreatedByUserSK	String
CreatedDate	Datetime
CreatedDateSK	Integer
CycleTimeDays	Double
DateValue	Datetime
DueDate	Datetime
Effort	Double
FinishDate	Datetime
FoundIn	String
InProgressDate	Datetime
InProgressDateSK	Integer
IntegrationBuild	String
IsLastDayOfPeriod	String
Issue	String
IterationSK	String
LeadTimeDays	Double
Microsoft_VSTS_CodeReview_AcceptedBySK	String
Microsoft_VSTS_CodeReview_AcceptedDate	Datetime
Microsoft_VSTS_CodeReview_ClosedStatus	String
Microsoft_VSTS_CodeReview_ClosedStatusCode	Double
Microsoft_VSTS_CodeReview_ClosingComment	String
Microsoft_VSTS_CodeReview_Context	String
Microsoft_VSTS_CodeReview_ContextCode	Double
Microsoft_VSTS_CodeReview_ContextOwner	String
Microsoft_VSTS_CodeReview_ContextType	String
Microsoft_VSTS_Common_ReviewedBySK	String
Microsoft_VSTS_Common_StateCode	Double
Microsoft_VSTS_Feedback_ApplicationType	String
Microsoft_VSTS_TCM_TestSuiteType	String
Microsoft_VSTS_TCM_TestSuiteTypeId	Double
OriginalEstimate	Double
ParentWorkItemId	Integer
Priority	Integer
ProjectSK	String
Rating	String
Reason	String
RemainingWork	Double
ResolvedByUserSK	String
ResolvedDate	Datetime
ResolvedDateSK	Integer
ResolvedReason	String
RevisedDate	Datetime
RevisedDateSK	Integer
Revision	Integer
Risk	String
Severity	String
StackRank	Double
StartDate	Datetime
State	String
StateCategory	String
StateChangeDate	Datetime
StateChangeDateSK	Integer
StoryPoints	Double
TagNames	String
TargetDate	Datetime
TimeCriticality	Double
Title	String
ValueArea	String
Watermark	Integer
WorkItemRevisionSK	Integer
WorkItemType	String

WorkItemTypeFields

List WorkItemTypeFields

Columns


Name	Type	References	Description
ParentReference	String
FieldName [KEY]	String
ProjectSK	String
WorkItemType	String
FieldReferenceName	String
FieldType	String

Connection String Options

The connection string properties are the various options that can be used to establish a connection. This section provides a complete list of the options you can configure in the connection string for this provider. Click the links for further details.

For more information on establishing a connection, see Establishing a Connection.

Authentication

Property	Description
AuthScheme	The type of authentication to use when connecting to Azure DevOps. Azure DevOps OnPremise connections support only Basic authentication.
Organization	The name of the Organization or Collection, depending upon the value of AzureDevOpsEdition .
PersonalAccessToken	The personal access token used for accessing the data in your organization.
AzureDevOpsEdition	The edition of AzureDevOps being used. Set either [AzureDevOps Online] or [AzureDevOps OnPremise].
URL	The Public URL of the Azure DevOps OnPremise Instance; for example, http://localhost/defaultcollection.
User	The Azure DevOps user account used to authenticate.
AzureDevOpsServiceAPI	The REST API version of AzureDevOps being used. Set either [7.1], [6.0], or [5.1].

Azure Authentication

Property	Description
AzureTenant	Identifies the Azure DevOps tenant being used to access data, either by name (for example, contoso.omnicrosoft.com) or ID. (Conditional).
AzureEnvironment	Specifies the Azure network environment to which you will connect. Must be the same network to which your Azure account was added.

OAuth

Property	Description
OAuthClientId	Specifies the client Id that was assigned the custom OAuth application was created. (Also known as the consumer key.) This ID registers the custom application with the OAuth authorization server.
OAuthClientSecret	Specifies the client secret that was assigned when the custom OAuth application was created. (Also known as the consumer secret ). This secret registers the custom application with the OAuth authorization server.

SSL

Property	Description
SSLServerCert	Specifies the certificate to be accepted from the server when connecting using TLS/SSL.

Firewall

Property	Description
FirewallType	Specifies the protocol the provider uses to tunnel traffic through a proxy-based firewall.
FirewallServer	Identifies the IP address, DNS name, or host name of a proxy used to traverse a firewall and relay user queries to network resources.
FirewallPort	Specifies the TCP port to be used for a proxy-based firewall.
FirewallUser	Identifies the user ID of the account authenticating to a proxy-based firewall.
FirewallPassword	Specifies the password of the user account authenticating to a proxy-based firewall.

Proxy

Property	Description
ProxyAutoDetect	Specifies whether the provider checks your system proxy settings for existing proxy server configurations, rather than using a manually specified proxy server.
ProxyServer	The hostname or IP address of the proxy server that you want to route HTTP traffic through.
ProxyPort	The TCP port on your specified proxy server (set in the ProxyServer connection property) that has been reserved for routing HTTP traffic to and from the client.
ProxyAuthScheme	Specifies the authentication method the provider uses when authenticating to the proxy server specified in the ProxyServer connection property.
ProxyUser	The username of a user account registered with the proxy server specified in the ProxyServer connection property.
ProxyPassword	The password associated with the user specified in the ProxyUser connection property.
ProxySSLType	The SSL type to use when connecting to the proxy server specified in the ProxyServer connection property.
ProxyExceptions	A semicolon separated list of destination hostnames or IPs that are exempt from connecting through the proxy server set in the ProxyServer connection property.

Logging

Property	Description
LogModules	Specifies the core modules to include in the log file. Use a semicolon-separated list of module names. By default, all modules are logged.

Schema

Property	Description
Location	Specifies the location of a directory containing schema files that define tables, views, and stored procedures. Depending on your service's requirements, this may be expressed as either an absolute path or a relative path.
BrowsableSchemas	Optional setting that restricts the schemas reported to a subset of all available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC .
Tables	Optional setting that restricts the tables reported to a subset of all available tables. For example, Tables=TableA,TableB,TableC .
Views	Optional setting that restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC .
Schema	Specify this property to connect with a particular schema.
Catalog	Specify this property to connect with a particular catalog.

Miscellaneous

Property	Description
ApplyTransformations	A comma separated list of supported Apply transformations. To indicate none, set to 'off'. This disables auto detect.
IncludeCustomFields	A boolean indicating if you would like to include custom fields in the column listing.
MaxRows	Specifies the maximum rows returned for queries without aggregation or GROUP BY.
Other	Specifies additional hidden properties for specific use cases. These are not required for typical provider functionality. Use a semicolon-separated list to define multiple properties.
PseudoColumns	Specifies the pseudocolumns to expose as table columns. Use the format 'TableName=ColumnName;TableName=ColumnName'. The default is an empty string, which disables this property.
Timeout	Specifies the maximum time, in seconds, that the provider waits for a server response before throwing a timeout error. The default is 60 seconds. Set to 0 to disable the timeout.
UserDefinedViews	Specifies a filepath to a JSON configuration file defining custom views. The provider automatically detects and uses the views specified in this file.

Authentication

This section provides a complete list of the Authentication properties you can configure in the connection string for this provider.

Property	Description
AuthScheme	The type of authentication to use when connecting to Azure DevOps. Azure DevOps OnPremise connections support only Basic authentication.
Organization	The name of the Organization or Collection, depending upon the value of AzureDevOpsEdition .
PersonalAccessToken	The personal access token used for accessing the data in your organization.
AzureDevOpsEdition	The edition of AzureDevOps being used. Set either [AzureDevOps Online] or [AzureDevOps OnPremise].
URL	The Public URL of the Azure DevOps OnPremise Instance; for example, http://localhost/defaultcollection.
User	The Azure DevOps user account used to authenticate.
AzureDevOpsServiceAPI	The REST API version of AzureDevOps being used. Set either [7.1], [6.0], or [5.1].

AuthScheme

The type of authentication to use when connecting to Azure DevOps. Azure DevOps OnPremise connections support only Basic authentication.

Remarks

AzureAD: Authenticate via Azure Active Directory (OAuth).
Basic: Authenticate via a Personal Access Token.

Organization

The name of the Organization or Collection, depending upon the value of AzureDevOpsEdition .

Remarks

The request returns data mapped under this Organization or Collection depending upon AzureDevOpsEdition value. The name of the Organization is set to [AzureDevOps Online]. The name of the Collection is set to [AzureDevOps OnPremise].

PersonalAccessToken

The personal access token used for accessing the data in your organization.

Remarks

The personal access token can be found in your Organization > Profile > Personal Access Tokens.

AzureDevOpsEdition

The edition of AzureDevOps being used. Set either [AzureDevOps Online] or [AzureDevOps OnPremise].

Remarks

[AzureDevOps OnPremise] supports only Basic authentication. The URL and User properties are mandatory. [AzureDevOps Online] supports all available authschemes.

URL

The Public URL of the Azure DevOps OnPremise Instance; for example, http://localhost/defaultcollection.

Remarks

Enter only if AzureDevOpsEdition is set to [AzureDevOps OnPremise].

User

The Azure DevOps user account used to authenticate.

Remarks

Enter only if AzureDevOpsEdition is set to [AzureDevOps OnPremise].

AzureDevOpsServiceAPI

The REST API version of AzureDevOps being used. Set either [7.1], [6.0], or [5.1].

Remarks

The REST API version of AzureDevOps being used. Set either [7.1], [6.0], or [5.1]

Azure Authentication

This section provides a complete list of the Azure Authentication properties you can configure in the connection string for this provider.

Property	Description
AzureTenant	Identifies the Azure DevOps tenant being used to access data, either by name (for example, contoso.omnicrosoft.com) or ID. (Conditional).
AzureEnvironment	Specifies the Azure network environment to which you will connect. Must be the same network to which your Azure account was added.

AzureTenant

Identifies the Azure DevOps tenant being used to access data, either by name (for example, contoso.omnicrosoft.com) or ID. (Conditional).

Remarks

A tenant is a digital representation of your organization, primarily associated with a domain (for example, microsoft.com). The tenant is managed through a Tenant ID (also known as the directory ID), which is specified whenever you assign users permissions to access or manage Azure resources.

To locate the directory ID in the Azure Portal, navigate to Azure Active Directory > Properties.

Specifying AzureTenant is required when AuthScheme = either AzureServicePrincipal or AzureServicePrincipalCert, or if AuthScheme = AzureAD and the user belongs to more than one tenant.

AzureEnvironment

Specifies the Azure network environment to which you will connect. Must be the same network to which your Azure account was added.

Remarks

Required if your Azure account is part of a different network than the Global network, such as China, USGOVT, or USGOVTDOD.

OAuth

This section provides a complete list of the OAuth properties you can configure in the connection string for this provider.

Property	Description
OAuthClientId	Specifies the client Id that was assigned the custom OAuth application was created. (Also known as the consumer key.) This ID registers the custom application with the OAuth authorization server.
OAuthClientSecret	Specifies the client secret that was assigned when the custom OAuth application was created. (Also known as the consumer secret ). This secret registers the custom application with the OAuth authorization server.

OAuthClientId

Specifies the client Id that was assigned the custom OAuth application was created. (Also known as the consumer key.) This ID registers the custom application with the OAuth authorization server.

Remarks

OAuthClientId is one of a handful of connection parameters that need to be set before users can authenticate via OAuth. For details, see Establishing a Connection.

OAuthClientSecret

Specifies the client secret that was assigned when the custom OAuth application was created. (Also known as the consumer secret ). This secret registers the custom application with the OAuth authorization server.

Remarks

OAuthClientSecret is one of a handful of connection parameters that need to be set before users can authenticate via OAuth. For details, see Establishing a Connection.

SSL

This section provides a complete list of the SSL properties you can configure in the connection string for this provider.

Property	Description
SSLServerCert	Specifies the certificate to be accepted from the server when connecting using TLS/SSL.

SSLServerCert

Specifies the certificate to be accepted from the server when connecting using TLS/SSL.

Remarks

If using a TLS/SSL connection, this property can be used to specify the TLS/SSL certificate to be accepted from the server. Any other certificate that is not trusted by the machine is rejected.

This property can take the following forms:


Description	Example
A full PEM Certificate (example shortened for brevity)	-----BEGIN CERTIFICATE----- MIIChTCCAe4CAQAwDQYJKoZIhv......Qw== -----END CERTIFICATE-----
A path to a local file containing the certificate	C:\cert.cer
The public key (example shortened for brevity)	-----BEGIN RSA PUBLIC KEY----- MIGfMA0GCSq......AQAB -----END RSA PUBLIC KEY-----
The MD5 Thumbprint (hex values can also be either space or colon separated)	ecadbdda5a1529c58a1e9e09828d70e4
The SHA1 Thumbprint (hex values can also be either space or colon separated)	34a929226ae0819f2ec14b4a3d904f801cbb150d

If not specified, any certificate trusted by the machine is accepted.

Use '*' to signify to accept all certificates. Note that this is not recommended due to security concerns.

Firewall

This section provides a complete list of the Firewall properties you can configure in the connection string for this provider.

Property	Description
FirewallType	Specifies the protocol the provider uses to tunnel traffic through a proxy-based firewall.
FirewallServer	Identifies the IP address, DNS name, or host name of a proxy used to traverse a firewall and relay user queries to network resources.
FirewallPort	Specifies the TCP port to be used for a proxy-based firewall.
FirewallUser	Identifies the user ID of the account authenticating to a proxy-based firewall.
FirewallPassword	Specifies the password of the user account authenticating to a proxy-based firewall.

FirewallType

Specifies the protocol the provider uses to tunnel traffic through a proxy-based firewall.

Remarks

A proxy-based firewall (or proxy firewall) is a network security device that acts as an intermediary between user requests and the resources they access. The proxy accepts the request of an authenticated user, tunnels through the firewall, and transmits the request to the appropriate server.

Because the proxy evaluates and transfers data backets on behalf of the requesting users, the users never connect directly with the servers, only with the proxy.

Note: By default, the Sync App connects to the system proxy. To disable this behavior and connect to one of the following proxy types, set ProxyAutoDetect to false.

The following table provides port number information for each of the supported protocols.


Protocol	Default Port	Description
TUNNEL	80	The port where the Sync App opens a connection to Azure DevOps. Traffic flows back and forth via the proxy at this location.
SOCKS4	1080	The port where the Sync App opens a connection to Azure DevOps. SOCKS 4 then passes theFirewallUser value to the proxy, which determines whether the connection request should be granted.
SOCKS5	1080	The port where the Sync App sends data to Azure DevOps. If the SOCKS 5 proxy requires authentication, set FirewallUser and FirewallPassword to credentials the proxy recognizes.

To connect to HTTP proxies, use ProxyServer and ProxyPort. To authenticate to HTTP proxies, use ProxyAuthScheme, ProxyUser, and ProxyPassword.

FirewallServer

Identifies the IP address, DNS name, or host name of a proxy used to traverse a firewall and relay user queries to network resources.

Remarks

A proxy-based firewall (or proxy firewall) is a network security device that acts as an intermediary between user requests and the resources they access. The proxy accepts the request of an authenticated user, tunnels through the firewall, and transmits the request to the appropriate server.

Because the proxy evaluates and transfers data backets on behalf of the requesting users, the users never connect directly with the servers, only with the proxy.

FirewallPort

Specifies the TCP port to be used for a proxy-based firewall.

Remarks

A proxy-based firewall (or proxy firewall) is a network security device that acts as an intermediary between user requests and the resources they access. The proxy accepts the request of an authenticated user, tunnels through the firewall, and transmits the request to the appropriate server.

Because the proxy evaluates and transfers data backets on behalf of the requesting users, the users never connect directly with the servers, only with the proxy.

FirewallUser

Identifies the user ID of the account authenticating to a proxy-based firewall.

Remarks

A proxy-based firewall (or proxy firewall) is a network security device that acts as an intermediary between user requests and the resources they access. The proxy accepts the request of an authenticated user, tunnels through the firewall, and transmits the request to the appropriate server.

Because the proxy evaluates and transfers data backets on behalf of the requesting users, the users never connect directly with the servers, only with the proxy.

FirewallPassword

Specifies the password of the user account authenticating to a proxy-based firewall.

Remarks

A proxy-based firewall (or proxy firewall) is a network security device that acts as an intermediary between user requests and the resources they access. The proxy accepts the request of an authenticated user, tunnels through the firewall, and transmits the request to the appropriate server.

Because the proxy evaluates and transfers data backets on behalf of the requesting users, the users never connect directly with the servers, only with the proxy.

Proxy

This section provides a complete list of the Proxy properties you can configure in the connection string for this provider.

Property	Description
ProxyAutoDetect	Specifies whether the provider checks your system proxy settings for existing proxy server configurations, rather than using a manually specified proxy server.
ProxyServer	The hostname or IP address of the proxy server that you want to route HTTP traffic through.
ProxyPort	The TCP port on your specified proxy server (set in the ProxyServer connection property) that has been reserved for routing HTTP traffic to and from the client.
ProxyAuthScheme	Specifies the authentication method the provider uses when authenticating to the proxy server specified in the ProxyServer connection property.
ProxyUser	The username of a user account registered with the proxy server specified in the ProxyServer connection property.
ProxyPassword	The password associated with the user specified in the ProxyUser connection property.
ProxySSLType	The SSL type to use when connecting to the proxy server specified in the ProxyServer connection property.
ProxyExceptions	A semicolon separated list of destination hostnames or IPs that are exempt from connecting through the proxy server set in the ProxyServer connection property.

ProxyAutoDetect

Specifies whether the provider checks your system proxy settings for existing proxy server configurations, rather than using a manually specified proxy server.

Remarks

When this connection property is set to True, the Sync App checks your system proxy settings for existing proxy server configurations (no need to manually supply proxy server details).

This connection property takes precedence over other proxy settings. Set to False if you want to manually configure the Sync App to connect to a specific proxy server.

To connect to an HTTP proxy, see ProxyServer. For other proxies, such as SOCKS or tunneling, see FirewallType.

ProxyServer

The hostname or IP address of the proxy server that you want to route HTTP traffic through.

Remarks

The Sync App only routes HTTP traffic through the proxy server specified in this connection property when ProxyAutoDetect is set to False. If ProxyAutoDetect is set to True, which is the default, the Sync App instead routes HTTP traffic through the proxy server specified in your system proxy settings.

ProxyPort

The TCP port on your specified proxy server (set in the ProxyServer connection property) that has been reserved for routing HTTP traffic to and from the client.

Remarks

The Sync App only routes HTTP traffic through the proxy server port specified in this connection property when ProxyAutoDetect is set to False. If ProxyAutoDetect is set to True, which is the default, the Sync App instead routes HTTP traffic through the proxy server port specified in your system proxy settings.

For other proxy types, see FirewallType.

ProxyAuthScheme

Specifies the authentication method the provider uses when authenticating to the proxy server specified in the ProxyServer connection property.

Remarks

The authentication type can be one of the following:

BASIC: The Sync App performs HTTP BASIC authentication.
DIGEST: The Sync App performs HTTP DIGEST authentication.
NTLM: The Sync App retrieves an NTLM token.
NEGOTIATE: The Sync App retrieves an NTLM or Kerberos token based on the applicable protocol for authentication.
NONE: Set this when the ProxyServer does not require authentication.

For all values other than "NONE", you must also set the ProxyUser and ProxyPassword connection properties.

If you need to use another authentication type, such as SOCKS 5 authentication, see FirewallType.

ProxyUser

The username of a user account registered with the proxy server specified in the ProxyServer connection property.

Remarks

The ProxyUser and ProxyPassword connection properties are used to connect and authenticate against the HTTP proxy specified in ProxyServer.

After selecting one of the available authentication types in ProxyAuthScheme, set this property as follows:


ProxyAuthScheme Value	Value to set for ProxyUser
BASIC	The user name of a user registered with the proxy server.
DIGEST	The user name of a user registered with the proxy server.
NEGOTIATE	The username of a Windows user who is a valid user in the domain or trusted domain that the proxy server is part of, in the format `user@domain` or `domain\user`.
NTLM	The username of a Windows user who is a valid user in the domain or trusted domain that the proxy server is part of, in the format `user@domain` or `domain\user`.
NONE	Do not set the ProxyPassword connection property.

The Sync App only uses this username if ProxyAutoDetect is set to False. If ProxyAutoDetect is set to True, which is the default, the Sync App instead uses the username specified in your system proxy settings.

ProxyPassword

The password associated with the user specified in the ProxyUser connection property.

Remarks

The ProxyUser and ProxyPassword connection properties are used to connect and authenticate against the HTTP proxy specified in ProxyServer.

After selecting one of the available authentication types in ProxyAuthScheme, set this property as follows:


ProxyAuthScheme Value	Value to set for ProxyPassword
BASIC	The password associated with the proxy server user specified in ProxyUser.
DIGEST	The password associated with the proxy server user specified in ProxyUser.
NEGOTIATE	The password associated with the Windows user account specified in ProxyUser.
NTLM	The password associated with the Windows user account specified in ProxyUser.
NONE	Do not set the ProxyPassword connection property.

For SOCKS 5 authentication or tunneling, see FirewallType.

The Sync App only uses this password if ProxyAutoDetect is set to False. If ProxyAutoDetect is set to True, which is the default, the Sync App instead uses the password specified in your system proxy settings.

ProxySSLType

The SSL type to use when connecting to the proxy server specified in the ProxyServer connection property.

Remarks

This property determines when to use SSL for the connection to the HTTP proxy specified by ProxyServer. You can set this connection property to the following values :


AUTO	Default setting. If ProxyServer is set to an HTTPS URL, the Sync App uses the TUNNEL option. If ProxyServer is set to an HTTP URL, the component uses the NEVER option.
ALWAYS	The connection is always SSL enabled.
NEVER	The connection is not SSL enabled.
TUNNEL	The connection is made through a tunneling proxy. The proxy server opens a connection to the remote host and traffic flows back and forth through the proxy.

ProxyExceptions

A semicolon separated list of destination hostnames or IPs that are exempt from connecting through the proxy server set in the ProxyServer connection property.

Remarks

The ProxyServer is used for all addresses, except for addresses defined in this property. Use semicolons to separate entries.

Note that the Sync App uses the system proxy settings by default, without further configuration needed. If you want to explicitly configure proxy exceptions for this connection, set ProxyAutoDetect to False.

Logging

This section provides a complete list of the Logging properties you can configure in the connection string for this provider.

Property	Description
LogModules	Specifies the core modules to include in the log file. Use a semicolon-separated list of module names. By default, all modules are logged.

LogModules

Specifies the core modules to include in the log file. Use a semicolon-separated list of module names. By default, all modules are logged.

Remarks

This property lets you customize the log file content by specifying the logging modules to include. Logging modules categorize logged information into distinct areas, such as query execution, metadata, or SSL communication. Each module is represented by a four-character code, with some requiring a trailing space for three-letter names.

For example, EXEC logs query execution, and INFO logs general provider messages. To include multiple modules, separate their names with semicolons as follows: INFO;EXEC;SSL.

The Verbosity connection property takes precedence over the module-based filtering specified by this property. Only log entries that meet the verbosity level and belong to the specified modules are logged. Leave this property blank to include all available modules in the log file.

For a complete list of available modules and detailed guidance on configuring logging, refer to the Advanced Logging section in Logging.

Schema

This section provides a complete list of the Schema properties you can configure in the connection string for this provider.

Property	Description
Location	Specifies the location of a directory containing schema files that define tables, views, and stored procedures. Depending on your service's requirements, this may be expressed as either an absolute path or a relative path.
BrowsableSchemas	Optional setting that restricts the schemas reported to a subset of all available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC .
Tables	Optional setting that restricts the tables reported to a subset of all available tables. For example, Tables=TableA,TableB,TableC .
Views	Optional setting that restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC .
Schema	Specify this property to connect with a particular schema.
Catalog	Specify this property to connect with a particular catalog.

Location

Specifies the location of a directory containing schema files that define tables, views, and stored procedures. Depending on your service's requirements, this may be expressed as either an absolute path or a relative path.

Remarks

The Location property is only needed if you want to either customize definitions (for example, change a column name, ignore a column, etc.) or extend the data model with new tables, views, or stored procedures.

Note: Since this Sync App supports multiple schemas, custom schema files for Azure DevOps should be structured such that:

Each schema should have its own folder, named for that schema.
All schema folders should be contained in a parent folder.

Location should always be set to the parent folder, and not to an individual schema's folder.

If left unspecified, the default location is %APPDATA%\\CData\\AzureDevOps Data Provider\\Schema, where %APPDATA% is set to the user's configuration directory:


Platform	%APPDATA%
Windows	The value of the APPDATA environment variable
Linux	~/.config

BrowsableSchemas

Optional setting that restricts the schemas reported to a subset of all available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC .

Remarks

Listing all available database schemas can take extra time, thus degrading performance. Providing a list of schemas in the connection string saves time and improves performance.

Tables

Optional setting that restricts the tables reported to a subset of all available tables. For example, Tables=TableA,TableB,TableC .

Remarks

Listing all available tables from some databases can take extra time, thus degrading performance. Providing a list of tables in the connection string saves time and improves performance.

If there are lots of tables available and you already know which ones you want to work with, you can use this property to restrict your viewing to only those tables. To do this, specify the tables you want in a comma-separated list. Each table should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Tables=TableA,[TableB/WithSlash],WithCatalog.WithSchema.`TableC With Space`.

Note: If you are connecting to a data source with multiple schemas or catalogs, you must specify each table you want to view by its fully qualified name. This avoids ambiguity between tables that may exist in multiple catalogs or schemas.

Views

Optional setting that restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC .

Remarks

Listing all available views from some databases can take extra time, thus degrading performance. Providing a list of views in the connection string saves time and improves performance.

If there are lots of views available and you already know which ones you want to work with, you can use this property to restrict your viewing to only those views. To do this, specify the views you want in a comma-separated list. Each view should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Views=ViewA,[ViewB/WithSlash],WithCatalog.WithSchema.`ViewC With Space`.

Note: If you are connecting to a data source with multiple schemas or catalogs, you must specify each view you want to examine by its fully qualified name. This avoids ambiguity between views that may exist in multiple catalogs or schemas.

Schema

Specify this property to connect with a particular schema.

Remarks

Within the 'CData' catalog, this property can be set to 'Information' to access general Azure DevOps information not related to a specific project.

Within any of the Project catalogs, this property can be set to 'Project', 'Analytics', or any of the Repository schemas.

The 'Project' schema contains data relating to the project specified in the catalog name.

The Repository schemas contain data relating to the repository specified in the schema name. The schema name includes the RepositoryId, such as 'Repository_51b94efc-fe12-4179-825c-5ab3d0fee245'.

The 'Analytics' schema connects to the OData Analytics service, while all other schemas connect to REST endpoints.

Catalog

Specify this property to connect with a particular catalog.

Remarks

The 'CData' catalog contains general data not relating to a specific project.

The Project catalogs contain data relating to the project specified in the catalog name. Either the ProjectId or Project Name can be used in the catalog name, such as 'Project_837ccd31-8159-4db3-b8ce-de0c36d2a0bf' or 'Project_drivers'.

Miscellaneous

This section provides a complete list of the Miscellaneous properties you can configure in the connection string for this provider.

Property	Description
ApplyTransformations	A comma separated list of supported Apply transformations. To indicate none, set to 'off'. This disables auto detect.
IncludeCustomFields	A boolean indicating if you would like to include custom fields in the column listing.
MaxRows	Specifies the maximum rows returned for queries without aggregation or GROUP BY.
Other	Specifies additional hidden properties for specific use cases. These are not required for typical provider functionality. Use a semicolon-separated list to define multiple properties.
PseudoColumns	Specifies the pseudocolumns to expose as table columns. Use the format 'TableName=ColumnName;TableName=ColumnName'. The default is an empty string, which disables this property.
Timeout	Specifies the maximum time, in seconds, that the provider waits for a server response before throwing a timeout error. The default is 60 seconds. Set to 0 to disable the timeout.
UserDefinedViews	Specifies a filepath to a JSON configuration file defining custom views. The provider automatically detects and uses the views specified in this file.

ApplyTransformations

A comma separated list of supported Apply transformations. To indicate none, set to 'off'. This disables auto detect.

Remarks

By default, the CData Sync App attempts to determine which Apply transformations are available automatically, while reading metadata. However, if the $metadata does not supply that sort of information, you can supply a comma separated list. Supported transformation: aggregate, filter, and groupby.

IncludeCustomFields

A boolean indicating if you would like to include custom fields in the column listing.

Remarks

Setting this to true will cause custom fields to be included in the column listing, but may cause poor performance when listing metadata.

MaxRows

Specifies the maximum rows returned for queries without aggregation or GROUP BY.

Remarks

This property sets an upper limit on the number of rows the Sync App returns for queries that do not include aggregation or GROUP BY clauses. This limit ensures that queries do not return excessively large result sets by default.

When a query includes a LIMIT clause, the value specified in the query takes precedence over the MaxRows setting. If MaxRows is set to "-1", no row limit is enforced unless a LIMIT clause is explicitly included in the query.

This property is useful for optimizing performance and preventing excessive resource consumption when executing queries that could otherwise return very large datasets.

Other

Specifies additional hidden properties for specific use cases. These are not required for typical provider functionality. Use a semicolon-separated list to define multiple properties.

Remarks

This property allows advanced users to configure hidden properties for specialized scenarios. These settings are not required for normal use cases but can address unique requirements or provide additional functionality. Multiple properties can be defined in a semicolon-separated list.

Note: It is strongly recommended to set these properties only when advised by the support team to address specific scenarios or issues.

Specify multiple properties in a semicolon-separated list.

Integration and Formatting


DefaultColumnSize	Sets the default length of string fields when the data source does not provide column length in the metadata. The default value is 2000.
ConvertDateTimeToGMT	Determines whether to convert date-time values to GMT, instead of the local time of the machine.
RecordToFile=filename	Records the underlying socket data transfer to the specified file.

PseudoColumns

Specifies the pseudocolumns to expose as table columns. Use the format 'TableName=ColumnName;TableName=ColumnName'. The default is an empty string, which disables this property.

Remarks

This property allows you to define which pseudocolumns the Sync App exposes as table columns.

To specify individual pseudocolumns, use the following format: "Table1=Column1;Table1=Column2;Table2=Column3"

To include all pseudocolumns for all tables use: "*=*"

Timeout

Specifies the maximum time, in seconds, that the provider waits for a server response before throwing a timeout error. The default is 60 seconds. Set to 0 to disable the timeout.

Remarks

This property controls the maximum time, in seconds, that the Sync App waits for an operation to complete before canceling it. If the timeout period expires before the operation finishes, the Sync App cancels the operation and throws an exception.

The timeout applies to each individual communication with the server rather than the entire query or operation. For example, a query could continue running beyond 60 seconds if each paging call completes within the timeout limit.

Setting this property to 0 disables the timeout, allowing operations to run indefinitely until they succeed or fail due to other conditions such as server-side timeouts, network interruptions, or resource limits on the server. Use this property cautiously to avoid long-running operations that could degrade performance or result in unresponsive behavior.

UserDefinedViews

Specifies a filepath to a JSON configuration file defining custom views. The provider automatically detects and uses the views specified in this file.

Remarks

This property allows you to define and manage custom views through a JSON-formatted configuration file called UserDefinedViews.json. These views are automatically recognized by the Sync App and enable you to execute custom SQL queries as if they were standard database views. The JSON file defines each view as a root element with a child element called "query", which contains the SQL query for the view. For example:

{
	"MyView": {
		"query": "SELECT * FROM WorkItems WHERE MyColumn = 'value'"
	},
	"MyView2": {
		"query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
	}
}

You can define multiple views in a single file and specify the filepath using this property. For example: UserDefinedViews=C:\Path\To\UserDefinedViews.json. When you use this property, only the specified views are seen by the Sync App.

Refer to User Defined Views for more information.

Azure DevOps Connector for CData Sync

Azure DevOps Connector for CData Sync

Overview

Azure DevOps Version Support

Establishing a Connection

Adding a Connection to Azure DevOps

Connecting to Azure DevOps

Authenticating to Azure DevOps

Basic

Azure AD

Fine-Tuning Data Access

Fine Tuning Data Access

Advanced Features

User Defined Views

SSL Configuration

Firewall and Proxy

Query Processing

Logging

SSL Configuration

Customizing the SSL Configuration

Firewall and Proxy

Connecting Through a Firewall or Proxy

HTTP Proxies

Other Proxies

Information Data Model

Overview

Key Features

Tables

Views

Stored Procedures

Tables

Azure DevOps Connector for CData Sync Tables

AgentPools

Table Specific Information

Select

Insert

Update

Delete

Columns

Pseudo-Columns

BuildDefinitions

Table Specific Information

Select

Insert

Update

Delete

Columns

Pseudo-Columns

Projects

Table Specific Information

Select

Insert

Update

Delete

Columns

Pseudo-Columns

Users

Table Specific Information

Select

Insert

Update

Delete

Columns

Views

Azure DevOps Connector for CData Sync Views

AuditLogEntries

Table Specific Information

Select

Columns

Pseudo-Columns

ProjectProperties

Table Specific Information

Select

Columns

WorkItemIds

Columns

WorkItemsHistory

Table Specific Information

Select

Columns