Adding a Connection to Jira

To add a connection to Jira:

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

For required properties, see the Settings tab.

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

Connecting to Jira

To connect set the URL to your Jira endpoint; for example, https://yoursitename.atlassian.net.

Accessing Custom Fields

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.

Authenticating to Jira

Jira supports authentication via either a Personal Access Token or OAuth 2.0.

Personal Access Token (PAT)

PATs are a secure way to authenticate to Jira, acting as a safe alternative to authenticating with a username and password. The advantage of authentication via PAT is that if the system is ever compromised, the administrator can revoke existing tokens immediately. This avoids the need to change the access password in all scripts and integrations.

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:

1. Select the profile picture at the top right of the screen. Jira displays a drop-down list.
2. Choose Settings >Profile.
3. At the left panel, select Personal Access Tokens
4. Click Create token
5. Give the new token a name.
6. If desired, set the token to automatically expire after a certain number of days. (Atlassian displays a reminder message five days before the token actually expires, which gives the user time to create a new token.)
7. Click Create.

The personal token displays. It cannot be viewed or retrieved after creation, so be sure to copy the token now and store it in a safe place.

Now set the following Jira Sync App properties:

• AuthScheme = PAT
• PAT = the value of the generated Personal Access Token.

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.

OAuth 2.0

You can leverage Jira's "three-legged" OAuth 2.0 support (3LO) to connect to data without providing your login credentials.

The following procedures assume that AuthScheme has been set to OAuth in all OAuth flows.

API Token

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.

Basic

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

LDAP

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

Crowd

Set the AuthScheme to Crowd, then configure these connection properties:

• User: The CROWD user account.
• Password: The password associated with the Crowd account.
• SSOLoginURL: The login URL associated with the Crowd account. You can find the IDP URL by navigating to your application > SSO > SSO information > Identity provider single sign-on URL.
• SSOAppName: The name of the application in which SSO is enabled.
• SSOAppPassword: The password of the application in which SSO is enabled.
• SSOExchangeUrl: The URL used used to exchange the SAML token for Jira cookies. This URL may have the following formats:
  • https://<authority of Jira instance>/plugins/servlet/samlconsumer
  • https://<authority of Jira instance>/plugins/servlet/samlsso

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;

Okta

To connect to Okta, set the AuthScheme to Okta, and set these properties:

• User: The Okta user.
• Password: The Okta user's password.
• SSOLoginURL: The SSO provider's login URL.
• SSOExchangeUrl: The URL used used to exchange the SAML token for Jira cookies. This URL may have the following formats:
  • https://<authority of Jira instance>/plugins/servlet/samlconsumer
  • https://<authority of Jira instance>/plugins/servlet/samlsso

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:

• APIToken: When authenticating a user via a trusted application or proxy that overrides the Okta client request context, set this to the API Token the customer created from the Okta organization.
• MFAType: If you have configured the MFA flow, set this to one of the following supported types: OktaVerify, Email, or SMS.
• MFAPassCode: If you have configured the MFA flow, set this to a valid passcode.
  If you set this to empty or an invalid value, the Sync App issues a one-time password challenge to your device or email. After the passcode is received, reopen the connection where the retrieved one-time password value is set to the MFAPassCode connection property.
• MFARememberDevice: True by default. Okta supports remembering devices when MFA is required. If remembering devices is allowed according to the configured authentication policies, the Sync App sends a device token to extend MFA authentication lifetime. If you do not want MFA to be remembered, set this variable to False.

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;

Required Scopes and Endpoint Domains for Jira

When integrating with Jira, your application needs specific permissions to interact with the API.

These permissions are defined by access scopes, which determine what data your application can access and what actions it can perform.

This topic provides information about the required access scopes and endpoint domains for the Jira Sync App.

Understanding Scopes

Scopes are a way to limit an application's access to a user's data. They define the specific actions that an application can perform on behalf of the user.

For example, a read-only scope might allow an application to view data, while a full access scope might allow it to modify data.

Required Scopes for Jira

Scopes can be controlled using the Scope connection property.

Jira Cloud has two types of scopes: Classic and Granular. Jira recommends Classic scopes when available.

Scope	Type	Description
read:jira-user	Classic	View user information in Jira that the user has access to, including usernames, email addresses, and avatars. Required for read and write access.
read:jira-work	Classic	Read Jira project and issue data, search for issues and objects associated with issues like attachments and worklogs. Required for read and write access.
write:jira-work	Classic	Create and edit issues in Jira, post comments as the user, create worklogs, and delete issues. Required for write access.
manage:jira-project	Classic	Create and edit project settings and create new project-level objects (for example, versions and components). Required for write access.
manage:jira-configuration	Classic	Take Jira administration actions (for example, create projects and custom fields, view workflows, and manage issue link types). Required for write access.
read:application-role:jira	Granular	View application roles. Required for read and write access.
read:audit-log:jira	Granular	View audit logs. Required for read and write access.
read:dashboard:jira	Granular	View dashboards. Required for read and write access.
read:filter:jira	Granular	View filters. Required for read and write access.
read:filter.column:jira	Granular	View filter columns. Required for read and write access.
read:group:jira	Granular	View user groups. Required for read and write access.
read:issue:jira	Granular	View issues. Required for read and write access.
write:issue:jira	Granular	Create and update issues. Required for write access.
delete:issue:jira	Granular	Delete issues. Required for write access.
read:issue-meta:jira	Granular	View issue meta. Required for read and write access.
read:attachment:jira	Granular	View issue attachments. Required for read and write access.
write:attachment:jira	Granular	Create and update issue attachments. Required for write access.
delete:attachment:jira	Granular	Delete issue attachments. Required for write access.
read:comment:jira	Granular	View issue comments. Required for read and write access.
write:comment:jira	Granular	Create and update issue comments. Required for write access.
delete:comment:jira	Granular	Delete issue comments. Required for write access.
read:comment.property:jira	Granular	View issue comment properties. Required for read and write access.
write:comment.property:jira	Granular	Create and update issue comment properties. Required for write access.
delete:comment.property:jira	Granular	Delete issue comment properties. Required for write access.
read:field:jira	Granular	View fields. Required for read and write access.
write:field:jira	Granular	Create and update fields. Required for write access.
read:field.option:jira	Granular	View field options. Required for read and write access.
read:field.options:jira	Granular	Read field options. Required for read and write access.
read:custom-field-contextual-configuration:jira	Granular	Read custom field contextual configurations. Required for read and write access.
write:custom-field-contextual-configuration:jira	Granular	Save custom field contextual configurations. Required for write access.
read:field-configuration:jira	Granular	Read field configurations. Required for read and write access.
write:field-configuration:jira	Granular	Save field configurations. Required for write access.
read:issue-link:jira	Granular	View issue links. Required for read and write access.
read:issue-link-type:jira	Granular	View issue link types. Required for read and write access.
read:issue.property:jira	Granular	View issue properties. Required for read and write access.
read:priority:jira	Granular	View priorities. Required for read and write access.
read:resolution:jira	Granular	View resolutions. Required for read and write access.
read:issue-details:jira	Granular	View issue details. Required for read and write access.
read:issue-security-scheme:jira	Granular	View issue security schemes. Required for read and write access.
read:issue-type:jira	Granular	View issue types. Required for read and write access.
read:issue-type-scheme:jira	Granular	View issue type schemes. Required for read and write access.
read:issue-type.property:jira	Granular	View issue type properties. Required for read and write access.
read:issue.watcher:jira	Granular	View issue watchers. Required for read and write access.
read:issue-worklog:jira	Granular	View issue worklogs. Required for read and write access.
read:issue-field-values:jira	Granular	View issue field values. Required for read and write access.
read:issue-security-level:jira	Granular	View issue security levels. Required for read and write access.
read:issue-status:jira	Granular	View issue statuses. Required for read and write access.
read:issue.changelog:jira	Granular	View issue changelogs. Required for read and write access.
read:issue.transition:jira	Granular	View issue transitions. Required for read and write access.
delete:issue-type:jira	Granular	Delete issue types. Required for write access.
write:issue-type:jira	Granular	Create and update issue types. Required for write access.
read:user:jira	Granular	View users. Required for read and write access.
read:user.columns:jira	Granular	View user columns. Required for read and write access.
read:project:jira	Granular	View projects. Required for read and write access.
write:project:jira	Granular	Create and update projects. Required for write access.
delete:project:jira	Granular	Delete projects and their details, such as issue types, project lead, and avatars. Required for write access.
read:project-category:jira	Granular	View project categories. Required for read and write access.
read:project.component:jira	Granular	View project components. Required for read and write access.
write:project.component:jira	Granular	Create and update project components. Required for write access.
delete:project.component:jira	Granular	Delete project components. Required for write access.
read:project.property:jira	Granular	View project properties. Required for read and write access.
write:project.property:jira	Granular	Create and update project properties. Required for write access.
delete:project.property:jira	Granular	Delete project properties. Required for write access.
read:project-role:jira	Granular	View project roles. Required for read and write access.
write:project-role:jira	Granular	Create and update project roles. Required for write access.
delete:project-role:jira	Granular	Delete project roles. Required for write access.
read:project-version:jira	Granular	View project versions. Required for read and write access.
write:project-version:jira	Granular	Create and update project versions. Required for write access.
delete:project-version:jira	Granular	Delete project versions. Required for write access.
read:issue.time-tracking:jira	Granular	View issue time tracking. Required for read and write access.
write:issue.time-tracking:jira	Granular	Create and update issue time tracking. Required for write access.
read:user.property:jira	Granular	View user properties. Required for read and write access.
write:user.property:jira	Granular	Create and update user properties. Required for write access.
delete:user.property:jira	Granular	Delete user properties. Required for write access.
read:workflow:jira	Granular	View workflows. Required for read and write access.
read:workflow-scheme:jira	Granular	View workflow schemes. Required for read and write access.
read:status:jira	Granular	View statuses. Required for read and write access.
read:workflow.property:jira	Granular	View workflow properties. Required for read and write access.
read:project-type:jira	Granular	View project types. Required for read and write access.
read:project.email:jira	Granular	View project emails. Required for read and write access.
write:project.email:jira	Granular	Create and update project emails. Required for write access.
read:role:jira	Granular	View roles. Required for read and write access.
read:user-configuration:jira	Granular	View user configurations. Required for read and write access.
write:user-configuration:jira	Granular	Create and update user configurations. Required for write access.
delete:user-configuration:jira	Granular	Delete user configurations. Required for write access.
read:jql:jira	Granular	View JQL. Required for read and write access.
validate:jql:jira	Granular	Validate JQL. Required for read and write access.

Jira Data Center has one type of scopes as shown in the table below.

Scope	Description
READ	View projects and issues the user account can view, including any related items, such as dashboards, filters, attachments, or comments. Also view the user profile.
WRITE	Create, update, and delete projects and issues the user account can change, including any related items, such as dashboards, filters, attachments, or comments. Also change the user profile.
ADMIN	Perform most administrative functions on the entire Jira instance, excluding functions such as backups, imports, and infrastructure settings which are limited to system administrators.
SYSTEM_ADMIN	Perform all administrative functions on the entire Jira instance, including functions such as backups, imports, and infrastructure settings. Note: This scope is not utilized by CData Sync App.

Understanding Endpoint Domains

Endpoint domains are the specific URLs that the application needs to communicate with in order to authenticate, retrieve records, and perform other essential operations.

Allowlisting these domains ensures that the network traffic between your application and the API is not blocked by firewalls or security settings.

Note: Most users do not need to make any special configurations. Allowlisting is typically only necessary for environments with strict security measures, such as restricted outbound network traffic.

Required Endpoint Domains for Jira

Domain	Always Required	Description
<URL>	TRUE	The URL of your Jira instance.
api.atlassian.com	FALSE	The base URL of the Jira API. Required when AuthScheme is set to OAuth.
<SSOLoginURL>	FALSE	The URL of your SSO provider. Required when AuthScheme is set to OKTA or CROWD.
<SSOExchangeURL>	FALSE	Your SSO Exchange URL. Required when AuthScheme is set to OKTA or CROWD.

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

User Defined Views

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

SSL Configuration

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

Firewall and Proxy

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

Query Processing

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

For further information, see Query Processing.

Logging

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

Customizing the SSL Configuration

To enable TLS, set the following:

• URL: Prefix the connection string with https://

With this configuration, the Sync App attempts to negotiate TLS with the server. The server certificate is validated against the default system trusted certificate store. You can override how the certificate gets validated using the SSLServerCert connection property.

To specify another certificate, see the SSLServerCert connection property.

Client SSL Certificates

The Jira Sync App also supports setting client certificates. Set the following to connect using a client certificate.

• SSLClientCert: The name of the certificate store for the client certificate.
• 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.

Connecting Through a Firewall or Proxy

HTTP Proxies

To authenticate to an HTTP proxy, set the following:

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

Other Proxies

Set the following properties:

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

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.

Accessing Custom Fields

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.

Controlling Column Names

You can use the following properties to gain more control over column names:

• UseSimpleNames: boolean determining if simple names should be used for columns.
• UseDisplayNames: boolean determining if the display names for custom fields should be used instead of the API names.

Handling Key Column Operators

By default, all supported operators are passed directly to the Jira API, enabling efficient server-side filtering. However, when queries include invalid Id or Key values, this approach can lead to inconsistent outcomes. On Jira Cloud, depending on the UseJqlSearchEndpoint setting, invalid filters may return 0 results instead of throwing an error, whereas on Jira Data Center they may cause API errors. The KeyColumnOperators connection property allows you to control this behavior.

By default, the KeyColumnOperators is set to Server-Side.

• All supported operators are sent as-is to the Jira API.
• On legacy endpoints (when UseJqlSearchEndpoint is set to False) or on Jira Data Center, this may cause API errors if invalid filter values are provided.
• On Jira Cloud with UseJqlSearchEndpoint set to true (the default), the API does not throw an error for filters with invalid keys or IDs; instead, it returns 0 results. For example, a JQL filter such as id > 123 (when 123 is not a valid issue ID) will yield 0 results rather than failing. This behavior can lead to inconsistent outcomes if you expect a complete result set.

To ensure consistent results, you can set the KeyColumnOperators connection property to Client-Side. In this mode, the following operators applied to the Id and Key columns are handled client-side by the Sync App rather than being included in the JQL sent to the Jira API:

• Logical operator: OR
• Comparison operators: IN, NOT IN, >, >=, <, <=, !=

By handling these operators client-side, the Sync App avoids strict server-side filtering rules that could cause the entire query to fail or return zero results due to a single invalid value. However, enabling this property can impact performance:

• For IN filters, the Sync App will execute a separate request for each value, potentially increasing query execution time.
• For other filters, since the Sync App retrieves all records before applying the filter client-side, queries may take longer due to the additional processing.

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.

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

Extending Schemas

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.

Defining Custom Field Columns

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:

• name
• xs:type

Custom fields require the following additional parameters:

• other:urlname: This field must be specified to return the column in "SELECT MyCustomField" queries. This field has the format MyCustomFieldName_MyCustomFieldId. The Id of the custom field can be obtained by querying the Fields table.
• other:relativexpath: This field must be specified to return the column in "SELECT *" queries. This field is an XPath that maps to the column value in the JSON response from Jira. The XPaths are relative to a repeat element that separates the JSON response into rows.

See the following sections for examples of specifying the XPath to the value you want for different types of columns.

Adding Simple-Type Custom Field 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." />

Adding Object-Type Custom Field Columns

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." />

Adding Array-Type Custom Fields

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.

• XPath: Set this to "/json/issues" for compatibility with future versions of the Jira API. The XPath value is relative to the RepeatElement and SubRepeatElement values, described below.
• SubRepeatElement: Set this to "/json/issues/fields/your-custom-field-name_your-custom-field-id". Query the Fields table to obtain the Id of a custom field.
• RepeatElement: Set this to a column that is in every custom field row.
• IsSearch: Set this to "true".
• JiraTableCode: Set this to the name of the custom field plus the Id of the custom field, which can be retrieved from the Fields table. Separate the custom field name and custom field Id with an underscore.

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.

Complete Issues Schema

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="=,!=,&gt;,&gt;=,&lt;,&lt;=,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="=,!=,&gt;,&gt;=,&lt;,&lt;=,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="=,!=,&gt;,&gt;=,&lt;,&lt;=,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="=,!=,&gt;,&gt;=,&lt;,&lt;=,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="=,!=,&gt;,&gt;=,&lt;,&lt;=,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="=,!=,&gt;,&gt;=,&lt;,&lt;=,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="=,!=,&gt;,&gt;=,&lt;,&lt;=,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="=,!=,&gt;,&gt;=,&lt;,&lt;=,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="=,!=,&gt;,&gt;=,&lt;,&lt;=,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="=,!=,&gt;,&gt;=,&lt;,&lt;=,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="=,!=,&gt;,&gt;=,&lt;,&lt;=,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="=,!=,&gt;,&gt;=,&lt;,&lt;=,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="=,!=,&gt;,&gt;=,&lt;,&lt;=,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="=,!=,&gt;,&gt;=,&lt;,&lt;=,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.

Jira Connector for CData Sync Tables

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.
WorkflowSchemes	Create, modify, delete and query the available workflow schemes in Jira.
Worklogs	Create, modify, delete and query the available Worklogs in Jira.

Query, insert and delete the available Attachments in Jira.

Table Specific Information

Select

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

To retrieve attachments linked to a specific issue, query the Attachments table by specifying either the IssueId or IssueKey column.

SELECT * FROM Attachments WHERE IssueId = 123

To retrieve attachments linked to issues within a specific project, query the Attachments table by specifying either the ProjectId or ProjectName column.

SELECT * FROM Attachments WHERE ProjectId = 10100

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' 

Insert

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

Delete

Remove an attachment uploaded to any issue by specifying the Id of the attachment

DELETE FROM Attachments WHERE Id = 10003

Columns

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.
ProjectId	Integer	True	Projects.Id	=,IS,IS NOT,IN,NOT IN	Common	The project Id of the attachment's issue.
ProjectName	String	True		=,IS,IS NOT,IN,NOT IN	Common	The project name of the attachment's 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-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
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.

Table Specific Information

Select

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'

Insert

To insert a new board you must specify Name, Type and FilterId.

INSERT INTO Boards (Name, Type, FilterId) VALUES ('New board', 'scrum', 10200)

Delete

To delete a board you must specify its Id.

DELETE FROM Boards WHERE Id = 130

Columns

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-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
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.

Table Specific Information

Select

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

To retrieve comments associated with a specific issue, query the Comments table by specifying either the IssueId or IssueKey column.

SELECT * FROM Comments WHERE IssueId = 10003

To retrieve comments associated to issues within a specific project, query the Comments table by specifying either the ProjectId or ProjectName column.

SELECT * FROM Comments WHERE ProjectId = 10100

Additionally, the Created column can be used in the ORDER BY clause:

SELECT * FROM Comments WHERE IssueId = 10003 ORDER BY Created DESC

Insert

Post a comment as the logged in user. IssueId and Body are required.

INSERT INTO Comments (IssueId, Body) VALUES ('10003', 'Test Comment')

Update

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

Delete a comment posted in an issue by providing the Id and IssueId

DELETE FROM Comments WHERE Id = 10202 AND IssueId = 10003

Columns

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.
ProjectId	Integer	True	Projects.Id	=,IS,IS NOT,IN,NOT IN	Common	The project Id of the comment's issue.
ProjectName	String	True		=,IS,IS NOT,IN,NOT IN	Common	The project name of the comment's 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.
ItemURL	String	True			Common	A browsable link of the comment.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
JQL	String	JQL (Jira Query Language) allows you to build structured queries.

Create, modify, delete and query Issues in Jira.

Table Specific Information

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

JQL

SELECT * FROM Issues WHERE JQL = ' project = "New project" AND Status WAS "open" '

SQL

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.

SQL and JQL

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'

Insert

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

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"}]}')

IssueLinksAggregate

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

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'

Columns

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.
ItemURL	String	True			Common	A browsable link of the issue.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
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.

Select

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

Insert

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)

Update

You can update by specifying the issue type Id:

UPDATE IssueTypes SET Name = 'Updated Name 3', Description = 'Updated description' WHERE Id = 10306

Delete

Delete an issue type by specifying the issue type Id:

DELETE FROM IssueTypes WHERE Id = 10306

Columns

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.
HierarchyLevel	Int	False			Cloud	Hierarchy level of the issue type.
IconUrl	String	True			Common	The icon URL of the issue type.
EntityId	String	True			Cloud	Unique ID for next-gen projects.
Self	String	True			Common	The URL of these issue type details.
Scope	String	True			Cloud	Details of the next-gen projects the issue type is available in.
AvatarId	Int	True			Common	The ID of the issue type's avatar.

Query, create, modify, and delete the available Components of a project in Jira.

Table Specific Information

Select

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' 

Insert

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

Update

Make changes to a project component, specifying the Id column of the component to modify.

UPDATE ProjectComponents SET LeadKey = 'newlead' WHERE Id = 10065

Delete

Remove a component of the project by specifying the Id.

DELETE FROM ProjectComponents WHERE Id = 10065

Columns

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.

Select

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'

Insert

Cloud

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

Server

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

Cloud

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'

Server

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'

Columns

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-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
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.

Select

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

INSERT

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

Update

You can update by specifying either Id or Key:

UPDATE Projects SET Description = 'Updated description', AssigneeType = 'UNASSIGNED', LeadName = 'admin2' WHERE Key = 'EXE4'

Delete

Delete a project by specifying either Id or Key:

DELETE FROM Projects WHERE Key = 'EXE4'

Columns

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.
Email	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.
ItemURL	String	True			Common	A browsable link of the project.

Query,create, modify and delete the available Versions of a project in Jira.

Table Specific Information

Select

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' 

Insert

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

Update

Make changes to a project version, specifying the Id column of the version to modify.

UPDATE ProjectVersions SET Released = 'true' WHERE Id = 10065

Delete

Remove a version of the project by specifying the Id column.

DELETE FROM ProjectVersions WHERE Id = 10065

Columns

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.

Select

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'

Insert

Cloud

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

Server

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

Cloud

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' 

Server

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'

Columns

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-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
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.

Select

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

Update the role name and description by specifying the role Id

UPDATE Roles SET Description = 'test_updated', Name = 'test_updated' WHERE Id = 10002

Delete

Delete a role by specifying the Id.

DELETE FROM Roles WHERE Id = '10002'

Columns

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.

Table Specific Information

Select

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

INSERT

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

Update

Make changes to a sprint by specifying the sprint's Id.

UPDATE Sprints SET State = 'active' WHERE Id = 305

Delete

Remove a sprint by specifying the Id.

DELETE FROM Sprints WHERE Id = 305

Columns

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.

Select

The Sync App executes filters other than the following client-side within the Sync App. The Jira APIs have limited support for filtering the Name, DisplayName, and EmailAddress columns with the LIKE operator.

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

The input to INSERT must be an email address:

INSERT INTO Users(EmailAddress) VALUES ('[email protected]')

Columns

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-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
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.

Select

Most filters are processed client side, though there are a few exceptions to this:

• The Sync App uses the Jira APIs to process filters on the Name that use the equals (=) and IN operators only.
• The Sync App processes WHERE filters for the IsActive and Query columns with only the equals (=) operator server-side.

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)

Columns

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 workflow schemes in Jira.

Note: This table is available only in the Jira Cloud version.

Select

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 below queries are processed server side.

Retrieve all the available workflow schemes:

SELECT * FROM WorkflowSchemes

Retrieve a specific workflow scheme by Id:

SELECT * FROM WorkflowSchemes WHERE Id = 10400

Insert

Name is required to insert. The following query creates a new workflow scheme:

INSERT INTO WorkflowSchemes (name, Description, DefaultWorkflow, IssueTypeMappingsAggregate) VALUES('Jira Service Management IT Support Workflow', 'Incident Management workflow for Jira Service Management.', 'jira', '{"10201": "jira"}')

Update

You can update by specifying the workflow scheme Id:

UPDATE WorkflowSchemes SET Name = 'Jira Updated Service Management IT Support Workflow', Description='Updated incident Management workflow for Jira Service Management.' WHERE Id =11526

Delete

Delete a workflow scheme by specifying the workflow scheme Id:

DELETE WorkflowSchemes WHERE Id=11526

Columns

Name	Type	ReadOnly	References	SupportedOperators	Platform	Description
Id [KEY]	Integer	True		=	Cloud	The ID of the workflow scheme.
Name	String	False			Cloud	The name of the workflow scheme.
Description	String	False			Cloud	The description of the workflow scheme.
IssueTypeMappingsAggregate	String	False			Cloud	The issue type to workflow mappings aggregate, where each mapping is an issue type ID and workflow name pair.
DefaultWorkflow	String	False			Cloud	The name of the default workflow for the workflow scheme.
Self	String	True			Cloud	The self/url column.

Create, modify, delete and query the available Worklogs in Jira.

Table Specific Information

Select

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. The below example queries are processed server side.

To retrieve worklogs linked to a specific issue, query the Worklogs table by specifying either the IssueId or IssueKey column.

SELECT * FROM Worklogs WHERE IssueId = 123

To retrieve worklogs linked to issues within a specific project, query the Worklogs table by specifying either the ProjectId or ProjectName column.

SELECT * FROM Worklogs WHERE ProjectId = 10100

Insert

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)

Update

Both the worklog Id and IssueId are required to modify the worklog:

UPDATE Worklogs SET TimeSpentSeconds = 200000 WHERE Id = 10602 AND IssueId = 10003

Delete

Delete a worklog of an issue by providing the Id and IssueId:

DELETE FROM Worklogs WHERE Id = 10602 AND IssueId = 10003

GetDeleted

This retrieves Ids of the deleted worklogs:

GETDELETED FROM Worklogs

Columns

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		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		Cloud	The name of the author who has updated the worklog.
AuthorAccountKey	String	True	Users.Key		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		Server	The key of the author who has updated the worklog.
UpdateAuthorAccountName	String	True	Users.Name		Server	The name of the author who has updated the worklog.
ItemURL	String	True				A browsable link of 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.

Jira Connector for CData Sync Views

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.
CustomFieldContext	Returns a list of contexts for a custom field 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.
FiltersUsers	Query the users that filters are shared with.
Groups	Query the available Groups in Jira.
GroupsUsers	Query the users available in Jira and their associated groups.
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 the set up Issue Custom Field Options in Jira. This resource is not available 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.
IssueSecurityLevelMembers	Query the members of issue security level entities.
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.
PermissionGrants	Query the available permission grants for a permission scheme in Jira.
Permissions	Query the available Permissions in Jira.
PermissionSchemes	Query the available permission schemes in Jira.
ProjectCategories	Query all the project categories.
ProjectRoles	Query roles for all projects in Jira.
ProjectsIssueTypes	Query the available Issue Types of projects.
ProjectsPermissionScheme	Query the available permissions schemes for a project in Jira.
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.

Table Specific Information

Select

SELECT * FROM AdvancedSettings

Columns

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.

Table Specific Information

Select

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'

Columns

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.

Table Specific Information

Select

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'

Columns

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-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
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.

Columns

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.

Table Specific Information

Select

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) 

Columns

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.

Table Specific Information

Select

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

Columns

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.

Table Specific Information

Select

The Sync App processes all filters specified on this table client-side within the Sync App.

SELECT * FROM Configuration

Columns

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.

Returns a list of contexts for a custom field in Jira.

Columns

Name	Type	References	SupportedOperators	Platform	Description
Id [KEY]	String				The Id of the context.
CustomFieldId	String		=		The CustomFieldId.
Name	String				The Name of the context.
Description	String				The Description of the context.
IsGlobalContext	Boolean				Whether the context is global.
IsAnyIssueType	Boolean				Whether the context apply to all issue types.

Query the available Dashboards in Jira.

Table Specific Information

Select

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 = 10001

You can also filter on the pseudo column Filter:

SELECT * FROM Dashboards WHERE Filter = 'favourite'

Columns

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-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
Filter	String		This is used to filter data. Valid values include favourite and my. The allowed values are favourite, my.

Query all the Epics.

Table Specific Information

Select

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

Columns

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.

Columns

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.

Columns

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.

Table Specific Information

Select

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 

Columns

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 users that filters are shared with.

Columns

Name	Type	References	SupportedOperators	Platform	Description
AccountId [KEY]	String		=		The accountId of the user, which uniquely identifies the user across all Atlassian products.
FilterId [KEY]	String	Filters.Id	=		The key of the project.
DisplayName	String		=		The display name of the user.
EmailAddress	String		=		The email address of the user.
Active	Bool				Indicates whether the user is active.
TimeZone	String				The time zone specified in the user's profile.
Locale	String				The locale of the user.
AccountType	String				The accountType of the user.

Query the available Groups in Jira.

Table Specific Information

Select

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

Columns

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 users available in Jira and their associated groups.

View Specific Information

Select

The Sync App utilizes the Jira API to process WHERE clause conditions that include the following columns and supported operators. Any remaining filters are applied client-side by the Sync App.

To effectively query the GroupsUsers view, you must specify a value for either the GroupName column or the GroupId column (the latter is exclusive to cloud instances). If neither GroupName nor GroupId is provided, the Sync App will automatically iterate through all groups to retrieve all associated users, which may result in increased query times.

As shown in the examples below, to retrieve users who belong to a specific group, include either the GroupName or GroupId in your query.

• Using GroupId:
  

  SELECT * FROM GroupsUsers WHERE GroupId = '37f2babd-27ef-442e-8642-15b6cafba31c'

• Using GroupName:
  

  SELECT * FROM GroupsUsers WHERE GroupName = 'jira-software-users'

Columns

Name	Type	References	SupportedOperators	Platform	Description
AccountId [KEY]	String			Cloud	The accountId of the user, which uniquely identifies the user across all Atlassian products.
Key [KEY]	String			Server	The key of the user.
GroupId [KEY]	String		=	Cloud	The group Id the user is part of.
GroupName [KEY]	String		=	Common	The group name the user is part of.
Name	String			Server	The name of the user.
DisplayName	String			Common	The display name of the user.
EmailAddress	String			Common	The email address of the user.
Active	Bool			Common	Indicates whether the user is active.
TimeZone	String			Common	The time zone specified in the user's profile.
Locale	String			Cloud	The locale of the user.
AccountType	String			Cloud	The accountType of the user.
Products	String			Cloud	A delimited string of jira products to which the user has access to, ex: jira-core,jira-servicedesk,jira-product-discovery,jira-software

Query the Affected Versions of an issue in Jira.

Table Specific Information

You can search IssueAffectedVersions by using SQL and JQL (Jira Query Language).

Select

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

Columns

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-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
JQL	String		JQL (Jira Query Language) allows you to build structured queries.

Query the available IssueChangelogs in Jira.

Table Specific Information

You can search IssueChangelogs by using SQL and JQL (Jira Query Language).

Select

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'

Columns

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-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
JQL	String		JQL (Jira Query Language) allows you to build structured queries.

Query the available IssueComponents in Jira.

Table Specific Information

You can search IssueComponents by using SQL and JQL (Jira Query Language).

Select

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

Columns

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-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
JQL	String		JQL (Jira Query Language) allows you to build structured queries.

Query the set up Issue Custom Field Options in Jira. This resource is not available when using OAuth authentication. Specifying at least CUSTOMFIELDID, or ID is required.

Columns

Name	Type	References	SupportedOperators	Platform	Description
OptionId [KEY]	String		=		The ID of the custom field option.
Name	String				The Name of the custom field option.
CustomFieldId	String	IssueCustomFields.CustomId	=		The ID of the custom field.
Context	String		=,IN		The context of the custom field option.
Disabled	Boolean				Whether the option is disabled.