Getting Started

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

Connecting to Azure DevOps

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

Accessing Data from CData Cloud Services

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

Establishing a Connection

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

Connecting to Azure DevOps

To connect to your Azure DevOps account, specify Organization, the name of your Organization in the account, by navigating to Profile > Organizations.

For example, Organization=MyAzureDevOpsOrganization;

NOTE : Along with Organization you must provide ProjectId when connecting to Analytics schema.

Authenticating to Azure DevOps

Basic

You can connect to your Azure DevOps account by providing the Organization and PersonalAccessToken. To generate a perosonal access token, log in to your Azure DevOps Organization account and navigate to Profile > Personal Access Tokens > New Token. The generated token is displayed.

Azure AD

Azure AD is a connection type that leverages OAuth to authenticate. OAuth requires the authenticating user to interact with Azure DevOps using an internet browser. The Cloud facilitates this in several ways as described below. Set AuthScheme to AzureAD and Organization to the name of your Azure DevOps organization. The rest of the AzureAD flows assume that you set these two properties.

Desktop Applications

CData provides an embedded OAuth application that simplifies OAuth desktop Authentication. Alternatively, you can create a custom OAuth application. See Creating a Custom OAuth Application for information about creating custom applications and reasons for doing so.

For authentication, the only difference between the two methods is that you must set two additional connection properties when using custom OAuth applications.

After setting the following connection properties, you are ready to connect:

OAuthClientId: (custom applications only) Set this to the client Id in your application settings.
OAuthClientSecret: (custom applications only) Set this to the client secret in your application settings.
CallbackURL: Set this to the Redirect URL in your application settings.

When you connect the Cloud opens the OAuth endpoint in your default browser. Log in and grant permissions to the application.

Headless Machines

To configure the driver to use OAuth with a user account on a headless machine, you need to authenticate on another device that has an internet browser.

Choose one of these two options:
- Option 1: Obtain the OAuthVerifier value as described in "Obtain and Exchange a Verifier Code" below.
- Option 2: Install the Cloud on another machine and transfer the OAuth authentication values after you authenticate through the usual browser-based flow, as described in "Transfer OAuth Settings" below.
Then configure the Cloud to automatically refresh the access token from the headless machine.

Option 1: Obtain and Exchange a Verifier Code

To obtain a verifier code, you must authenticate at the OAuth authorization URL.

Follow the steps below to authenticate from the machine with an internet browser and obtain the OAuthVerifier connection property.

Choose one of these options:
- If you are using the Embedded OAuth Application click Azure DevOps OAuth endpoint to open the endpoint in your browser.
- If you are using a custom OAuth application, create the Authorization URL by setting the following properties:
  - InitiateOAuth: Set to OFF.
  - OAuthClientId: Set to the client Id assigned when you registered your application.
  - OAuthClientSecret: Set to the client secret assigned when you registered your application.
  Then call the GetOAuthAuthorizationURL stored procedure with the appropriate CallbackURL. Open the URL returned by the stored procedure in a browser.
Log in and grant permissions to the Cloud. You are then redirected to the callback URL, which contains the verifier code.
Save the value of the verifier code. Later you will set this in the OAuthVerifier connection property.

Next, you need to exchange the OAuth verifier code for OAuth refresh and access tokens. Set the following properties:

On the headless machine, set the following connection properties to obtain the OAuth authentication values:

InitiateOAuth: Set this to REFRESH.
OAuthVerifier: Set this to the verifier code.
OAuthClientId: (custom applications only) Set this to the client Id in your custom OAuth application settings.
OAuthClientSecret: (custom applications only) Set this to the client secret in the custom OAuth application settings.
OAuthSettingsLocation: Set this to the location of the file where the driver saves the OAuth token values that persist across connections.

After the OAuth settings file is generated, you need to re-set the following properties to connect:

InitiateOAuth: Set this to REFRESH.
OAuthClientId: (custom applications only) Set this to the client Id assigned when you registered your application.
OAuthClientSecret: (custom applications only) Set this to the client secret assigned when you registered your application.
OAuthSettingsLocation: Set this to the location containing the encrypted OAuth authentication values. Make sure this location grants read and write permissions to the Cloud to enable the automatic refreshing of the access token.

Option 2: Transfer OAuth Settings

Prior to connecting on a headless machine, you need to create and install a connection with the driver on a device that supports an internet browser. Set the connection properties as described in "Desktop Applications" above.

After completing the instructions in "Desktop Applications", the resulting authentication values are encrypted and written to the location specified by OAuthSettingsLocation. The default filename is OAuthSettings.txt.

Once you have successfully tested the connection, copy the OAuth settings file to your headless machine.

On the headless machine, set the following connection properties to connect to data:

InitiateOAuth: Set this to REFRESH.
OAuthClientId: (custom applications only) Set this to the client Id assigned when you registered your application.
OAuthClientSecret: (custom applications only) Set this to the client secret assigned when you registered your application.
OAuthSettingsLocation: Set this to the location of your OAuth settings file. Make sure this location gives read and write permissions to the Cloud to enable the automatic refreshing of the access token.

Creating a Custom AzureAD App

When to Create a Custom AzureAD App

Creating a custom AzureAD application and a service principal that can access the necessary resources is required when authenticating using an Azure Service Principal.

Follow the steps below to create a custom AzureAD application and obtain the connection properties for the Azure Service Principal authentication.

Create a Custom AzureAD App

Desktop Apps

Register an application in the Azure portal to obtain the following connection properties:

OAuthClientId
OAuthClientSecret
CallbackURLt

After logging in to the Azure Portal with your account, click Azure Active Directory > App Registrations.
Click New Registration.
Enter an application name and select Any Azure AD Directory - Multi Tenant. Then set the redirect url to http://localhost:33333, the provider's default. Or, set a different port and set the CallbackURL to the exact reply URL you defined.
On the main page of your application obtain the OAuthClientId, and then generate a OAuthClientSecret in Certificates and Secrets > Client Secrets.

You must also add permission to use the Azure DevOps API:

In the section, click API permissions > Add > Select an API your organization uses > Azure DevOps. If Azure DevOps does not appear as an option, navigate to Organization Settings > Azure Active Directory > Connect directory on your DevOps site and connect the organization to you Active Directory first.
In the Select Permissions step, enable the user_impersonation permission.

Headless Machines

Register your Azure DevOps app on the Azure portal to obtain the following connection properties:

OAuthClientId
OAuthClientSecret

After logging in to the Azure Portal with your account, click Azure Active Directory > App Registrations.
Click New Registration.
Enter an app name and select Any Azure AD Directory - Multi Tenant. Then set the redirect url to http://localhost:33333, the provider's default. Or, set a different port and set the CallbackURL to the exact reply URL you defined.
On the main page of your application, obtain the OAuthClientId, and then generate a OAuthClientSecret in Certificates and Secrets > Client Secrets.

You must also add permission to use the Azure DevOps API:

In the section, click API permissions > Add > Select an API your organization uses > Azure DevOps. If Azure DevOps does not appear as an option, navigate to Organization Settings > Azure Active Directory > Connect directory on your DevOps site and connect the organization to you Active Directory first.
In the Select Permissions step, enable the user_impersonation permission.

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:

ProjectId: The ID of the specific project you would like to connect to. By default, the Cloud gets the first Id returned from an extra call to get all projects.
You can get the ProjectId by executing a SELECT query to the Projects view.

SSL Configuration

Customizing the SSL Configuration

By default, the Cloud attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store.

To specify another certificate, see the SSLServerCert property for the available formats to do so.

Firewall and Proxy

Connecting Through a Firewall or Proxy

HTTP Proxies

To connect through the Windows system proxy, you do not need to set any additional connection properties. To connect to other proxies, set ProxyAutoDetect to false.

In addition, to authenticate to an HTTP proxy, set ProxyAuthScheme, ProxyUser, and ProxyPassword, in addition to ProxyServer and ProxyPort.

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.

REST Data Model

Overview

This section shows the available API objects and provides more information on executing SQL to Azure DevOps APIs.

Key Features

The Cloud 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 Cloud.
ProjectId connection property allows you to get the data from the specific project. ProjectId specified in query will be given priority over the one specified in the connection string. If not specified in both query and connection string, then the first project from the source will be used.

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 Cloud models the data in Azure DevOps as a list of tables in a relational database that can be queried using standard SQL statements.

CData Cloud - Azure DevOps Tables


Name	Description
AgentPools	Retrieves a list of agent pools.
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.
Feeds	Retrieves all feeds in an account.
FeedViews	Retrieves all views for the specific feed.
GitBranches	Retrieves a collection of git branch.
Projects	Get all projects in the organization that the authenticated user has access to and details of the specific project.
PullRequestReviewers	Retrieves a list of reviewers for the specific pull request
Pushes	Retrieves pushes associated with the specified repository.
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.
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..
TestCases	Retrieves a list of all test cases.
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.
Users	Retrieves a list of users. This table will not retrieve results for the On-premise edition.
VariableGroups	Retrieves a list of variable groups.
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.

AgentPools

Retrieves a list of agent pools.

Table Specific Information

Select

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

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

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

For example:

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

Insert

The following are examples of inserting into an AgentPools table:

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

Update

The following is an example of updating an AgentPools table:

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

Delete

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

DELETE FROM AgentPools WHERE Id = '1'

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		Id of the agent pool.
AgentCloudId	Integer	False		The ID of the associated agent cloud.
AutoProvision	Boolean	False		Whether or not a queue should be automatically provisioned for each project collection.
AutoSize	Boolean	False		Whether or not the pool should autosize itself based on the agent cloud provider settings.
AutoUpdate	Boolean	False		Whether or not a pool should be automatically updated.
CreatedByDescriptor	String	False		The descriptor is the primary way to reference the creator while the system is running.
CreatedByDisplayName	String	False		This is the non-unique display name of the creator.
CreatedById	String	False		Id of the creator.
CreatedByImageUrl	String	False		The Image Url of the creator.
CreatedByUniqueName	String	False		The Unique name 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.
OwnerImageUrl	String	False		The Image Url of the owner.
OwnerUniqueName	String	False		The unique name 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.

Name

Type

Description

Action

String

Filter by whether the calling user has use or manage permissions.

The allowed values are manage, none, use.

BuildDefinitionDrafts

Retrieves a list of drafts associated with the specific definition.

Table Specific Information

Select

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

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

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

NOTE: DefinitionId is required in order to query BuildDefinitionDrafts.

For example:

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

Insert

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

INSERT INTO BuildDefinitionDrafts (projectId, Name, RepositoryId, RepositoryType, ProcessYamlFilename) VALUES ('b154d8f3-bfd9-4bfb-90ae-2e6c8cda8937', cdata11211, 'e50698d4-bb6e-400f-a1a0-5f4d17517d9e', TfsGit, 'data.txt')

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	Projects.Id	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 Cloud uses the Azure DevOps API to process WHERE clause conditions built with the following columns and operators:

Id supports the '=,in' operators.
ProjectId supports the '=' operator.
Name supports the '=' operator.
Path supports the '=' operator.
ProcessType supports the '=' operator.
ProcessYamlFileName supports the '=' operator.
Properties supports the '=,in' operators.
RepositoryId supports the '=' operator.
RepositoryType supports the '=' operator.
Revision supports the '=' operator.
BuildDate supports the '<,<=,>,>=' operators.
MinMetricsTime supports the '=' operator.
IncludeLatestBuilds supports the '=' operator.
TaskId supports the '=' operator.
IncludeAllProperties supports the '=' operator.

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

For example:

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

Insert

The following is an example of inserting into BuildDefinitions table:

INSERT INTO BuildDefinitions (Id, Name, ProjectId, RepositoryType, ProcessYamlFilename, RepositoryId, Tags) VALUES (1, 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.
LinksBadgeHref	String	True		Badge reference link.
LinksEditorHref	String	True		Editor reference link.
LinksSelfHref	String	True		Self reference link.
LinksWebHref	String	True		Web reference link.
AuthoredByDisplayName	String	False		This is the non-unique display name of the user.
AuthoredById	String	False		Id of the user.
AuthoredByImageUrl	String	False		The image Url of the user.
AuthoredByUniqueName	String	False		The unique name 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.
JobCancelTimeoutInMinutes	Integer	False		The job cancel timeout (in minutes) for builds cancelled by user for this definition.
JobTimeoutInMinutes	Integer	False		The job execution timeout (in minutes) for builds queued against this definition.
LatestBuildId	String	False	Builds.Id	Id of the latest build.
LatestCompletedBuildId	String	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.
ProcessYamlFilename	String	False		The process YAML file name.
ProjectId	String	False	Projects.Id	Project identifier.
Properties	String	False		Properties of the build definition.
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.
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.
RepositoryType	String	False		The type of the repository.
Revision	Integer	False		The definition revision number.
Tags	String	False		The tags associated with this definition.
Triggers	String	False		The build triggers.
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.


Name	Type	Description
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 Cloud uses the Azure DevOps API to process WHERE clause conditions built with the following columns and operators:

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

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

For example:

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

Update

The following is an example of updating a Builds table:

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

Delete

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

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

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		Id of the Build.
AgentSpecificationIdentifier	String	False		The agent specification for the build.
BuildNumber	String	False		The build number/name of the build.
BuildNumberRevision	Integer	False		The build number revision.
ControllerCreatedDate	Datetime	False		The date the controller was created. This is only set if definition type is XAML.
ControllerDescription	String	False		The description of the controller. This is only set if definition type is XAML.
ControllerEnabled	Boolean	False		Indicates whether the controller is enabled. This is only set if definition type is XAML.
ControllerId	Integer	False		Id of the build controller. This is set only if definition type is XAML.
ControllerName	String	False		Name of the controller. This is set only if definition type is XAML.
ControllerStatus	String	False		Status of the controller. This is set only if definition type is XAML.
ControllerUpdatedDate	Datetime	False		The date the controller was last updated. This is only set if definition type is XAML.
ControllerUri	String	False		The controller URI. This is only set if definition type is XAML.
ControllerUrl	String	False		Full Http Link to the resource. This is set only if definition type is XAML.
DefinitionId	Integer	False	BuildDefinitions.Id	The Id of the definition associated with the build.
Deleted	Boolean	False		Indicates whether the build has been deleted.
DeletedByDisplayName	String	False		This is the non-unique display name of the user.
DeletedById	String	False		The Id of the user.
DeletedByUniqueName	String	False		The Unique name 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.
LastChangedByUniqueName	String	False		The Unique name of the user.
LastChangedDate	Datetime	False		The date the build was last changed.
LinksBadgeHref	String	True		Badge reference link.
LinksSelfHref	String	True		Self reference link.
LinksSourceVersionDisplayUriHref	String	True		Source version display Uri reference link.
LinksTimelineHref	String	True		Timeline reference link.
LinksWebHref	String	True		Web reference link.
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	Projects.Id	Project identifier.
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.
RequestedByUniqueName	String	False		The Unique name of the user.
RequestedForDisplayName	String	False		This is the non-unique display name of the user.
RequestedForId	String	False		The Id of the user.
RequestedForUniqueName	String	False		The Unique name 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.


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

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 is an example of inserting into Dashboards table:

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

Update

The following is an example of updating a Dashboards table:

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

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

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		The Id of the project to which this dashboard belongs to.
TeamId	String	True		The Id fo the team to which this dashboard belongs to.
Description	String	False		Description of the dashboard.
ETag	String	False		Server defined version tracking value, used for edit collision detection.
LinksGroupHref	String	True		Group reference link.
LinksSelfHref	String	True		Self reference link.
LinksWidget	String	True		Widget reference link.
Name	String	False		Name of the dashboard.
OwnerId	String	False		ID of the owner for a dashboard.
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.

DeploymentGroups

Retrieves a list of all deployment groups.

Table Specific Information

Select

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

ProjectId supports the '=' operator.
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 Cloud.

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	AgentPools.Id	Id of the 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	Projects.Id	Id of the project.
ProjectName	String	True		Name 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.


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	Boolean	Include these additional details in the returned object. The allowed values are none, tags.

Feeds

Retrieves all feeds in an account.

Table Specific Information

Select

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

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

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

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.
LinksPackagesHref	String	True		Packages reference link.
LinksPermissionsHref	String	True		Permissions reference link.
LinksSelfHref	String	True		Self Reference link.
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	Projects.Id	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.

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

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	Projects.Id	Id of the project.
FeedId	String	True	Feeds.Id	Id of the feed.
LinksFeedHref	String	True		Feed reference link.
LinksPackagesHref	String	True		Packages reference link.
LinksSelfHref	String	True		Self reference link.
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.

Table Specific Information

Select

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

ProjectId supports the '=' operator.
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 Cloud.

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	Projects.Id	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.
CreatorUniqueName	String	True		Unique name of the creator.
CreatorImageUrl	String	True		Image URL of the creator.
CreatorDescriptor	String	True		Descriptor of the creator.
LinksSelfHref	String	True		Self reference link.
LinksRepositoryHref	String	True		Repository reference link.
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.


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.

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 Cloud uses the Azure DevOps API to process WHERE clause conditions built with the following columns and operators:

Id supports the '=' operator.
State supports the '=' operator.

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

For example:

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

Insert

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

INSERT INTO Projects (Name, Description, Visibility, CapabilitiesVersionControlType, CapabilitiesProcessTemplateTypeId) VALUES ('TestProject', 'This is a test', 'private', 'Git', '6b724908-ef14-45cf')

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.
LinksCollectionHref	String	True		Collection reference links.
LinksSelfHref	String	True		Self reference link.
LinksWebHref	String	True		Web reference link.
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.


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

PullRequestReviewers

Retrieves a list of reviewers for the specific pull request

Table Specific Information

Select

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

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

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

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	Projects.Id	Id of the project.
RepositoryId	String	True	Repositories.Id	Id of the repository.
PullRequestId	Integer	True	PullRequests.Id	Id of the pullrequest.
DisplayName	String	False		Display name of the reviewer.
ImageUrl	String	False		Image URL of the reviewer.
ReviewerUrl	String	False		URL to retrieve information about the reviewer.
UniqueName	String	False		The Unique name of 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		Vote on a pull request: 10 - approved, 5 - approved with suggestions, 0 - no vote, -5 - waiting for author, -10 - rejected.
hasDeclined	Boolean	False		Vote on a pull request: 10 - approved, 5 - approved with suggestions, 0 - no vote, -5 - waiting for author, -10 - rejected.

Pushes

Retrieves pushes associated with the specified repository.

Table Specific Information

Select

The Cloud 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 Cloud.

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.
PushedByImageUrl	String	False		The image URL of the user.
PushedByUniqueName	String	False		The unique name 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.


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

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	Projects.Id	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.
LinksHtmlHref	String	True		Reference links.
LinksSelfHref	String	True		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.


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

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	Projects.Id	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.
ApprovedByImageUrl	String	False		The image Url of the user who approved.
ApprovedByUniqueName	String	False		The Unique name 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.
ApproverImageUrl	String	False		The image URL of the user who should approve.
ApproverUniqueName	String	False		The unique name 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.


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

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	Projects.Id	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 Cloud 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 Cloud.

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.
LinksSelfHref	String	True		Self reference links.
LinksWebHref	String	True		Web 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.
CreatedByImageUrl	String	False		The Image Url of the user.
CreatedByUniqueName	String	False		The Unique name 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.
LastReleaseCreatedByImageUrl	String	False		The Image URL of the user who created last release.
LastReleaseCreatedByUniqueName	String	False		The Unique name 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.
LastReleaseModifiedByUniqueName	String	False		The unique name 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.
ModifiedByImageUrl	String	False		The Image URL of the user who modified this release definition.
ModifiedByUniqueName	String	False		The Unique name of the user who modified this relase 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	Projects.Id	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.


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.

Releases

Retrieves a list of releases.

Table Specific Information

Select

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

Id supports the '=,in' operators.
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 Cloud.

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.
LinksSelfHref	String	True		Self reference links.
LinksWebHref	String	True		Web 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.
CreatedByImageUrl	String	True		The image URL of the user who created this release.
CreatedByUniqueName	String	True		The unique name 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.
ModifiedByImageUrl	String	True		The image URL of the user who modified this release.
ModifiedByUniqueName	String	True		The unique Name 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	True		Release name.
PoolName	String	True		Pool name.
ProjectId	String	True	Projects.Id	Id of the project.
ProjectName	String	True		Name of the project.
Properties	String	False		Release properties.
Reason	String	False		Reason of release.
ReleaseDefinitionId	Integer	True	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.


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

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.
LinksCommitsHref	String	True		Commits reference link.
LinksItemsHref	String	True		Items reference link.
LinksProjectHref	String	True		Project reference link.
LinksPullRequestsHref	String	True		Pull requests reference link.
LinksPushesHref	String	True		Pushes reference link.
LinksRefsHref	String	True		Refs reference link.
LinksSelfHref	String	True		Self reference link.
LinksSshHref	String	True		Ssh reference link.
LinksWebHref	String	True		Web reference link.
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	Projects.Id	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.


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

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	Projects.Id	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.
CreatedByUniqueName	String	True		The Unique name of the user.
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.
ModifiedByUniqueName	String	True		The unique name 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.


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

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	True		Id of the iteration.
ProjectId	String	True	Projects.Id	Id of the project.
TeamId	String	True	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.
LinksCapacityHref	String	True		Capacity reference link.
LinksClassificationNodeHref	String	True		Classification node reference link.
LinksProjectHref	String	True		Project reference link.
LinksSelfHref	String	True		Self reference link.
LinksTeamHref	String	True		Team reference link.
LinksTeamDaysOffHref	String	True		Team days off reference link.
LinksTeamIterationsHref	String	True		Team iterations reference link.
LinksTeamSettingsHref	String	True		Team settings reference link.
LinksWorkItemsHref	String	True		Work items 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 Cloud 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 Cloud.

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 ('837ccd31-8159-4db3', 'TestTeam')

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		Unique Identifier of the team.
Description	String	True		The description of the team.
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	Projects.Id	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.


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

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	Projects.Id	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.
LinksClassificationNode	String	True		Classification node reference link.
LinksProjectHref	String	True		Project reference link.
LinksSelfHref	String	True		Self reference link.
LinksTeamHref	String	True		Team reference link.
LinksTeamFieldValuesHref	String	True		Team field values reference link.
LinksTeamIterationsHref	String	True		Team iterations reference link.
Url	String	True		Full http link to the resource.
WorkingDays	String	False		Days that the team is working.

TestCases

Retrieves a list of all test cases.

Table Specific Information

Select

The Cloud 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 Cloud.

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

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

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

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		Work item id.
LinksSelfHref	String	True		Self reference link.
LinksConfigurationHref	String	True		Configuration reference link.
LinksSourcePlanHref	String	True		Source plan reference link.
LinksSourceProjectHref	String	True		Source project reference link.
LinksSourceSuiteHref	String	True		Source suite reference link.
LinksTestPointsHref	String	True		Test points reference link.
Order	Integer	True		Order of the test case in the suite.
ProjectId	String	True	Projects.Id	Id of the project.
ProjectLastUpdateTime	Date	True		Last updated time of the project.
ProjectName	String	True		Name of the project.
ProjectState	String	True		State of the project.
ProjectVisibility	String	True		Visibility of the project.
TestPlanId	Integer	True	TestPlans.Id	Id of the test plan.
TestPlanName	String	True		Name of the test plan.
TestSuiteId	Integer	True	TestSuites.Id	Id of the test suite.
TestSuiteName	String	True		Name of the test suite.
WorkItemId	Integer	True		Work item Id.
WorkItemName	String	True		Work item name.
WorkItemFields	String	True		Work item fields.

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.


Name	Type	Description
ConfigurationIds	String	Fetch Test Cases which contains all the configuration Ids specified.

TestConfigurations

Retrieves a list test configurations.

Table Specific Information

Select

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

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

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

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	Projects.Id	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 Cloud 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 Cloud.

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.
LinksSelfHref	String	True		Self Reference Link.
LinksBuildHref	String	True		Build Reference Link.
LinksClientUrlHref	String	True		Client Url Reference Link.
LinksRootSuiteHref	String	True		Root Suite Reference Link.
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.
OwnerUniqueName	String	False		The Unique name of the owner.
OwnerImageUrl	String	False		The Image Url of the owner.
PreviousBuildId	Integer	True		Previous build Id associated with the test plan.
ProjectId	String	True	Projects.Id	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.
UpdatedByUniqueName	String	True		The Unique Name of the user.
UpdatedByImageUrl	String	True		The Image URL of the user.
UpdatedDate	Datetime	True		Updated date of the test plan.

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.


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 Cloud 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.
TestRunId supports the '=' operator.
Outcome supports the '=' operator.

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

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.
LastUpdatedByImageUrl	String	False		The Image Url of the user who last updated this test result.
LastUpdatedByUniqueName	String	False		The Unique name of the user.
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	Projects.Id	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.
RunByImageUrl	String	False		The Image URL of the user who executed the test.
RunByUniqueName	String	False		The Unique Name 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 Cloud 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 Cloud.

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.
LastUpdatedByImageUrl	String	True		The Image Url of the user who last updated this test run.
LastUpdatedByUniqueName	String	True		The unique name 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.
OwnerImageUrl	String	False		The Image URL of the owner.
OwnerUniqueName	String	False		The unique name 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	Projects.Id	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.


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

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.
LastUpdatedByImageUrl	String	False		The Image URL of the user who last updated this session.
LastUpdatedByUniqueName	String	False		The unique name 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.
OwnerImageUrl	String	False		The Image URL of the owner of the testowner of the test session.
OwnerUniqueName	String	False		The unique name of the owner of the test session.
OwnerUrl	String	False		The URL of the owner of the test session.
ProjectId	String	True	Projects.Id	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.


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 Cloud 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.
TestCaseId supports the '=' operator.
Expand supports the '=' operator.
TreeView supports the '=' operator.

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

NOTE: PlanId or TestCaseId is required in order to query TestSuites.

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
	SELECT * FROM TestSuites WHERE TestCaseId = 1

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.
TestCaseId	String	True	TestCases.Id	Id of the test case.
LinksSelfHref	String	True		Self reference link.
LinksTestCasesHref	String	True		Test cases reference link.
LinksTestPointsHref	String	True		Test points reference link.
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.
LastUpdatedByImageUrl	String	True		The Image URL of the user who last updated this test suite.
LastUpdatedByUniqueName	String	True		The Unique name 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	Projects.Id	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.

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.


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

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	Projects.Id	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.

Users

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

Table Specific Information

Select

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

Id supports the '=' operator.

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

For example:

	SELECT * FROM Users WHERE Id = '7f1d8582-a070-4d2b'

Insert

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

INSERT INTO Users (UserPrincipalName, AccessLevelAccountLicenseType, UserSubjectKind) VALUES ('[email protected]', 'express', 'user')

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

VariableGroups

Retrieves a list of variable groups.

Table Specific Information

Select

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

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

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

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	Projects.Id	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.
CreatedByImageUrl	String	True		The Image URL of the user who created this variable group.
CreatedByUniqueName	String	True		The Unique name 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.
ModifiedByImageUrl	String	True		The Image URL of the user who modified this variable group.
ModifiedByUniqueName	String	True		The Unique Name 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.

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.

Name

Type

Description

Action

String

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

The allowed values are manage, none, use.

WikiPages

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

Table Specific Information

Select

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

ProjectId supports the '=' operator.
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 Cloud.

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'

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 [KEY]	Integer	True		Id of the wiki page.
ProjectId	String	True	Projects.Id	Id of the project for which this wiki was created.
WikiId	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	String	True		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.


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

For example:

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

Insert

The following is an example of inserting into Wikis table:

INSERT INTO Wikis (ProjectId, Name) VALUES ('03e4b7af-3bff-49d0', 'TestWiki')

Update

The following is an example of updating a 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 in a 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	Projects.Id	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.

WorkItems

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

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	Projects.Id	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.
CreatedByName	String	True		User 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.
ChangedByName	String	True		User 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.
AssignedToName	String	True		User name of current work item assignee.
AssignedToUrl	String	True		Profile link of current work item assignee.
LinksFieldsHref	String	True		Work item fields reference link.
LinksHtmlHref	String	True		Html reference link.
LinksSelfHref	String	True		Self reference link.
LinksWorkItemHistoryHref	String	True		Work item history reference link.
LinksWorkItemRevisionsHref	String	True		Work item revisions reference link.
LinksWorkItemTypeHref	String	True		Work item type reference link.
LinksWorkItemUpdatesHref	String	True		Work item updates reference link.
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.


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.

CData Cloud - Azure DevOps Views


Name	Description
AuditLogEntries	Retrieves all audit log entries. This table includes custom fields which are automatically discovered when 'IncludeCustomFields' is enabled.
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.
CommitGitStatus	Retrieve git status for the specific commit.
Commits	Retrieve git commits for a project.
CommitWorkItems	Retrieve work items for the specific commit.
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.
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.
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.
QueryClauses	Retrieves clauses for the specific query.
QueryColumns	Retrieves all columns for the specific query.
ReleaseArtifacts	Retrieves a list of release artifacts.
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.
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.
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
WorkItemsHistory	Retrieves a list of work items history
WorkItemUpdatesHistory	Retrieves a list of work items updates history. WorkItemId is a required column in order to retrieve results.

AuditLogEntries

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

Table Specific Information

Select

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

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

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

For example:

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

Columns


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

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.


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.

BacklogColumnFields

Retrieves column fields for the specific backlog level..

Table Specific Information

Select

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

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

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

NOTE: TeamId and BacklogId are required in order to query 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	String	Projects.Id	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 these column fields belong to.
ColumnFieldName	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 Cloud 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 Cloud.

NOTE: TeamId and BacklogId are required in order to query 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	String	Projects.Id	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 these column fields belong to.
Name	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 Cloud 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 Cloud.

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	Projects.Id	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 Cloud 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 Cloud.

NOTE: TeamId and BacklogId are required in order to query 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	Projects.Id	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 Cloud 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 Cloud.

NOTE: BoardId is required in order to query BoardColumns.

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	Projects.Id	Id of the project in which this was created.
TeamId	String	Teams.Id	Id of the team this board belongs to.
BoardId	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 Cloud 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 Cloud.

NOTE: BoardId is required in order to query BoardRows.

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	Projects.Id	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	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 Cloud 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 Cloud.

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	Projects.Id	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.
LinksChartsHref	String		Charts Reference Links. this field will be populated with a value only when the Id is specified.
LinksColumnsHref	String		Columns reference links. This field will be populated with a value only when the Id is specified.
LinksProjectHref	String		Project reference links. This field will be populated with a value only when the Id is specified.
LinksRowsHref	String		Rows reference links. This field will be populated with a value only when the Id is specified.
LinksSelfHref	String		Rows reference links. This field will be populated with a value only when the Id is specified.
LinksTeamHref	String		Team reference links. This field will be populated with a value only when the Id is specified.
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 Cloud 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 Cloud.

NOTE: BuildId is required in order to query BuildChanges.

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	String		Id of the build change.
ProjectId	String	Projects.Id	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.
AuthorUniqueName	String		The unique name 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.


Name	Type	Description
IncludeSourceChange	Boolean		Indicates whether to include source change.

BuildDefinitionMetrics

Retrieves metadata for the specific build.

Table Specific Information

Select

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

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

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

NOTE: DefinitionId is required in order to query BuildDefinitionMetrics.

For example:

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

Columns


Name	Type	References	Description
ProjectId	String	Projects.Id	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 Cloud 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 Cloud.

NOTE: BuildId is required in order to query BuildDemands.

For example:

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

Columns


Name	Type	References	Description
ProjectId	String	Projects.Id	Id of the project.
BuildId	Integer	Builds.Id	Id of the build.
Name	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 Cloud 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 Cloud.

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

NOTE: BuildId is required in order to query BuildLogs.

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	Projects.Id	Id of the project.
BuildId	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 Cloud 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 Cloud.

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	Projects.Id	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 Cloud 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 Cloud.

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	Projects.Id	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 Cloud 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 Cloud.

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	Projects.Id	Id of the project.
BuildId	Integer	Builds.Id	Id of the builds.

CommitChanges

Retrieve changes for a particular commit.

Table Specific Information

Select

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

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

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

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	Projects.Id	Id of the project.
RepositoryId	String	Repositories.Id	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 Cloud 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 Cloud.

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

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	Projects.Id	Id of the project.
RepositoryId	String	Repositories.Id	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.
CreatedByImageUrl	String		The image Url of the user who created the status.
CreatedByUniqueName	String		The unique name 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 Cloud 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 Cloud.

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	Projects.Id	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.
LinksChangesHref	String		Commit changes reference link.
LinksRepositoryHref	String		Repository reference link.
LinksSelfHref	String		Self reference link.
LinksWebHref	String		Web reference link.
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.
PushedByImageUrl	String		The image url of the user resource.
PushedByUniqueName	String		The unique name of the user resource.
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.


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

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	Projects.Id	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 Cloud 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 Cloud.

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	Projects.Id	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 Cloud 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 Cloud.

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	Projects.Id	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.


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

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	Projects.Id	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.


Name	Type	Description
IncludeDeletedUpstreams	Boolean		Include upstreams that have been deleted in the response.

GitStats

Retrieve statistics about all branches within a repository.

Table Specific Information

Select

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

ProjectId supports the '=' operator.
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 Cloud.

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	Projects.Id	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.


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

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	Projects.Id	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 Cloud uses the Azure DevOps API to process WHERE clause conditions built with the following columns and operators:

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

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

For example:

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

Columns


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

PullRequestAttachments

Retrieves a list of attachments for the specific pull request.

Table Specific Information

Select

The Cloud 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 Cloud.

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	Projects.Id	Id of the project.
RepositoryId	String	Repositories.Id	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.
AuthorImageUrl	String		The image URL of the author.
AuthorUniqueName	String		The unique name 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 Cloud 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 Cloud.

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	Projects.Id	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.
AutoCompleteSetByImageUrl	String		Image URL of the resource.
AutoCompleteSetByUniqueName	String		The unique name 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.
ClosedByImageUrl	String		Image URL of the user.
ClosedByUniqueName	String		The unique name 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.
CreatedByImageUrl	String		Image URL of the user.
CreatedByUniqueName	String		The Unique Name 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.
ForkSourceCreatorUniqueName	String		The unique name 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.
ForkSourceIsLockedByUniqueName	String		The Unique name 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.
LinksCreatedByHref	String		Created by reference link.
LinksIterationsHref	String		Iterations reference link.
LinksRepositoryHref	String		Repository reference link.
LinksSelfHref	String		Self reference link.
LinksSourceBranchHref	String		Source branch reference link.
LinksSourceCommitHref	String		Source commit reference link.
LinksStatusesHref	String		Statuses reference link.
LinksTargetBranchHref	String		Target branch reference link.
LinksTargetCommitHref	String		Target commit reference link.
LinksWorkItemsHref	String		Work items reference link.
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.


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

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	Projects.Id	Id of the project.
RepositoryId	String	Repositories.Id	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 Cloud 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 Cloud.

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	Projects.Id	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	Repositories.Id	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 Cloud 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 Cloud.

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	Projects.Id	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 Cloud 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 Cloud.

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	Projects.Id	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 Cloud 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 Cloud.

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	Projects.Id	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.

ReleaseDeployments

Retrieves a list of deployments.

Table Specific Information

Select

The Cloud 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 Cloud.

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.
LastModifiedByImageUrl	String		The image URL of the user who last modified this deployment.
LastModifiedByUniqueName	String		The unique name 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	Projects.Id	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.
RequestedByImageUrl	String		The image URL of the user who requested.
RequestedByUniqueName	String		The unique name 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.
RequestedForImageUrl	String		The image URL of the user for whom deployment is requested.
RequestedForUniqueName	String		The unique name 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.


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

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	Projects.Id	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 Cloud 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 Cloud.

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	Projects.Id	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 Cloud 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 Cloud.

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	Projects.Id	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 Cloud 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 Cloud.

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	Projects.Id	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.
IdentityImageUrl	String		The Url of the image.
IdentityUniqueName	String		The unique name 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 Cloud 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 Cloud.

NOTE: TestRunId is required in order to query TestAttachments.

Id supports the '=,in' operators.
ProjectId supports the '=' operator.
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	Projects.Id	Id of the project.
TestRunId	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 Cloud uses the Azure DevOps API to process WHERE clause conditions built with the following columns and operators:

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

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

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	Projects.Id	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.
TesterImageUrl	String		The Image URL of the tester.
TesterUniqueName	String		The Unique Name of the tester.
TesterUrl	String		The URL of the tester.

TestPoints

Retrieves a list of test points.

Table Specific Information

Select

The Cloud 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 Cloud.

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	Projects.Id	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.
LastUpdatedByImageUrl	String		The Image Url of the user who last updated this test point.
LastUpdatedByUniqueName	String		The Unique Name 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.


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

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	Projects.Id	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.


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

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	Projects.Id	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 Cloud 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 Cloud.

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	Projects.Id	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 Cloud 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 Cloud.

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	Projects.Id	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.
OwnerImageUrl	String		The Image URL of the owner.
OwnerUniqueName	String		The Unique name 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.


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.

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.
AuthorUniqueName	String		The Unique name of the author.
AuthorUrl	String		The Full HTTP URL of the author.
AuthorImageUrl	String		The Image 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.
CheckedInByUniqueName	String		The Unique name of the user who has checked in.
CheckedInByUrl	String		The Full HTTP URL of the user who has checked in.
CheckedInByImageUrl	String		The Image 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.

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.


Name	Type	Description
ProjectId	String		Id of the project this changeset belongs to.
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 Cloud 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 Cloud.

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	Projects.Id	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.

Columns


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

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	Projects.Id	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.


Name	Type	Description
AsOf	Datetime		AsOf UTC date time string.

WorkItemRevisionFields

Retrieves a list of work item revision fields

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.

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.


Name	Type	Description
ProjectId	String		Id of the project this changeset belongs to.
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.

Columns


Name	Type	References	Description
Id [KEY]	Integer		Id of the work item.
Revision	Integer		Revision of 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.


Name	Type	Description
ProjectId	String		Id of the project this changeset belongs to.
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	Projects.Id	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.


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.

WorkItemsHistory

Retrieves a list of work items history

Table Specific Information

Select

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

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

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

NOTE: WorkItemId is required in order to query WorkItemsHistory.

For example:

SELECT * FROM WorkItemsHistory WHERE WorkItemId = 1

Columns


Name	Type	References	Description
Revision [KEY]	String		The WorkItem Revision.
Id	Integer		Revised By Id.
WorkItemId	Integer		The WorkItem Id.
Name	String		Revised By Name.
Value	String		Work Item Value.
ImageURL	String		Revised By Image URL.
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.


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 list of work items updates history. WorkItemId is a required column in order to retrieve results.

Columns


Name	Type	References	Description
Id [KEY]	Integer		Id
WorkItemId	Integer		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.
AuthorizedAsImageUrl	String		Field Value for the work item updates.
AuthorizedAsuniqueName	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.
ChangedByImageUrl	String		Field Value for the work item updates.
ChangedByUniqueName	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.
CreatedByImageUrl	String		Field Value for the work item updates.
CreatedByUniqueName	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.
RevisedByImageUrl	String		Field Value for the work item updates.
RevisedByName	String		Field Value for the work item updates.
RevisedByUniqueName	String		Field Value for the work item updates.
RevisedByUrl	String		Field Value for the work item updates.
RevisedDate	Datetime		Field Value for the work item updates.
Url	String		Field Value for the work item updates.
Relations	String		Relations in work items updates history

Stored Procedures

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

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

CData Cloud - Azure DevOps Stored Procedures


Name	Description
DeleteTestCase	Deletes the test case
GetPullRequestCommits	Get the commits for the specified pull request.
PushChanges	Pushes changes to a repository in your Azure DevOps instance.
RunPipeline	Runs a pipeline with the specified configuration.
SetProjectProperties	Create, update, and delete team project properties.

DeleteTestCase

Deletes the test case

Stored Procedures Specific Information

Process of Delete Test Case

Azure DevOps allows only a small subset of columns to be used in the EXEC query. These columns can typically be used with only = comparison. The available columns for DeleteTestCase are ProjectId and TestCaseId For example:

EXECUTE DeleteTestCase ProjectId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf', TestCaseId = 296

Input


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

Result Set Columns


Name	Type	Description
Status	String	Stored procedure execution status

GetPullRequestCommits

Get the commits for the specified pull request.

Input


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

Result Set Columns


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

PushChanges

Pushes changes to a repository in your Azure DevOps instance.

Stored Procedure Specific Information

Pushing Changes

Pushing to Azure DevOps repositories through the REST API requires highly specific configurations. For this reason, the ChangesAggregate input must be provided as JSON. This can be achieved through temporary tables:

INSERT INTO ChangesAggregate#TEMP ([changeType], [item.path], [newContent.contentPath], [newContent.contentType]) 
VALUES ('add', 
		'/images/people/files.jpg', 
		'C:/path/to/file.jpg', 
		'base64encoded')
EXEC PushChanges @BranchRefName = 'refs/heads/main', @OldObjectId = '503b549b6dd4b0c4692fce1b726bfa03bebb6ffb', @CommitComment = 'Test Comment', @ChangesAggregate = 'ChangesAggregate#TEMP', @RepositoryId = '28478171-fc07-468e'

Or by providing the JSON body of the object-array directly:

EXEC PushChanges @BranchRefName = 'refs/heads/main', 
				 @OldObjectId = '503b549b6dd4b0c4692fce1b726bfa03bebb6ffb', 
				 @CommitComment = 'Test Comment',
				 @ChangesAggregate = '[{"changetype": "add","newcontent": {"contenttype": "base64encoded","contentpath": "C:/test.jpg"},"item": {"path": "/images/test.jpg"}}]', 
				 @RepositoryId = '28478171-fc07-468e-8a53-4a9553fde744'

For more information on the available change types, see the "Pushes - Create" chapter of the Azure DevOps REST API documentation.

Input


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

Result Set Columns


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

RunPipeline

Runs a pipeline with the specified configuration.

Stored Procedure Specific Information

Running a Pipeline

Azure DevOps pipelines require a lot of configuration to be run from the REST API. For this reason, the Resources, Variables, StagesToSkip, and TemplateParameters inputs must be provided as JSON inputs. This can be achieved through temporary tables:

INSERT INTO resources#TEMP ([resources.repositories.self.refName]) VALUES ('refs/heads/main')
INSERT INTO variables#TEMP ([variables.HELLO_WORLD.value], [variables.HELLO_WORLD.isSecret]) VALUES ('HelloWorldValue', false)
EXEC RunPipeline @ProjectId = 'TestProjectReview', @PipelineId = 2, @variables = 'variables#TEMP', @resources = 'resources#TEMP'

Or by providing the JSON bodies of each element directly:

EXEC RunPipeline @ProjectId = 'TestProjectReview', 
				 @PipelineId = 2, 
				 @variables = '{"HELLO_WORLD": {"isSecret": false, "value": "HelloWorldValue"}}', 
				 @resources = '{"repositories": {"self": {"refname": "refs/heads/main"}}}', 
				 @StagesToSkip = '["Build"]'

Input


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

Result Set Columns


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

SetProjectProperties

Create, update, and delete team project properties.

Stored Procedures Specific Information

Process of Set Project Properties

Azure DevOps allows only a small subset of columns to be used in the EXEC query. These columns can typically be used with only = comparison. The available columns for SetProjectProperties are ProjectId, From, Op, Path and Value For example:

	EXECUTE SetProjectProperties ProjectId = '837ccd31-8159-4db3-b8ce-de0c36d2a0bf', Op = 'remove', Path = '/Alias'

Input


Name	Type	Required	Description
ProjectId	String	True	Id of the Project.
From	String	False	The path to copy from for the Move/Copy operation.
Op	String	False	The patch operation.
Path	String	False	The path for the operation.
Value	String	False	The value for the operation.

Result Set Columns


Name	Type	Description
Status	String	Stored procedure execution status.

Analytics Data Model

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

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.

CData Cloud - Azure DevOps 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
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 Cloud 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 Cloud.

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

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

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

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 Cloud uses the Azure DevOps API to process WHERE clause conditions built with the following columns and operators:

WorkItemId supports the '=,in' operators.

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

NOTE: WorkItemId is required in order to query WorkItems.

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

Stored Procedures

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

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

CData Cloud - Azure DevOps Stored Procedures


Name	Description

System Tables

You can query the system tables described in this section to access schema information, information on data source functionality, and batch operation statistics.

Schema Tables

The following tables return database metadata for Azure DevOps:

sys_catalogs: Lists the available databases.
sys_schemas: Lists the available schemas.
sys_tables: Lists the available tables and views.
sys_tablecolumns: Describes the columns of the available tables and views.
sys_procedures: Describes the available stored procedures.
sys_procedureparameters: Describes stored procedure parameters.
sys_keycolumns: Describes the primary and foreign keys.
sys_indexes: Describes the available indexes.

Data Source Tables

The following tables return information about how to connect to and query the data source:

sys_connection_props: Returns information on the available connection properties.
sys_sqlinfo: Describes the SELECT queries that the Cloud can offload to the data source.

Query Information Tables

The following table returns query statistics for data modification queries, including batch operations::

sys_identity: Returns information about batch operations or single updates.

sys_catalogs

Lists the available databases.

The following query retrieves all databases determined by the connection string:

SELECT * FROM sys_catalogs

Columns


Name	Type	Description
CatalogName	String	The database name.

sys_schemas

Lists the available schemas.

The following query retrieves all available schemas:

          SELECT * FROM sys_schemas

Columns


Name	Type	Description
CatalogName	String	The database name.
SchemaName	String	The schema name.

sys_tables

Lists the available tables.

The following query retrieves the available tables and views:

          SELECT * FROM sys_tables

Columns


Name	Type	Description
CatalogName	String	The database containing the table or view.
SchemaName	String	The schema containing the table or view.
TableName	String	The name of the table or view.
TableType	String	The table type (table or view).
Description	String	A description of the table or view.
IsUpdateable	Boolean	Whether the table can be updated.

sys_tablecolumns

Describes the columns of the available tables and views.

The following query returns the columns and data types for the WorkItems table:

SELECT ColumnName, DataTypeName FROM sys_tablecolumns WHERE TableName='WorkItems'

Columns


Name	Type	Description
CatalogName	String	The name of the database containing the table or view.
SchemaName	String	The schema containing the table or view.
TableName	String	The name of the table or view containing the column.
ColumnName	String	The column name.
DataTypeName	String	The data type name.
DataType	Int32	An integer indicating the data type. This value is determined at run time based on the environment.
Length	Int32	The storage size of the column.
DisplaySize	Int32	The designated column's normal maximum width in characters.
NumericPrecision	Int32	The maximum number of digits in numeric data. The column length in characters for character and date-time data.
NumericScale	Int32	The column scale or number of digits to the right of the decimal point.
IsNullable	Boolean	Whether the column can contain null.
Description	String	A brief description of the column.
Ordinal	Int32	The sequence number of the column.
IsAutoIncrement	String	Whether the column value is assigned in fixed increments.
IsGeneratedColumn	String	Whether the column is generated.
IsHidden	Boolean	Whether the column is hidden.
IsArray	Boolean	Whether the column is an array.
IsReadOnly	Boolean	Whether the column is read-only.
IsKey	Boolean	Indicates whether a field returned from sys_tablecolumns is the primary key of the table.

sys_procedures

Lists the available stored procedures.

The following query retrieves the available stored procedures:

          SELECT * FROM sys_procedures

Columns


Name	Type	Description
CatalogName	String	The database containing the stored procedure.
SchemaName	String	The schema containing the stored procedure.
ProcedureName	String	The name of the stored procedure.
Description	String	A description of the stored procedure.
ProcedureType	String	The type of the procedure, such as PROCEDURE or FUNCTION.

sys_procedureparameters

Describes stored procedure parameters.

The following query returns information about all of the input parameters for the SelectEntries stored procedure:

SELECT * FROM sys_procedureparameters WHERE ProcedureName='SelectEntries' AND Direction=1 OR Direction=2

Columns


Name	Type	Description
CatalogName	String	The name of the database containing the stored procedure.
SchemaName	String	The name of the schema containing the stored procedure.
ProcedureName	String	The name of the stored procedure containing the parameter.
ColumnName	String	The name of the stored procedure parameter.
Direction	Int32	An integer corresponding to the type of the parameter: input (1), input/output (2), or output(4). input/output type parameters can be both input and output parameters.
DataTypeName	String	The name of the data type.
DataType	Int32	An integer indicating the data type. This value is determined at run time based on the environment.
Length	Int32	The number of characters allowed for character data. The number of digits allowed for numeric data.
NumericPrecision	Int32	The maximum precision for numeric data. The column length in characters for character and date-time data.
NumericScale	Int32	The number of digits to the right of the decimal point in numeric data.
IsNullable	Boolean	Whether the parameter can contain null.
IsRequired	Boolean	Whether the parameter is required for execution of the procedure.
IsArray	Boolean	Whether the parameter is an array.
Description	String	The description of the parameter.
Ordinal	Int32	The index of the parameter.

sys_keycolumns

Describes the primary and foreign keys.

The following query retrieves the primary key for the WorkItems table:

         SELECT * FROM sys_keycolumns WHERE IsKey='True' AND TableName='WorkItems'

Columns


Name	Type	Description
CatalogName	String	The name of the database containing the key.
SchemaName	String	The name of the schema containing the key.
TableName	String	The name of the table containing the key.
ColumnName	String	The name of the key column.
IsKey	Boolean	Whether the column is a primary key in the table referenced in the TableName field.
IsForeignKey	Boolean	Whether the column is a foreign key referenced in the TableName field.
PrimaryKeyName	String	The name of the primary key.
ForeignKeyName	String	The name of the foreign key.
ReferencedCatalogName	String	The database containing the primary key.
ReferencedSchemaName	String	The schema containing the primary key.
ReferencedTableName	String	The table containing the primary key.
ReferencedColumnName	String	The column name of the primary key.

sys_foreignkeys

Describes the foreign keys.

The following query retrieves all foreign keys which refer to other tables:

         SELECT * FROM sys_foreignkeys WHERE ForeignKeyType = 'FOREIGNKEY_TYPE_IMPORT'

Columns


Name	Type	Description
CatalogName	String	The name of the database containing the key.
SchemaName	String	The name of the schema containing the key.
TableName	String	The name of the table containing the key.
ColumnName	String	The name of the key column.
PrimaryKeyName	String	The name of the primary key.
ForeignKeyName	String	The name of the foreign key.
ReferencedCatalogName	String	The database containing the primary key.
ReferencedSchemaName	String	The schema containing the primary key.
ReferencedTableName	String	The table containing the primary key.
ReferencedColumnName	String	The column name of the primary key.
ForeignKeyType	String	Designates whether the foreign key is an import (points to other tables) or export (referenced from other tables) key.

sys_primarykeys

Describes the primary keys.

The following query retrieves the primary keys from all tables and views:

         SELECT * FROM sys_primarykeys

Columns


Name	Type	Description
CatalogName	String	The name of the database containing the key.
SchemaName	String	The name of the schema containing the key.
TableName	String	The name of the table containing the key.
ColumnName	String	The name of the key column.
KeySeq	String	The sequence number of the primary key.
KeyName	String	The name of the primary key.

sys_indexes

Describes the available indexes. By filtering on indexes, you can write more selective queries with faster query response times.

The following query retrieves all indexes that are not primary keys:

          SELECT * FROM sys_indexes WHERE IsPrimary='false'

Columns


Name	Type	Description
CatalogName	String	The name of the database containing the index.
SchemaName	String	The name of the schema containing the index.
TableName	String	The name of the table containing the index.
IndexName	String	The index name.
ColumnName	String	The name of the column associated with the index.
IsUnique	Boolean	True if the index is unique. False otherwise.
IsPrimary	Boolean	True if the index is a primary key. False otherwise.
Type	Int16	An integer value corresponding to the index type: statistic (0), clustered (1), hashed (2), or other (3).
SortOrder	String	The sort order: A for ascending or D for descending.
OrdinalPosition	Int16	The sequence number of the column in the index.

sys_connection_props

Returns information on the available connection properties and those set in the connection string.

When querying this table, the config connection string should be used:

jdbc:cdata:azuredevops:config:

This connection string enables you to query this table without a valid connection.

The following query retrieves all connection properties that have been set in the connection string or set through a default value:

SELECT * FROM sys_connection_props WHERE Value <> ''

Columns


Name	Type	Description
Name	String	The name of the connection property.
ShortDescription	String	A brief description.
Type	String	The data type of the connection property.
Default	String	The default value if one is not explicitly set.
Values	String	A comma-separated list of possible values. A validation error is thrown if another value is specified.
Value	String	The value you set or a preconfigured default.
Required	Boolean	Whether the property is required to connect.
Category	String	The category of the connection property.
IsSessionProperty	String	Whether the property is a session property, used to save information about the current connection.
Sensitivity	String	The sensitivity level of the property. This informs whether the property is obfuscated in logging and authentication forms.
PropertyName	String	A camel-cased truncated form of the connection property name.
Ordinal	Int32	The index of the parameter.
CatOrdinal	Int32	The index of the parameter category.
Hierarchy	String	Shows dependent properties associated that need to be set alongside this one.
Visible	Boolean	Informs whether the property is visible in the connection UI.
ETC	String	Various miscellaneous information about the property.

sys_sqlinfo

Describes the SELECT query processing that the Cloud can offload to the data source.

See SQL Compliance for SQL syntax details.

Discovering the Data Source's SELECT Capabilities

Below is an example data set of SQL capabilities. Some aspects of SELECT functionality are returned in a comma-separated list if supported; otherwise, the column contains NO.


Name	Description	Possible Values
AGGREGATE_FUNCTIONS	Supported aggregation functions.	AVG, COUNT, MAX, MIN, SUM, DISTINCT
COUNT	Whether COUNT function is supported.	YES, NO
IDENTIFIER_QUOTE_OPEN_CHAR	The opening character used to escape an identifier.	[
IDENTIFIER_QUOTE_CLOSE_CHAR	The closing character used to escape an identifier.	]
SUPPORTED_OPERATORS	A list of supported SQL operators.	=, >, <, >=, <=, <>, !=, LIKE, NOT LIKE, IN, NOT IN, IS NULL, IS NOT NULL, AND, OR
GROUP_BY	Whether GROUP BY is supported, and, if so, the degree of support.	NO, NO_RELATION, EQUALS_SELECT, SQL_GB_COLLATE
OJ_CAPABILITIES	The supported varieties of outer joins supported.	NO, LEFT, RIGHT, FULL, INNER, NOT_ORDERED, ALL_COMPARISON_OPS
OUTER_JOINS	Whether outer joins are supported.	YES, NO
SUBQUERIES	Whether subqueries are supported, and, if so, the degree of support.	NO, COMPARISON, EXISTS, IN, CORRELATED_SUBQUERIES, QUANTIFIED
STRING_FUNCTIONS	Supported string functions.	LENGTH, CHAR, LOCATE, REPLACE, SUBSTRING, RTRIM, LTRIM, RIGHT, LEFT, UCASE, SPACE, SOUNDEX, LCASE, CONCAT, ASCII, REPEAT, OCTET, BIT, POSITION, INSERT, TRIM, UPPER, REGEXP, LOWER, DIFFERENCE, CHARACTER, SUBSTR, STR, REVERSE, PLAN, UUIDTOSTR, TRANSLATE, TRAILING, TO, STUFF, STRTOUUID, STRING, SPLIT, SORTKEY, SIMILAR, REPLICATE, PATINDEX, LPAD, LEN, LEADING, KEY, INSTR, INSERTSTR, HTML, GRAPHICAL, CONVERT, COLLATION, CHARINDEX, BYTE
NUMERIC_FUNCTIONS	Supported numeric functions.	ABS, ACOS, ASIN, ATAN, ATAN2, CEILING, COS, COT, EXP, FLOOR, LOG, MOD, SIGN, SIN, SQRT, TAN, PI, RAND, DEGREES, LOG10, POWER, RADIANS, ROUND, TRUNCATE
TIMEDATE_FUNCTIONS	Supported date/time functions.	NOW, CURDATE, DAYOFMONTH, DAYOFWEEK, DAYOFYEAR, MONTH, QUARTER, WEEK, YEAR, CURTIME, HOUR, MINUTE, SECOND, TIMESTAMPADD, TIMESTAMPDIFF, DAYNAME, MONTHNAME, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, EXTRACT
REPLICATION_SKIP_TABLES	Indicates tables skipped during replication.
REPLICATION_TIMECHECK_COLUMNS	A string array containing a list of columns which will be used to check for (in the given order) to use as a modified column during replication.
IDENTIFIER_PATTERN	String value indicating what string is valid for an identifier.
SUPPORT_TRANSACTION	Indicates if the provider supports transactions such as commit and rollback.	YES, NO
DIALECT	Indicates the SQL dialect to use.
KEY_PROPERTIES	Indicates the properties which identify the uniform database.
SUPPORTS_MULTIPLE_SCHEMAS	Indicates if multiple schemas may exist for the provider.	YES, NO
SUPPORTS_MULTIPLE_CATALOGS	Indicates if multiple catalogs may exist for the provider.	YES, NO
DATASYNCVERSION	The CData Data Sync version needed to access this driver.	Standard, Starter, Professional, Enterprise
DATASYNCCATEGORY	The CData Data Sync category of this driver.	Source, Destination, Cloud Destination
SUPPORTSENHANCEDSQL	Whether enhanced SQL functionality beyond what is offered by the API is supported.	TRUE, FALSE
SUPPORTS_BATCH_OPERATIONS	Whether batch operations are supported.	YES, NO
SQL_CAP	All supported SQL capabilities for this driver.	SELECT, INSERT, DELETE, UPDATE, TRANSACTIONS, ORDERBY, OAUTH, ASSIGNEDID, LIMIT, LIKE, BULKINSERT, COUNT, BULKDELETE, BULKUPDATE, GROUPBY, HAVING, AGGS, OFFSET, REPLICATE, COUNTDISTINCT, JOINS, DROP, CREATE, DISTINCT, INNERJOINS, SUBQUERIES, ALTER, MULTIPLESCHEMAS, GROUPBYNORELATION, OUTERJOINS, UNIONALL, UNION, UPSERT, GETDELETED, CROSSJOINS, GROUPBYCOLLATE, MULTIPLECATS, FULLOUTERJOIN, MERGE, JSONEXTRACT, BULKUPSERT, SUM, SUBQUERIESFULL, MIN, MAX, JOINSFULL, XMLEXTRACT, AVG, MULTISTATEMENTS, FOREIGNKEYS, CASE, LEFTJOINS, COMMAJOINS, WITH, LITERALS, RENAME, NESTEDTABLES, EXECUTE, BATCH, BASIC, INDEX
PREFERRED_CACHE_OPTIONS	A string value specifies the preferred cacheOptions.
ENABLE_EF_ADVANCED_QUERY	Indicates if the driver directly supports advanced queries coming from Entity Framework. If not, queries will be handled client side.	YES, NO
PSEUDO_COLUMNS	A string array indicating the available pseudo columns.
MERGE_ALWAYS	If the value is true, The Merge Mode is forcibly executed in Data Sync.	TRUE, FALSE
REPLICATION_MIN_DATE_QUERY	A select query to return the replicate start datetime.
REPLICATION_MIN_FUNCTION	Allows a provider to specify the formula name to use for executing a server side min.
REPLICATION_START_DATE	Allows a provider to specify a replicate startdate.
REPLICATION_MAX_DATE_QUERY	A select query to return the replicate end datetime.
REPLICATION_MAX_FUNCTION	Allows a provider to specify the formula name to use for executing a server side max.
IGNORE_INTERVALS_ON_INITIAL_REPLICATE	A list of tables which will skip dividing the replicate into chunks on the initial replicate.
CHECKCACHE_USE_PARENTID	Indicates whether the CheckCache statement should be done against the parent key column.	TRUE, FALSE
CREATE_SCHEMA_PROCEDURES	Indicates stored procedures that can be used for generating schema files.

The following query retrieves the operators that can be used in the WHERE clause:

SELECT * FROM sys_sqlinfo WHERE Name = 'SUPPORTED_OPERATORS'

Note that individual tables may have different limitations or requirements on the WHERE clause; refer to the REST Data Model section for more information.

Columns


Name	Type	Description
NAME	String	A component of SQL syntax, or a capability that can be processed on the server.
VALUE	String	Detail on the supported SQL or SQL syntax.

sys_identity

Returns information about attempted modifications.

The following query retrieves the Ids of the modified rows in a batch operation:

         SELECT * FROM sys_identity

Columns


Name	Type	Description
Id	String	The database-generated Id returned from a data modification operation.
Batch	String	An identifier for the batch. 1 for a single operation.
Operation	String	The result of the operation in the batch: INSERTED, UPDATED, or DELETED.
Message	String	SUCCESS or an error message if the update in the batch failed.

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. AzureDevOps OnPremise connections will only support Basic authentication.
Organization	The name of the Organization or Collection depending upon AzureDevOpsEdition value.
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].
ProjectId	The unique identifier (id) associated with your Organization's Project.
User	The Azure DevOps user account used to authenticate.
AzureDevOpsServiceAPI	The REST API version of AzureDevOps being used. Set either [6.0] or [5.1].

Azure Authentication

Property	Description
AzureTenant	The Microsoft Online tenant being used to access data. If not specified, your default tenant is used.
AzureEnvironment	The Azure Environment to use when establishing a connection.

SSL

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

Logging

Property	Description
Verbosity	The verbosity level that determines the amount of detail included in the log file.

Schema

Property	Description
BrowsableSchemas	This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.
Schema	Specify this property to connect with REST or Analytics services.

Miscellaneous

Property	Description
ApplyTransformations	A comma separated list of supported apply transformations. Set to off to indicate none, will disable auto detect.
IncludeCustomFields	A boolean indicating if you would like to include custom fields in the column listing.
MaxRows	Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
PseudoColumns	This property indicates whether or not to include pseudo columns as columns to the table.
Timeout	The value in seconds until the timeout error is thrown, canceling the operation.

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. AzureDevOps OnPremise connections will only support Basic authentication.
Organization	The name of the Organization or Collection depending upon AzureDevOpsEdition value.
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].
ProjectId	The unique identifier (id) associated with your Organization's Project.
User	The Azure DevOps user account used to authenticate.
AzureDevOpsServiceAPI	The REST API version of AzureDevOps being used. Set either [6.0] or [5.1].

Property	Description
AzureTenant	The Microsoft Online tenant being used to access data. If not specified, your default tenant is used.
AzureEnvironment	The Azure Environment to use when establishing a connection.

AzureTenant

The Microsoft Online tenant being used to access data. If not specified, your default tenant is used.

Data Type

string

Default Value

""

Remarks

The Microsoft Online tenant being used to access data. For instance, contoso.onmicrosoft.com. Alternatively, specify the tenant Id. This value is the directory Id in the Azure Portal > Azure Active Directory > Properties.

Typically it is not necessary to specify the Tenant. This can be automatically determined by Microsoft when using the OAuthGrantType set to CODE (default). However, it may fail in the case that the user belongs to multiple tenants. For instance, if an Admin of domain A invites a user of domain B to be a guest user. The user will now belong to both tenants. It is a good practice to specify the Tenant, although in general things should normally work without having to specify it.

The AzureTenant is required when setting OAuthGrantType to CLIENT. When using client credentials, there is no user context. The credentials are taken from the context of the app itself. While Microsoft still allows client credentials to be obtained without specifying which Tenant, it has a much lower probability of picking the specific tenant you want to work with. For this reason, we require AzureTenant to be explicitly stated for all client credentials connections to ensure you get credentials that are applicable for the domain you intend to connect to.

AzureEnvironment

The Azure Environment to use when establishing a connection.

Possible Values

GLOBAL, CHINA, USGOVT, USGOVTDOD

Data Type

string

Default Value

"GLOBAL"

Remarks

In most cases, leaving the environment set to global will work. However, if your Azure Account has been added to a different environment, the AzureEnvironment may be used to specify which environment. The available values are GLOBAL, CHINA, USGOVT, USGOVTDOD.

SSL

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

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

SSLServerCert

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

Data Type

string

Default Value

""

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.

Logging

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

Property	Description
Verbosity	The verbosity level that determines the amount of detail included in the log file.

Verbosity

The verbosity level that determines the amount of detail included in the log file.

Data Type

string

Default Value

"1"

Remarks

The verbosity level determines the amount of detail that the Cloud reports to the Logfile. Verbosity levels from 1 to 5 are supported. These are detailed in the Logging page.

Schema

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

Property	Description
BrowsableSchemas	This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.
Schema	Specify this property to connect with REST or Analytics services.

BrowsableSchemas

This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.

Data Type

string

Default Value

""

Remarks

Listing the schemas from databases can be expensive. Providing a list of schemas in the connection string improves the performance.

Schema

Specify this property to connect with REST or Analytics services.

Possible Values

REST, Analytics

Data Type

string

Default Value

"REST"

Remarks

Set this property to REST to access AzureDevops REST endpoints or set to Analytics to access Odata Analytics service.

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. Set to off to indicate none, will disable auto detect.
IncludeCustomFields	A boolean indicating if you would like to include custom fields in the column listing.
MaxRows	Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
PseudoColumns	This property indicates whether or not to include pseudo columns as columns to the table.
Timeout	The value in seconds until the timeout error is thrown, canceling the operation.

Remarks

If Timeout = 0, operations do not time out. The operations run until they complete successfully or until they encounter an error condition.

If Timeout expires and the operation is not yet complete, the Cloud throws an exception.

Cloud

Azure DevOps - CData Cloud

Overview

Key Features

Getting Started

Connecting to Azure DevOps

Accessing Data from CData Cloud Services

Establishing a Connection

Connecting to Azure DevOps

Authenticating to Azure DevOps

Basic

Azure AD

Desktop Applications

Headless Machines

Creating a Custom AzureAD App

When to Create a Custom AzureAD App

Create a Custom AzureAD App

Desktop Apps

Headless Machines

Fine-Tuning Data Access

Fine Tuning Data Access

SSL Configuration

Customizing the SSL Configuration

Firewall and Proxy

Connecting Through a Firewall or Proxy

HTTP Proxies

Other Proxies

REST Data Model

Overview

Key Features

Tables

Views

Stored Procedures

Tables

CData Cloud - Azure DevOps Tables

AgentPools

Table Specific Information

Select

Insert

Update

Delete

Columns

Pseudo-Columns

BuildDefinitionDrafts

Table Specific Information

Select

Insert

Update

Delete

Columns

BuildDefinitions

Table Specific Information

Select

Insert

Update

Delete

Columns

Pseudo-Columns

Builds

Table Specific Information

Select

Update

Delete

Columns

Pseudo-Columns

Dashboards

Table Specific Information

Select

Insert

Update

Delete

Columns

DeploymentGroups

Table Specific Information

Select

Insert

Update

Delete

Columns

Pseudo-Columns