The CData Sync App provides a straightforward way to continuously pipeline your Jira data to any database, data lake, or data warehouse, making it easily available for Analytics, Reporting, AI, and Machine Learning.
The Jira connector can be used from the CData Sync application to pull data from Jira and move it to any of the supported destinations.
The Sync App utilizes version 2 and 3 of the API. This is managed internally, and no extra configurations are needed.
For required properties, see the Settings tab.
For connection properties that are not typically required, see the Advanced tab.
To connect set the URL to your Jira endpoint; for example, https://yoursitename.atlassian.net.
By default the Sync App surfaces only system fields. To access the custom fields for Issues, either set IncludeCustomFields to true, or extend the Sync App schemas to configure access to custom fields. (See Custom Fields.) Note that the server response time can be significantly slower when custom fields are included.
PATs are created by each individual user. When created, they automatically have the same permissions level as the user. Users can create a PAT either via their Atlassian Settings menu, or using a REST API.
From the Atlassian application:
Now set the following Jira Sync App properties:
Note: PATs can also be created using the REST API. For further information, see Jira's documentation on Creating PATs at https://confluence.atlassian.com/enterprise/using-personal-access-tokens-1026032365.html.
The following procedures assume that AuthScheme has been set to OAuth in all OAuth flows.
You can establish a connection to any Jira Cloud account by setting the AuthScheme to APIToken and providing the User and APIToken.
An API token is necessary for basic authentication to Cloud instances. To generate one, log in to your Atlassian account and navigate to Security > Create and manage API tokens > Create API token. The generated token will be displayed.
You can establish a connection to any Jira Server instance by setting the AuthScheme to Basic. To connect to a Server Instance provide the User and Password.
(Note: Password has been deprecated for connecting to a Cloud Account and is now used only to connect to a Server Instance.)
You can establish a connection to any Jira Server instance by setting the AuthScheme to LDAP. Additionally provide the URL, User and Password of the Jira instance.
(Note: LDAP Authentication is not currently supported for Cloud accounts.)
Set the AuthScheme to Crowd. The following connection properties are used to connect to Crowd:
The following is an example connection string:
AuthScheme=Crowd;Url=https://yoursitename.atlassian.net;SSOLoginURL='https://<authority>/crowd/console/secure/saml/sso.action';User=crowdUserName;Password=crowdPassword;SSOExchangeUrl=https://<authority of Jira instance>/plugins/servlet/samlconsumer;SSOAppName=CrowdAppName;SSOAppPassword=CrowdAppPassword;
To connect to Okta, set the AuthScheme to Okta, and set these properties:
If you are using a trusted application or proxy that overrides the Okta client request OR configuring MFA, you must use combinations of SSOProperties to authenticate using Okta. Set any of the following, as applicable:
Example connection string:
AuthScheme=Okta;Url=https://yoursitename.atlassian.net;SSOLoginURL='https://example.okta.com/home/appType/0bg4ivz6cJRZgCz5d6/46';User=oktaUserName;Password=oktaPassword;SSOExchangeUrl=https://<authority of Jira instance>/plugins/servlet/samlconsumer;
This section details a selection of advanced features of the Jira Sync App.
The Sync App allows you to define virtual tables, called user defined views, whose contents are decided by a pre-configured query. These views are useful when you cannot directly control queries being issued to the drivers. See User Defined Views for an overview of creating and configuring custom views.
Use SSL Configuration to adjust how Sync App handles TLS/SSL certificate negotiations. You can choose from various certificate formats; see the SSLServerCert property under "Connection String Options" for more information.
Configure the Sync App for compliance with Firewall and Proxy, including Windows proxies and HTTP proxies. You can also set up tunnel connections.
The Sync App offloads as much of the SELECT statement processing as possible to Jira and then processes the rest of the query in memory (client-side).
See Query Processing for more information.
See Logging for an overview of configuration settings that can be used to refine CData logging. For basic logging, you only need to set two connection properties, but there are numerous features that support more refined logging, where you can select subsets of information to be logged using the LogModules connection property.
By default, the Sync App 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.
The Jira Sync App also supports setting client certificates. Set the following to connect using a client certificate.
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.
Set the following properties:
The Sync App models the Jira API as relational tables, views, and stored procedures, allowing database-like connectivity to Jira Cloud accounts or Jira Server instances.
By default, the table and view schemas describe the column behavior of system fields for Jira entities. To access Custom Fields for Issues, as well as system fields, you can set IncludeCustomFields in the connection string.
You can use the following properties to gain more control over column names:
By default, the table and view schemas describe the column behavior of only system fields. To access custom fields for Issues, as well as system fields, you can set IncludeCustomFields in the connection string. You can also extend the schemas to configure access to custom fields. The following sections show how to surface different types of custom fields.
Set the IncludeCustomFields property to True to expose access to all custom fields.
Please note that there is a performance overhead associated with exposing all custom field. If this represents an obstacle in realizing the desired use case, granular control of the exposed metadata is possible using custom schema files.
Custom schema improve Sync App performance by enabling connections to a subset of all available custom field metadata.
Table and view schemas are defined in .rsd configuration files with a simple format that makes them easy to customize. You can call CreateSchema to save a schema file; for example, with the IncludeCustomFields connection property set. Or, adapt the example Issues schema, which you can find at the end of this section.
To connect to custom schemas, set the Location property to the folder containing the schema files. The schemas included with the Sync App are located in the db subfolder of the installation folder.
Columns are defined in the api:info section of the schema file. You can use any of the existing column definitions as a template for creating custom field columns. All column definitions have at least a name and data type:
Custom fields require the following additional parameters:
See the following sections for examples of specifying the XPath to the value you want for different types of columns.
The following example is a column definition for a custom field with a simple data type.
<attr name="MyCustomField" xs:type="string" other:relativexpath="customfield_10002" other:urlname="customfield_10002" desc="My custom field." />
Below is the JSON representation of an object-type custom field:
"customfield_10001" : {
"name" : "New name",
"description" : "desc"
}
The following example models the fields of the object-type custom field as columns.
<attr name="MyCustomFieldName" xs:type="string" other:relativexpath="name" other:urlname="customfield_10001" desc="My custom field." />
<attr name="MyCustomFieldDesc" xs:type="string" other:relativexpath="description" other:urlname="customfield_10001" desc="My custom field." />
To surface array-type custom fields, you will also need to pass several additional inputs to the processing operations of the Sync App, in addition to the relativexpath and urlname properties described in the Defining Custom Fields section. Use the api:set keyword to define the following variables.
Below is an example of an array-type custom field in the raw JSON response of a query to the Issues table. You can use the logging facilities of the Sync App to obtain the JSON response: Set Logfile to a location that the Sync App can write to and set Verbosity to 3.
{
"expand": "schema,names",
"startAt": 0,
"maxResults": 50,
"total": 43,
"issues" : [{
"expand" : "operations,versionedRepresentations,editmeta,changelog,renderedFields",
"id" : "10042",
"self" : "https://cdatadev.atlassian.net/rest/api/2/issue/10042",
"key" : "TEST-43",
"fields" : {
"customfield_1002" : [{
"description" : "MyCF",
"id" : "10001",
"created" : "2016-22-08",
"author" : {
"name" : "admin",
"key" : "admin",
}
}, {
"description" : "MyCF2",
"id" : "10002",
"created" : "2016-22-08",
"author" : {
"name" : "admin",
"key" : "admin",
}
},
...
]
}
...
]
}
Below is an example schema that surfaces the nested values in the array-type custom field.
<api:script xmlns:api="vhttp://apiscript.com/ns?v1">
<api:info title="CustomFieldSample" description="Custom field example.">
<attr name="IssueId" xs:type="string" other:absolutexpath="/json/issues/id" desc="The Id of the issue." other:urlname="id" references="Issues.Id" />
<attr name="IssueKey" xs:type="string" other:absolutexpath="/json/issues/key" desc="The key of the issue." other:urlname="key" references="Issues.Key" />
<attr name="CfId" xs:type="string" other:relativexpath="id" desc="The Id of the custom field." />
<attr name="CfDescription" xs:type="string" other:relativexpath="description" desc="The description of the custom field." />
<attr name="CfCreatedDate" xs:type="datetime" other:relativexpath="created" desc="The date the custom field was created." />
<input name="JQL" description="JQL(Jira Query Language) allows you to build structured queries." />
<input name="Rows@Next" description="This is used to page through multiple pages of results and should not be set manually." />
</api:info>
<!--XPath should stay the same. Do not change this. -->
<api:set attr="XPath" value="/json/issues" />
<!--Subrepeat elment XPath. Set this based on the custom field structure. -->
<api:set attr="SubRepeatElement" value="/json/issues/fields/customfield_10020"/>
<!--Choose a column that is in every custom field row. -->
<api:set attr="RepeatElement" value="CfId" />
<!--Required. Do not change this. -->
<api:set attr="IsSearch" value="true" />
<!--Set this to the name of the custom field plus the Id, separated by an underscore. -->
<api:set attr="JiraTableCode" value="customfield_10020" />
<api:script method="GET">
<api:call op="jiraadoSelect" output="out">
<api:push item="out" />
</api:call>
</api:script>
</api:script>
Note that the table also includes columns outside the custom field. You can use the absolutexpath column property to add these. This optional field can be used to specify an absolute XPath not relative to the repeat element.
Below is an example schema for the Issues table.
<api:script xmlns:api="http://apiscript.com/ns?v1">
<api:info title="Issues" description="" other:isissues="true">
<attr name="Id" xs:type="int" key="true" readonly="true" description="The Id of the issue." other:urlname="id" other:aggregate="" other:supportedoperators="=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN" other:fieldname="" other:insertfield="" other:relativexpath="id"/>
<attr name="Key" xs:type="string" columnsize="2000" readonly="true" description="The key of the issue." other:urlname="key" other:aggregate="" other:supportedoperators="=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN" other:fieldname="" other:insertfield="" other:relativexpath="key"/>
<attr name="IssueTypeId" xs:type="string" columnsize="2000" readonly="false" description="The issue type Id." other:urlname="issuetype" other:aggregate="" other:supportedoperators="=,!=,IS,IS NOT,IN,NOT IN" other:fieldname="" other:insertfield="" other:relativexpath="fields/issuetype/id"/>
<attr name="IssueTypeName" xs:type="string" columnsize="2000" readonly="true" description="The issue type name." other:urlname="issuetype" other:aggregate="" other:supportedoperators="=,!=,IS,IS NOT,IN,NOT IN" other:fieldname="" other:insertfield="" other:relativexpath="fields/issuetype/name"/>
<attr name="ProjectId" xs:type="string" columnsize="2000" readonly="false" description="The project Id of the issue." other:urlname="project" other:aggregate="" other:supportedoperators="=,!=,IS,IS NOT,IN,NOT IN" other:fieldname="" other:insertfield="" other:relativexpath="fields/project/id"/>
<attr name="ProjectName" xs:type="string" columnsize="2000" readonly="true" description="The project name of the issue." other:urlname="project" other:aggregate="" other:supportedoperators="=,!=,IS,IS NOT,IN,NOT IN" other:fieldname="" other:insertfield="" other:relativexpath="fields/project/name"/>
<attr name="ProjectKey" xs:type="string" columnsize="2000" readonly="false" description="The project key of the issue." other:urlname="project" other:aggregate="" other:supportedoperators="=,!=,IS,IS NOT,IN,NOT IN" other:fieldname="" other:insertfield="" other:relativexpath="fields/project/key"/>
<attr name="ResolutionId" xs:type="string" columnsize="2000" readonly="false" description="The resolution Id of the issue." other:urlname="resolution" other:aggregate="" other:supportedoperators="=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN" other:fieldname="" other:insertfield="" other:relativexpath="fields/resolution/id"/>
<attr name="ResolutionName" xs:type="string" columnsize="2000" readonly="false" description="The resolution name of the issue." other:urlname="resolution" other:aggregate="" other:supportedoperators="=,!=,IS,IS NOT,IN,NOT IN" other:fieldname="" other:insertfield="" other:relativexpath="fields/resolution/name"/>
<attr name="ResolutionDescription" xs:type="string" columnsize="2000" readonly="true" description="The resolution description of the issue." other:urlname="resolution" other:aggregate="" other:supportedoperators="" other:fieldname="" other:insertfield="" other:relativexpath="fields/resolution/description"/>
<attr name="ResolutionDate" xs:type="datetime" readonly="false" description="The resolution date of the issue." other:urlname="resolutiondate" other:aggregate="" other:supportedoperators="=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN" other:fieldname="" other:insertfield="" other:relativexpath="fields/resolutiondate"/>
<attr name="Workratio" xs:type="long" readonly="false" description="The work ratio of the issue." other:urlname="workratio" other:aggregate="" other:supportedoperators="=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN" other:fieldname="" other:insertfield="" other:relativexpath="fields/workratio"/>
<attr name="LastViewed" xs:type="datetime" readonly="true" description="The last time that the issue was viewed." other:urlname="lastViewed" other:aggregate="" other:supportedoperators="=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN" other:fieldname="" other:insertfield="" other:relativexpath="fields/lastViewed"/>
<attr name="WatchCount" xs:type="int" readonly="true" description="The number of watches of the issue." other:urlname="watches" other:aggregate="" other:supportedoperators="" other:fieldname="" other:insertfield="" other:relativexpath="fields/watches/watchCount"/>
<attr name="IsWatching" xs:type="bool" readonly="false" description="Whether the currently authenticated user is watching the issue." other:urlname="watches" other:aggregate="" other:supportedoperators="" other:fieldname="" other:insertfield="" other:relativexpath="fields/watches/isWatching"/>
<attr name="Created" xs:type="datetime" readonly="true" description="The creation date of the issue." other:urlname="created" other:aggregate="" other:supportedoperators="=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN" other:fieldname="" other:insertfield="" other:relativexpath="fields/created"/>
<attr name="PriorityId" xs:type="string" columnsize="2000" readonly="false" description="The priority Id of the issue." other:urlname="priority" other:aggregate="" other:supportedoperators="=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN" other:fieldname="" other:insertfield="" other:relativexpath="fields/priority/id"/>
<attr name="PriorityName" xs:type="string" columnsize="2000" readonly="true" description="The priority name of the issue." other:urlname="priority" other:aggregate="" other:supportedoperators="=,!=,IS,IS NOT,IN,NOT IN" other:fieldname="" other:insertfield="" other:relativexpath="fields/priority/name"/>
<attr name="TimeSpentSeconds" xs:type="long" readonly="true" description="The time spent in seconds on the issue." other:urlname="timespent" other:aggregate="" other:supportedoperators="" other:fieldname="timetracking" other:insertfield="" other:relativexpath="fields/timetracking/timeSpentSeconds"/>
<attr name="TimeSpent" xs:type="string" columnsize="2000" readonly="true" description="The time spent on the issue." other:urlname="timespent" other:aggregate="" other:supportedoperators="=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN" other:fieldname="timetracking" other:insertfield="" other:relativexpath="fields/timetracking/timeSpent"/>
<attr name="TimeRemainingEstimateSeconds" xs:type="long" readonly="true" description="The time estimate in seconds of the issue." other:urlname="timeestimate" other:aggregate="" other:supportedoperators="" other:fieldname="timetracking" other:insertfield="" other:relativexpath="fields/timetracking/remainingEstimateSeconds"/>
<attr name="TimeRemainingEstimate" xs:type="string" columnsize="2000" readonly="false" description="The time estimate of the issue." other:urlname="timeestimate" other:aggregate="" other:supportedoperators="=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN" other:fieldname="timetracking" other:insertfield="" other:relativexpath="fields/timetracking/remainingEstimate"/>
<attr name="TimeOriginalEstimateSeconds" xs:type="long" readonly="true" description="The original time estimate in seconds of the issue." other:urlname="timeoriginalestimate" other:aggregate="" other:supportedoperators="" other:fieldname="timetracking" other:insertfield="" other:relativexpath="fields/timetracking/originalEstimateSeconds"/>
<attr name="TimeOriginalEstimate" xs:type="string" columnsize="2000" readonly="false" description="The original time estimate of the issue." other:urlname="timeoriginalestimate" other:aggregate="" other:supportedoperators="=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN" other:fieldname="timetracking" other:insertfield="" other:relativexpath="fields/timetracking/originalEstimate"/>
<attr name="AggregateTimeSpent" xs:type="long" readonly="true" description="The aggregate time spent of the issue." other:urlname="aggregatetimespent" other:aggregate="" other:supportedoperators="" other:fieldname="" other:insertfield="" other:relativexpath="fields/aggregatetimespent"/>
<attr name="AggregateTimeOriginalEstimate" xs:type="long" readonly="true" description="The original aggregate time estimate of the issue." other:urlname="aggregatetimeoriginalestimate" other:aggregate="" other:supportedoperators="" other:fieldname="" other:insertfield="" other:relativexpath="fields/aggregatetimeoriginalestimate"/>
<attr name="AggregateTimeEstimate" xs:type="long" readonly="true" description="The aggregate time estimate of the issue." other:urlname="aggregatetimeestimate" other:aggregate="" other:supportedoperators="" other:fieldname="" other:insertfield="" other:relativexpath="fields/aggregatetimeestimate"/>
<attr name="AssigneeDisplayName" xs:type="string" columnsize="2000" readonly="true" description="Assignee display name." other:urlname="assignee" other:aggregate="" other:supportedoperators="" other:fieldname="" other:insertfield="" other:relativexpath="fields/assignee/displayName"/>
<attr name="AssigneeKey" xs:type="string" columnsize="2000" readonly="false" description="The assignee key of the issue." other:urlname="assignee" other:aggregate="" other:supportedoperators="=,!=,IS,IS NOT,IN,NOT IN" other:fieldname="" other:insertfield="" other:relativexpath="fields/assignee/key"/>
<attr name="AssigneeName" xs:type="string" columnsize="2000" readonly="true" description="The assignee name of the issue." other:urlname="assignee" other:aggregate="" other:supportedoperators="=,!=,IS,IS NOT,IN,NOT IN" other:fieldname="" other:insertfield="" other:relativexpath="fields/assignee/name"/>
<attr name="AssigneeEmail" xs:type="string" columnsize="2000" readonly="true" description="The assignee email of the issue." other:urlname="assignee" other:aggregate="" other:supportedoperators="=,!=,IS,IS NOT,IN,NOT IN" other:fieldname="" other:insertfield="" other:relativexpath="fields/assignee/emailAddress"/>
<attr name="Updated" xs:type="datetime" readonly="true" description="The updated date of the issue." other:urlname="updated" other:aggregate="" other:supportedoperators="=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN" other:fieldname="" other:insertfield="" other:relativexpath="fields/updated"/>
<attr name="StatusId" xs:type="string" columnsize="2000" readonly="false" description="The status Id of the issue." other:urlname="status" other:aggregate="" other:supportedoperators="=,!=,IS,IS NOT,IN,NOT IN" other:fieldname="" other:insertfield="" other:relativexpath="fields/status/id"/>
<attr name="StatusName" xs:type="string" columnsize="2000" readonly="true" description="The status name of the issue." other:urlname="status" other:aggregate="" other:supportedoperators="=,!=,IS,IS NOT,IN,NOT IN" other:fieldname="" other:insertfield="" other:relativexpath="fields/status/name"/>
<attr name="Description" xs:type="string" columnsize="2000" readonly="false" description="The description of the issue." other:urlname="description" other:aggregate="" other:supportedoperators="LIKE,NOT LIKE,IS,IS NOT" other:fieldname="" other:insertfield="" other:relativexpath="fields/description"/>
<attr name="Summary" xs:type="string" columnsize="2000" readonly="false" description="The summary of the issue." other:urlname="summary" other:aggregate="" other:supportedoperators="LIKE,NOT LIKE,IS,IS NOT" other:fieldname="" other:insertfield="" other:relativexpath="fields/summary"/>
<attr name="CreatorDisplayName" xs:type="string" columnsize="2000" readonly="true" description="Issue creator display name." other:urlname="creator" other:aggregate="" other:supportedoperators="" other:fieldname="" other:insertfield="" other:relativexpath="fields/creator/displayName"/>
<attr name="CreatorName" xs:type="string" columnsize="2000" readonly="true" description="The creator name of the issue." other:urlname="creator" other:aggregate="" other:supportedoperators="=,!=,IS,IS NOT,IN,NOT IN" other:fieldname="" other:insertfield="" other:relativexpath="fields/creator/name"/>
<attr name="CreatorKey" xs:type="string" columnsize="2000" readonly="true" description="The creator key of the issue." other:urlname="creator" other:aggregate="" other:supportedoperators="=,!=,IS,IS NOT,IN,NOT IN" other:fieldname="" other:insertfield="" other:relativexpath="fields/creator/key"/>
<attr name="CreatorEmail" xs:type="string" columnsize="2000" readonly="true" description="The creator email of the issue." other:urlname="creator" other:aggregate="" other:supportedoperators="=,!=,IS,IS NOT,IN,NOT IN" other:fieldname="" other:insertfield="" other:relativexpath="fields/creator/emailAddress"/>
<attr name="ReporterDisplayName" xs:type="string" columnsize="2000" readonly="true" description="Issue reporter display name." other:urlname="reporter" other:aggregate="" other:supportedoperators="" other:fieldname="" other:insertfield="" other:relativexpath="fields/reporter/displayName"/>
<attr name="ReporterName" xs:type="string" columnsize="2000" readonly="true" description="The reporter name of the issue." other:urlname="reporter" other:aggregate="" other:supportedoperators="=,!=,IS,IS NOT,IN,NOT IN" other:fieldname="" other:insertfield="" other:relativexpath="fields/reporter/name"/>
<attr name="ReporterKey" xs:type="string" columnsize="2000" readonly="true" description="The reporter key of the issue." other:urlname="reporter" other:aggregate="" other:supportedoperators="=,!=,IS,IS NOT,IN,NOT IN" other:fieldname="" other:insertfield="" other:relativexpath="fields/reporter/key"/>
<attr name="ReporterEmail" xs:type="string" columnsize="2000" readonly="true" description="The reporter email of the issue." other:urlname="reporter" other:aggregate="" other:supportedoperators="=,!=,IS,IS NOT,IN,NOT IN" other:fieldname="" other:insertfield="" other:relativexpath="fields/reporter/emailAddress"/>
<attr name="Environment" xs:type="string" columnsize="2000" readonly="true" description="Search for issues where the environment contains particular text." other:urlname="reporter" other:aggregate="" other:supportedoperators="LIKE,NOT LIKE,IS,IS NOT" other:fieldname="" other:insertfield="" other:relativexpath="fields/environment"/>
<attr name="SecurityLevel" xs:type="string" columnsize="2000" readonly="true" description="Search for issues with a particular security level." other:urlname="reporter" other:aggregate="" other:supportedoperators="=,!=,IS,IS NOT,IN,NOT IN" other:fieldname="" other:insertfield="" other:relativexpath="fields/security/name"/>
<attr name="AggregateProgress" xs:type="long" readonly="true" description="The aggregate progress of the issue." other:urlname="aggregateprogress" other:aggregate="" other:supportedoperators="" other:fieldname="" other:insertfield="" other:relativexpath="fields/aggregateprogress/progress"/>
<attr name="TotalProgress" xs:type="long" readonly="true" description="The aggregate total progress of the issue." other:urlname="aggregateprogress" other:aggregate="" other:supportedoperators="" other:fieldname="" other:insertfield="" other:relativexpath="fields/aggregateprogress/total"/>
<attr name="Votes" xs:type="int" readonly="true" description="Votes of the issue." other:urlname="votes" other:aggregate="" other:supportedoperators="=,!=,>,>=,<,<=,IN,NOT IN" other:fieldname="" other:insertfield="" other:relativexpath="fields/votes/votes"/>
<attr name="HasVotes" xs:type="bool" readonly="true" description="The vote status of the issue." other:urlname="votes" other:aggregate="" other:supportedoperators="" other:fieldname="" other:insertfield="" other:relativexpath="fields/votes/hasVoted"/>
<attr name="DueDate" xs:type="datetime" readonly="false" description="The due date of the issue." other:urlname="duedate" other:aggregate="" other:supportedoperators="=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN" other:fieldname="" other:insertfield="" other:relativexpath="fields/duedate"/>
<attr name="Labels" xs:type="string" columnsize="2000" readonly="false" description="The labels of an issue" other:urlname="labels" other:aggregate="" other:supportedoperators="=,!=,IS,IS NOT,IN,NOT IN" other:fieldname="" other:insertfield="" other:relativexpath="fields/labels"/>
<attr name="FixVersionsAggregate" xs:type="string" columnsize="2000" readonly="false" description="The fix versions of the issue" other:urlname="fixVersions" other:aggregate="/json/issues/fields/fixVersions" other:supportedoperators="" other:fieldname="" other:insertfield="fixVersions" other:relativexpath="fields/fixVersions"/>
<attr name="ComponentsAggregate" xs:type="string" columnsize="2000" readonly="false" description="Search for issues that belong to a particular component of a project. You can search by component name or component Id." other:urlname="components" other:aggregate="/json/issues/fields/components" other:supportedoperators="" other:fieldname="" other:insertfield="" other:relativexpath="fields/components"/>
<input name="JQL" xs:type="string" columnsize="2000" description="JQL (Jira Query Language) allows you to build structured queries." other:supportedoperators="IS,IS NOT" />
<input name="Attachments" xs:type="string" columnsize="2000" description="Search for issues that have or do not have attachments. This column can be queried with IS EMPTY or IS NOT EMPTY." other:supportedoperators="LIKE,NOT LIKE" />
<input name="Comment" xs:type="string" columnsize="2000" description="Search for issues that have a comment that contains particular text." other:urlname="" other:aggregate="" other:supportedoperators="=,!=,IS,IS NOT,IN,NOT IN"/>
<input name="Category" xs:type="string" columnsize="2000" description="Search for issues that belong to projects in a particular category." other:supportedoperators="=,!=,IS,IS NOT,IN,NOT IN"/>
<input name="SprintId" xs:type="integer" columnsize="2000" description="Search for issues that belongs to a specific sprint, by specifying the Id of the Sprint." other:supportedoperators="=,!=,IS,IS NOT,IN,NOT IN"/>
<input name="SprintName" xs:type="string" columnsize="2000" description="Search for issues that belongs to a specific sprint, by specifying the name of the Sprint." other:supportedoperators="=,!=,IS,IS NOT,IN,NOT IN"/>
<input name="Filter" xs:type="string" columnsize="2000" description="Search for issues of saved filters. This column can be queried with filter name or filter id." other:supportedoperators="=,!=,IN,NOT IN"/>
<input name="Rows@Next" desc="Identifier for the next page of results. Do not set this value manually." />
</api:info>
<api:script method="GET">
<api:call op="jiraadoSelect">
<api:push/>
</api:call>
</api:script>
<api:script method="POST">
<api:call op="jiraadoInsert">
<api:push/>
</api:call>
</api:script>
<api:script method="MERGE">
<api:call op="jiraadoUpdate">
<api:push/>
</api:call>
</api:script>
<api:script method="DELETE">
<api:call op="jiraadoDelete">
<api:push/>
</api:call>
</api:script>
</api:script>
The Sync App models the data in Jira as a list of tables in a relational database that can be queried using standard SQL statements.
| Name | Description |
| Attachments | Query, insert and delete the available Attachments in Jira. |
| Boards | Query, insert and delete the agile Boards in Jira. |
| Comments | Create, modify, delete and query the available Comment types in Jira. |
| Issues | Create, modify, delete and query Issues in Jira. |
| IssueTypes | Query the available Issue Types in Jira. |
| ProjectComponents | Query, create, modify, and delete the available Components of a project in Jira. |
| ProjectRoleActors | Query the actors who act in a role for specific projects in Jira. |
| Projects | Query, update, insert and delete the available Projects in Jira. |
| ProjectVersions | Query,create, modify and delete the available Versions of a project in Jira. |
| RoleActors | Query the default actors for project roles in Jira. |
| Roles | Query the available roles in Jira. |
| Sprints | Query,create, modify and delete the agile Sprints in Jira. |
| Users | Query the available Users in Jira. |
| Workflows | Query the available Workflows in Jira. |
| Worklogs | Create, modify, delete and query the available Worklogs in Jira. |
Query, insert and delete the available Attachments in Jira.
The Sync App uses the Jira API to process some of the filters.
The Sync App processes other filters within the Sync App.
For example, the following queries are processed server side:
SELECT * FROM Attachments WHERE Id = 10003
Retrieve attachments of an issue:
SELECT * FROM Attachments WHERE IssueId = '123'
Use the JQL pseudocolumn for more complex filters on the issues to retrieve attachments from. For example, retrieve attachments of issues that were created after January 7th:
SELECT * FROM Attachments WHERE JQL = 'created > 2018-01-07'
Upload an attachment to an issue.
INSERT INTO Attachments (FilePath, IssueKey) VALUES ('C:/Users/User/Desktop/info.txt', 'SSFSF-1')
INSERT INTO Attachments (ContentEncoded, Name, IssueKey) VALUES ('U29tZSBjb250ZW50IGhlcmU=', 'Uploaded File', 'SSFSF-1')
Remove an attachment uploaded to any issue by specifying the Id of the attachment
DELETE FROM Attachments WHERE Id = 10003
| Name | Type | ReadOnly | References | SupportedOperators | Platform | Description |
| Id [KEY] | Integer | False | Common |
The Id of the attachment. | ||
| IssueId [KEY] | Integer | False |
Issues.Id | =,<>,>,>=,<,<=,IN,NOT IN | Common |
The Id of the issue. |
| IssueKey | String | False |
Issues.Key | =,<>,>,>=,<,<=,IN,NOT IN | Common |
The key of the issue. |
| IssueCreatedDate | Datetime | False | =,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN | Common |
The date the attachment's issue was created. | |
| IssueUpdatedDate | Datetime | False | =,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN | Common |
The date the attachment's issue was last updated. | |
| Filename | String | False | Common |
The filename of the attachment. | ||
| AuthorName | String | False | Common |
The name of the author of the attachment. | ||
| AuthorDisplayName | String | False |
Users.DisplayName | Common |
The display name of the author of the attachment. | |
| Created | Datetime | False | Common |
The creation date of the attachment. | ||
| Size | Integer | False | Common |
The size of the attachment. | ||
| MimeType | String | False | Common |
The MIME type of the attachment. | ||
| Content | String | False | Common |
The URI of the actual attached file. | ||
| Thumbnail | String | False | Common |
The thumbnail of the attachment. | ||
| AuthorAccountId | String | False |
Users.AccountId | Cloud |
The account Id of the author of the attachment. | |
| AuthorKey | String | False | Server |
The author key of the attachment. |
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 |
| JQL | String |
JQL (Jira Query Language) allows you to build structured queries. |
| FilePath | String |
The path of the file to insert. |
| ContentEncoded | String |
BASE64 encoded content of the file to insert. |
| Name | String |
The name of the file to upload. Also the file extension should be specified. |
Query, insert and delete the agile Boards in Jira.
The Sync App uses the Jira API to process some of the filters. The Sync App processes other filters within the Sync App.
The following is an example of a query with server-side filters
SELECT * FROM Boards WHERE Id = 10022
Using other filters:
SELECT * FROM Boards WHERE ProjectKeyOrId = 'QMTH' AND Type = 'scrum'
To insert a new board you must specify Name, Type and FilterId.
INSERT INTO Boards (Name, Type, FilterId) VALUES ('New board', 'scrum', 10200)
To delete a board you must specify its Id.
DELETE FROM Boards WHERE Id = 130
| Name | Type | ReadOnly | References | SupportedOperators | Platform | Description |
| Id [KEY] | Integer | True | = | Common |
The Id of the board. | |
| Name | String | False | = | Common |
The name of the board. | |
| Type | String | False | = | Common |
The type of the board. The allowed values are scrum, kanban, simple. |
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 |
| ProjectKeyOrId | String |
Filter the agile boards based on the project they are located in. |
| FilterId | String |
ID of a filter that the user has permissions to view. Not supported for next-gen boards. |
Create, modify, delete and query the available Comment types in Jira.
You can query this table to retrieve comments posted on all issues.
The Sync App uses the Jira API to process some of the filters.
The Sync App processes other filters within the Sync App.
For example, the following queries are processed server side:
SELECT * FROM Comments
You can also retrieve comments posted on a specific issue.
SELECT * FROM Comments WHERE IssueId = 10003
Additionally, the Created column can be used in the ORDER BY clause:
SELECT * FROM Comments WHERE IssueId = 10003 ORDER BY Created DESC
Post a comment as the logged in user. IssueId and Body are required.
INSERT INTO Comments (IssueId, Body) VALUES ('10003', 'Test Comment')
Only the body of the comment can be updated. The IssueId is required for an UPDATE as well.
UPDATE Comments SET Body = 'Updated Comment' WHERE Id = 10202 AND IssueId = 10003
Delete a comment posted in an issue by providing the Id and IssueId
DELETE FROM Comments WHERE Id = 10202 AND IssueId = 10003
| Name | Type | ReadOnly | References | SupportedOperators | Platform | Description |
| Id [KEY] | Integer | True | = | Common |
The Id of the comment. | |
| IssueId [KEY] | Integer | False |
Issues.Id | =,<>,>,>=,<,<=,IN,NOT IN | Common |
The Id of the issue. |
| IssueKey | String | False |
Issues.Key | =,<>,>,>=,<,<=,IN,NOT IN | Common |
The key of the issue. |
| IssueCreatedDate | Datetime | True | =,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN | Common |
The date the comment's issue was created. | |
| IssueUpdatedDate | Datetime | True | =,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN | Common |
The date the comment's issue was last updated. | |
| AuthorDisplayName | String | True |
Users.DisplayName | Common |
The display name of the author who made the comment. | |
| AuthorEmail | String | True |
Users.EmailAddress | Common |
The email address of the author who made the comment. | |
| UpdateDisplayName | String | True |
Users.DisplayName | Common |
The display name of the author who changed the comment. | |
| UpdateAuthorEmail | String | True |
Users.EmailAddress | Common |
The email address of the author who edited the comment. | |
| Body | String | False | Common |
The body of the comment. | ||
| RenderedBody | String | True | Common |
The rendered body of the comment. | ||
| Created | Datetime | True | Common |
The date the comment was created. | ||
| Updated | Datetime | True | Common |
The date the comment was updated. | ||
| VisibilityType | String | False | Common |
The visibility type of the comment. | ||
| VisibilityValue | String | False | Common |
The visibility value of the comment. | ||
| AuthorAccountName | String | True | Server |
The name of the author who made the comment. | ||
| AuthorAccountKey | String | True | Server |
The key of the author who made the comment. | ||
| UpdateAuthorAccountKey | String | True | Server |
The Id of the author who edited the comment. | ||
| UpdateAuthorAccountName | String | True | Server |
The name of the author who edited the comment. | ||
| AuthorAccountId | String | True |
Users.AccountId | Cloud |
The name of the author who made the comment. | |
| UpdateAuthorAccountId | String | True |
Users.AccountId | Cloud |
The Id of the author who edited the comment. |
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 |
| JQL | String |
JQL (Jira Query Language) allows you to build structured queries. |
Create, modify, delete and query Issues in Jira.
You should set the IncludeCustomFields connection property to 'True' in order to dynamically retrieve the Custom Fields columns and values (like Sprint, Epic, ...) when querying this table.
You can search for Issues by using SQL and JQL (Jira Query Language).
SELECT * FROM Issues WHERE JQL = ' project = "New project" AND Status WAS "open" '
SELECT * FROM Issues WHERE ProjectId > 10001 OR Updated <= '2016/03/29 15:00'The Sync App uses the Jira API to process some of the filters. The Sync App processes other filters client-side within the Sync App. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria that refers to other columns will cause inconsistent data. For example, the following queries are processed server side.
SELECT * FROM Issues WHERE ProjectName = 'New project' AND JQL = ' status WAS "open" '
Filter time interval fields like TimeSpent, OriginalEstimate and RemainingEstimate by specifying hours, days, weeks, minutes and so on.
SELECT * FROM Issues WHERE RemainingEstimate = '1m 2d' AND TimeSpent = '3m 2w'
Create a new issue in the Project. Project (specify either ProjectId or ProjectKey), IssueType (specify either IssueTypeId or IssueTypeName), and Summary are required for Inserts. ProjectName may also be specified instead, but it will cost an additional request to the server to find the ProjectKey value that it belongs to. Depending on the Project and the IssueType more fields may be required.
INSERT INTO Issues (ProjectId, Summary, IssueTypeId, CustomField1) VALUES (10000, 'Desc from prod', 10100, 'test')
INSERT INTO Issues (ProjectKey, Summary, IssueTypeName, CustomField1) VALUES ('PROD', 'Desc from prod', 'Task', 'test')
Update info of existing issues by providing the Id
UPDATE Issues SET Summary = 'Desc FROM prod', CustomField1 = 'ValidValue' WHERE Id = '10003'
You can also modify some aggregate fields for a specific issue. You can modify FixVersionsAggregate, AffectedVersionsAggregate, ComponentsAggregate and IssueLinksAggregate. Using the special inputs for these columns, you can assign, remove and update the appropriate field for an issue. Note that the object must first be created in the Jira application to use it with the provider queries. NOTE: The IssuelinksAggregate field has slightly different input requirements, outlined further below.
Structure for adding a fix version:
{ "add": {"name":"example"} }
or
{ "add": {"id":"1234"} }
An example query:
UPDATE Issues SET FixVersionsAggregate = '{"add":{"name":"extra"}}' WHERE Id = 19683
You can only add one object at a time, but using the set command you can assign multiple objects.
IMPORTANT NOTE: The set command will overwrite all the previous objects, meaning that previous entries will be discarded, and
only the objects specified in the set command will be saved to the issue. The set command must be followed by an array:
{ "set": [ {"name":"New Component 1"}, {"name":"Old Component 2"} ] }
or
{ "set": [ {"id":"1234"}, {"name":"Old Component 2"} ] }
And an example query would be:
UPDATE Issues SET ComponentsAggregate = '{ "set": [ {"id":"1234"}, {"name":"Old Components 2"} ] }' WHERE Id = 19683
You can also remove an entry one at a time using the remove command:
{ "remove": {"name":"example"} }
or
{ "remove": {"id":"1234"} }
UPDATE Issues SET AffectedVersionsAggregate = '{"remove":{"name":"extra"}}' WHERE id = 19683
These commands are also applicable when inserting a new issue:
INSERT INTO Issues (ProjectId, Summary, IssueTypeId, FixVersionsAggregate) VALUES (10000, 'Desc from prod', 10100, '{"add":{"name":"Example1"}}')
INSERT INTO Issues (ProjectId, Summary, IssueTypeId, FixVersionsAggregate) VALUES (10000, 'Desc from prod', 10100, '{"set":[{"name":"Example1"}]}')
Only the 'add' operation is supported with IssueLinksAggregate, and you must specify the type of the link and the direction of the link, either 'outwardIssue' or 'inwardIssue', but not both.
The valid link types are Blocks, RootCause, Relates, Duplicate and Cloners
{ "add":
{ "outwardIssue":
{"key":"COB-15"},
"type":
{"name":"Cloners"}
}
}
or
{ "add":
{ "inwardIssue":
{"key":"FIL-78"},
"type":
{"name":"Blocks"}
}
}
INSERT INTO Issues (ProjectId, Summary, IssueTypeId, FixVersionsAggregate) VALUES (10000, 'Desc from prod', 10100, '{"add":{"outwardIssue":{"key":"COB-15"},"type":{"name":"Cloners"}}}')
"UPDATE Issues SET IssuelinksAggregate = '{"add":{"inwardIssue":{"key":"FIL-1"},"type":{"name":"Blocks"}}}' WHERE Id = '22204'"
Delete an issue by providing the Id. Note that subtasks that have not been completed will be deleted as well.
DELETE FROM Issues WHERE Id = '10003'
| Name | Type | ReadOnly | References | SupportedOperators | Platform | Description |
| Id [KEY] | Int | True | =,!=,>,>=,<,<=,IN,NOT IN | Common |
The Id of the issue. | |
| Key | String | True | =,!=,>,>=,<,<=,IN,NOT IN | Common |
The key of the issue. | |
| IssueTypeId | String | False | =,!=,IS,IS NOT,IN,NOT IN | Common |
The issue type Id. | |
| IssueTypeName | String | True | =,!=,IS,IS NOT,IN,NOT IN | Common |
The issue type name. | |
| ProjectId | String | False | =,!=,IS,IS NOT,IN,NOT IN | Common |
The project Id of the issue. | |
| ProjectName | String | True | =,!=,IS,IS NOT,IN,NOT IN | Common |
The project name of the issue. | |
| ProjectKey | String | False | =,!=,IS,IS NOT,IN,NOT IN | Common |
The project key of the issue. | |
| ParentId | Int | False | =,!=,IN,NOT IN | Common |
The Id of the issue's parent if the issue is a subtask. | |
| ParentKey | String | True | =,!=,IN,NOT IN | Common |
The key of the issue's parent if the issue is a subtask. | |
| ParentIssueTypeId | String | True | Common |
The issue type id of the issue's parent. | ||
| ParentIssueTypeName | String | True | Common |
The issue type name of the issue's parent. | ||
| ParentStatusId | String | True | Common |
The status id of the issue's parent. | ||
| ParentStatusName | String | True | Common |
The status name of the issue's parent. | ||
| ResolutionId | String | True | =,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN | Common |
The resolution Id of the issue. | |
| ResolutionName | String | True | =,!=,IS,IS NOT,IN,NOT IN | Common |
The resolution name of the issue. | |
| ResolutionDescription | String | True | Common |
The resolution description of the issue. | ||
| ResolutionDate | Datetime | True | =,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN | Common |
The resolution date of the issue. | |
| Workratio | Long | True | =,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN | Common |
The work ratio of the issue. | |
| LastViewed | Datetime | True | =,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN | Common |
The last time that the issue was viewed. | |
| WatchCount | Int | True | =,!=,>,>=,<,<=,IN,NOT IN | Common |
The number of watches of the issue. | |
| IsWatching | Bool | True | Common |
Whether the currently authenticated user is watching the issue. | ||
| Created | Datetime | True | =,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN | Common |
The creation date of the issue. | |
| PriorityId | String | False | =,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN | Common |
The priority Id of the issue. | |
| PriorityName | String | True | =,!=,IS,IS NOT,IN,NOT IN | Common |
The priority name of the issue. | |
| TimeSpentSeconds | Long | True | Common |
The time spent in seconds on the issue. | ||
| TimeSpent | String | True | =,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN | Common |
The time spent on the issue. | |
| RemainingEstimateSeconds | Long | True | Common |
The time estimate in seconds of the issue. | ||
| RemainingEstimate | String | False | =,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN | Common |
The time estimate of the issue. | |
| OriginalEstimateSeconds | Long | True | Common |
The original time estimate in seconds of the issue. | ||
| OriginalEstimate | String | False | =,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN | Common |
The original time estimate of the issue. | |
| AggregateTimeSpent | Long | True | Common |
The aggregate time spent of the issue. | ||
| AggregateTimeOriginalEstimate | Long | True | Common |
The original aggregate time estimate of the issue. | ||
| AggregateTimeEstimate | Long | True | Common |
The aggregate time estimate of the issue. | ||
| AssigneeDisplayName | String | True | Common |
Assignee display name. | ||
| AssigneeKey | String | True | =,!=,IS,IS NOT,IN,NOT IN | Common |
[DEPRECATED] The assignee key of the issue. | |
| AssigneeAccountId | String | False | =,!=,IS,IS NOT,IN,NOT IN | Common |
The assignee account id. | |
| AssigneeName | String | False | =,!=,IS,IS NOT,IN,NOT IN | Common |
The assignee name of the issue. | |
| AssigneeEmail | String | True | =,!=,IS,IS NOT,IN,NOT IN | Common |
The assignee email of the issue. | |
| Updated | Datetime | True | =,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN | Common |
The updated date of the issue. | |
| StatusId | String | True | =,!=,IS,IS NOT,IN,NOT IN | Common |
The status Id of the issue. | |
| StatusName | String | True | =,!=,IS,IS NOT,IN,NOT IN | Common |
The status name of the issue. | |
| StatusCategoryId | String | True | =,!=,IS,IS NOT,IN,NOT IN | Common |
The status category id of the issue. | |
| StatusCategoryKey | String | True | =,!=,IS,IS NOT,IN,NOT IN | Common |
The status category key of the issue. | |
| StatusCategoryName | String | True | =,!=,IS,IS NOT,IN,NOT IN | Common |
The status category name of the issue. | |
| Description | String | False | IS,IS NOT | Common |
The description of the issue. | |
| Summary | String | False | IS,IS NOT | Common |
The summary of the issue. | |
| CreatorDisplayName | String | True | Common |
Issue creator display name. | ||
| CreatorName | String | True | =,!=,IS,IS NOT,IN,NOT IN | Common |
The creator name of the issue. | |
| CreatorAccountId | String | True | =,!=,IS,IS NOT,IN,NOT IN | Common |
The creator account id. | |
| CreatorKey | String | True | =,!=,IS,IS NOT,IN,NOT IN | Common |
[DEPRECATED] The creator key of the issue. | |
| CreatorEmail | String | True | =,!=,IS,IS NOT,IN,NOT IN | Common |
The creator email of the issue. | |
| ReporterDisplayName | String | True | Common |
Issue reporter display name. | ||
| ReporterName | String | False | =,!=,IS,IS NOT,IN,NOT IN | Common |
The reporter name of the issue. | |
| ReporterKey | String | True | =,!=,IS,IS NOT,IN,NOT IN | Common |
[DEPRECATED]The reporter key of the issue. | |
| ReporterAccountId | String | False | =,!=,IS,IS NOT,IN,NOT IN | Common |
The reporter account id. | |
| ReporterEmail | String | True | =,!=,IS,IS NOT,IN,NOT IN | Common |
The reporter email of the issue. | |
| AggregateProgress | Long | True | Common |
The aggregate progress of the issue. | ||
| TotalProgress | Long | True | Common |
The aggregate total progress of the issue. | ||
| Votes | Int | True | =,!=,>,>=,<,<=,IN,NOT IN | Common |
Votes of the issue. | |
| HasVotes | Bool | True | Common |
The vote status of the issue. | ||
| DueDate | Date | False | =,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN | Common |
The due date of the issue. | |
| Labels | String | False | =,!=,IS,IS NOT,IN,NOT IN | Common |
The labels of an issue. | |
| Environment | String | False | LIKE,NOT LIKE,IS,IS NOT | Common |
The environment of an issue. | |
| SecurityLevel | String | False | =,!=,IS,IS NOT,IN, NOT IN | Common |
The security level of an issue. | |
| FixVersionsAggregate | String | False | Common |
The fix versions of the issue | ||
| ComponentsAggregate | String | False | Common |
Aggregate list of components included in the issue. | ||
| IssueLinksAggregate | String | False | Common |
The issue links of the issue. | ||
| AffectedVersionsAggregate | String | False | Common |
The affected versions of the issue. | ||
| SubtasksAggregate | String | True | Common |
The subtasks of the issue. | ||
| CustomField1 | Any | False | Common |
A custom field created for the Issues. The custom fields will be listed if the 'IncludeCustomFields' connection property is set to true. | ||
| CustomField2 | Any | False | Common |
A custom field created for the Issues. The custom fields will be listed if the 'IncludeCustomFields' connection property is set to true. |
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 |
| JQL | String |
JQL (Jira Query Language) allows you to build structured queries. |
| Attachments | String |
Search for issues that have or do not have attachments. This column can be queried with IS EMPTY or IS NOT EMPTY. |
| Comment | String |
Search for issues that have a comment that contains particular text. |
| Category | String |
Search for issues that belong to projects in a particular category. |
| SprintId | Integer |
Search for issues that belongs to a specific sprint, by specifying the Id of the Sprint. |
| SprintName | String |
Search for issues that belongs to a specific sprint, by specifying the name of the Sprint. |
| Filter | String |
Search for issues of saved filters. This column can be queried with filter name or filter id. |
Query the available Issue Types in Jira.
The Sync App uses the Jira API to process some of the filters.
The Sync App processes other filters within the Sync App.
SELECT * FROM IssueTypes WHERE Id = 3
Name is required to insert. The following query creates a new issue type:
INSERT INTO IssueTypes (Name, Description, Subtask) VALUES ('Issue type name 3', 'test description', false)
You can update by specifying the issue type Id:
UPDATE IssueTypes SET Name = 'Updated Name 3', Description = 'Updated description' WHERE Id = 10306
Delete an issue type by specifying the issue type Id:
DELETE FROM IssueTypes WHERE Id = 10306
| Name | Type | ReadOnly | References | SupportedOperators | Platform | Description |
| Id [KEY] | String | True | = | Common |
The Id of the issue type. | |
| Name | String | False | Common |
The name of the issue type. | ||
| Description | String | False | Common |
The description of the issue type. | ||
| Subtask | Boolean | False | Common |
The subtask of the issue type. Not applicable for update. | ||
| IconUrl | String | True | Common |
The icon URL of the issue type. |
Query, create, modify, and delete the available Components of a project in Jira.
The Sync App uses the Jira API to process some of the filters. The Sync App processes other filters within the Sync App.
For example, the following queries are processed server side:
Retrieve the components of each project
SELECT * FROM ProjectComponents
You can also filter the components by their Id or the Project they are part of:
SELECT * FROM ProjectComponents WHERE Id = 10022
Using ProjectId:
SELECT * FROM ProjectComponents WHERE ProjectId = 10027
Using ProjectKey:
SELECT * FROM ProjectComponents WHERE ProjectKey = 'QMTH'
Create a new Component for the project. The ProjectKey and Name columns are required.
INSERT INTO ProjectComponents (ProjectKey, Name, AssigneeType) VALUES ('COB', 'Testing Component', 'PROJECT_LEAD')
Make changes to a project component, specifying the Id column of the component to modify.
UPDATE ProjectComponents SET LeadKey = 'newlead' WHERE Id = 10065
Remove a component of the project by specifying the Id.
DELETE FROM ProjectComponents WHERE Id = 10065
| Name | Type | ReadOnly | References | SupportedOperators | Platform | Description |
| Id [KEY] | Integer | True | = | Common |
The Id of the component. | |
| ProjectId | Integer | True |
Projects.Id | =,IN | Common |
The selected project's id. |
| ProjectKey | String | False | =,IN | Common |
The selected project's key. | |
| Name | String | False | Common |
The name of the component. | ||
| Description | String | False | Common |
The description of the component. | ||
| LeadDisplayName | String | True | Common |
The display name of the component's lead. | ||
| LeadKey | String | False | Common |
The key of the component's lead. | ||
| AssigneeType | String | False | Common |
The type of the component's default assignee. The allowed values are PROJECT_DEFAULT, COMPONENT_LEAD, PROJECT_LEAD, UNASSIGNED. | ||
| AssigneeDisplayName | String | True | Common |
The display name of the component's default assignee. | ||
| AssigneeKey | String | True | Common |
The key of the component's default assignee. | ||
| IsAssigneeTypeValid | Boolean | True | Common |
Whether the assignee type is valid. |
Query the actors who act in a role for specific projects in Jira.
The Sync App will use the Jira API to process filters that refer to ProjectId, ProjectKey and RoleId while the rest of the filter is executed client side within the Sync App. The Jira APIs support filters using the equals (=) and IN operator with ProjectId, ProjectKey and RoleId. Note that either ProjectId, ProjectKey or RoleId must be specified with a comparison type of equals (=) for SELECT SQL statement.
For example, the following queries are processed server side:
Retrieve all actors for a specific project:
SELECT * FROM ProjectRoleActors WHERE ProjectId = '10650'
SELECT * FROM ProjectRoleActors WHERE ProjectKey = 'MYP'
Retrieve all actors for a specific role in all projects:
SELECT * FROM ProjectRoleActors WHERE RoleId = '10329'
Retrieve all actors for a specific project:
SELECT * FROM ProjectRoleActors WHERE RoleId = '10329' AND ProjectId = '10650'
Retrieve all actors for specific projects:
SELECT * FROM ProjectRoleActors WHERE RoleId = '10329' AND ProjectId IN ('10650', '10651', '10652')
Retrieve all actors for specific roles in a project:
SELECT * FROM ProjectRoleActors WHERE RoleId IN ('10329', '10330', '10331') AND ProjectId = '10650'
Add a user as an actor to a role in a specific project by specifying the user's account Id.
INSERT INTO ProjectRoleActors (ProjectId, RoleId, ActorUserAccountId) VALUES ('10650', '10329', '63acbf336f068efec8f6e0c3')
Add a group as an actor to a role in a specific project by either specifying the group's Id or name.
INSERT INTO ProjectRoleActors (ProjectId, RoleId, ActorGroupGroupId) VALUES ('10650', '10329', '0eed15e3-80cc-41d0-ba1a-b408658caf4d')
INSERT INTO ProjectRoleActors (ProjectId, RoleId, ActorGroupName) VALUES ('10650', '10329', 'JohnTest1')
Add a user as an actor to a role by specifying the project, role and user's name.
INSERT INTO ProjectRoleActors (ProjectId, RoleId, UserName) VALUES ('10000', '10101', 'myuser')
Add a group as an actor to a role by specifying the project, role and group's name.
INSERT INTO ProjectRoleActors (ProjectId, RoleId, GroupName) VALUES ('10000', '10101', 'test-group')
Delete an actor from a role by specifying the project, role and the user's account Id, the group's name or the group's Id.
DELETE FROM ProjectRoleActors WHERE ProjectId = '10650' AND RoleId = '10329' AND ActorUserAccountId = '63acbf336f068efec8f6e0c3'
DELETE FROM ProjectRoleActors WHERE ActorGroupGroupId = '0eed15e3-80cc-41d0-ba1a-b408658caf4d' AND ProjectId = '10650' AND RoleId = '10330'
DELETE FROM ProjectRoleActors WHERE ActorGroupName = 'JohnTest1' AND ProjectId = '10650' AND RoleId = '10330'
Delete a default actor from a role by specifying the user's name or the group's name.
DELETE FROM ProjectRoleActors WHERE ProjectId = '10000' AND RoleId = '10101' AND UserName = 'myuser'
DELETE FROM ProjectRoleActors WHERE ProjectId = '10000' AND RoleId = '10101' AND GroupName = 'test-group'
| Name | Type | ReadOnly | References | SupportedOperators | Platform | Description |
| Id [KEY] | Long | False | Common |
The ID of the role actor. | ||
| DisplayName | String | False | Common |
The display name of the role actor. For users, depending on the user?s privacy setting, this may return an alternative value for the user's name. | ||
| Type | String | False | Common |
The type of role actor. | ||
| Name | String | False | Server |
The name of the role. | ||
| AvatarUrl | String | False | Common |
The avatar of the role actor. | ||
| ActorUserAccountId | String | False | Cloud |
Details of the user associated with the role. The account ID of the user, which uniquely identifies the user across all Atlassian products. | ||
| ActorGroupName | String | False | Cloud |
Details of the group associated with the role. The name of the group. As a group's name can change, use of groupId is recommended to identify the group. | ||
| ActorGroupDisplayName | String | False | Cloud |
Details of the group associated with the role. The display name of the group. | ||
| ActorGroupGroupId | String | False | Cloud |
Details of the group associated with the role. The ID of the group. | ||
| RoleId | Long | False |
Roles.Id | =,IN | Common |
The Id of the role. |
| ProjectId | Integer | False |
Projects.Id | =,IN | Common |
The Id of the project associated with the role. |
| ProjectKey | String | False |
Projects.Key | =,IN | Common |
The key of the project associated with the role. |
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 |
| UserName | String |
Server platform only. Input for Insert and Delete statements. Details of the user associated with the role. The name of the user. |
| GroupName | String |
Server platform only. Input for Insert and Delete statements. Details of the group associated with the role. The name of the group. |
Query, update, insert and delete the available Projects in Jira.
The Sync App uses the Jira API to process some of the filters.
The Sync App processes other filters within the Sync App.
For example, the following queries are processed server side:
SELECT * FROM Projects WHERE Id = 10000
Key, Name, LeadName and ProjectTypeKey are required to insert. The following query creates a new project:
INSERT INTO Projects (Key, Name, LeadName, ProjectTypeKey, Description) VALUES ('EXE4', 'Inserted Project 4', 'admin', 'business', 'New business project.')
You can update by specifying either Id or Key:
UPDATE Projects SET Description = 'Updated description', AssigneeType = 'UNASSIGNED', LeadName = 'admin2' WHERE Key = 'EXE4'
Delete a project by specifying either Id or Key:
DELETE FROM Projects WHERE Key = 'EXE4'
| Name | Type | ReadOnly | References | SupportedOperators | Platform | Description |
| Id [KEY] | Int | True | = | Common |
The Id of the project. | |
| Key | String | False | = | Common |
The key of the project. | |
| Name | String | False | Common |
The name of the project. | ||
| Description | String | False | Common |
The description of the project. | ||
| LeadEmailAddress | String | True |
Users.EmailAddress | Common |
The email address of the project lead. | |
| LeadDisplayName | String | True |
Users.DisplayName | Common |
The display name of the project lead. | |
| ComponentsAggregate | String | True | Common |
The components of the project. | ||
| IssueTypesAggregate | String | True | Common |
The issue types of the project. | ||
| Url | String | True | Common |
The URL of the project. | ||
| String | True | Common |
The email of the project. | |||
| AssigneeType | String | False | Common |
The assignee type of the project. | ||
| VersionsAggregate | String | True | Common |
The versions of the project. | ||
| RolesAggregate | String | True | Common |
The roles of the project. | ||
| ProjectKeysAggregate | String | True | Common |
The project keys of the project. | ||
| ProjectCategoryId | String | False | Common |
The Id of the project category. | ||
| ProjectCategoryName | String | True | Common |
The name of the project category. | ||
| ProjectCategoryDescription | String | True | Common |
The description of the project category. | ||
| ProjectTypeKey | String | False | Common |
The key of the project type. Not applicable for update. | ||
| LeadAccountId | String | False |
Users.AccountId | Cloud |
The Id of the project lead. | |
| LeadAccountKey | String | True |
Users.Key | Server |
The Key of the project lead. | |
| LeadAccountName | String | False |
Users.Name | Server |
The user name of the project lead. | |
| Simplified | Boolean | True | Cloud |
Whether the project is team-managed or not. |
Query,create, modify and delete the available Versions of a project in Jira.
The Sync App uses the Jira API to process some of the filters. The Sync App processes other filters within the Sync App. For example, the following queries are processed server side:
Retrieve the versions for all projects:
SELECT * FROM ProjectVersions
You can also filter by the Id, ProjectKey, or ProjectId. For example:
SELECT * FROM ProjectVersions WHERE Id = 10022
Using ProjectId:
SELECT * FROM ProjectVersions WHERE ProjectId = 10022
Using ProjectKey:
SELECT * FROM ProjectVersions WHERE ProjectKey = 'QMTH'
Create a new version to be used throughout the project. The Name and either ProjectId or ProjectKey columns are required.
INSERT INTO ProjectVersions (ProjectId, Name, Description, ReleaseDate, StartDate) VALUES ('10000', 'HR Component', 'Example version description', '2018-04-04', '2018-02-02')
Make changes to a project version, specifying the Id column of the version to modify.
UPDATE ProjectVersions SET Released = 'true' WHERE Id = 10065
Remove a version of the project by specifying the Id column.
DELETE FROM ProjectVersions WHERE Id = 10065
| Name | Type | ReadOnly | References | SupportedOperators | Platform | Description |
| Id [KEY] | Integer | True | = | Common |
The Id of the version. | |
| ProjectId | Integer | False |
Projects.Id | =,IN | Common |
The selected project's id. |
| ProjectKey | String | False | =,IN | Common |
The selected project's key. | |
| Name | String | False | Common |
The name of the version. | ||
| Description | String | False | Common |
The description of the version. | ||
| Released | Boolean | False | Common |
Whether the version has been released. | ||
| ReleaseDate | Date | False | Common |
Release date of the version. | ||
| StartDate | Date | False | Common |
Optional start date of the version. | ||
| UserStartDate | Date | False | Common |
The date on which work on this version is expected to start. | ||
| UserReleaseDate | Date | False | Common |
The date on which work on this version is expected to finish. | ||
| Overdue | Boolean | True | Common |
Whether the version is overdue for release. | ||
| Archived | Boolean | False | Common |
Whether the version has been archived. When a new version is created, this field is always set to False. |
Query the default actors for project roles in Jira.
The Sync App will use the Jira API to process filters that refer to RoleId while the rest of the filter is executed client side within the Sync App. The Jira APIs support filters using the equals (=) and IN operator with RoleId.
For example, the following queries are processed server side:
Retrieve all the default actors for all roles:
SELECT * FROM RoleActors
Retrieve all the default actors for a specific role:
SELECT * FROM RoleActors WHERE RoleId = '10002'
Add a user as a default actor to a role by specifying the user's account Id.
INSERT INTO RoleActors (ActorUserAccountId, RoleId) VALUES ('63acbf336f068efec8f6e0c3', '10326')
Add a group as a default actor to a role by either specifying the group's Id or name.
INSERT INTO RoleActors (ActorGroupGroupId, RoleId) VALUES ('ad8da7e1-7b66-408e-a61c-c4c8383ca5d1', '10326')
INSERT INTO RoleActors (ActorGroupName, RoleId) VALUES ('testGroupName', '10326')
Add a user as a default actor to a role by specifying the user's key.
INSERT INTO RoleActors (RoleId, UserKey) VALUES ('10102', 'JIRAUSER10001')
Add a group as a default actor to a role by specifying the group's name.
INSERT INTO RoleActors (RoleId, GroupName) VALUES ('10102', 'test-group')
Delete a default actor from a role by specifying the RoleId and the user's account Id, the group's name or the group's Id.
DELETE FROM RoleActors WHERE RoleId = '10326' AND ActorUserAccountId = '63acbf336f068efec8f6e0c3'
DELETE FROM RoleActors WHERE RoleId = '10325' AND ActorGroupName = 'Developers'
DELETE FROM RoleActors WHERE RoleId = '10325' AND ActorGroupGroupId = '6dd7f7e1-77f0-4cd1-b8e1-a069830a8f1d'
Delete a default actor from a role by specifying the user's key or the group's name.
DELETE FROM RoleActors WHERE RoleId = '10102' AND UserKey = 'JIRAUSER10001'
DELETE FROM RoleActors WHERE RoleId = '10102' AND GroupName = 'test-group'
| Name | Type | ReadOnly | References | SupportedOperators | Platform | Description |
| Id [KEY] | Long | False | Common |
The ID of the role actor. | ||
| DisplayName | String | False | Common |
The display name of the role actor. For users, depending on the user?s privacy setting, this may return an alternative value for the user's name. | ||
| Type | String | False | Common |
The type of role actor. | ||
| AvatarUrl | String | False | Common |
The avatar of the role actor. | ||
| ActorUserAccountId | String | False |
Users.AccountId | Cloud |
Details of the user associated with the role. The account ID of the user, which uniquely identifies the user across all Atlassian products. | |
| ActorGroupName | String | False |
Groups.Name | Cloud |
Details of the group associated with the role. The name of the group. As a group's name can change, use of groupId is recommended to identify the group. | |
| ActorGroupDisplayName | String | False | Cloud |
Details of the group associated with the role. The display name of the group. | ||
| ActorGroupGroupId | String | False |
Groups.Id | Cloud |
Details of the group associated with the role. The ID of the group. | |
| Name | String | False | Server |
The name of the role. | ||
| RoleId | Long | False |
Roles.Id | =,IN | Common |
The Id of the role. |
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 |
| UserKey | String |
Server platform only. Input for Insert and Delete statements. Details of the user associated with the role. The key of the user. |
| GroupName | String |
Server platform only. Input for Insert and Delete statements. Details of the group associated with the role. The name of the group. |
Query the available roles in Jira.
The Sync App will use the Jira API to process filters that refer to Id while the rest of the filter is executed client side within the Sync App. The Jira APIs support filters using the equals (=) operator with Id.
For example, the following queries are processed server side:
Retrieve all the available Roles:
SELECT * FROM Roles
Retrieve a specific Role:
SELECT * FROM Roles WHERE Id = '10002'
Update the role name and description by specifying the role Id
UPDATE Roles SET Description = 'test_updated', Name = 'test_updated' WHERE Id = 10002
Delete a role by specifying the Id.
DELETE FROM Roles WHERE Id = '10002'
| Name | Type | ReadOnly | References | SupportedOperators | Platform | Description |
| Id [KEY] | Long | False | = | Common |
The Id of the role. | |
| Name | String | False | Common |
The name of the role. | ||
| Description | String | False | Common |
The description of the role. | ||
| Actors | String | False | Common |
The list of users who act in this role. | ||
| Scope | String | False | Cloud |
The scope of the role. | ||
| IsAdmin | Boolean | False | Cloud |
Whether this role is the admin role for the project. | ||
| IsDefault | Boolean | False | Cloud |
Whether this role is the default role for the project. |
Query,create, modify and delete the agile Sprints in Jira.
To retrieve a list of Sprints in your Jira account, you can query the BoardSprints view.
The Sync App uses the Jira API to process some of the filters. The Sync App will process the rest of the filter client side within itself. For example, the following query is processed server-side:
SELECT * FROM Sprints WHERE Id = 122
SELECT * FROM Sprints WHERE Id IN ('1', '2', '3')
Create a new sprint and link it to a board. The OriginBoardId and the Name columns are required for inserting a new sprint.
INSERT INTO Sprints (OriginBoardId, Name, Goal, StartDate, EndDate) VALUES ('1', 'Inserted Sprint', 'Complete target', '2018-02-02', '2018-04-04')
Make changes to a sprint by specifying the sprint's Id.
UPDATE Sprints SET State = 'active' WHERE Id = 305
Remove a sprint by specifying the Id.
DELETE FROM Sprints WHERE Id = 305
| Name | Type | ReadOnly | References | SupportedOperators | Platform | Description |
| Id [KEY] | Integer | True | =,IN | Common |
The Id of the sprint. | |
| Name | String | False | Common |
The name of the sprint. | ||
| State | String | False | Common |
The state of the sprint. A newly created sprint starts in the 'future' state. The state can only transition from 'future' to 'active', and from 'active' to 'closed' The allowed values are future, active, closed. | ||
| Goal | String | False | Common |
The goal assigned for the sprint. | ||
| OriginBoardId | Integer | False |
Boards.Id | = | Common |
The board Id the sprint originated from. This field cannot be updated |
| StartDate | Datetime | False | Common |
The date when the sprint was started. | ||
| EndDate | Datetime | False | Common |
The date when the sprint has ended. | ||
| CompleteDate | Datetime | True | Common |
The date when the sprint was completed. |
Query the available Users in Jira.
Specify Name, DisplayName, and EmailAddress one at a time.
The Jira supports filters based on these columns combined with the Active or IncludeInactive columns. For example the following queries are processed server side:
SELECT * FROM Users WHERE Name LIKE '%John%' AND Active = false AND IncludeInactive = true
Query the Users belonging to a specific group by specifying either the GroupName or the GroupId:
SELECT * FROM Users WHERE GroupName = 'Testing team' SELECT * FROM Users WHERE GroupId = '3dd7e7a1-77f7-4ac1-b8e1-a069830a7f1d'
You can also retrieve a list of all groups and their members:
SELECT * FROM Users WHERE GroupName IN (SELECT Name FROM Groups)
Use the IssueKey and ProjectKey pseudo-columns to find users who can browse a specific issue or any issue in a project.
SELECT * FROM Users WHERE IssueKey = 'PRJ-1' SELECT * FROM Users WHERE ProjectKey = 'PRJ'
INSERT INTO Users(EmailAddress) VALUES ('[email protected]')
| Name | Type | ReadOnly | References | SupportedOperators | Platform | Description |
| GroupName | String | True | =,IN | Common |
The name of the group the user is part of. | |
| GroupId | String | True | =,IN | Common |
The Id of the group the user is part of. | |
| DisplayName | String | False | = | Common |
The display name of the user. | |
| EmailAddress | String | False | = | Common |
The email address of the user. | |
| Active | Bool | True | Common |
Indicates whether the user is active. | ||
| TimeZone | String | True | Common |
The time zone specified in the user's profile. | ||
| Locale | String | True | Common |
The locale of the user. | ||
| AccountId [KEY] | String | True | Cloud |
The accountId of the user, which uniquely identifies the user across all Atlassian products. | ||
| AccountType | String | False | Cloud |
The accountType of the user. | ||
| Products | String | False |
A delimited string of jira products to which the user has access to, ex: jira-core,jira-servicedesk,jira-product-discovery,jira-software | |||
| Key [KEY] | String | True | Server |
The key of the user. | ||
| Name | String | False | Server |
The name of the user. |
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 |
| IssueKey | String |
Search for users who can browse a specific issue. |
| ProjectKey | String |
Search for users who can browse any issue in a project. |
Query the available Workflows in Jira.
Most filters are processed client side, though there are a few exceptions to this:
For example, the following queries are executed server side:
SELECT * FROM Workflows WHERE Name = 'abc'
SELECT * FROM Workflows WHERE Name IN ('abc','def','ghi')
SELECT * FROM Workflows WHERE IsActive = true AND Query = 'flow'
The Scope, Statuses, and Workflows columns accept aggregate values and are only used for inserting to and updating Workflows.
INSERT INTO Workflows(Scope, Statuses, Workflows) VALUES ('{"type":"GLOBAL"}', '[{"description":"","name":"To Do #2","statusCategory":"TODO","statusReference":"f0b24de5-25e7-4fab-ab94-63d81db6c0c0"}]', '[{"description":"","name":"Software workflow 1","startPointLayout":{"x":-100.00030899047852,"y":-153.00020599365235},"statuses":[{"layout":{"x":114.99993896484375,"y":-16},"properties":{},"statusReference":"f0b24de5-25e7-4fab-ab94-63d81db6c0c0"}],"transitions":[{"actions":[],"description":"","from":[],"id":"1","name":"Create","properties":{},"to":{"statusReference":"f0b24de5-25e7-4fab-ab94-63d81db6c0c0"},"triggers":[],"type":"INITIAL","validators":[]}]}]')", true)
| Name | Type | ReadOnly | References | SupportedOperators | Platform | Description |
| Id | String | False |
The entity ID of the workflow. | |||
| Name | String | False | IN |
The name of the workflow. | ||
| Description | String | False |
The description of the workflow. | |||
| Created | String | False |
The date when the workflow was created. | |||
| Updated | String | False |
The date when the workflow was updated. | |||
| IsDefault | Boolean | False |
Boolean indicating if the workflow is set as the default. | |||
| CanDelete | Boolean | False |
Boolean indicating whether the workflow can be deleted. | |||
| CanEdit | Boolean | False |
Boolean indicating whether the workflow can be edited. | |||
| IsActive | Boolean | False | = |
Filters active and inactive workflows. | ||
| Query | String | False | = |
String used to perform a case-insensitive partial match with workflow name. | ||
| Scope | String | False |
A JSON aggregate outlining the scope of the workflow. This column used exclusively for inserting to and updating workflows (does not return in SELECT query results). | |||
| Statuses | String | False |
A JSON aggregate outlining the statuses to associate with the workflows. This column used exclusively for inserting to and updating workflows (does not return in SELECT query results). | |||
| Workflows | String | False |
The details of the workflows to create. This column used exclusively for inserting to and updating workflows (does not return in SELECT query results). |
Create, modify, delete and query the available Worklogs in Jira.
You can query this table to retrieve worklogs of Jira projects. The Sync App will use the Jira APIs to process filters based on the following columns and operators. The Sync App will execute other filters client-side.
For example, the following query is processed server side:
SELECT * FROM Worklogs WHERE IssueId = 10001
IssueId, Started, and TimeSpentSeconds are required to insert. The following query logs work done for an issue:
INSERT INTO Worklogs (IssueId, Started, TimeSpentSeconds) VALUES (10003, '2017-03-20 20:00:00', 10000)
Both the worklog Id and IssueId are required to modify the worklog:
UPDATE Worklogs SET TimeSpentSeconds = 200000 WHERE Id = 10602 AND IssueId = 10003
Delete a worklog of an issue by providing the Id and IssueId:
DELETE FROM Worklogs WHERE Id = 10602 AND IssueId = 10003
This retrieves Ids of the deleted worklogs:
GETDELETED FROM Worklogs
| Name | Type | ReadOnly | References | SupportedOperators | Platform | Description |
| Id [KEY] | String | True | = | Common |
The Id of the worklog. | |
| IssueId [KEY] | Integer | False |
Issues.Id | =,<>,>,>=,<,<=,IN,NOT IN | Common |
The issue Id of the worklog. |
| IssueKey | String | False |
Issues.Key | =,IN,NOT IN | Common |
The key of the issue. |
| ProjectId | Integer | True |
Projects.Id | =,IS,IS NOT,IN,NOT IN | Common |
The project Id of the worklog's issue. |
| ProjectName | String | True | =,IS,IS NOT,IN,NOT IN | Common |
The project name of the worklog's issue. | |
| IssueCreatedDate | Datetime | True | =,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN | Common |
The date the worklogs's issue was created. | |
| IssueUpdatedDate | Datetime | True | =,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN | Common |
The date the worklogs's issue was last updated. | |
| AuthorDisplayName | String | True |
Users.DisplayName | =,!=,IS,IS NOT,IN,NOT IN | Common |
The display name of the author of the worklog. |
| UpdateAuthorDisplayName | String | True |
Users.DisplayName | =,!=,IS,IS NOT,IN,NOT IN | Common |
The display name of the author who has updated the worklog. |
| Comment | String | False | =,LIKE,NOT LIKE | Common |
The comment of the worklog. | |
| Created | Datetime | True | =,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN | Common |
The date the worklog was created. | |
| Updated | Datetime | True | Common |
The update date of the worklog. | ||
| VisibilityType | String | False | Common |
The visibility type of the worklog. | ||
| VisibilityValue | String | False | Common |
The visibility value of the worklog. | ||
| Started | Datetime | False | Common |
The start date of the worklog. | ||
| TimeSpent | String | False | Common |
The spent time of the worklog. | ||
| TimeSpentSeconds | Long | False | Common |
The time spent in seconds for the worklog. | ||
| AuthorAccountId | String | True |
Users.AccountId | =,!=,IS,IS NOT,IN,NOT IN | Cloud |
The name of the author of the worklog. |
| UpdateAuthorAccountId | String | True |
Users.AccountId | =,!=,IS,IS NOT,IN,NOT IN | Cloud |
The name of the author who has updated the worklog. |
| AuthorAccountKey | String | True |
Users.Key | =,!=,IS,IS NOT,IN,NOT IN | Server |
The key of the author of the worklog. |
| AuthorAccountName | String | True |
Users.Name | =,!=,IS,IS NOT,IN,NOT IN | Server |
The name of the author of the worklog. |
| UpdateAuthorAccountKey | String | True |
Users.Key | =,!=,IS,IS NOT,IN,NOT IN | Server |
The key of the author who has updated the worklog. |
| UpdateAuthorAccountName | String | True |
Users.Name | =,!=,IS,IS NOT,IN,NOT IN | Server |
The name of the author who has updated the worklog. |
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.
| Name | Description |
| AdvancedSettings | Query the application properties that are accessible on the Advanced Settings page. |
| ApplicationRoles | Query all application roles. In Jira, application roles are managed using the Application access configuration page. |
| Audit | Query the audit log of your Jira account. |
| BoardConfiguration | Query the agile Board Configuration in Jira. |
| BoardIssues | Query the agile Board Issues in Jira. |
| BoardSprints | Query the agile Sprints related to a Jira Board. |
| Configuration | Query the available Configurations in Jira. |
| Dashboards | Query the available Dashboards in Jira. |
| Epics | Query all the Epics. |
| FavouriteFilters | Query the favourite Filters in Jira. |
| Fields | Query the available System and Custom Fields in Jira. |
| Filters | Query the set up Filters in Jira. This table is not available in Jira Server. |
| Groups | Query the available Groups in Jira. |
| IssueAffectedVersions | Query the Affected Versions of an issue in Jira. |
| IssueChangelogs | Query the available IssueChangelogs in Jira. |
| IssueComponents | Query the available IssueComponents in Jira. |
| IssueCustomFieldOptions | Query Issue Custom Field Options in Jira. This resource is not available in Jira Server or when using OAuth authentication. Specifying at least CUSTOMFIELDID, or ID is required. |
| IssueCustomFields | Query the available Custom Fields in Jira. |
| IssueFixVersions | Query the available IssueFixVersions in Jira. |
| IssueLinks | Query the available IssueLinks in Jira. |
| IssueLinkTypes | Lists all the issue link types. |
| IssueNavigatorDefaultColumns | Query the default issue navigator columns. |
| IssuePriorities | Query the list of all issue priorities. |
| IssueResolutions | Query the list of all issue resolution values. |
| IssueSubtasks | Query the available IssueSubtasks in Jira. |
| IssueTransitions | Query the available IssueTransitions in Jira. |
| MyPermissions | Query the available Permissions of the current user in Jira. |
| Permissions | Query the available Permissions in Jira. |
| ProjectCategories | Query all the project categories. |
| ProjectRoles | Query roles for all projects in Jira. |
| ProjectsIssueTypes | Query the available Issue Types of projects. |
| ProjectTypes | Query the list of all issue priorities. |
| SecurityLevels | Query all the created security levels. |
| SecuritySchemes | Query all the created security schemes. |
| SprintIssues | Query the agile Sprint Issues in Jira. |
| Statuses | Query the possible Statuses in Jira. |
| TimeTrackingProviders | Query the list of all time tracking providers. |
| Votes | Query the available Votes in Jira. |
| Watchers | Query the available Watchers in Jira. |
| WorkflowStatusCategories | Query the list of all status categories. |
| WorkflowStatuses | Query the list of all statuses associated with workflows. |
| WorklogDeleted | Queries the deleted worklogs in your Jira account. |
Query the application properties that are accessible on the Advanced Settings page.
SELECT * FROM AdvancedSettings
| Name | Type | References | SupportedOperators | Platform | Description |
| Id [KEY] | String | = | Common | The ID of the application property. | |
| Name | String | Common | The name of the application property. | ||
| Description | String | Common | The description of the application property. | ||
| Type | String | Common | The data type of the application property. | ||
| Value | String | Common | The new value. | ||
| DefaultValue | String | Common | The default value of the application property. | ||
| AllowedValues | String | Common | The allowed values, if applicable. |
Query all application roles. In Jira, application roles are managed using the Application access configuration page.
The Sync App uses the Jira API to process some of the filters.
The Sync App processes other filters within the Sync App.
For example, the following queries are processed server side:
SELECT * FROM ApplicationRoles WHERE Key = 'jira-software'
| Name | Type | References | SupportedOperators | Platform | Description |
| Key [KEY] | String | = | Common | The key of the application role. | |
| Name | String | Common | The display name of the application role. | ||
| Groups | String | Common | The groups associated with the application role. | ||
| DefaultGroups | String | Common | The groups that are granted default access for this application role. | ||
| SelectedByDefault | Boolean | Common | Determines whether this application role should be selected by default on user creation. | ||
| NumberOfSeats | Integer | Common | The maximum count of users on your license. | ||
| RemainingSeats | Integer | Common | The count of users remaining on your license. | ||
| UserCount | Integer | Common | The number of users counting against your license. | ||
| UserCountDescription | String | Common | The type of users being counted against your license. | ||
| HasUnlimitedSeats | Boolean | Common | Determines whether this application role has unlimited seats. | ||
| Platform | Boolean | Common | Indicates if the application role belongs to Jira platform (jira-core). |
Query the audit log of your Jira account.
The Sync App uses the Jira API to process some of the filters.
The Sync App processes other filters within the Sync App.
For example, the following queries are processed server side:
SELECT * FROM Audit WHERE Filter = 'ab'
| Name | Type | References | SupportedOperators | Platform | Description |
| RecordId | Integer | Common | The Id of the audit record. | ||
| Summary | String | Common | Summary of the change. | ||
| RemoteAddress | String | Common | The remote address of the record. | ||
| AuthorKey | String | Common | Key of the author that made the change. | ||
| Created | Datetime | Common | Date on when the change was made. | ||
| Category | String | Common | Category of the change. | ||
| EventSource | String | Common | What triggered the event. | ||
| Description | String | Common | Description of the change. | ||
| ItemId | String | Common | Unique identifier of the object item. | ||
| ItemName | String | Common | Name of the object item. | ||
| ItemTypeName | String | Common | Typename of the object item. | ||
| ItemParentId | String | Common | Identifier of the parent of the object item. | ||
| ItemParentName | String | Common | Parent name of the object item. | ||
| FieldName | String | Common | The name of the changed field. | ||
| ChangedFrom | String | Common | The updated value. | ||
| ChangedTo | String | Common | The previous value. |
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 | |
| Filter | String | Containing a string in at least one field. For example, providing up will return all audit records where one or more fields contains words such as update. |
Query the agile Board Configuration in Jira.
| Name | Type | References | SupportedOperators | Platform | Description |
| BoardId [KEY] | Integer |
Boards.Id | =,IN | The Id of the board. | |
| ColumnConfig | String | The column configuration lists the columns for the board, in the order defined in the column configuration. For each column, it shows the issue status mapping as well as the constraint type (Valid values: none, issueCount, issueCountExclSubs) for the min/max number of issues. | |||
| Filter | String | Reference to the filter used by the given board. | |||
| Name | String | The Name of the board. | |||
| CustomFieldIdRank | Integer | The CustomFieldId ranking. | |||
| Self | String | The self/url column. | |||
| SubQuery | String | JQL subquery used by the given board. | |||
| Type | String | The type of the board. | |||
| EstimationDisplayName | String | Display name of the field used for estimation. | |||
| EstimationFieldId | String | The Id of the field used for estimation. | |||
| EstimationType | String | Contains information about type of estimation used for the board. Valid values: none, issueCount, field. If the estimation type is |
Query the agile Board Issues in Jira.
The Sync App uses the Jira API to process some of the filters. The Sync App processes other filters within the Sync App.
For example, the following queries are processed server side:
SELECT * FROM BoardIssues WHERE BoardId = '1' SELECT * FROM BoardIssues WHERE BoardId IN (70, 215)
| Name | Type | References | SupportedOperators | Platform | Description |
| Id [KEY] | Integer |
Issues.Id | =,IN | Common | The Id of the issue. |
| Key | String |
Issues.Key | =,IN | Common | The key of the issue. |
| BoardId [KEY] | Integer |
Boards.Id | =,IN | Common | The board the issue is included in. |
| IssueTypeId | String |
IssueTypes.Id | =,!=,IS,IS NOT,IN,NOT IN | Common | The issue type Id. |
| IssueTypeName | String | =,!=,IS,IS NOT,IN,NOT IN | Common | The issue type name. | |
| StatusId | Integer |
Statuses.Id | =,!=,IS,IS NOT,IN,NOT IN | Common | The issue status Id. |
| StatusName | String | =,!=,IS,IS NOT,IN,NOT IN | Common | The issue status name. | |
| ProjectId | Integer |
Projects.Id | =,!=,IS,IS NOT,IN,NOT IN | Common | The project Id of the issue. |
| ProjectKey | String |
Projects.Key | =,!=,IS,IS NOT,IN,NOT IN | Common | The project key of the issue. |
| ProjectName | String | =,!=,IS,IS NOT,IN,NOT IN | Common | The project name of the issue. | |
| ClosedSprintsAggregate | String | Common | The issue's previous sprints that have been closed. | ||
| Created | Datetime | =,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN | Common | The creation date of the issue. | |
| Updated | Datetime | =,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN | Common | The updated date of the issue. |
Query the agile Sprints related to a Jira Board.
The Sync App uses the Jira API to process some of the filters. The Sync App processes other filters within the Sync App.
For example, the following queries are processed server side.
SELECT * FROM BoardSprints WHERE BoardId = 122
Using BoardId:
SELECT * FROM BoardSprints WHERE BoardId IN (12, 42)
Boards of type 'kanban' do not support sprints, so you can retrieve all the sprints faster if you use a query like the following:
SELECT * FROM BoardSprints WHERE BoardId IN (SELECT Id FROM Boards WHERE Type != 'kanban')
| Name | Type | References | SupportedOperators | Platform | Description |
| Id [KEY] | Integer | Common | The Id of the sprint. | ||
| BoardId [KEY] | Integer |
Boards.Id | =,IN | Common | The board Id the sprint is in. |
| Name | String | Common | The name of the sprint. | ||
| State | String | = | Common | The state of the sprint. The sprint state can only transition from 'future' to 'active', and from 'active' to 'close'
The allowed values are future, active, closed. | |
| Goal | String | Common | The goal assigned for the sprint. | ||
| OriginBoardId | Integer |
Boards.Id | = | Common | The board Id the sprint originated from. This field cannot be updated |
| StartDate | Datetime | Common | The date when the sprint was started. | ||
| EndDate | Datetime | Common | The date when the sprint has ended. | ||
| CompleteDate | Datetime | Common | The date when the sprint was completed. |
Query the available Configurations in Jira.
The Sync App processes all filters specified on this table client-side within the Sync App.
SELECT * FROM Configuration
| Name | Type | References | SupportedOperators | Platform | Description |
| VotingEnabled | Boolean | Common | Configuration for voting. | ||
| WatchingEnabled | Boolean | Common | Configuration for watching. | ||
| UnassignedIssuesAllowed | Boolean | Common | Configuration to allow unassigned issues. | ||
| SubTasksEnabled | Boolean | Common | Configuration to enable subtasks. | ||
| IssueLinkingEnabled | Boolean | Common | Configuration to enable issue linking. | ||
| TimeTrackingEnabled | Boolean | Common | Configuration to enable time tracking. | ||
| AttachmentsEnabled | Boolean | Common | Configuration to enable attachments. | ||
| WorkingHoursPerDay | Double | Common | Configuration of the working hours per day. | ||
| WorkingDaysPerWeek | Double | Common | Configuration of the working days per week. | ||
| TimeFormat | String | Common | Configuration of the time format. | ||
| DefaultUnit | String | Common | Configuration of the default unit. |
Query the available Dashboards in Jira.
The Sync App uses the Jira API to process some of the filters. Jira supports the Id and Filter columns but not both at the same time.
The Sync App processes other filters within the Sync App.
For example, the following queries are processed server side.
SELECT * FROM Dashboards WHERE Id = 10001You can also filter on the pseudo column Filter:
SELECT * FROM Dashboards WHERE Filter = 'favourite'
| Name | Type | References | SupportedOperators | Platform | Description |
| Id [KEY] | String | = | Common | The Id of the dashboard. | |
| Name | String | Common | The name of the dashboard | ||
| View | String | Common | The view URL of the dashboard. |
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 | |
| Filter | String | This is used to filter data. Valid values include favourite and my.
The allowed values are favourite, my. |
Query all the Epics.
The Sync App uses the Jira API to process some of the filters.
The Sync App processes other filters within the Sync App.
For example, the following queries are filtered server-side:
SELECT * FROM Epics WHERE BoardId = 1
SELECT * FROM Epics WHERE BoardId IN (1, 2, 3)
By specifying the Id or Key of a certain epic:
SELECT * FROM Epics WHERE Id = 10000
SELECT * FROM Epics WHERE Id IN (10000, 10001)
SELECT * FROM Epics WHERE Key = 'TSS-9151'
SELECT * FROM Epics WHERE Key IN ('TSS-9151', 'TSS-9152')
| Name | Type | References | SupportedOperators | Platform | Description |
| Id [KEY] | Integer | =,IN | Common | The Id of the Epic. | |
| Key [KEY] | String | =,IN | Common | The key of the Epic. | |
| BoardId [KEY] | Integer |
Boards.Id | =,IN | Common | The board Id the Epic is in. |
| Name | String | Common | The name of the Epic. | ||
| Done | Boolean | Common | Whether or not the Epic is done. | ||
| ColorKey | String | Common | The key of the Epic's color. | ||
| Summary | String | Common | A brief summary for the Epic. |
Query the favourite Filters in Jira.
| Name | Type | References | SupportedOperators | Platform | Description |
| Id [KEY] | Integer | Common | The Id of the favourite filter. | ||
| Name | String | Common | The name assigned to the favourite filter. | ||
| JQLExpression | String | Common | The JQL by which the favourite filter will search issues by. | ||
| Description | String | Common | The description given to the favourite filter. | ||
| OwnerDisplayName | String | Common | The display name of the user who owns the favourite filter. | ||
| OwnerAccountId | String | Cloud | The account ID of the user who owns the favourite filter. | ||
| Count | Integer | Cloud | How many users have this filter as a favourite. | ||
| OwnerKey | String | Server | The key of the user who owns the favourite filter. |
Query the available System and Custom Fields in Jira.
| Name | Type | References | SupportedOperators | Platform | Description |
| Id [KEY] | String | Common | The Id of the field. | ||
| Key | String | Common | The key of the field. | ||
| Name | String | Common | The name of the field. | ||
| Custom | Boolean | Common | A boolean indicating if the field is custom. | ||
| Orderable | Boolean | Common | A boolean indicating if the field is orderable. | ||
| Navigable | Boolean | Common | A boolean indicating if the field is navigable. | ||
| Searchable | Boolean | Common | A boolean indicating if the field is searchable. |
Query the set up Filters in Jira. This table is not available in Jira Server.
The Sync App uses the Jira API to process some of the filters.
The Sync App processes other filters within the Sync App.
Retrieve all filters:
SELECT * FROM Filters
By specifying the Id of a certain filter:
SELECT * FROM Filters WHERE Id = 10001
| Name | Type | References | SupportedOperators | Platform | Description |
| Id [KEY] | Integer | = | Cloud | The Id of the filter. | |
| Name | String | Cloud | The name assigned to the filter. | ||
| JQLExpression | String | Cloud | The JQL by which the filter will search issues by. | ||
| Description | String | Cloud | The description given to the filter. | ||
| OwnerDisplayName | String | = | Cloud | The board Id the sprint originated from. | |
| Favourite | Boolean | Cloud | Whether you have set this filter as a favourite. | ||
| FavouritedCount | Integer | Cloud | How many users have this filter as a favourite. | ||
| SubscriptionsAggregate | String | Cloud | Collection of user subscriptions to the filter . | ||
| SharePermissionsAggregate | String | Cloud | Aggregate of share permissions to the filter. |
Query the available Groups in Jira.
The Sync App uses the Jira API to process some of the filters. The Sync App processes other filters within the Sync App.
SELECT * FROM GROUPS WHERE Name <> 'administrator' AND CONTAINS(Name, 'jira')
| Name | Type | References | SupportedOperators | Platform | Description |
| Id [KEY] | String | Common | The Id of the group. | ||
| Name | String | =,!=,CONTAINS | Common | The name of the group. | |
| Html | String | Common | The HTML of the group. | ||
| LabelsAggregate | String | Common | The labels of the group. |
Query the Affected Versions of an issue in Jira.
You can search IssueAffectedVersions by using SQL and JQL (Jira Query Language).
The Sync App uses the Jira API to process some of the filters.
The Sync App processes other filters within the Sync App.
The available columns for IssueAffectedVersions are IssueId, IssueKey, and JQL. For example:
SELECT * FROM IssueAffectedVersions WHERE IssueId > 10022 AND IssueId < 10090
| Name | Type | References | SupportedOperators | Platform | Description |
| VersionId [KEY] | String | =,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN | Common | The Id of the affected version. | |
| IssueId [KEY] | Integer |
Issues.Id | =,<>,>,>=,<,<=,IN,NOT IN | Common | The issue Id of the affected version. |
| IssueKey | String |
Issues.Key | =,<>,>,>=,<,<=,IN,NOT IN | Common | The issue key of the affected version. |
| IssueCreatedDate | Datetime | =,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN | Common | The date the affected version's issue was created. | |
| IssueUpdatedDate | Datetime | =,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN | Common | The date the affected version's issue was last updated. | |
| Name | String | Common | The name of the affected version. | ||
| Archived | Boolean | Common | A boolean indicating if the affected is archived. | ||
| Released | Boolean | Common | A boolean indicating if the affected is released. | ||
| ReleaseDate | Date | Common | The release date. |
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 | |
| JQL | String | JQL (Jira Query Language) allows you to build structured queries. |
Query the available IssueChangelogs in Jira.
You can search IssueChangelogs by using SQL and JQL (Jira Query Language).
The Sync App uses the Jira API to process some of the filters.
The Sync App processes other filters within the Sync App.
The available columns for server-side filtering for IssueChangelogs are IssueId, IssueKey, ProjectId, ProjectKey, ProjectName, and JQL. For example:
SELECT * FROM IssueChangelogs WHERE IssueId<>10022 SELECT * FROM IssueChangelogs WHERE ProjectKey = 'PRJ'
| Name | Type | References | SupportedOperators | Platform | Description |
| HistoryId | Integer | Common | The Id of the change. | ||
| IssueId | Integer |
Issues.Id | =,<>,>,>=,<,<=,IN,NOT IN | Common | The Id of the issue. |
| IssueKey | String |
Issues.Key | =,<>,>,>=,<,<=,IN,NOT IN | Common | The key of the issue. |
| Created | Datetime | Common | The creation date of the change. | ||
| IssueCreatedDate | Datetime | =,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN | Common | The date the change's issue was created. | |
| IssueUpdatedDate | Datetime | =,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN | Common | The date the change's issue was last updated. | |
| ItemField | String | Common | The item field of the change. | ||
| ItemFieldType | String | Common | The item field type of the change. | ||
| ItemFrom | String | Common | The item the issue changed from. | ||
| ItemFromString | String | Common | The description of the item the issue changed from. | ||
| ItemTo | String | Common | The item the issue changed to. | ||
| ItemToString | String | Common | The description of the item the issue changed to. | ||
| AuthorDisplayName | String | Common | The display name of the author who has updated the change. | ||
| AuthorAccountId | String | Cloud | The author name of the change. | ||
| AuthorName | String | Cloud | The author name of the change. | ||
| AuthorAccountKey | String | Server | The key of the author of the change. | ||
| AuthorAccountName | String | Server | The name of the author who has updated the change. | ||
| ProjectId | Integer |
Projects.Id | =,!=,IS,IS NOT,IN,NOT IN | Common | The project Id of the issue. |
| ProjectKey | String |
Projects.Key | =,!=,IS,IS NOT,IN,NOT IN | Common | The project key of the issue. |
| ProjectName | String | =,!=,IS,IS NOT,IN,NOT IN | Common | The project name of the issue. |
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 | |
| JQL | String | JQL (Jira Query Language) allows you to build structured queries. |
Query the available IssueComponents in Jira.
You can search IssueComponents by using SQL and JQL (Jira Query Language).
The Sync App uses the Jira API to process some of the filters.
The Sync App processes other filters within the Sync App.
The available columns for IssueComponents are IssueId, IssueKey, and JQL. For example:
SELECT * FROM IssueComponents WHERE IssueId > 10022 AND IssueId < 10090
| Name | Type | References | SupportedOperators | Platform | Description |
| Id [KEY] | String | =,<>,IS,IS NOT,IN,NOT IN | Common | The Id of the component. | |
| Name | String | Common | The name of the component. | ||
| IssueId [KEY] | Integer |
Issues.Id | =,<>,>,>=,<,<=,IN,NOT IN | Common | The issue Id of the component. |
| IssueKey | String |
Issues.Key | =,<>,>,>=,<,<=,IN,NOT IN | Common | The issue key of the component. |
| IssueCreatedDate | Datetime | =,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN | Common | The date the component's issue was created. | |
| IssueUpdatedDate | Datetime | =,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN | Common | The date the component's issue was last updated. | |
| Description | String | Common | The description of the component. |
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 | |
| JQL | String | JQL (Jira Query Language) allows you to build structured queries. |
Query Issue Custom Field Options in Jira. This resource is not available in Jira Server or when using OAuth authentication. Specifying at least CUSTOMFIELDID, or ID is required.
| Name | Type | References | SupportedOperators | Platform | Description |
| Id [KEY] | Long | = | The ID of the custom field option. | ||
| CustomFieldId | Long |
IssueCustomFields.CustomId | = | The ID of the custom field. | |
| Name | String | The name of the custom field option. |
Query the available Custom Fields in Jira.
| Name | Type | References | SupportedOperators | Platform | Description |
| Id [KEY] | String | Common | The Id of the custom field. | ||
| Key | String | Common | The key of the custom field. | ||
| CustomId | Long | The custom ID of the field. | |||
| Name | String | Common | The name of the custom field. | ||
| CDataName | String | Common | The name of the custom field on the Issues table provided by CData, when IncludeCustomFields=true | ||
| Orderable | Boolean | Common | A boolean indicating if the field is orderable. | ||
| Navigable | Boolean | Common | A boolean indicating if the field is navigable. | ||
| Searchable | Boolean | Common | A boolean indicating if the field is searchable. |
Query the available IssueFixVersions in Jira.
You can search IssueFixVersions by using SQL and JQL (Jira Query Language).
The Sync App uses the Jira API to process some of the filters.
The Sync App processes other filters within the Sync App.
The available columns for IssueFixVersions are IssueId, IssueKey, and JQL. For example:
SELECT * FROM IssueFixVersions WHERE IssueId > 10022 AND IssueId < 10090
| Name | Type | References | SupportedOperators | Platform | Description |
| VersionId [KEY] | String | =,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN | Common | The Id of the fix version. | |
| IssueId [KEY] | Integer |
Issues.Id | =,<>,>,>=,<,<=,IN,NOT IN | Common | The issue Id of the fix version. |
| IssueKey | String |
Issues.Key | =,<>,>,>=,<,<=,IN,NOT IN | Common | The issue key of the fix version. |
| IssueCreatedDate | Datetime | =,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN | Common | The date the fix version's issue was created. | |
| IssueUpdatedDate | Datetime | =,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN | Common | The date the fix version's issue was last updated. | |
| Name | String | Common | The name of the fix version. | ||
| Archived | Boolean | Common | A boolean indicating if the fix is archived. | ||
| Released | Boolean | Common | A boolean indicating if the fix is released. | ||
| ReleaseDate | Datetime | Common | The release date. |
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 | |
| JQL | String | JQL (Jira Query Language) allows you to build structured queries. |
Query the available IssueLinks in Jira.
You can search IssueLinks by using SQL and JQL (Jira Query Language).
The Sync App uses the Jira API to process some of the filters.
The Sync App processes other filters within the Sync App.
The available columns for IssueLinks are IssueId, IssueKey, and JQL. For example:
SELECT * FROM IssueLinks WHERE IssueId > 10022 AND IssueId < 10090
| Name | Type | References | SupportedOperators | Platform | Description |
| Id [KEY] | String | Common | The Id of the link. | ||
| InwardIssueId | Integer |
Issues.Id | Common | The Id of the issue targeting this issue in the link. | |
| InwardIssueKey | String |
Issues.Key | Common | The Key of the issue targeting this issue in the link. | |
| OutWardIssueId | Integer |
Issues.Id | Common | The Id of the issue this link is targeting. | |
| OutwardIssueKey | String |
Issues.Key | Common | The Key of the issue this link is targeting. | |
| IssueId [KEY] | Integer |
Issues.Id | =,<>,>,>=,<,<=,IN,NOT IN | Common | The link's original issue Id. |
| IssueKey | String |
Issues.Key | =,<>,>,>=,<,<=,IN,NOT IN | Common | The link's original issue Key. |
| IssueCreatedDate | Datetime | =,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN | Common | The date the link's issue was created. | |
| IssueUpdatedDate | Datetime | =,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN | Common | The date the links's issue was last updated. | |
| TypeId | Id | Common | The Id of the link type. | ||
| TypeName | String | Common | The Name of the link type. |
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 | |
| JQL | String | JQL (Jira Query Language) allows you to build structured queries. |
Lists all the issue link types.
The Sync App will use the Jira APIs to process filters that refer to the Id column.
The Sync App will process the rest of the filter client side within itself. For example, the following queries are processed server-side:
SELECT * FROM IssueLinkTypes WHERE Id = '10003'
SELECT * FROM IssueLinkTypes WHERE Id IN ('10002', '10000')
| Name | Type | References | SupportedOperators | Platform | Description |
| Id [KEY] | String | =,IN | Common | Unique identifier of the issue link type. | |
| Name | String | Common | The name of the issue link type. | ||
| Inward | String | Common | The description of the issue link type inward link. | ||
| Outward | String | Common | The description of the issue link type outward link. |
Query the list of all issue priorities.
The Sync App uses the Jira API to process some of the filters.
The Sync App processes other filters within the Sync App.
For example, the following queries are processed server side:
SELECT * FROM IssuePriorities WHERE Id = 1
| Name | Type | References | SupportedOperators | Platform | Description |
| Id [KEY] | String | = | Common | The ID of the issue priority. | |
| Name | String | Common | The name of the issue priority. | ||
| Description | String | Common | The description of the issue priority. | ||
| URL | String | Common | The URL of the issue priority. | ||
| StatusColor | String | Common | The color used to indicate the issue priority. | ||
| IconUrl | String | Common | The URL of the icon for the issue priority. |
Query the list of all issue resolution values.
The Sync App uses the Jira API to process some of the filters.
The Sync App processes other filters within the Sync App.
For example, the following queries are processed server side:
SELECT * FROM IssueResolutions WHERE Id = 10000
| Name | Type | References | SupportedOperators | Platform | Description |
| Id [KEY] | String | = | Common | The ID of the issue resolution. | |
| Name | String | Common | The name of the issue resolution. | ||
| Description | String | Common | The description of the issue resolution. | ||
| URL | String | Common | The URL of the issue resolution. |
Query the available IssueSubtasks in Jira.
You can search for IssueSubtasks by using SQL and JQL (Jira Query Language).
The Sync App uses the Jira API to process some of the filters.
The Sync App processes other filters within the Sync App.
The available columns for IssueSubtasks are IssueId, IssueKey, and JQL. For example:
SELECT * FROM IssueSubtasks WHERE IssueId = 10009 AND JQL = 'creator = john'
| Name | Type | References | SupportedOperators | Platform | Description |
| SubtaskId [KEY] | String | Common | The Id of the subtask. | ||
| SubtaskKey | String | Common | The key of the subtask. | ||
| IssueId | Integer |
Issues.Id | =,<>,>,>=,<,<=,IN,NOT IN | Common | The Id of the parent issue. |
| IssueKey | String |
Issues.Key | =,<>,>,>=,<,<=,IN,NOT IN | Common | The key of the parent issue. |
| IssueCreatedDate | Datetime | =,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN | Common | The date the subtask's issue was created. | |
| IssueUpdatedDate | Datetime | =,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN | Common | The date the subtask's issue was last updated. | |
| Summary | String | Common | The summary of the subtask. | ||
| TypeId | String |
IssueTypes.Id | Common | The issue type Id of the subtask. | |
| TypeName | String |
IssueTypes.Name | Common | The issue type name of the subtask. | |
| TypeDescription | String |
IssueTypes.Description | Common | The issue type description of the subtask. | |
| TypeSubtask | Boolean |
IssueTypes.Subtask | Common | A boolean indicating if the issue is a subtask. | |
| PriorityId | String | Common | The priority Id of the subtask. | ||
| PriorityName | String | Common | The priority name of the subtask. | ||
| StatusId | String | Common | The status Id of the subtask. |
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 | |
| JQL | String | JQL (Jira Query Language) allows you to build structured queries. |
Query the available IssueTransitions in Jira.
You can search for IssueTransitions by using SQL and JQL (Jira Query Language).
The Sync App uses the Jira API to process some of the filters.
The Sync App processes other filters within the Sync App.
The available columns for IssueTransitions are IssueId, IssueKey, and JQL. For example:
SELECT * FROM IssueTransitions WHERE JQL = 'id=10022'
| Name | Type | References | SupportedOperators | Platform | Description |
| Id [KEY] | String | Common | The Id of the transition. | ||
| IssueId [KEY] | Integer |
Issues.Id | =,<>,>,>=,<,<=,IN,NOT IN | Common | The Id of the issue. |
| IssueKey | String |
Issues.Key | =,<>,>,>=,<,<=,IN,NOT IN | Common | The key of the issue. |
| IssueCreatedDate | Datetime | =,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN | Common | The date the transition's issue was created. | |
| IssueUpdatedDate | Datetime | =,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN | Common | The date the transition's issue was last updated. | |
| HasScreen | String | Common | Whether the transition has a screen. | ||
| Name | String | Common | The name of the transition. | ||
| ToDescription | String | Common | The description of the status the issue is transitioning to. | ||
| ToId | String | Common | The Id of the status the issue is transitioning to. | ||
| ToName | String | Common | The name of the status the issue is transitioning to. | ||
| StatusCategoryId | String | Common | The status category Id of the transition. | ||
| StatusCategoryKey | String | Common | The status category key of the transition. | ||
| StatusCategoryName | String | Common | The status category name of the transition. |
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 | |
| JQL | String | JQL (Jira Query Language) allows you to build structured queries. |
Query the available Permissions of the current user in Jira.
The Sync App processes all filters specified on this table client-side within the Sync App.
SELECT * FROM MyPermissions
| Name | Type | References | SupportedOperators | Platform | Description |
| Id [KEY] | Integer | Common | The Id of the permission. | ||
| Key [KEY] | String | Common | The key of the permission. | ||
| Name | String | Common | The name of the permission. | ||
| Type | String | Common | The type of the permission. | ||
| Description | String | Common | The description of the permission. | ||
| HavePermission | Boolean | Common | Boolean indicating if the permission is set or not. | ||
| DeprecatedKey | Boolean | Common | Boolean indicating if the permission is deprecated. | ||
| Permissions | String | = | Common | Permissions as comma seperated values. |
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 | |
| ProjectKey | String | The key of the project associated with the permission. | |
| ProjectId | String | The Id of the project associated with the permission. | |
| IssueKey | String | The key of the issue associated with the permission. | |
| IssueId | String | The Id of the issue associated with the permission. |
Query the available Permissions in Jira.
| Name | Type | References | SupportedOperators | Platform | Description |
| Key [KEY] | String | Common | The key of the permission. | ||
| Name | String | Common | The name of the permission. | ||
| Type | String | Common | The type of the permission. | ||
| Description | String | Common | The description of the permission. |
Query all the project categories.
| Name | Type | References | SupportedOperators | Platform | Description |
| Id [KEY] | Integer | = | Common | Unique identifier of the project category. | |
| Name | String | Common | Name of the project category. | ||
| Description | String | Common | Description of the project category. | ||
| Link | String | Common | Link to this project category. |
Query roles for all projects in Jira.
The Sync App will use the Jira API to process filters that refer to ProjectId and ProjectKey while the rest of the filter is executed client side within the Sync App. The Jira APIs support filters using the equals (=) or IN operator with ProjectId and ProjectKey.
For example, the following queries are processed server side:
Retrieve all roles for each project:
SELECT * FROM ProjectRoles
Retrieve all roles for a specific project:
SELECT * FROM ProjectRoles WHERE ProjectId = '10000'
| Name | Type | References | SupportedOperators | Platform | Description |
| RoleId [KEY] | Long |
Roles.Id | Common | The Id of the role. | |
| RoleName | String |
Roles.Name | Common | The name of the role. | |
| ProjectId [KEY] | Integer |
Projects.Id | =,IN | Common | The Id of the project associated with the role. |
| ProjectKey [KEY] | String |
Projects.Key | =,IN | Common | The key of the project associated with the role. |
Query the available Issue Types of projects.
| Name | Type | References | SupportedOperators | Platform | Description |
| Id [KEY] | String | The Id of the issue type. | |||
| ProjectId [KEY] | Int |
Projects.Id | The Id of the project. | ||
| ProjectName | String |
Projects.Name | The name of the project | ||
| Name | String | The name of the issue type. | |||
| Description | String | The description of the issue type. | |||
| Subtask | Boolean | The subtask of the issue type. Not applicable for update. | |||
| IconUrl | String | The icon URL of the issue type. | |||
| AvatarId | String | The id of the avatar |
Query the list of all issue priorities.
The Sync App uses the Jira API to process some of the filters.
The Sync App processes other filters within the Sync App.
For example, the following queries are processed server side:
SELECT * FROM ProjectTypes WHERE Key = 'software'
| Name | Type | References | SupportedOperators | Platform | Description |
| Key [KEY] | String | = | Common | The key of the project type. | |
| DescriptionKey | String | Common | The key of the project type's description. | ||
| Icon | String | Common | The icon of the project type. | ||
| Color | String | Common | The color of the project type. |
Query all the created security levels.
| Name | Type | References | SupportedOperators | Platform | Description |
| Id [KEY] | Integer | Common | The ID of the issue level security item. | ||
| SecuritySchemeId | Long | =,IN | Common | Link to this security level. | |
| Name | String | Common | The name of the issue level security item. | ||
| Description | String | Common | The description of the issue level security item. |
Query all the created security schemes.
| Name | Type | References | SupportedOperators | Platform | Description |
| Id [KEY] | Long | = | Common | The ID of the issue security scheme. | |
| Name | String | Common | The name of the issue security scheme. | ||
| Description | String | Common | The description of the issue security scheme. | ||
| DefaultSecurityLevelId | Long | Common | The ID of the default security level. |
Query the agile Sprint Issues in Jira.
The Jira APIs support the IN operator in filters on SprintId; the Sync App executes the rest of the filter client side within itself. For example, following query's filter is processed server side:
SELECT * FROM SprintIssues WHERE SprintId = '1'
Using the IN operator:
SELECT * FROM SprintIssues WHERE SprintId IN (70, 215)
Note that 'kanban' type boards do not support sprints. You can retrieve all sprint issues faster if you use a filter like the following:
SELECT * FROM SprintIssues WHERE SprintId IN (SELECT Id FROM BoardSprints WHERE BoardId IN (SELECT Id FROM Boards WHERE Type != 'kanban'))
| Name | Type | References | SupportedOperators | Platform | Description |
| Id [KEY] | Integer |
Issues.Id | Common | The Id of the issue. | |
| Key | String |
Issues.Key | = | Common | The key of the issue. |
| SprintId [KEY] | Integer |
Sprints.Id | =,IN | Common | The sprint the issue is assigned to. |
| IssueTypeId | String |
IssueTypes.Id | =,!=,IS,IS NOT,IN,NOT IN | Common | The issue type Id. |
| IssueTypeName | String | =,!=,IS,IS NOT,IN,NOT IN | Common | The issue type name. | |
| StatusId | Integer |
Statuses.Id | =,!=,IS,IS NOT,IN,NOT IN | Common | The issue status Id. |
| StatusName | String | =,!=,IS,IS NOT,IN,NOT IN | Common | The issue status name. | |
| ProjectId | Integer |
Projects.Id | =,!=,IS,IS NOT,IN,NOT IN | Common | The project Id of the issue. |
| ProjectKey | String |
Projects.Key | =,!=,IS,IS NOT,IN,NOT IN | Common | The project key of the issue. |
| ProjectName | String | =,!=,IS,IS NOT,IN,NOT IN | Common | The project name of the issue. | |
| ClosedSprintsAggregate | String | Common | The issue's previous sprints that have been closed. | ||
| Created | Datetime | =,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN | Common | The creation date of the issue. | |
| Updated | Datetime | =,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN | Common | The updated date of the issue. |
Query the possible Statuses in Jira.
The Sync App will use the Jira APIs to process filters that refer to the Id or Name columns. Searches on the Name column are case sensitive.
The Sync App will process the rest of the filter client side within itself. For example, the following queries are processed server-side:
SELECT * FROM Statuses WHERE Id = '10003' SELECT * FROM Statuses WHERE Name = 'Closed'
| Name | Type | References | SupportedOperators | Platform | Description |
| Id [KEY] | Integer | = | Common | The Id of the status. | |
| Name | String | = | Common | The name of the status. | |
| Description | String | Common | The description of the status. | ||
| CategoryId | Integer | Common | The Category Id of the status. | ||
| CategoryKey | String | Common | The Category Key of the status. |
Query the list of all time tracking providers.
SELECT * FROM TimeTrackingProviders
| Name | Type | References | SupportedOperators | Platform | Description |
| Key [KEY] | String | = | Cloud | The key for the time tracking provider. For example, Jira. | |
| Name | String | Cloud | The name of the time tracking provider. For example, Jira provided time tracking. | ||
| URL | String | Cloud | The URL of the configuration page for the time tracking provider app. For example, /example/config/url. This property is only returned if the adminPageKey property is set in the module descriptor of the time tracking provider app. |
Query the available Votes in Jira.
The Sync App will execute filters other than the following client-side within itself.
For example, the following queries are processed server side:
SELECT * FROM Votes WHERE IssueId = 10100 SELECT * FROM Votes WHERE IssueKey = 'MKY-1'
| Name | Type | References | SupportedOperators | Platform | Description |
| Key [KEY] | String | Common | The key of the vote. | ||
| Name | String | Common | The name of the vote. | ||
| DisplayName | String | Common | The display name of the vote. | ||
| Active | Boolean | Common | Boolean indicating if the vote is active. | ||
| AccountId | String | Common | The account ID of the user, which uniquely identifies the user across all Atlassian products. For example, 5b10ac8d82e05b22cc7d4ef5. | ||
| IssueId | String | = | Common | The issue Id of the vote. | |
| IssueKey | String | = | Common | The issue key of the vote. |
Query the available Watchers in Jira.
The Sync App will use the Jira APIs to process filters based on IssueId or IssueKey. One of these is required to retrieve results. IssueId and IssueKey can be used only with the equal (=) operator.
The Sync App will execute other filters client-side within itself.
For example, the following query is processed server side:
SELECT * FROM Watchers WHERE IssueId = '10100' SELECT * FROM Watchers WHERE IssueKey = 'PRJ-1'
| Name | Type | References | SupportedOperators | Platform | Description |
| DisplayName | String | Common | The displayed name of the watcher. | ||
| EmailAddress | String | Common | The email address of the watcher. | ||
| AccountId [KEY] | String | Cloud | The accountId of the watcher. | ||
| Key [KEY] | String | Server | The key of the watcher. | ||
| Name | String | Server | The name of the watcher. | ||
| IssueId | String | = | Common | The issue Id of the watcher. | |
| IssueKey | String | = | Common | The issue key of the watcher. |
Query the list of all status categories.
The Sync App uses the Jira API to process some of the filters.
The Sync App processes other filters within the Sync App.
For example, the following queries are processed server side:
SELECT * FROM WorkflowStatusCategories WHERE Id = 1 SELECT * FROM WorkflowStatusCategories WHERE Key = 'undefined'
| Name | Type | References | SupportedOperators | Platform | Description |
| Id [KEY] | Long | = | Common | The ID of the status category. | |
| Key | String | = | Common | The key of the status category. | |
| Name | String | Common | The name of the status category. | ||
| ColorName | String | Common | The name of the color used to represent the status category. | ||
| URL | String | Common | The URL of the status category. |
Query the list of all statuses associated with workflows.
The Sync App uses the Jira API to process some of the filters.
The Sync App processes other filters within the Sync App.
For example, the following queries are processed server side:
SELECT * FROM WorkflowStatuses WHERE Id = 1 SELECT * FROM WorkflowStatuses WHERE Name = 'Open'
| Name | Type | References | SupportedOperators | Platform | Description |
| Id [KEY] | String | = | Common | The ID of the status. | |
| StatusCategoryId | Long | Common | The ID of the status category. | ||
| Name | String | = | Common | The name of the status. | |
| Description | String | Common | The description of the status. | ||
| URL | String | Common | The description of the status. | ||
| IconUrl | String | Common | The URL of the icon used to represent the status. |
Queries the deleted worklogs in your Jira account.
| Name | Type | References | SupportedOperators | Platform | Description |
| Id | String | The Id of the audit record. | |||
| Updated | Datetime | >=,> | The date on when the change was made. | ||
| Properties | String | The properties of the deleted Worklog. |
The Sync App maps types from the data source to the corresponding data type available in the schema. The table below documents these mappings.
| Jira | CData Schema |
| Assets | string |
| Checkbox | string |
| Date of First Response | datetime |
| Date Picker | date |
| Date Time Picker | datetime |
| Days since last comment | string |
| Domain of Assignee | string |
| Domain of Reporter | string |
| Group Picker (multiple groups) | string |
| Group Picker (single group) | string |
| Labels | string |
| Last commented by a User Flag | string |
| Last public comment date | string |
| Message Custom Field (for edit) | string |
| Message Custom Field (for view) | string |
| Number Field | decimal |
| Number of attachments | string |
| Number of comments | string |
| Parent Link | string |
| Participants of an issue | string |
| Project Picker (single project) | string |
| Radio Buttons | string |
| Select List (cascading) | string |
| Select List (multiple choices) | string |
| Select List (single choice) | string |
| Team | string |
| Text Field (multi-line) | string |
| Text Field (read only) | string |
| Text Field (single line) | string |
| Time in Status | string |
| URL Field | string |
| User Picker (multiple users) | string |
| User Picker (single user) | string |
| User Property Field | string |
| Username of last updater or commenter | string |
| Version Picker (multiple versions) | string |
| Version Picker (single version) | string |
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.
| Property | Description |
| AuthScheme | The type of authentication to use when connecting to Jira. |
| URL | The URL to your Jira endpoint. |
| User | The Jira user account used to authenticate. |
| Password | The password used to authenticate the user. |
| APIToken | APIToken of the currently authenticated user. |
| PAT | Personal Access Token of the currently authenticated user. |
| Property | Description |
| SSOLoginURL | The identity provider's login URL. |
| SSOProperties | Additional properties required to connect to the identity provider in a semicolon-separated list. |
| SSOExchangeUrl | The URL used for consuming the SAML response and exchanging it for service specific credentials. |
| SSOAppName | App Name used with SSO for IdPs that require it. |
| SSOAppPassword | App Password used with SSO for IdPs that require it. |
| Property | Description |
| OAuthVersion | The version of OAuth being used. |
| OAuthClientId | The client Id assigned when you register your application with an OAuth authorization server. |
| OAuthClientSecret | The client secret assigned when you register your application with an OAuth authorization server. |
| Scope | This determines the scopes that the OAuth application requests from Jira. The default scopes are: read:jira-work manage:jira-project manage:jira-configuration read:jira-user write:jira-work manage:jira-data-provider read:board-scope:jira-software write:board-scope:jira-software read:board-scope.admin:jira-software read:epic:jira-software read:sprint:jira-software write:sprint:jira-software delete:sprint:jira-software read:project:jira read:issue-details:jira read:jql:jira offline_access. |
| AuthToken | The authentication token used to request and obtain the OAuth Access Token. |
| AuthKey | The authentication secret used to request and obtain the OAuth Access Token. |
| CertificateStoreType | The type of certificate store used with Jira Private Application authentication. |
| CertificateStore | The certificate store used for Jira authentication. |
| CertificateSubject | The subject of the certificate used with Jira Private Application authentication. |
| CertificateStorePassword | The password of the certificate store used with Jira authentication. |
| Property | Description |
| SSLClientCert | The TLS/SSL client certificate store for SSL Client Authentication (2-way SSL). |
| SSLClientCertType | The type of key store containing the TLS/SSL client certificate. |
| SSLClientCertPassword | The password for the TLS/SSL client certificate. |
| SSLClientCertSubject | The subject of the TLS/SSL client certificate. |
| SSLServerCert | The certificate to be accepted from the server when connecting using TLS/SSL. |
| Property | Description |
| FirewallType | The protocol used by a proxy-based firewall. |
| FirewallServer | The name or IP address of a proxy-based firewall. |
| FirewallPort | The TCP port for a proxy-based firewall. |
| FirewallUser | The user name to use to authenticate with a proxy-based firewall. |
| FirewallPassword | A password used to authenticate to a proxy-based firewall. |
| Property | Description |
| ProxyAutoDetect | This indicates whether to use the system proxy settings or not. |
| ProxyServer | The hostname or IP address of a proxy to route HTTP traffic through. |
| ProxyPort | The TCP port the ProxyServer proxy is running on. |
| ProxyAuthScheme | The authentication type to use to authenticate to the ProxyServer proxy. |
| ProxyUser | A user name to be used to authenticate to the ProxyServer proxy. |
| ProxyPassword | A password to be used to authenticate to the ProxyServer proxy. |
| ProxySSLType | The SSL type to use when connecting to the ProxyServer proxy. |
| ProxyExceptions | A semicolon separated list of destination hostnames or IPs that are exempt from connecting through the ProxyServer . |
| Property | Description |
| LogModules | Core modules to be included in the log file. |
| Property | Description |
| Location | A path to the directory that contains the schema files defining tables, views, and stored procedures. |
| BrowsableSchemas | This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC. |
| Tables | This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC. |
| Views | Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC. |
| Property | Description |
| 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. |
| MaxThreads | Specifies the number of concurrent requests. |
| Other | These hidden properties are used only in specific use cases. |
| Pagesize | The maximum number of results to return per page from Jira. |
| 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. |
| Timezone | Specify the timezone of the Jira instance in order to use the datetime filters accordingly and retrieve the results according to your timezone. An example of a timezone would be America/New_York. |
| UseDefaultOrderBy | Indicates if a default order by should be applied if none is specified in the query. |
| UseDisplayNames | Boolean determining if the display names for custom fields should be used instead of the API names. |
| UserDefinedViews | A filepath pointing to the JSON configuration file containing your custom views. |
| UseSimpleNames | Boolean determining if simple names should be used for tables and columns. |
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 Jira. |
| URL | The URL to your Jira endpoint. |
| User | The Jira user account used to authenticate. |
| Password | The password used to authenticate the user. |
| APIToken | APIToken of the currently authenticated user. |
| PAT | Personal Access Token of the currently authenticated user. |
The type of authentication to use when connecting to Jira.
The URL to your Jira endpoint.
The URL to your Jira endpoint; for example, https://yoursitename.atlassian.net.
The Jira user account used to authenticate.
Together with Password, this field is used to authenticate against the Jira server.
The password used to authenticate the user.
The User and Password are together used to authenticate with the server.
APIToken of the currently authenticated user.
APIToken of the currently authenticated user.
Personal Access Token of the currently authenticated user.
Personal Access Token of the currently authenticated user.
This section provides a complete list of the SSO properties you can configure in the connection string for this provider.
| Property | Description |
| SSOLoginURL | The identity provider's login URL. |
| SSOProperties | Additional properties required to connect to the identity provider in a semicolon-separated list. |
| SSOExchangeUrl | The URL used for consuming the SAML response and exchanging it for service specific credentials. |
| SSOAppName | App Name used with SSO for IdPs that require it. |
| SSOAppPassword | App Password used with SSO for IdPs that require it. |
The identity provider's login URL.
The identity provider's login URL.
Additional properties required to connect to the identity provider in a semicolon-separated list.
Additional properties required to connect to the identity provider in a semicolon-separated list. is used in conjunction with the SSOLoginURL.
SSO configuration is discussed further in .
The URL used for consuming the SAML response and exchanging it for service specific credentials.
The CData Sync App will use the URL specified here to consume a SAML response and exchange it for service specific credentials. The retrieved credentials are the final piece during the SSO connection that are used to communicate with Jira.
App Name used with SSO for IdPs that require it.
Along with SSOAppPassword, may be be specified to identify and authenticate to your app configured in the SSO IdP. Currently only Crowd supports it.
App Password used with SSO for IdPs that require it.
Along with SSOAppName, may be be specified to identify and authenticate to your app configured in the SSO IdP. Currently only Crowd supports it.
This section provides a complete list of the OAuth properties you can configure in the connection string for this provider.
| Property | Description |
| OAuthVersion | The version of OAuth being used. |
| OAuthClientId | The client Id assigned when you register your application with an OAuth authorization server. |
| OAuthClientSecret | The client secret assigned when you register your application with an OAuth authorization server. |
| Scope | This determines the scopes that the OAuth application requests from Jira. The default scopes are: read:jira-work manage:jira-project manage:jira-configuration read:jira-user write:jira-work manage:jira-data-provider read:board-scope:jira-software write:board-scope:jira-software read:board-scope.admin:jira-software read:epic:jira-software read:sprint:jira-software write:sprint:jira-software delete:sprint:jira-software read:project:jira read:issue-details:jira read:jql:jira offline_access. |
| AuthToken | The authentication token used to request and obtain the OAuth Access Token. |
| AuthKey | The authentication secret used to request and obtain the OAuth Access Token. |
| CertificateStoreType | The type of certificate store used with Jira Private Application authentication. |
| CertificateStore | The certificate store used for Jira authentication. |
| CertificateSubject | The subject of the certificate used with Jira Private Application authentication. |
| CertificateStorePassword | The password of the certificate store used with Jira authentication. |
The version of OAuth being used.
The version of OAuth being used. The following options are available: 1.0,2.0
The client Id assigned when you register your application with an OAuth authorization server.
As part of registering an OAuth application, you will receive the OAuthClientId value, sometimes also called a consumer key, and a client secret, the OAuthClientSecret.
The client secret assigned when you register your application with an OAuth authorization server.
As part of registering an OAuth application, you will receive the OAuthClientId, also called a consumer key. You will also receive a client secret, also called a consumer secret. Set the client secret in the OAuthClientSecret property.
This determines the scopes that the OAuth application requests from Jira. The default scopes are: read:jira-work manage:jira-project manage:jira-configuration read:jira-user write:jira-work manage:jira-data-provider read:board-scope:jira-software write:board-scope:jira-software read:board-scope.admin:jira-software read:epic:jira-software read:sprint:jira-software write:sprint:jira-software delete:sprint:jira-software read:project:jira read:issue-details:jira read:jql:jira offline_access.
Specify scope to obtain the initial access and refresh token.
By default the Sync App will request that the user authorize all available scopes. If you want to override this, you can set this property to a space-separated list of OAuth scopes.
The authentication token used to request and obtain the OAuth Access Token.
This property is required only when performing headless authentication in OAuth 1.0. It can be obtained from the GetOAuthAuthorizationUrl stored procedure.
It can be supplied alongside the AuthKey in the GetOAuthAccessToken stored procedure to obtain the OAuthAccessToken.
The authentication secret used to request and obtain the OAuth Access Token.
This property is required only when performing headless authentication in OAuth 1.0. It can be obtained from the GetOAuthAuthorizationUrl stored procedure.
It can be supplied alongside the AuthToken in the GetOAuthAccessToken stored procedure to obtain the OAuthAccessToken.
The type of certificate store used with Jira Private Application authentication.
The name of the certificate store for the client certificate used in Jira private application authentication. Jira private application authentication uses SSL client authentication with digital certificates.
The CertificateStoreType property can take one of the following values:
| User - default | For Windows, this specifies that the certificate store is a certificate store owned by the current user. Note: This store type is not available in Java. |
| Machine | For Windows, this specifies that the certificate store is a machine store. Note: this store type is not available in Java. |
| PFXFile | The certificate store is the name of a PFX (PKCS12) file containing certificates. |
| PFXBlob | The certificate store is a string (base-64-encoded) representing a certificate store in PFX (PKCS12) format. |
| JKSFile | The certificate store is the name of a Java key store (JKS) file containing certificates. Note: this store type is only available in Java. |
| JKSBlob | The certificate store is a string (base-64-encoded) representing a certificate store in Java key store (JKS) format. Note: this store type is only available in Java. |
| PEMKeyFile | The certificate store is the name of a PEM-encoded file that contains a private key and an optional certificate. |
| PublicKeyFile | The certificate store is the name of a file that contains a PEM- or DER-encoded public key certificate. |
| PublicKeyBlob | The certificate store is a string (base-64-encoded) that contains a PEM- or DER-encoded public key certificate. |
| SSHPublicKeyBlob | The certificate store is a string (base-64-encoded) that contains an SSH-style public key. |
| P10BFile | The certificate store is the name of a PKCS10 file containing certificates. |
| SSHPublicKeyFile | The certificate store is the name of a file that contains an SSH-style public key. |
| PPKFile | The certificate store is the name of a file that contains a PPK (PuTTY Private Key). |
| XMLFile | The certificate store is the name of a file that contains a certificate in XML format. |
| XMLBlob | The certificate store is a string that contains a certificate in XML format. |
To specify the private certificate, you may need to set the following properties in addition to CertificateStoreType:
The certificate store used for Jira authentication.
The location of the certificate store for the client certificate used in Jira application authentication.
The subject of the certificate used with Jira Private Application authentication.
The subject of the client certificate used in Jira private application authentication.
Jira private application authentication uses SSL client authentication with digital certificates.
When loading a certificate the subject is used to locate the certificate in the store.
The certificate subject is a comma-separated list of distinguished name fields and values. For instance "CN=www.server.com, OU=test, C=US, [email protected]". Common fields and their meanings are displayed below.
| Field | Meaning |
| CN | Common Name. This is commonly a host name like www.server.com. |
| O | Organization |
| OU | Organizational Unit |
| L | Locality |
| S | State |
| C | Country |
| E | Email Address |
Note: If a field value contains a comma it must be quoted.
To specify the private certificate, you need to set CertificateSubject and the following additional properties:
The password of the certificate store used with Jira authentication.
If the certificate store is of a type that requires a password, this property is used to specify that password in order to open the certificate store.
This section provides a complete list of the SSL properties you can configure in the connection string for this provider.
| Property | Description |
| SSLClientCert | The TLS/SSL client certificate store for SSL Client Authentication (2-way SSL). |
| SSLClientCertType | The type of key store containing the TLS/SSL client certificate. |
| SSLClientCertPassword | The password for the TLS/SSL client certificate. |
| SSLClientCertSubject | The subject of the TLS/SSL client certificate. |
| SSLServerCert | The certificate to be accepted from the server when connecting using TLS/SSL. |
The TLS/SSL client certificate store for SSL Client Authentication (2-way SSL).
The name of the certificate store for the client certificate.
The SSLClientCertType field specifies the type of the certificate store specified by SSLClientCert. If the store is password protected, specify the password in SSLClientCertPassword.
SSLClientCert is used in conjunction with the SSLClientCertSubject field in order to specify client certificates. If SSLClientCert has a value, and SSLClientCertSubject is set, a search for a certificate is initiated. See SSLClientCertSubject for more information.
Designations of certificate stores are platform-dependent.
The following are designations of the most common User and Machine certificate stores in Windows:
| MY | A certificate store holding personal certificates with their associated private keys. |
| CA | Certifying authority certificates. |
| ROOT | Root certificates. |
| SPC | Software publisher certificates. |
In Java, the certificate store normally is a file containing certificates and optional private keys.
When the certificate store type is PFXFile, this property must be set to the name of the file. When the type is PFXBlob, the property must be set to the binary contents of a PFX file (for example, PKCS12 certificate store).
The type of key store containing the TLS/SSL client certificate.
This property can take one of the following values:
| USER - default | For Windows, this specifies that the certificate store is a certificate store owned by the current user. Note that this store type is not available in Java. |
| MACHINE | For Windows, this specifies that the certificate store is a machine store. Note that this store type is not available in Java. |
| PFXFILE | The certificate store is the name of a PFX (PKCS12) file containing certificates. |
| PFXBLOB | The certificate store is a string (base-64-encoded) representing a certificate store in PFX (PKCS12) format. |
| JKSFILE | The certificate store is the name of a Java key store (JKS) file containing certificates. Note that this store type is only available in Java. |
| JKSBLOB | The certificate store is a string (base-64-encoded) representing a certificate store in JKS format. Note that this store type is only available in Java. |
| PEMKEY_FILE | The certificate store is the name of a PEM-encoded file that contains a private key and an optional certificate. |
| PEMKEY_BLOB | The certificate store is a string (base64-encoded) that contains a private key and an optional certificate. |
| PUBLIC_KEY_FILE | The certificate store is the name of a file that contains a PEM- or DER-encoded public key certificate. |
| PUBLIC_KEY_BLOB | The certificate store is a string (base-64-encoded) that contains a PEM- or DER-encoded public key certificate. |
| SSHPUBLIC_KEY_FILE | The certificate store is the name of a file that contains an SSH-style public key. |
| SSHPUBLIC_KEY_BLOB | The certificate store is a string (base-64-encoded) that contains an SSH-style public key. |
| P7BFILE | The certificate store is the name of a PKCS7 file containing certificates. |
| PPKFILE | The certificate store is the name of a file that contains a PuTTY Private Key (PPK). |
| XMLFILE | The certificate store is the name of a file that contains a certificate in XML format. |
| XMLBLOB | The certificate store is a string that contains a certificate in XML format. |
The password for the TLS/SSL client certificate.
If the certificate store is of a type that requires a password, this property is used to specify that password to open the certificate store.
The subject of the TLS/SSL client certificate.
When loading a certificate the subject is used to locate the certificate in the store.
If an exact match is not found, the store is searched for subjects containing the value of the property. If a match is still not found, the property is set to an empty string, and no certificate is selected.
The special value "*" picks the first certificate in the certificate store.
The certificate subject is a comma separated list of distinguished name fields and values. For example, "CN=www.server.com, OU=test, C=US, [email protected]". The common fields and their meanings are shown below.
| Field | Meaning |
| CN | Common Name. This is commonly a host name like www.server.com. |
| O | Organization |
| OU | Organizational Unit |
| L | Locality |
| S | State |
| C | Country |
| E | Email Address |
If a field value contains a comma, it must be quoted.
The certificate to be accepted from the server when connecting using TLS/SSL.
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.
This section provides a complete list of the Firewall properties you can configure in the connection string for this provider.
| Property | Description |
| FirewallType | The protocol used by a proxy-based firewall. |
| FirewallServer | The name or IP address of a proxy-based firewall. |
| FirewallPort | The TCP port for a proxy-based firewall. |
| FirewallUser | The user name to use to authenticate with a proxy-based firewall. |
| FirewallPassword | A password used to authenticate to a proxy-based firewall. |
The protocol used by a proxy-based firewall.
This property specifies the protocol that the Sync App will use to tunnel traffic through the FirewallServer proxy. Note that by default, the Sync App connects to the system proxy; to disable this behavior and connect to one of the following proxy types, set ProxyAutoDetect to false.
| Type | Default Port | Description |
| TUNNEL | 80 | When this is set, the Sync App opens a connection to Jira and traffic flows back and forth through the proxy. |
| SOCKS4 | 1080 | When this is set, the Sync App sends data through the SOCKS 4 proxy specified by FirewallServer and FirewallPort and passes the FirewallUser value to the proxy, which determines if the connection request should be granted. |
| SOCKS5 | 1080 | When this is set, the Sync App sends data through the SOCKS 5 proxy specified by FirewallServer and FirewallPort. If your proxy requires authentication, set FirewallUser and FirewallPassword to credentials the proxy recognizes. |
To connect to HTTP proxies, use ProxyServer and ProxyPort. To authenticate to HTTP proxies, use ProxyAuthScheme, ProxyUser, and ProxyPassword.
The name or IP address of a proxy-based firewall.
This property specifies the IP address, DNS name, or host name of a proxy allowing traversal of a firewall. The protocol is specified by FirewallType: Use FirewallServer with this property to connect through SOCKS or do tunneling. Use ProxyServer to connect to an HTTP proxy.
Note that the Sync App uses the system proxy by default. To use a different proxy, set ProxyAutoDetect to false.
The TCP port for a proxy-based firewall.
This specifies the TCP port for a proxy allowing traversal of a firewall. Use FirewallServer to specify the name or IP address. Specify the protocol with FirewallType.
The user name to use to authenticate with a proxy-based firewall.
The FirewallUser and FirewallPassword properties are used to authenticate against the proxy specified in FirewallServer and FirewallPort, following the authentication method specified in FirewallType.
A password used to authenticate to a proxy-based firewall.
This property is passed to the proxy specified by FirewallServer and FirewallPort, following the authentication method specified by FirewallType.
This section provides a complete list of the Proxy properties you can configure in the connection string for this provider.
| Property | Description |
| ProxyAutoDetect | This indicates whether to use the system proxy settings or not. |
| ProxyServer | The hostname or IP address of a proxy to route HTTP traffic through. |
| ProxyPort | The TCP port the ProxyServer proxy is running on. |
| ProxyAuthScheme | The authentication type to use to authenticate to the ProxyServer proxy. |
| ProxyUser | A user name to be used to authenticate to the ProxyServer proxy. |
| ProxyPassword | A password to be used to authenticate to the ProxyServer proxy. |
| ProxySSLType | The SSL type to use when connecting to the ProxyServer proxy. |
| ProxyExceptions | A semicolon separated list of destination hostnames or IPs that are exempt from connecting through the ProxyServer . |
This indicates whether to use the system proxy settings or not.
This takes precedence over other proxy settings, so you'll need to set ProxyAutoDetect to FALSE in order use custom proxy settings.
To connect to an HTTP proxy, see ProxyServer. For other proxies, such as SOCKS or tunneling, see FirewallType.
The hostname or IP address of a proxy to route HTTP traffic through.
The hostname or IP address of a proxy to route HTTP traffic through. The Sync App can use the HTTP, Windows (NTLM), or Kerberos authentication types to authenticate to an HTTP proxy.
If you need to connect through a SOCKS proxy or tunnel the connection, see FirewallType.
By default, the Sync App uses the system proxy. If you need to use another proxy, set ProxyAutoDetect to false.
The TCP port the ProxyServer proxy is running on.
The port the HTTP proxy is running on that you want to redirect HTTP traffic through. Specify the HTTP proxy in ProxyServer. For other proxy types, see FirewallType.
The authentication type to use to authenticate to the ProxyServer proxy.
This value specifies the authentication type to use to authenticate to the HTTP proxy specified by ProxyServer and ProxyPort.
Note that the Sync App will use the system proxy settings by default, without further configuration needed; if you want to connect to another proxy, you will need to set ProxyAutoDetect to false, in addition to ProxyServer and ProxyPort. To authenticate, set ProxyAuthScheme and set ProxyUser and ProxyPassword, if needed.
The authentication type can be one of the following:
If you need to use another authentication type, such as SOCKS 5 authentication, see FirewallType.
A user name to be used to authenticate to the ProxyServer proxy.
The ProxyUser and ProxyPassword options are used to connect and authenticate against the HTTP proxy specified in ProxyServer.
You can select one of the available authentication types in ProxyAuthScheme. If you are using HTTP authentication, set this to the user name of a user recognized by the HTTP proxy. If you are using Windows or Kerberos authentication, set this property to a user name in one of the following formats:
user@domain domain\user
A password to be used to authenticate to the ProxyServer proxy.
This property is used to authenticate to an HTTP proxy server that supports NTLM (Windows), Kerberos, or HTTP authentication. To specify the HTTP proxy, you can set ProxyServer and ProxyPort. To specify the authentication type, set ProxyAuthScheme.
If you are using HTTP authentication, additionally set ProxyUser and ProxyPassword to HTTP proxy.
If you are using NTLM authentication, set ProxyUser and ProxyPassword to your Windows password. You may also need these to complete Kerberos authentication.
For SOCKS 5 authentication or tunneling, see FirewallType.
By default, the Sync App uses the system proxy. If you want to connect to another proxy, set ProxyAutoDetect to false.
The SSL type to use when connecting to the ProxyServer proxy.
This property determines when to use SSL for the connection to an HTTP proxy specified by ProxyServer. This value can be AUTO, ALWAYS, NEVER, or TUNNEL. The applicable values are the following:
| AUTO | Default setting. If the URL is an HTTPS URL, the Sync App will use the TUNNEL option. If the URL is an HTTP URL, the component will use the NEVER option. |
| ALWAYS | The connection is always SSL enabled. |
| NEVER | The connection is not SSL enabled. |
| TUNNEL | The connection is through a tunneling proxy. The proxy server opens a connection to the remote host and traffic flows back and forth through the proxy. |
A semicolon separated list of destination hostnames or IPs that are exempt from connecting through the ProxyServer .
The ProxyServer is used for all addresses, except for addresses defined in this property. Use semicolons to separate entries.
Note that the Sync App uses the system proxy settings by default, without further configuration needed; if you want to explicitly configure proxy exceptions for this connection, you need to set ProxyAutoDetect = false, and configure ProxyServer and ProxyPort. To authenticate, set ProxyAuthScheme and set ProxyUser and ProxyPassword, if needed.
This section provides a complete list of the Logging properties you can configure in the connection string for this provider.
| Property | Description |
| LogModules | Core modules to be included in the log file. |
Core modules to be included in the log file.
Only the modules specified (separated by ';') will be included in the log file. By default all modules are included.
See the Logging page for an overview.
This section provides a complete list of the Schema properties you can configure in the connection string for this provider.
| Property | Description |
| Location | A path to the directory that contains the schema files defining tables, views, and stored procedures. |
| BrowsableSchemas | This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC. |
| Tables | This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC. |
| Views | Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC. |
A path to the directory that contains the schema files defining tables, views, and stored procedures.
The path to a directory which contains the schema files for the Sync App (.rsd files for tables and views, .rsb files for stored procedures). The folder location can be a relative path from the location of the executable. The Location property is only needed if you want to customize definitions (for example, change a column name, ignore a column, and so on) or extend the data model with new tables, views, or stored procedures.
If left unspecified, the default location is "%APPDATA%\\CData\\JIRA Data Provider\\Schema" with %APPDATA% being set to the user's configuration directory:
| Platform | %APPDATA% |
| Windows | The value of the APPDATA environment variable |
| Linux | ~/.config |
This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.
Listing the schemas from databases can be expensive. Providing a list of schemas in the connection string improves the performance.
This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC.
Listing the tables from some databases can be expensive. Providing a list of tables in the connection string improves the performance of the Sync App.
This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.
Specify the tables you want in a comma-separated list. Each table should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Tables=TableA,[TableB/WithSlash],WithCatalog.WithSchema.`TableC With Space`.
Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.
Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC.
Listing the views from some databases can be expensive. Providing a list of views in the connection string improves the performance of the Sync App.
This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.
Specify the views you want in a comma-separated list. Each view should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Views=ViewA,[ViewB/WithSlash],WithCatalog.WithSchema.`ViewC With Space`.
Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.
This section provides a complete list of the Miscellaneous properties you can configure in the connection string for this provider.
| Property | Description |
| 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. |
| MaxThreads | Specifies the number of concurrent requests. |
| Other | These hidden properties are used only in specific use cases. |
| Pagesize | The maximum number of results to return per page from Jira. |
| 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. |
| Timezone | Specify the timezone of the Jira instance in order to use the datetime filters accordingly and retrieve the results according to your timezone. An example of a timezone would be America/New_York. |
| UseDefaultOrderBy | Indicates if a default order by should be applied if none is specified in the query. |
| UseDisplayNames | Boolean determining if the display names for custom fields should be used instead of the API names. |
| UserDefinedViews | A filepath pointing to the JSON configuration file containing your custom views. |
| UseSimpleNames | Boolean determining if simple names should be used for tables and columns. |
A boolean indicating if you would like to include custom fields in the column listing.
Setting this to true will cause custom fields to be included in the column listing, but may cause poor performance when listing metadata.
Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
Specifies the number of concurrent requests.
This property allows you to issue multiple requests simultaneously, thereby improving performance.
These hidden properties are used only in specific use cases.
The properties listed below are available for specific use cases. Normal driver use cases and functionality should not require these properties.
Specify multiple properties in a semicolon-separated list.
| DefaultColumnSize | Sets the default length of string fields when the data source does not provide column length in the metadata. The default value is 2000. |
| ConvertDateTimeToGMT | Determines whether to convert date-time values to GMT, instead of the local time of the machine. |
| RecordToFile=filename | Records the underlying socket data transfer to the specified file. |
The maximum number of results to return per page from Jira.
The Pagesize property affects the maximum number of results to return per page from Jira. Setting a higher value may result in better performance at the cost of additional memory allocated per page consumed.
This property indicates whether or not to include pseudo columns as columns to the table.
This setting is particularly helpful in Entity Framework, which does not allow you to set a value for a pseudo column unless it is a table column. The value of this connection setting is of the format "Table1=Column1, Table1=Column2, Table2=Column3". You can use the "*" character to include all tables and all columns; for example, "*=*".
The value in seconds until the timeout error is thrown, canceling the operation.
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 Sync App throws an exception.
Specify the timezone of the Jira instance in order to use the datetime filters accordingly and retrieve the results according to your timezone. An example of a timezone would be America/New_York.
Note: You can find the timezone in your Jira Cloud by navigating to Settings -> System -> General configuration -> Default user time zone
Indicates if a default order by should be applied if none is specified in the query.
By default, the CData Sync App applies an order by when none is specified. This ensures that duplicate records do not get output when returning data. However, this will have a significant impact on performance for very large tables. If your use case can handle the possibility of duplicate primary keys, it is recommended to turn this off if you would like to have better performance.
Boolean determining if the display names for custom fields should be used instead of the API names.
Boolean determining if the display names for custom fields should be used instead of the API names.
A filepath pointing to the JSON configuration file containing your custom views.
User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The Sync App automatically detects the views specified in this file.
You can also have multiple view definitions and control them using the UserDefinedViews connection property. When you use this property, only the specified views are seen by the Sync App.
This User Defined View configuration file is formatted as follows:
For example:
{
"MyView": {
"query": "SELECT * FROM Projects WHERE MyColumn = 'value'"
},
"MyView2": {
"query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
}
}
Use the UserDefinedViews connection property to specify the location of your JSON configuration file. For example:
"UserDefinedViews", C:\Users\yourusername\Desktop\tmp\UserDefinedViews.jsonNote that the specified path is not embedded in quotation marks.
Boolean determining if simple names should be used for tables and columns.
Jira tables and columns can use special characters in names that are normally not allowed in standard databases. UseSimpleNames makes the Sync App easier to use with traditional database tools.
Setting UseSimpleNames to true will simplify the names of tables and columns returned. It will enforce a naming scheme such that only alphanumeric characters and the underscore are valid for the displayed table and column names. Any nonalphanumeric characters will be converted to an underscore.