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

Connecting to Jira

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

Accessing Data from CData Cloud Services

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

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

Connecting to Jira

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

Authenticating to Jira

Jira supports several authentication schemes. The available methods depend on your instance type: Cloud or Data Center (on-premise).

• Cloud
  • OAuth
  • API Token
• Data Center
  • OAuth
  • Personal Access Token (PAT)
  • Okta
  • Crowd
  • LDAP
  • Basic (User and Password)
  • Anonymous

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

This authentication method is only available in Jira Cloud.

You can authenticate to Jira Cloud instances using API tokens. To generate an API token, log in to your Atlassian account, click the user icon in the top-right corner of the page, and navigate to Account settings > Security > API tokens > Create and manage API tokens, then click Create API token. Store this token for later use. Note that the token is shown only once. If you leave the page without copying the token, you will have to create another token and copy its value. Set the following to connect:

• AuthScheme: APIToken
• User: The username of your Jira account.
• APIToken: The API token you created using the above process.

Personal Access Token (PAT)

This authentication method is only available in Jira Data Center.

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 the 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 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 Cloud properties:

• AuthScheme: PAT
• PAT: The token you generated using the above procedure.

NOTE: PATs can also be created using the REST API. For further information, see Creating Personal Access Tokens.

Okta

To connect to Okta, set these properties:

• AuthScheme: Okta.
• User: The authenticating Okta user.
• Password: The password of the authenticating Okta user.
• SSOLoginURL: The SSO provider's login URL.
• SSOExchangeURL: The URL 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 either 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 Cloud 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 Cloud 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;

Crowd

To authenticate via Crowd SSO:

• AuthScheme: Crowd
• 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 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;

LDAP

This authentication method is only available in Jira Data Center.

To establish a connection to any Jira Server instance, set the following:

• AuthScheme: LDAP
• User: The username of one of your LDAP server's users.
• Password The password associated with the specified LDAP user.

Basic

This authentication method is only available in Jira Data Center.

You can authenticate to Jira Data Center using a username and password. To do this, set the following:

• AuthScheme Basic
• User: Your Jira Data Center username.
• Password: The password associated with the specified username.

Anonymous

This authentication method is only available in Jira Data Center.

You can connect to public endpoints in Jira that do not require authentication. This allows the Cloud to access data without sending user credentials. To connect, set the following:

• AuthScheme: Anonymous
• Timezone: The timezone associated with your Jira Data Center instance.

IMPORTANT: This scheme should only be used when connecting to publicly accessible endpoints that are configured to allow anonymous access. If the Jira instance does not support anonymous access for the requested resource, the connection fails or returns an empty result set.

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

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

Jira Cloud has two types of scopes: Classic and Granular. Jira recommends Classic scopes when available. For the full list of available scopes, see Scopes for OAuth 2.0 (3LO) and Forge apps.

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.
write:group:jira	Granular	Create and update user groups. Required for write access.
delete:group:jira	Granular	Delete user groups. Required for 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.remote-link:jira	Granular	View issue remote links. Required for read and write access.
write:issue.remote-link:jira	Granular	Create and update issue remote links. Required for write access.
delete:issue.remote-link:jira	Granular	Delete issue remote links. Required for 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-screen-scheme:jira	Granular	View issue type screen 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.
write:issue.watcher:jira	Granular	Add and remove issue watchers. Required for write access.
read:issue-worklog:jira	Granular	View issue worklogs. Required for read and write access.
write:issue-worklog:jira	Granular	Create and update issue worklogs. Required for write access.
delete:issue-worklog:jira	Granular	Delete issue worklogs. Required for 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:label:jira	Granular	View labels. Required for read and write access.
read:permission:jira	Granular	View permissions. Required for read and write access.
read:permission-scheme:jira	Granular	View permission schemes. 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:screen-scheme:jira	Granular	View screen schemes. Required for read and write access.
read:screen-tab:jira	Granular	View screen tabs. Required for read and write access.
read:screenable-field:jira	Granular	View screen tab fields. Required for read and 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

Jira Data Center supports READ and WRITE scopes for OAuth 2.0. WRITE inherits READ access. Only endpoints under /rest/api/2/ are exposed.

Scope	Description
READ	View projects and issues the user account can access, including related items such as dashboards, filters, attachments, and comments. Also view the user profile.
WRITE	Create, update, and delete projects and issues the user account can change, including related items such as dashboards, filters, attachments, and comments. Also change the user profile. Inherits READ access.
ADMIN	Perform most administrative functions on the entire Jira instance, excluding functions such as backups, imports, and infrastructure settings. NOTE: Only available with OAuth 1.0.
SYSTEM_ADMIN	Perform all administrative functions on the entire Jira instance, including backups, imports, and infrastructure settings. NOTE: Only available with OAuth 1.0.

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 or APIToken (Cloud instances only).
<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.

Customizing the SSL Configuration

To enable TLS, set the following:

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

With this configuration, the Cloud 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 Cloud 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 Cloud 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.

Overview

The Cloud models Jira entities as relational tables/views or stored procedures, allowing you to write SQL to query Jira data or execute operations.

Live connectivity to these objects means any changes to your Jira account are immediately reflected when using the Cloud.

• Tables are entities that can be modified. Tables support SELECT, INSERT, UPDATE, and DELETE statements. For example: Issues.
• Views are read-only tables. Only SELECT statements are supported for them. For example: Dashboards.
• Stored procedures are actions that are invoked via SQL queries. They perform tasks beyond standard CRUD operations. For example: CreateCustomField.

Jira Schema (non-cloud)

The Jira Data Center schema exposes Jira Core, Jira Software, and Jira administration-related objects as SQL entities.

Jira Cloud Schema

The Jira schema exposes tables, views, and stored procedures that apply exclusively to Jira Cloud instances.

Shared entities

The entities in System Tables and Stored Procedures are shared across the data model and can be used regardless of the schema chosen.

Customizing Schemas

Custom schema objects (such as tables and views) are defined in .rsd configuration files. These files have a simple format that makes them easy to extend; for example, to gain more control over custom fields.

To save a schema to an .rsd file, call the CreateSchema stored procedure; for example, with the IncludeCustomFields connection property set.

To include tables/views modeled by these .rsd files in the Cloud schema, set the Location property to the folder containing your custom .rsd files. The schemas included with the Cloud are located in the db subfolder of the installation folder.

Controlling Column Names

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

• UseSimpleNames: Specifies whether to simplify column names by hiding special characters. If enabled, column names only include alphanumeric characters and underscores.
• UseDisplayNames: Specifies whether columns are displayed using their display names, rather than their API names.

Custom Fields

By default, the table and view schemas describe the column behavior of only system fields. However, you can enable custom fields for the Issues table. See Custom Fields for more information.

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, errors can occur, or queries may return 0 results.

See Handling Key Column Operators for information about controlling how these operators are handled by the Cloud.

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, 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 Jira Data Center, this may cause API errors if invalid filter values are provided.
• On Jira Cloud, the API does not throw an error for filters with invalid keys or ID-s; instead, it returns 0 results. For example, a JQL filter such as id > 123 (when 123 is not a valid issue ID) returns 0 results rather than an error. 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 and are handled client-side by the Cloud 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 Cloud 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 Cloud executes a separate request for each value, potentially increasing query execution time.
• For other filters, since the Cloud retrieves all records before applying the filter client-side, queries may take longer due to the additional processing.

Data Type Mapping

The Cloud maps types from the data source to the corresponding data type available in the schema. The table below documents these mappings.

Jira	CData Schema
Assets	string
Checkbox	string
Date of First Response	datetime
Date Picker	date
Date Time Picker	datetime
Days since last comment	string
Domain of Assignee	string
Domain of Reporter	string
Group Picker (multiple groups)	string
Group Picker (single group)	string
Labels	string
Last commented by a User Flag	string
Last public comment date	string
Message Custom Field (for edit)	string
Message Custom Field (for view)	string
Number Field	decimal
Number of attachments	string
Number of comments	string
Parent Link	string
Participants of an issue	string
Project Picker (single project)	string
Radio Buttons	string
Select List (cascading)	string
Select List (multiple choices)	string
Select List (single choice)	string
Team	string
Text Field (multi-line)	string
Text Field (read only)	string
Text Field (single line)	string
Time in Status	string
URL Field	string
User Picker (multiple users)	string
User Picker (single user)	string
User Property Field	string
Username of last updater or commenter	string
Version Picker (multiple versions)	string
Version Picker (single version)	string

Overview

In the following sections, you can find information on Jira schema entities which are exclusive to Jira Cloud instances: Tables, Views, and Stored Procedures.

The Cloud models the data in Jira as a list of tables in a relational database that can be queried using standard SQL statements.

CData Cloud - Jira Tables

Name	Description
IssueRemoteLinks	Creates, reads, updates, and deletes the available remote issue links.
WorkflowSchemes	Handles the configuration of workflow schemes that assign workflows to issue types across projects.

Creates, reads, updates, and deletes the available remote issue links.

Table-Specific Information

SELECT

Retrieves details about remote issue links for a specific Jira issue.

The Cloud uses the Jira API to process filters based on the IssueId, IssueKey, and Id columns. The Cloud processes all other filters client-side.

You must specify an IssueId or IssueKey to retrieve results. If neither is specified, the Cloud automatically queries the Issues table to retrieve the required identifiers. This may result in increased query times.

For example, the following queries are processed server-side:

SELECT * FROM IssueRemoteLinks WHERE IssueId = 19409;
SELECT * FROM IssueRemoteLinks WHERE IssueKey = 'ACOL-4963' AND Id = '10001';

INSERT

Insert a remote issue link by specifying the following fields:

• ObjectUrl
• ObjectTitle
• Either IssueId or IssueKey

INSERT INTO IssueRemoteLinks (ObjectUrl, ObjectTitle, IssueKey) VALUES('http://www.mycompany.com/support?id=1', 'TSTSUP-111', 'ACOL-4963');

UPDATE

Update a remote issue link by specifying its Id and either the IssueId or IssueKey of the associated issue.

UPDATE IssueRemoteLinks
SET
  Relationship = 'relates to',
  ObjectStatusResolved = true,
  ObjectStatusIconTitle = 'Resolved',
  ObjectStatusIconLink = 'https://tracker.orion-labs.net/status/resolved',
  ObjectStatusIconUrl16x16 = 'https://tracker.orion-labs.net/icons/status_resolved_16.png'
WHERE
  Id = 10059
  AND IssueKey = 'ACOL-4971';

DELETE

Delete a remote issue link by specifying the Id of the link and either the IssueId or IssueKey of the issue.

DELETE FROM IssueRemoteLinks WHERE Id = 10058 AND IssueId = 19409;

Columns

Name	Type	ReadOnly	References	SupportedOperators	Description
Id [KEY]	Integer	True		=	The unique identifier of the remote link.
GlobalId	String	False			The global identifier of the link, typically representing the linked item on a remote system.
ApplicationName	String	False			The name of the remote application where the linked item resides.
ApplicationType	String	False			The namespace-qualified type of the remote application where the linked item resides.
Relationship	String	False			The description of the relationship between the issue and the linked item.
Self	String	True			The API URL of this remote link resource.
ObjectTitle	String	False			The title of the linked item.
ObjectSummary	String	False			The summary of the linked item.
ObjectUrl	String	False			The direct URL to view the linked item.
ObjectIconTitle	String	False			The tooltip title of the linked item's icon.
ObjectIconUrl16x16	String	False			The URL of the linked item's 16×16 pixel icon displayed in Jira.
ObjectStatusResolved	Bool	False			Indicates whether the linked item is resolved.
ObjectStatusIconTitle	String	False			The tooltip title of the linked item's status icon.
ObjectStatusIconLink	String	False			The URL associated with the linked item's status icon tooltip.
ObjectStatusIconUrl16x16	String	False			The URL of the linked item's 16×16 pixel status icon displayed in Jira.
IssueId [KEY]	String	False	Issues.Id	=	The ID of the issue associated with the remote link.
IssueKey	String	False	Issues.Key	=	The key of the issue associated with the remote link.

Handles the configuration of workflow schemes that assign workflows to issue types across projects.

Table-Specific Information

SELECT

The Cloud uses the Jira API to process filters based on the Id column. The Cloud processes all other filters client-side.

For example, the following queries are processed server-side:

SELECT * FROM WorkflowSchemes WHERE Id = 10400;

INSERT

Insert a workflow scheme by specifying the Name column.

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

Update a workflow scheme by specifying the Id and the columns to update.

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

DELETE FROM WorkflowSchemes WHERE Id = 11526;

Columns

Name	Type	ReadOnly	References	SupportedOperators	Description
Id [KEY]	Integer	True		=	The unique identifier of the workflow scheme. This ID is used to manage and reference the scheme in Jira operations.
Name	String	False			The user-defined name of the workflow scheme. Workflow schemes define how issues transition through workflows based on their issue types.
Description	String	False			An optional description that explains the purpose or usage of the workflow scheme. Useful for distinguishing between multiple schemes.
IssueTypeMappingsAggregate	String	False			A JavaScript Object Notation (JSON)-style aggregate listing mappings between issue types and workflows. Each mapping pairs an issue type ID with the name of its assigned workflow.
DefaultWorkflow	String	False			The name of the workflow used for issue types not explicitly mapped in the scheme. Acts as a fallback when no direct mapping is available.
Self	String	True			A REST API URL pointing to the workflow scheme resource. Useful for retrieving or updating the scheme programmatically.

Views are similar to tables in the way that data is represented; however, views are read-only.

Queries can be executed against a view as if it were a normal table.

CData Cloud - Jira Views

Name	Description
Filters	Displays saved filters created by users to retrieve specific issue sets. Not available in Jira Server deployments.
FiltersUsers	Returns the users or groups that specific filters are shared with.
HistoricalWorkflows	Returns the stored version history of a workflow. Jira retains workflow history for 60 days.
IssueSecurityLevelMembers	Retrieves the users and groups associated with specific issue security levels, which control visibility of issues.
IssueTypeScreenSchemes	Returns the issue type screen scheme items, mapping issue types to screen schemes within each issue type screen scheme, on Jira Cloud.
ProjectIssueTypeScreenSchemes	Returns the issue type screen scheme associated with a Jira Cloud project.
ProjectWorkflows	Returns a read-only list of the workflows used by a given project.
ScreenSchemes	Returns a paginated list of screen schemes. Only screen schemes used in classic projects are returned. Each row represents a single screen mapped to a screen scheme.
ScreenTabFields	Returns the fields on a tab of a Jira screen on Jira Cloud. ScreenId and TabId filters are required to query this view.
ScreenTabs	Returns the tabs of a Jira screen on Jira Cloud. A ScreenId filter is required to query this view.
StatusProjects	Returns the projects using a given status.
StatusProjectsIssueTypes	Returns the issue types in a project using a given status.
StatusWorkflows	Returns the workflows using a given status.
TimeTrackingProviders	Displays the time tracking providers configured in the Jira instance, which manage estimation and logging.
WorkflowProjects	Returns the projects using a given workflow.
WorkflowProjectsIssueTypes	Returns the issue types using a given workflow within a project.
WorkflowSchemeProjects	Returns the projects using a given workflow schema.

Displays saved filters created by users to retrieve specific issue sets. Not available in Jira Server deployments.

View-Specific Information

SELECT

The Cloud uses the Jira API to process filters based on the Id column. The Cloud processes all other filters client-side.

For example, the following queries are processed server-side:

SELECT * FROM Filters WHERE Id = 10001;

Columns

Name	Type	References	SupportedOperators	Description
Id [KEY]	Integer		=	The unique identifier of the filter used to reference it in the Jira API and user interface.
Name	String			The display name assigned to the filter for easier identification.
JQLExpression	String			The Jira Query Language (JQL) expression that defines the filter's search criteria for issues.
Description	String			A short explanation of what the filter does or what it is intended to find.
OwnerDisplayName	String		=	The display name of the user who created and owns the filter.
Favourite	Boolean			Indicates whether the filter is marked as a favourite by the current user.
FavouritedCount	Integer			The number of users who have marked this filter as a favourite.
SubscriptionsAggregate	String			A list of users who are subscribed to receive notifications when the filter results change.
SharePermissionsAggregate	String			A list of groups or users with whom the filter has been shared.

Returns the users or groups that specific filters are shared with.

View-Specific Information

SELECT

The Cloud uses the Jira API to process filters based on the FilterId and AccountId columns. The Cloud processes all other filters client-side.

For example, the following queries are processed server-side:

SELECT * FROM FiltersUsers WHERE FilterId = '10001';
SELECT * FROM FiltersUsers WHERE FilterId = '10001' AND AccountId = '5b10a2844c20165700ede21g';

Columns

Name	Type	References	SupportedOperators	Description
AccountId [KEY]	String	Users.AccountId	=	The globally unique account ID of the user, which is used across all Atlassian cloud services to identify the user regardless of email or username changes.
FilterId [KEY]	String	Filters.Id	=	The unique identifier of the saved filter that the user has access to or is associated with. Filters define sets of issues based on Jira Query Language (JQL) criteria.
DisplayName	String		=	The name that is shown for the user in the Jira user interface. This is typically their full name and is used in dashboards, issue fields, and mentions.
EmailAddress	String		=	The primary email address associated with the user's Atlassian account. This is used for notifications and account recovery.
Active	Bool			Indicates whether the user is currently active and has permission to log in and interact with Jira.
TimeZone	String			The time zone configured in the user's profile. This setting affects how dates and times are displayed throughout the Jira interface for that user.
Locale	String			The language and regional settings selected by the user, which determine formats for numbers, dates, times, and the UI language.
AccountType	String			The classification of the user account. This can indicate whether the account belongs to a regular user, a third-party application, or an internal system user such as a bot or integration.

Returns the stored version history of a workflow. Jira retains workflow history for 60 days.

Columns

Name	Type	References	SupportedOperators	Description
Id	String	Workflows.Id	=	The unique identifier of the workflow the version belongs to.
Version	Integer			The number of the stored workflow version.
WrittenAt	String			The date when the workflow version was stored, in yyyy-MM-dd HH:mm:ss.SSS format.
IsIntermediate	Boolean			Whether the version is an intermediate workflow state, which Jira sometimes creates during a workflow update.

Retrieves the users and groups associated with specific issue security levels, which control visibility of issues.

View-Specific Information

SELECT

The Cloud uses the Jira API to process filters based on the IssueSecuritySchemeId and IssueSecurityLevelId columns. The Cloud processes all other filters client-side.

For example, the following queries are processed server-side:

SELECT * FROM IssueSecurityLevelMembers WHERE IssueSecuritySchemeId = '10000';
SELECT * FROM IssueSecurityLevelMembers WHERE IssueSecuritySchemeId = '10000' AND IssueSecurityLevelId = '10001';

Columns

Name	Type	References	SupportedOperators	Description
Id [KEY]	String		=,IN	A unique identifier for the issue security level member record.
IssueSecurityLevelId	String		=,IN	The ID of the specific security level assigned to issues to control visibility.
IssueSecuritySchemeId	String		=,IN	The ID of the security scheme that includes this issue security level.
HolderParameter	String			An optional parameter used to identify the permission holder. Recommended when group names might change.
HolderType	String			The type of entity that holds the permission, such as a group, user, or project role.
HolderValue	String			The specific identifier (such as group name or user ID) for the permission holder, used in conjunction with the holder type.

Returns the issue type screen scheme items, mapping issue types to screen schemes within each issue type screen scheme, on Jira Cloud.

View-Specific Information

SELECT

The Cloud uses the Jira API to process filters based on the IssueTypeScreenSchemeId column. The Cloud processes all other filters client-side.

For example, the following queries are processed server-side:

SELECT * FROM IssueTypeScreenSchemes WHERE IssueTypeScreenSchemeId = '10001';

Columns

Name	Type	References	SupportedOperators	Description
IssueTypeScreenSchemeId	String		=,IN	The unique identifier of the issue type screen scheme. Filter on this column to limit the results to specific schemes.
IssueTypeId	String	IssueTypes.Id		The unique identifier of the issue type, or 'default' for the scheme's default mapping.
ScreenSchemeId	String			The unique identifier of the screen scheme associated with the issue type.

Returns the issue type screen scheme associated with a Jira Cloud project.

View-Specific Information

SELECT

The Cloud uses the Jira API to process filters based on the ProjectId column. The Cloud processes all other filters client-side.

For example, the following queries are processed server-side:

SELECT * FROM ProjectIssueTypeScreenSchemes WHERE ProjectId = 10001;

Columns

Name	Type	References	SupportedOperators	Description
Id	String			The ID of the issue type screen scheme.
Name	String			The name of the issue type screen scheme.
Description	String			The description of the issue type screen scheme.
ProjectId	Int	Projects.Id	=	The unique identifier of the project.

Returns a read-only list of the workflows used by a given project.

Columns

Name	Type	References	SupportedOperators	Description
Id	String	Workflows.Id		The unique identifier of the workflow within Jira. This ID is used internally and in API operations to reference the workflow.
ProjectId	Integer	Projects.Id	=	The unique identifier of the project whose workflows are returned. This column is required in the WHERE clause and is echoed back on every row.
Name	String		=,LIKE	The name assigned to the workflow, which typically reflects the process or team it supports (for example, 'Software Development Workflow').
Description	String			A user-defined explanation of what the workflow represents or how it should be used. Helpful for distinguishing between similar workflows.
Created	String			The timestamp indicating when the workflow was initially created.
Updated	String			The date when the workflow was last modified.
CanEdit	Boolean			If the value is 'true', the workflow can be edited. This can be restricted based on permissions or if the workflow is active.
Version	String			A JavaScript Object Notation (JSON) object containing the version of the workflow, used for optimistic locking during updates.
Statuses	String			A JavaScript Object Notation (JSON) array of statuses associated with the workflow.
Scope	String			A JavaScript Object Notation (JSON) object defining the scope of the workflow, such as whether it is global or limited to specific projects.
Transitions	String			A JavaScript Object Notation (JSON) array describing the transitions defined in the workflow. Each entry specifies the source status, target status, and conditions that govern the transition.

Returns a paginated list of screen schemes. Only screen schemes used in classic projects are returned. Each row represents a single screen mapped to a screen scheme.

View-Specific Information

SELECT

The Cloud uses the Jira API to process filters based on the Id column. The Cloud processes all other filters client-side.

For example, the following queries are processed server-side:

SELECT * FROM ScreenSchemes WHERE Id = 10001;

Columns

Name	Type	References	SupportedOperators	Description
Id [KEY]	Integer		=,IN	The unique identifier of the screen scheme.
ScreenId [KEY]	Integer			The unique identifier of the screen mapped to this screen scheme.
ScreenType [KEY]	String			The type of operation the screen is used for (default, create, edit, or view).
Name	String			The name of the screen scheme.
Description	String			The description of the screen scheme.

Returns the fields on a tab of a Jira screen on Jira Cloud. ScreenId and TabId filters are required to query this view.

View-Specific Information

SELECT

The Cloud uses the Jira API to process filters based on the ScreenId and TabId columns. The Cloud processes all other filters client-side.

You must specify a ScreenId and TabId to retrieve results.

For example, the following queries are processed server-side:

SELECT * FROM ScreenTabFields WHERE ScreenId = 10001 AND TabId = 10100;

Columns

Name	Type	References	SupportedOperators	Description
Id	String			The unique identifier of the field (for example, summary or customfield_10001).
Name	String			The name of the field.
ScreenId	Integer	ScreenTabs.ScreenId	=	The unique identifier of the screen. A ScreenId filter is required to query this view.
TabId	Integer	ScreenTabs.Id	=	The unique identifier of the screen tab. A TabId filter is required to query this view.

Returns the tabs of a Jira screen on Jira Cloud. A ScreenId filter is required to query this view.

View-Specific Information

SELECT

The Cloud uses the Jira API to process filters based on the ScreenId column. The Cloud processes all other filters client-side.

You must specify a ScreenId to retrieve results.

For example, the following queries are processed server-side:

SELECT * FROM ScreenTabs WHERE ScreenId = 10001;

Columns

Name	Type	References	SupportedOperators	Description
Id	Integer			The unique identifier of the screen tab.
Name	String			The name of the screen tab.
ScreenId	Integer	ScreenSchemes.ScreenId	=	The unique identifier of the screen. A ScreenId filter is required to query this view.

Returns the projects using a given status.

View-Specific Information

SELECT

The Cloud uses the Jira API to process filters based on the StatusId column. The Cloud processes all other filters client-side.

If StatusId is not specified, the Cloud automatically queries the Statuses table to retrieve the StatusId values. This may result in increased query times.

For example, the following queries are processed server-side:

SELECT * FROM StatusProjects WHERE StatusId = 6;

Columns

Name	Type	References	SupportedOperators	Description
ProjectId	Int	Projects.Id		The unique identifier of the workflow for the given status.
StatusId	Int	Statuses.Id	=	The unique identifier of the status.

Returns the issue types in a project using a given status.

View-Specific Information

SELECT

The Cloud uses the Jira API to process filters based on the ProjectId and StatusId columns. The Cloud processes all other filters client-side.

You must specify the ProjectId and StatusId to retrieve results.

For example, the following queries are processed server-side:

SELECT * FROM StatusProjectsIssueTypes WHERE ProjectId = 10100 AND StatusId = 6;

Columns

Name	Type	References	SupportedOperators	Description
IssueTypeId	String	IssueTypes.Id		The unique identifier of the issue type for the project in the given status
ProjectId	Int	Projects.Id	=	The unique identifier of the project using this status
StatusId	Int	Statuses.Id	=	The unique identifier of the status

Returns the workflows using a given status.

View-Specific Information

SELECT

The Cloud uses the Jira API to process filters based on the StatusId column. The Cloud processes all other filters client-side.

If StatusId is not specified, the Cloud automatically queries the Statuses table to retrieve the StatusId values. This may result in increased query times.

For example, the following queries are processed server-side:

SELECT * FROM StatusWorkflows WHERE StatusId = 6;

Columns

Name	Type	References	SupportedOperators	Description
WorkflowId	String	Workflows.Id		The unique identifier of the workflow for the given status.
StatusId	Int	Statuses.Id	=	The unique identifier of the status.

Displays the time tracking providers configured in the Jira instance, which manage estimation and logging.

Columns

Name	Type	References	SupportedOperators	Description
Key [KEY]	String		=	The internal identifier used to reference the time tracking provider, such as 'Jira'.
Name	String			The user-facing name of the time tracking provider, typically shown in settings and configuration pages.
URL	String			The relative URL path to the configuration page for the time tracking provider. Only available if the provider defines an admin page in its module descriptor.

Returns the projects using a given workflow.

View-Specific Information

SELECT

The Cloud uses the Jira API to process filters based on the WorkflowId column. The Cloud processes all other filters client-side.

If WorkflowId is not specified, the Cloud automatically queries the Workflows table to retrieve the WorkflowId values. This may result in increased query times.

For example, the following queries are processed server-side:

SELECT * FROM WorkflowProjects WHERE WorkflowId = 'jira';

Columns

Name	Type	References	SupportedOperators	Description
ProjectId	Int	Projects.Id		The unique identifier of the project using this workflow.
WorkflowId	String	Workflows.Id	=	The unique identifier of the workflow.

Returns the issue types using a given workflow within a project.

View-Specific Information

SELECT

The Cloud uses the Jira API to process filters based on the ProjectId and WorkflowId columns. The Cloud processes all other filters client-side.

You must specify the ProjectId and WorkflowId to retrieve results.

For example, the following queries are processed server-side:

SELECT * FROM WorkflowProjectsIssueTypes WHERE ProjectId = 10100 AND WorkflowId = 'jira';

Columns

Name	Type	References	SupportedOperators	Description
IssueTypeId	String	IssueTypes.Id		The unique identifier of the issue type for the project in the given workflow.
ProjectId	Int	Projects.Id	=	The unique identifier of the project using this workflow.
WorkflowId	String	Workflows.Id	=	The unique identifier of the workflow.

Returns the projects using a given workflow schema.

View-Specific Information

SELECT

The Cloud uses the Jira API to process filters based on the WorkflowSchemeId column. The Cloud processes all other filters client-side.

If WorkflowSchemeId is not specified, the Cloud automatically queries the WorkflowSchemes table to retrieve the WorkflowSchemeId values. This may result in increased query times.

For example, the following queries are processed server-side:

SELECT * FROM WorkflowSchemeProjects WHERE WorkflowSchemeId = 10418;

Columns

Name	Type	References	SupportedOperators	Description
ProjectId	Int	Projects.Id		The unique identifier of the project using this workflow schema.
WorkflowSchemeId	Int	WorkflowSchemes.Id	=	The unique identifier of the workflow schema.

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

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

CData Cloud - Jira Stored Procedures

Name	Description
ArchiveIssuesJQL	Archives multiple issues based on a Jira Query Language (JQL) query, allowing bulk archival by filter.
CreateArchivedIssuesExport	Generates a CSV file containing all archived issues and sends a download link to the admin user.

Archives multiple issues based on a Jira Query Language (JQL) query, allowing bulk archival by filter.

Procedure-Specific Information

The execution time of this stored procedure may vary depending on the data processed in the Jira task. If execution exceeds the current Timeout limit, an exception is thrown. To prevent this, consider increasing the Timeout connection property as needed for your specific use case.

NOTE: This stored procedure is available only in the Jira Cloud version.

To execute this procedure, enter:

EXECUTE ArchiveIssuesJQL JQL='project = PROJ AND status = Done';

Input

Name	Type	Required	Description
JQL	String	True	The JQL expression used to select the issues to archive. For example, 'project = ABC AND status = Done'.

Result Set Columns

Name	Type	Description
NumberOfIssuesArchived	String	Returns the number of top-level issues that were successfully archived as a result of the JQL query.
NumberOfSubtasksArchived	String	Returns the number of subtasks that were successfully archived as part of the operation.

Generates a CSV file containing all archived issues and sends a download link to the admin user.

Procedure-Specific Information

The execution time of this stored procedure may vary depending on the data processed in the Jira task. If execution exceeds the current Timeout limit, an exception is thrown. To prevent this, consider increasing the Timeout connection property as needed for your specific use case.

NOTE: This stored procedure is available only in the Jira Cloud version.

To execute this procedure, enter:

EXECUTE CreateArchivedIssuesExport Projects='PROJ';

Input

Name	Type	Required	Description
ArchivedDateAfter	String	False	Filters archived issues to include only those that were archived after the specified date.
ArchivedDateBefore	String	False	Filters archived issues to include only those that were archived before the specified date.
IssueTypes#	String	False	Filters archived issues based on one or more specified issue type IDs, such as bug or task.
ArchivedBy#	String	False	Filters archived issues to include only those archived by specific user account IDs.
Projects#	String	False	Filters archived issues to include only those from specific project keys.
Reporters#	String	False	Filters archived issues based on one or more reporter account IDs.
IgnoreTaskResult	String	False	If set to 'true', the stored procedure returns immediately without waiting for the export task to complete. The default value is false.

Result Set Columns

Name	Type	Description
Success	String	Indicates whether the export operation for archived issues was completed successfully.
Error	String	Provides an error message if the export task fails or encounters issues during execution.

Overview

The Jira schema surfaces Jira Core, Jira Software, and Jira administration-related objects as SQL entities for both Jira Cloud and Jira Data Center instances.

• Entities which are available in both Jira Cloud and Jira Data Center instances are listed in the following sections: Tables, Views, and Stored Procedures.

  Even though the tables and views are available in both Jira Cloud and Jira Data Center instances, the set of columns exposed might differ depending on the platform. Refer to the Platform column to determine whether the column is available in your instance.

  • Platform=Common; column is available in both Jira Cloud and Jira Data Center instances.
  • Platform=Cloud; column is available only in Jira Cloud instances.
  • Platform=Server; column is available only in Jira Data Center instances.

• For entities which are available only in Jira Cloud instances, refer to Jira.

The Cloud models the data in Jira as a list of tables in a relational database that can be queried using standard SQL statements.

CData Cloud - Jira Tables

Name	Description
Attachments	Provides access to issue attachments in Jira. Supports querying, inserting, and deleting file attachments associated with issues.
Boards	Manages the agile boards in Jira.
Comments	Manages comments added to issues. Supports creating, reading, updating, and deleting issue comments.
Groups	Lists user groups configured in the Jira instance, including group names and descriptions.
IssueCustomFieldOptions	Creates, modifies, deletes, and queries the configured issue custom field options in Jira.
Issues	Provides full access to Jira issues. Supports creation, updates, deletions, and queries for issue records across all projects.
IssueTypes	Returns the available issue types in Jira.
PermissionGrants	Displays all permission grants defined in permission schemes, including scope and associated roles or groups.
ProjectComponents	Manages components within Jira projects. Supports creating, updating, deleting, and querying project components.
ProjectRoleActors	Manages role assignments within projects. Retrieves and updates users and groups assigned to project roles.
Projects	Provides access to all Jira projects. Supports querying, creating, updating, and deleting project records.
ProjectVersions	Provides access to project version management. Supports querying, creating, updating, and deleting versions.
RoleActors	Manages default role actors, who are automatically assigned to roles when new projects are created.
Roles	Lists all roles configured in Jira, such as 'Service Desk Team' or 'Project Manager'.
Sprints	Manages Jira sprints. Supports creating, modifying, deleting, and querying sprint data from agile boards.
Statuses	Creates, updates, deletes, and queries Statuses in Jira. On Cloud, uses the Status API which supports full CRUD. SELECT without filters returns only GLOBAL-scoped statuses; PROJECT-scoped statuses must be queried by Id or Name. Requires Administer Projects and Administer Jira permissions on Cloud. On Server, queries the read-only legacy endpoint; INSERT, UPDATE, and DELETE are not supported.
Users	Creates, modifies, deletes, and queries the available users in Jira.
Watchers	Manages the available issue watchers in Jira. Supports querying, inserting, and deleting watchers.
Workflows	Manages the available workflows in Jira, which define the sequence of statuses and transitions for issues.
Worklogs	Provides full access to Jira worklogs. Supports creating, updating, deleting, and querying logged work hours on issues.

Provides access to issue attachments in Jira. Supports querying, inserting, and deleting file attachments associated with issues.

Table-Specific Information

SELECT

The Cloud uses the Jira API to process filters based on the Id, IssueId, IssueKey, ProjectId, ProjectName, and JQL columns. The Cloud processes all other filters client-side.

For example, the following queries are processed server-side:

SELECT * FROM Attachments WHERE Id = 10003;
SELECT * FROM Attachments WHERE IssueId = 123;
SELECT * FROM Attachments WHERE ProjectId = 10100;
SELECT * FROM Attachments WHERE JQL = 'created > 2018-01-07';

INSERT

Insert an attachment by specifying the IssueKey and either a FilePath or ContentEncoded content with a Name.

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

Delete an attachment by specifying the Id.

DELETE FROM Attachments WHERE Id = 10003;

Columns

Name	Type	ReadOnly	References	SupportedOperators	Platform	Description
Id [KEY]	Integer	False			Common	The unique numeric identifier of the attachment within the Jira system.
IssueId [KEY]	Integer	False	Issues.Id	=,!=,IN,NOT IN	Common	The internal identifier of the issue that the attachment is associated with.
IssueKey	String	False	Issues.Key	=,!=,IN,NOT IN	Common	The unique key that identifies the issue related to the attachment, such as 'JIRA-101'.
ProjectId	Integer	True	Projects.Id	=,IS,IS NOT,IN,NOT IN	Common	The numeric identifier of the project that contains the issue to which the attachment is linked.
ProjectName	String	True		=,IS,IS NOT,IN,NOT IN	Common	The name of the project that includes the issue where this attachment was added.
IssueCreatedDate	Datetime	True		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The timestamp indicating when the issue associated with this attachment was originally created.
IssueUpdatedDate	Datetime	True		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The timestamp of the most recent update to the issue related to this attachment.
Filename	String	False			Common	The original name of the uploaded file, including the file extension.
AuthorName	String	False			Server	The username of the user who uploaded the attachment.
AuthorDisplayName	String	False	Users.DisplayName		Common	The full display name of the user who uploaded the attachment, typically shown in the Jira UI.
Created	Datetime	True			Common	The date and time when the attachment was added to the issue.
Size	Integer	False			Common	The total file size of the attachment, in bytes.
MimeType	String	False			Common	The Multipurpose Internet Mail Extensions (MIME) type of the file, used to indicate the file format such as 'application/pdf' or 'image/jpeg'.
Content	String	False			Common	The direct Uniform Resource Identifier (URI) to access or download the full content of the attachment from Jira.
Thumbnail	String	False			Common	The URI to a smaller, thumbnail version of the attachment image if available. Typically used for previews.
AuthorAccountId	String	False	Users.AccountId		Cloud	The unique account ID of the user who uploaded the attachment. This ID is consistent across the Atlassian platform.
AuthorKey	String	False			Server	An internal Jira user key that uniquely identifies the user who added the attachment.

Pseudocolumns

Pseudocolumn fields are used in the WHERE clause of SELECT statements and offer more granular control over the data returned from the data source.

Name	Type	Description
JQL	String	A field for specifying a Jira Query Language (JQL) expression to filter attachments by issue context when querying.
FilePath	String	The absolute path to the file on the local machine to be uploaded as an attachment. This column can only be specified in an INSERT statement; it cannot be used in the WHERE clause of a SELECT statement.
ContentEncoded	String	The content of the file to upload, encoded in BASE64 format. Used in scenarios where direct file access is not possible. This column can only be specified in an INSERT statement; it cannot be used in the WHERE clause of a SELECT statement.
Name	String	The name to assign to the uploaded file, including its extension, which appears in the issue's attachment list. This column can only be specified in an INSERT statement; it cannot be used in the WHERE clause of a SELECT statement.

Manages the agile boards in Jira.

Table-Specific Information

SELECT

The Cloud uses the Jira API to process filters based on the Id, ProjectKeyOrId, and Type columns. The Cloud processes all other filters client-side.

For example, the following queries are processed server-side:

SELECT * FROM Boards WHERE Id = 10022;
SELECT * FROM Boards WHERE ProjectKeyOrId = 'QMTH' AND Type = 'scrum';

Note: Valid Type filter values are scrum, kanban, and simple.

INSERT

Insert a board by specifying the following fields:

• Name
• Type
• FilterId

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

Note: Valid input Type values are scrum and kanban.

DELETE

Delete a board by specifying the 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. For READ operations valid values are 'scrum', 'kanban', and 'simple'. For CREATE operations only 'scrum' and 'kanban' are supported.

Pseudocolumns

Pseudocolumn fields are used in the WHERE clause of SELECT statements and offer more granular control over the data returned from the data source.

Name	Type	Description
ProjectKeyOrId	String	The key or the Id of the project the board is located in.
FilterId	String	The Id of a filter that the user has permissions to view. Not supported for team-managed boards.

Manages comments added to issues. Supports creating, reading, updating, and deleting issue comments.

Table-Specific Information

SELECT

You can query this table to retrieve comments posted on all issues.

The Cloud uses the Jira API to process filters based on the IssueId, IssueKey, ProjectId, and ProjectName columns. The Cloud processes all other filters client-side.

For example, the following queries are processed server-side:

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

INSERT

Insert a comment by specifying the IssueId and Body columns.

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

UPDATE

Update a comment by specifying the Id, IssueId, and the columns to update.

UPDATE Comments SET Body = 'Updated Comment' WHERE Id = 10202 AND IssueId = 10003;

ADF Fields (REST_V3)

When the APIVersion connection property is set to REST_V3, the Body_ADF column is available alongside Body. Use Body to insert or update a plain-text value, which the driver wraps into ADF format automatically. Use Body_ADF to supply raw ADF JSON directly.

Insert a comment with a plain-text body (wrapped as ADF automatically):

INSERT INTO Comments (IssueId, Body) VALUES ('10003', 'Plain text comment');

Insert a comment with a raw ADF JSON body:

INSERT INTO Comments (IssueId, Body_ADF) VALUES ('10003', '{"type":"doc","version":1,"content":[{"type":"paragraph","content":[{"type":"text","text":"Raw ADF comment"}]}]}');

Update a comment body using plain text:

UPDATE Comments SET Body = 'Updated comment' WHERE Id = 10202 AND IssueId = 10003;

Update a comment body using raw ADF JSON:

UPDATE Comments SET Body_ADF = '{"type":"doc","version":1,"content":[{"type":"paragraph","content":[{"type":"text","text":"Updated ADF comment"}]}]}' WHERE Id = 10202 AND IssueId = 10003;

NOTE: Specifying both Body and Body_ADF in the same statement results in an error. Use one or the other.

DELETE

Delete a comment by specifying 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 unique identifier assigned to the comment within the Jira system.
IssueId [KEY]	Integer	False	Issues.Id	=,!=,IN,NOT IN	Common	The numeric ID of the issue that the comment is associated with. This ID is internal to Jira.
IssueKey	String	False	Issues.Key	=,!=,IN,NOT IN	Common	The issue key linked to the comment, such as 'PROJ-101', which uniquely identifies the issue in a human-readable format.
ProjectId	Integer	True	Projects.Id	=,IS,IS NOT,IN,NOT IN	Common	The numeric ID of the project that contains the issue the comment belongs to.
ProjectName	String	True		=,IS,IS NOT,IN,NOT IN	Common	The name of the project that includes the issue associated with this comment.
IssueCreatedDate	Datetime	True		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The date and time when the issue linked to this comment was originally created.
IssueUpdatedDate	Datetime	True		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The date and time when the issue related to this comment was last updated.
AuthorDisplayName	String	True	Users.DisplayName		Common	The full display name of the user who originally created the comment.
AuthorEmail	String	True	Users.EmailAddress		Common	The email address associated with the user who created the comment.
UpdateDisplayName	String	True	Users.DisplayName		Common	The full display name of the user who last updated or edited the comment.
UpdateAuthorEmail	String	True	Users.EmailAddress		Common	The email address of the user who most recently modified the comment.
Body	String	False		=,LIKE	Common	The raw content of the comment, typically in plain text or markup, as entered by the author.
Body_ADF	String	False			Cloud	Raw Atlassian Document Format (ADF) JSON value of 'Body'. Available only when the APIVersion connection property is set to REST_V3.
Created	Datetime	True			Common	The timestamp indicating when the comment was first submitted.
Updated	Datetime	True			Common	The timestamp indicating the most recent update to the comment, if applicable.
VisibilityType	String	False			Common	The method used to restrict comment visibility, such as by group or project role.
VisibilityValue	String	False			Common	The specific group name or role name used to control who can view the comment.
AuthorAccountName	String	True			Server	The username of the Jira account that created the comment.
AuthorAccountKey	String	True			Server	The unique Jira user key corresponding to the account that authored the comment.
UpdateAuthorAccountKey	String	True			Server	The unique Jira user key of the account that last modified the comment.
UpdateAuthorAccountName	String	True			Server	The username of the user who most recently edited the comment.
AuthorAccountId	String	True	Users.AccountId		Cloud	The Jira account ID of the user who originally posted the comment. This ID is used in API interactions.
UpdateAuthorAccountId	String	True	Users.AccountId		Cloud	The Jira account ID of the user who most recently updated the comment. This is used for audit tracking.
ItemURL	String	True			Common	A URL pointing directly to the comment, allowing users to open it in the Jira interface.

Pseudocolumns

Pseudocolumn fields are used in the WHERE clause of SELECT statements and offer more granular control over the data returned from the data source.

Name	Type	Description
JQL	String	A Jira Query Language (JQL) expression used to retrieve issues with specific comment criteria, such as author or date range.

Lists user groups configured in the Jira instance, including group names and descriptions.

Table-Specific Information

SELECT

The Cloud uses the Jira API to process filters based on the Name column. The Cloud processes all other filters client-side.

For example, the following queries are processed server-side:

SELECT * FROM Groups WHERE Name <> 'administrator' AND CONTAINS(Name, 'jira');

INSERT

Insert a group by specifying the Name column.

INSERT INTO Groups (Name) VALUES ('engineering-team');

DELETE

Delete a group by specifying the Id or Name column.

• Jira Cloud: Requires the Id column.
• Jira Data Center: Requires the Name column.

DELETE FROM Groups WHERE Name = 'engineering-team';
DELETE FROM Groups WHERE Id = 'dfe32267-633a-421a-a5f8-cc2a4z83e711';

NOTE: To delete a group and transfer its restrictions to another group, refer to the DeleteAndSwapGroup stored procedure.

Columns

Name	Type	ReadOnly	References	SupportedOperators	Platform	Description
Id [KEY]	String	True		=	Cloud	A unique identifier for the group, used internally to reference the group within Jira.
Name [KEY]	False	True		=,!=,CONTAINS	Common	The name of the group as it appears in Jira. This is typically used for permission schemes and role assignments.
Html	String	True			Common	The raw HTML representation of the group object. This may be used in UI rendering or API responses.
LabelsAggregate	String	True			Common	A comma-separated list of labels that are associated with the group. Labels can help categorize or describe the group's function or purpose.

Creates, modifies, deletes, and queries the configured issue custom field options in Jira.

Table-Specific Information

In Jira Cloud, this object is exposed as a table and supports CREATE, UPDATE, and DELETE (CUD) operations. In Jira Data Center, this object is exposed as a view instead of a table, and CUD operations are not supported.

SELECT

Jira Cloud

To execute a query in Jira Cloud, you must specify both the CustomFieldId and the Context.

SELECT * FROM IssueCustomFieldOptions WHERE CustomFieldId = 'customfield_10107' AND Context = 10307;
SELECT * FROM IssueCustomFieldOptions WHERE OptionId = 10000;

Jira Data Center

To execute a query in Jira Data Center, you must specify the CustomFieldId if you are not using the OAuth 2.0 AuthScheme.

SELECT * FROM IssueCustomFieldOptions WHERE CustomFieldId = '10104';
SELECT * FROM IssueCustomFieldOptions WHERE OptionId = 10000;

INSERT

Insert a custom field option by specifying the following fields:

• Name
• CustomFieldId
• Context

INSERT INTO IssueCustomFieldOptions (CustomFieldId, Context, Name) VALUES ('customfield_10107', '10307', 'NewAddedOption');

NOTE: This statement is supported only for Jira Cloud instances.

UPDATE

Update a custom field option by specifying the OptionId, CustomFieldId, and Context in the WHERE clause.

UPDATE [IssueCustomFieldOptions] SET Name = 'NewUpdatedOption123', OptionId = '10866' WHERE CustomFieldId = 'customfield_10107' AND Context = '10307' AND OptionId = '10866';

NOTE: This statement is supported only for Jira Cloud instances.

DELETE

Delete a custom field option by specifying the OptionId, CustomFieldId, and Context in the WHERE clause.

DELETE FROM [IssueCustomFieldOptions] WHERE CustomFieldId = 'customfield_10107' AND Context = '10307' AND OptionId = '10866';

NOTE: This statement is supported only for Jira Cloud instances.

Columns

Name	Type	ReadOnly	References	SupportedOperators	Platform	Description
OptionId [KEY]	String	False		=	Common	The ID of the custom field option.
Name	String	False			Common	The Name of the custom field option.
CustomFieldId	String	False	IssueCustomFields.Id	=	Common	The ID of the custom field.
Context	String	False	CustomFieldContext.Id	=	Cloud	The context of the custom field option.
Disabled	Boolean	False			Common	Indicates whether the option is disabled.

Provides full access to Jira issues. Supports creation, updates, deletions, and queries for issue records across all projects.

Table-Specific Information

Set the IncludeCustomFields connection property to true to dynamically retrieve the Custom Fields columns and values (like Sprint, Epic, etc.) when querying this table.

You can query Issues by using a Jira Query Language (JQL) query as a filter.

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 Cloud uses the Jira API to process filters by translating them to JQL. The Cloud processes all other filters client-side.

For example, the following queries are processed server-side:

SQL and JQL

SELECT * FROM Issues WHERE ProjectName = 'New project' AND JQL = ' status WAS "open" ';
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 costs 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 an issue by specifying the Id and the columns to update.

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: The object must first be created in the Jira application to use it with the Cloud queries. 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: The set command overwrites all the previous objects, meaning that previous entries are discarded, and only the objects specified in the set command are 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"} ] }

An example query:

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

ADF Fields (REST_V3)

When the APIVersion connection property is set to REST_V3, the Description_ADF column is available alongside Description. Use Description to insert or update a plain-text value, which the driver wraps into ADF format automatically. Use Description_ADF to supply raw ADF JSON directly.

Insert a new issue with a plain-text description (wrapped as ADF automatically):

INSERT INTO Issues (ProjectKey, Summary, IssueTypeName, Description) VALUES ('PROJ', 'My Issue', 'Task', 'Plain text description');

Insert a new issue with a raw ADF JSON description:

INSERT INTO Issues (ProjectKey, Summary, IssueTypeName, Description_ADF) VALUES ('PROJ', 'My Issue', 'Task', '{"type":"doc","version":1,"content":[{"type":"paragraph","content":[{"type":"text","text":"Raw ADF description"}]}]}');

Update the description of an issue using plain text:

UPDATE Issues SET Description = 'Updated description' WHERE Id = '10003';

Update the description using raw ADF JSON:

UPDATE Issues SET Description_ADF = '{"type":"doc","version":1,"content":[{"type":"paragraph","content":[{"type":"text","text":"Updated ADF description"}]}]}' WHERE Id = '10003';

NOTE: Specifying both Description and Description_ADF in the same statement results in an error. Use one or the other.

DELETE

Delete an issue by specifying the Id. Subtasks that have not been completed are 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 unique numeric identifier assigned to the issue by Jira.
Key	String	True		=,!=,>,>=,<,<=,IN,NOT IN	Common	The unique key used to reference the issue, typically in the format 'PROJ-123'.
IssueTypeId	String	False		=,!=,IS,IS NOT,IN,NOT IN	Common	The internal identifier of the issue type, such as 'Bug', 'Task', or 'Story'.
IssueTypeName	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The human-readable name of the issue type, such as 'Bug' or 'Task'.
ProjectId	Integer	False		=,!=,IS,IS NOT,IN,NOT IN	Common	The unique identifier of the project that contains the issue.
ProjectName	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The name of the project that the issue belongs to.
ProjectKey	String	False		=,!=,IS,IS NOT,IN,NOT IN	Common	The unique key of the project, often used as the prefix in issue keys (for example, 'PROJ').
ParentId	Int	False		=,!=,IN,NOT IN	Common	The ID of the parent issue, used when the issue is a subtask.
ParentKey	String	True		=,!=,IN,NOT IN	Common	The key of the parent issue, used when the issue is a subtask.
ParentIssueTypeId	String	True			Common	The issue type ID of the parent issue, providing insight into the parent issue's classification.
ParentIssueTypeName	String	True			Common	The issue type name of the parent issue, such as 'Story' or 'Task'.
ParentStatusId	String	True			Common	The status ID of the parent issue, indicating the workflow status.
ParentStatusName	String	True			Common	The status name of the parent issue, such as 'In Progress' or 'Done'.
ResolutionId	String	True		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The resolution ID of the issue, representing how the issue was resolved.
ResolutionName	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The human-readable name of the issue's resolution, such as 'Fixed' or 'Duplicate'.
ResolutionDescription	String	True			Common	A detailed explanation of the resolution applied to the issue.
ResolutionDate	Datetime	True		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The date and time the issue was marked as resolved.
Workratio	Long	True		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	A percentage value comparing the amount of time spent versus the estimated effort.
LastViewed	Datetime	True		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The date and time the issue was last viewed by the current user.
WatchCount	Int	True		=,!=,>,>=,<,<=,IN,NOT IN	Common	The number of users who are watching the issue for updates.
IsWatching	Bool	True			Common	Indicates whether the current authenticated user is watching this issue.
Created	Datetime	True		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The timestamp when the issue was initially created in Jira.
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 name of the priority level, such as 'High', 'Medium', or 'Low'.
TimeSpentSeconds	Long	True			Common	The total time spent working on the issue, measured in seconds.
TimeSpent	String	True		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The total time spent on the issue, formatted as a human-readable string (for example, '2h 30m').
RemainingEstimateSeconds	Long	True			Common	The remaining estimated effort required to complete the issue, in seconds.
RemainingEstimate	String	False		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The remaining time estimate formatted as a string (for example, '1d 2h').
OriginalEstimateSeconds	Long	True			Common	The original estimated time required to complete the issue, in seconds.
OriginalEstimate	String	False		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The original time estimate displayed as a formatted string (for example, '3d').
AggregateTimeSpent	Long	True			Common	The total time spent across the issue and its subtasks, in seconds.
AggregateTimeOriginalEstimate	Long	True			Common	The total of the original time estimates for the issue and all its subtasks.
AggregateTimeEstimate	Long	True			Common	The current total remaining estimate for the issue and all of its subtasks.
AssigneeDisplayName	String	True			Common	The full display name of the user assigned to the issue.
AssigneeKey	String	True		=,!=,IS,IS NOT,IN,NOT IN	Server	[DEPRECATED] The key identifier of the assignee. Use AssigneeAccountId instead.
AssigneeAccountId	String	False	Users.AccountId	=,!=,IS,IS NOT,IN,NOT IN	Cloud	The Atlassian account ID of the user assigned to the issue.
AssigneeName	String	False		=,!=,IS,IS NOT,IN,NOT IN	Server	The username of the assignee, used in older Jira instances.
AssigneeEmail	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The email address of the user assigned to the issue.
Updated	Datetime	True		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The timestamp of the last update made to the issue.
StatusId	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The internal identifier of the issue's current workflow status.
StatusName	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The name of the issue's status, such as 'To Do', 'In Progress', or 'Done'.
StatusCategoryId	Integer	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The category ID associated with the issue status, used to group similar workflow statuses.
StatusCategoryKey	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The key used to identify the status category, such as 'new', 'indeterminate', or 'done'.
StatusCategoryName	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The display name of the status category, used to group workflow states.
Description	String	False		=,LIKE,IS,IS NOT,EMP_EQ	Common	A detailed explanation of the issue's problem, task, or request.
Description_ADF	String	False			Cloud	Raw Atlassian Document Format (ADF) JSON value of 'Description'. Available only when the APIVersion connection property is set to REST_V3.
Summary	String	False		=,LIKE,IS,IS NOT,EMP_EQ	Common	A brief summary or title that provides an overview of the issue.
CreatorDisplayName	String	True			Common	The full display name of the user who created the issue.
CreatorName	String	True		=,!=,IS,IS NOT,IN,NOT IN	Server	The username of the issue creator, typically used in legacy Jira systems.
CreatorAccountId	String	True	Users.AccountId	=,!=,IS,IS NOT,IN,NOT IN	Cloud	The Atlassian account ID of the user who created the issue.
CreatorKey	String	True		=,!=,IS,IS NOT,IN,NOT IN	Server	[DEPRECATED] The key identifier for the issue creator. Use CreatorAccountId instead.
CreatorEmail	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The email address of the user who created the issue.
ReporterDisplayName	String	True			Common	The full display name of the user who reported the issue.
ReporterName	String	False		=,!=,IS,IS NOT,IN,NOT IN	Server	The username of the reporter, used in legacy Jira systems.
ReporterKey	String	True		=,!=,IS,IS NOT,IN,NOT IN	Server	[DEPRECATED] The key identifier for the reporter. Use ReporterAccountId instead.
ReporterAccountId	String	False	Users.AccountId	=,!=,IS,IS NOT,IN,NOT IN	Cloud	The Atlassian account ID of the user who reported the issue.
ReporterEmail	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The email address of the user who reported the issue.
AggregateProgress	Long	True			Common	The total progress made on the issue, including progress on all subtasks, measured in seconds.
TotalProgress	Long	True			Common	The total estimated time for the issue and all its subtasks.
Votes	Int	True		=,!=,>,>=,<,<=,IN,NOT IN	Common	The number of users who have voted for this issue as being important or needing attention.
HasVotes	Bool	True			Common	Indicates whether the issue has any user votes. Returns 'true' if votes exist, otherwise 'false'.
DueDate	Date	False		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The due date by which the issue is expected to be completed.
Labels	String	False		=,!=,IS,IS NOT,IN,NOT IN	Common	A comma-separated list of labels applied to the issue for categorization or filtering.
Environment	String	False		=,LIKE,IS,IS NOT,EMP_EQ	Common	Describes the technical or physical environment relevant to the issue (such as OS, browser, or hardware).
Environment_ADF	String	False			Cloud	Raw Atlassian Document Format (ADF) JSON value of 'Environment'. Available only when the APIVersion connection property is set to REST_V3.
SecurityLevel	String	False		=,!=,IS,IS NOT,IN, NOT IN	Common	The security level applied to the issue, restricting visibility to a defined user group.
FixVersionsAggregate	String	False			Common	A comma-separated list of versions in which the issue is or is resolved.
ComponentsAggregate	String	False			Common	A list of project components that this issue is associated with.
IssueLinksAggregate	String	False			Common	A list of links to other issues that relate to or are dependent on this issue.
AffectedVersionsAggregate	String	False			Common	A list of versions affected by this issue.
SubtasksAggregate	String	True			Common	A collection of subtasks associated with the issue.
CustomField1	Any	False			Common	A user-defined custom field for the issue. Custom fields appear when 'IncludeCustomFields' is enabled.
CustomField2	Any	False			Common	Another user-defined custom field available when 'IncludeCustomFields' is set to 'true'.
ItemURL	String	True			Common	A browsable URL linking directly to the issue in the Jira web interface.

Pseudocolumns

Pseudocolumn fields are used in the WHERE clause of SELECT statements and offer more granular control over the data returned from the data source.

Name	Type	Description
JQL	String	Used to filter and search issues using Jira Query Language (JQL).
Attachments	String	Used to query issues that have attachments. Can be filtered using IS NULL or IS NOT NULL.
Comments	String	Search for issues containing specific comment text. On Jira Cloud, the LIKE operator supports only the StartsWith pattern. On Server, it also supports EndsWith and Contains.
Category	String	Allows filtering of issues based on the category of the associated project.
SprintId	Integer	Filters issues by the numeric ID of the sprint they belong to.
SprintName	String	Filters issues by the name of the sprint they are part of.
Filter	String	Queries issues using the name or ID of a saved filter.

Returns the available issue types in Jira.

Table-Specific Information

SELECT

The Cloud uses the Jira API to process filters based on the Id column. The Cloud processes all other filters client-side.

For example, the following queries are processed server-side:

SELECT * FROM IssueTypes WHERE Id = 3;

INSERT

Insert an issue type by specifying the Name column.

INSERT INTO IssueTypes (Name, Description, Subtask) VALUES ('Issue type name 3', 'test description', false);

UPDATE

Update an issue type by specifying the Id and the columns to update.

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

DELETE

Delete an issue type by specifying the 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.

Displays all permission grants defined in permission schemes, including scope and associated roles or groups.

Table-Specific Information

SELECT

Retrieves permission grants defined in Jira permission schemes.

The Cloud uses the Jira API to process filters based on the PermissionSchemeId and Id columns. The Cloud processes all other filters client-side.

You must specify a PermissionSchemeId to retrieve results. If PermissionSchemeId is not specified, the Cloud automatically queries the PermissionSchemes table to retrieve the relevant scheme identifiers. This may result in increased query times.

For example, the following queries are processed server-side:

SELECT * FROM PermissionGrants WHERE PermissionSchemeId = '10216';
SELECT * FROM PermissionGrants WHERE PermissionSchemeId = '10216' AND Id = '13406';

INSERT

Insert a permission grant in a permission scheme by specifying PermissionSchemeId, Permission, and the holder properties: HolderType and one of the following, depending on the Jira platform:

• Jira Cloud: HolderParameter or HolderValue
• Jira Data Center: HolderParameter

INSERT INTO PermissionGrants (PermissionSchemeId, Permission, HolderType, HolderParameter) VALUES ('10216', 'WORK_ON_ISSUES', 'group', 'engineering-team');
INSERT INTO PermissionGrants (PermissionSchemeId, Permission, HolderType, HolderValue) VALUES ('10216', 'WORK_ON_ISSUES', 'group', '6d595024-bfed-4d89-8faa-7068649103c5');

DELETE

Delete a permission grant from a permission scheme by specifying PermissionSchemeId and Id.

DELETE FROM PermissionGrants WHERE PermissionSchemeId = '10000' AND Id = '10500';

Columns

Name	Type	ReadOnly	References	SupportedOperators	Platform	Description
PermissionSchemeId [KEY]	String	False	PermissionSchemes.Id	=	Common	The unique identifier of the permission scheme that contains the granted permission.
Id [KEY]	String	True		=	Common	The unique identifier for the specific permission grant entry.
Permission	String	False	Permissions.Key		Common	The specific Jira permission that is being granted, such as 'Browse Projects' or 'Edit Issues'.
HolderParameter	String	False			Common	A value that identifies the permission holder. This can be a group name, project role, or user identifier, depending on the holder type.
HolderType	String	False			Common	The category of entity receiving the permission. This could be a user, group, project role, or other valid Jira permission holder.
HolderValue	String	False			Cloud	The identifier used by Jira to apply the permission to the selected holder type, for example a group name or project role ID.

Manages components within Jira projects. Supports creating, updating, deleting, and querying project components.

Table-Specific Information

SELECT

The Cloud uses the Jira API to process filters based on the Id, ProjectId, and ProjectKey columns. The Cloud processes all other filters client-side.

For example, the following queries are processed server-side:

SELECT * FROM ProjectComponents WHERE Id = 10022;
SELECT * FROM ProjectComponents WHERE ProjectId = 10027;
SELECT * FROM ProjectComponents WHERE ProjectKey = 'QMTH';

INSERT

Insert a component by specifying the ProjectKey and Name columns.

INSERT INTO ProjectComponents (ProjectKey, Name, AssigneeType) VALUES ('COB', 'Testing Component', 'PROJECT_LEAD');

UPDATE

Update a component by specifying the Id column and the columns to update.

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

DELETE

Delete a component by specifying the Id.

DELETE FROM ProjectComponents WHERE Id = 10065;

Columns

Name	Type	ReadOnly	References	SupportedOperators	Platform	Description
Id [KEY]	Integer	True		=	Common	A unique identifier for the project component, used to reference it in Jira.
ProjectId	Integer	True	Projects.Id	=,IN	Common	The internal ID of the project to which the component belongs.
ProjectKey	String	False		=,IN	Common	The unique key of the project associated with the component, typically used in issue keys.
Name	String	False			Common	The name assigned to the project component, used to categorize and organize issues.
Description	String	False			Common	A detailed explanation of the component's purpose or scope within the project.
LeadDisplayName	String	True			Common	The display name of the user designated as the lead for this component, often responsible for overseeing related work.
LeadKey	String	False			Server	The unique key identifying the user who is the lead of this component.
LeadAccountId	String	False			Cloud	The account ID of the component's lead.
AssigneeType	String	False			Common	Indicates the default assignee type for issues filed under this component, such as 'Project Lead' or 'Component Lead'. The allowed values are PROJECT_DEFAULT, COMPONENT_LEAD, PROJECT_LEAD, UNASSIGNED.
AssigneeDisplayName	String	True			Common	The display name of the user who is assigned issues by default for this component.
AssigneeKey	String	True			Server	The user key of the default assignee for issues in this component.
AssigneeAccountId	String	False	Users.AccountId		Cloud	The account ID of the component's default assignee.
IsAssigneeTypeValid	Boolean	True			Common	Indicates whether the specified assignee type is valid in the context of the project.

Manages role assignments within projects. Retrieves and updates users and groups assigned to project roles.

Table-Specific Information

SELECT

The Cloud uses the Jira API to process filters based on the ProjectId, ProjectKey, and RoleId columns. The Cloud processes all other filters client-side. You must specify a ProjectId, ProjectKey, or RoleId to retrieve results.

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';
SELECT * FROM ProjectRoleActors WHERE RoleId = '10329';
SELECT * FROM ProjectRoleActors WHERE RoleId = '10329' AND ProjectId = '10650';
SELECT * FROM ProjectRoleActors WHERE RoleId = '10329' AND ProjectId IN ('10650', '10651', '10652');
SELECT * FROM ProjectRoleActors WHERE RoleId IN ('10329', '10330', '10331') AND ProjectId = '10650';

INSERT

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

Jira Data Center

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

Jira Cloud

Delete an actor from a role by specifying the project, role, and the user's account Id, group name, or group 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';

Jira Data Center

Delete an actor from a role by specifying the project, role, and the user's Name or group 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	True			Common	A unique identifier for the role actor, which represents either a user or a group assigned to a project role.
DisplayName	String	True			Common	The display name of the role actor. For users, this can reflect privacy settings and return a masked or alternative value instead of the actual name.
Type	String	True			Common	The type of role actor, such as 'atlassian-user-role-actor' for users or 'atlassian-group-role-actor' for groups.
Name	String	True			Server	The name of the project role that this actor is associated with.
AvatarUrl	String	True			Common	The URL to the avatar image representing the role actor (user or group).
ActorUserAccountId	String	False	Users.AccountId		Cloud	The unique account ID of the user acting in the role, consistent across all Atlassian products.
ActorGroupName	String	False			Cloud	The name of the group acting in the role. As group names can change, groupId is recommended for reliable identification.
ActorGroupDisplayName	String	True			Cloud	The human-readable display name of the group acting in the role.
ActorGroupGroupId	String	False			Cloud	The ID of the group associated with the project role.
RoleId	Long	False	Roles.Id	=,IN	Common	The ID of the project role that the actor is assigned to.
ProjectId	Integer	False	Projects.Id	=,IN	Common	The numeric ID of the project in which the role actor is defined.
ProjectKey	String	False	Projects.Key	=,IN	Common	The unique key of the project associated with the project role.

Pseudocolumns

Pseudocolumn fields are used in the WHERE clause of SELECT statements and offer more granular control over the data returned from the data source.

Name	Type	Description
UserName	String	Used in Server platform only for Insert/Delete operations. Specifies the username of the user to be added or removed from the project role.
GroupName	String	Used in Server platform only for Insert/Delete operations. Specifies the name of the group to be added or removed from the project role.

Provides access to all Jira projects. Supports querying, creating, updating, and deleting project records.

Table-Specific Information

SELECT

The Cloud uses the Jira API to process filters based on the Id, Key, Name, ProjectTypeKey, and ProjectCategoryId columns when connected to Jira Cloud. The Cloud processes all other filters client-side.

For example, the following queries are processed server-side:

SELECT * FROM Projects WHERE Id = 10000;

The Jira Cloud API also supports ORDER BY for the Key, Name, LeadDisplayName, and ProjectCategoryName columns.

SELECT * FROM Projects ORDER BY Name ASC;

INSERT

Insert a project by specifying the following fields:

• Key
• Name
• LeadName
• ProjectTypeKey

INSERT INTO Projects (Key, Name, LeadName, ProjectTypeKey, Description) VALUES ('EXE4', 'Inserted Project 4', 'admin', 'business', 'New business project.');

UPDATE

Update a project by specifying the Id or Key column and the columns to update.

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

DELETE

Delete a project by specifying the Id or Key column.

DELETE FROM Projects WHERE Key = 'EXE4';

Columns

Name	Type	ReadOnly	References	SupportedOperators	Platform	Description
Id [KEY]	Int	True		=	Common	A unique numeric identifier assigned to the project within the Jira instance. This ID is used internally and in API requests to reference the project.
Key	String	False		=	Common	The short, unique project key used as a prefix for issue identifiers (such as 'PROJ-123'). It is typically assigned when the project is created and used in search queries and issue references.
Name	String	False			Common	The full display name of the project as seen in the Jira UI. This is the main title used to identify the project in dashboards, boards, and menus.
Description	String	False			Common	A free-text description that provides an overview of the project's purpose, goals, stakeholders, or context. Often used to help team members and collaborators understand the scope of the project.
LeadEmailAddress	String	True	Users.EmailAddress		Common	The email address of the user assigned as the project lead. The project lead is typically responsible for managing the project and receives notifications by default.
LeadDisplayName	String	True	Users.DisplayName		Common	The display name (as shown in the Jira UI) of the user designated as the lead for the project.
ComponentsAggregate	String	True			Common	A comma-separated list of all components configured in the project. Components are used to categorize issues within a project and can represent subsystems, teams, or functional areas.
IssueTypesAggregate	String	True			Common	A comma-separated list of the issue types available in the project, such as 'Bug', 'Task', 'Story', 'Epic', or custom issue types. Each issue type defines a distinct kind of work item.
Url	String	True			Common	A user-defined web URL that can be associated with the project for external references, documentation, or integration. This field is optional.
Email	String	True			Common	The email address associated with the project, typically used for contact or notification purposes. This value can be customized per project.
AssigneeType	String	False			Common	Indicates the default assignment strategy for new issues created in the project. Common values include 'UNASSIGNED' or 'PROJECT_LEAD'.
VersionsAggregate	String	True			Common	A comma-separated list of version names configured in the project. Versions can be used for release planning and tracking progress toward a release milestone.
RolesAggregate	String	True			Common	A comma-separated list of roles defined in the project, such as 'Administrator', 'Developer', or 'Viewer'. Each role controls the permissions and responsibilities of users in the project.
ProjectKeysAggregate	String	True			Common	A list of all project keys historically associated with this project. This includes previous keys if the project key has ever been changed.
ProjectCategoryId	String	False			Common	The identifier of the project category to which this project belongs. Categories group projects into logical sets for filtering and reporting.
ProjectCategoryName	String	True			Common	The name of the category assigned to this project. This is used to organize and display projects by type or purpose in the Jira interface.
ProjectCategoryDescription	String	True			Common	A free-text description of the project category, used to provide additional context or clarification for users browsing categorized projects.
ProjectTypeKey	String	False			Common	The key representing the type of project. Common values include 'software', 'service_desk', or 'business', which determine the features and interface available.
LeadAccountId	String	False	Users.AccountId		Cloud	The unique Atlassian account ID of the project lead. This identifier is consistent across all Atlassian Cloud products and replaces deprecated user keys.
LeadAccountKey	String	True	Users.Key		Server	[DEPRECATED] A legacy identifier used to represent the project lead, typically replaced by LeadAccountId in modern Jira Cloud instances.
LeadAccountName	String	False	Users.Name		Server	[DEPRECATED] The legacy username of the project lead, used in older Jira Server installations. Replaced by account ID in Jira Cloud.
Simplified	Boolean	True			Cloud	Indicates whether the project is team-managed (formerly called next-gen) or company-managed. Team-managed projects allow simplified configuration by project admins.
ItemURL	String	True			Common	A direct URL to the project in the Jira web interface, allowing users to quickly navigate to the project's main overview page.

Provides access to project version management. Supports querying, creating, updating, and deleting versions.

Table-Specific Information

SELECT

The Cloud uses the Jira API to process filters based on the Id, ProjectId, and ProjectKey columns. The Cloud processes all other filters client-side.

For example, the following queries are processed server-side:

SELECT * FROM ProjectVersions WHERE Id = 10022;
SELECT * FROM ProjectVersions WHERE ProjectId = 10022;
SELECT * FROM ProjectVersions WHERE ProjectKey = 'QMTH';

INSERT

Insert a version by specifying the Name and either the ProjectId or ProjectKey columns.

INSERT INTO ProjectVersions (ProjectId, Name, Description, ReleaseDate, StartDate) VALUES ('10000', 'HR Component', 'Example version description', '2018-04-04', '2018-02-02');

UPDATE

Update a version by specifying the Id column and the columns to update.

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

DELETE

Delete a version 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 unique identifier for the version within the project.
ProjectId	Integer	False	Projects.Id	=,IN	Common	The unique identifier of the project to which this version belongs.
ProjectKey	String	False		=,IN	Common	The key of the project associated with this version, used to reference the project in queries and URLs.
Name	String	False			Common	The name assigned to the version, often representing a milestone or release number such as '1.0.0' or 'Q4 2024'.
Description	String	False			Common	A textual description providing context or notes about what the version includes or represents.
Released	Boolean	False			Common	Indicates whether the version has been officially released. If the value is 'true', the version is considered complete and available.
ReleaseDate	Date	False			Common	The official release date of the version, typically used for scheduling and reporting purposes.
StartDate	Date	False			Common	The planned start date for work associated with this version. Optional and used mainly for roadmap planning.
UserStartDate	Date	False			Common	The user-defined date indicating when the work on this version is expected to begin. Used for manual planning.
UserReleaseDate	Date	False			Common	The user-defined date indicating when the work on this version is expected to end. Helps track expectations versus actuals.
Overdue	Boolean	True			Common	Indicates whether the version's release date has passed without being marked as released.
Archived	Boolean	False			Common	Indicates whether the version is archived. Archived versions are no longer active and are typically hidden from default views.

Manages default role actors, who are automatically assigned to roles when new projects are created.

Table-Specific Information

SELECT

The Cloud uses the Jira API to process filters based on the RoleId column. The Cloud processes all other filters client-side.

For example, the following queries are processed server-side:

SELECT * FROM RoleActors WHERE RoleId = '10002';

INSERT

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

Jira Data Center

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

Jira Cloud

Delete a default actor from a role by specifying the RoleId and the user's account Id, group name, or group 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';

Jira Data Center

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 unique identifier assigned to the role actor, used to distinguish users or groups associated with a Jira project role.
DisplayName	String	False			Common	The display name of the user or group associated with the project role. This can differ based on the user's privacy settings.
Type	String	False			Common	The type of role actor, such as 'atlassian-user-role-actor' for users or 'atlassian-group-role-actor' for groups.
AvatarUrl	String	False			Common	The URL to the avatar image representing the user or group in the UI.
ActorUserAccountId	String	False	Users.AccountId		Cloud	The Atlassian account ID of the user associated with the project role. This ID uniquely identifies the user across all Atlassian products.
ActorGroupName	String	False	Groups.Name		Cloud	The name of the group associated with the project role. Since group names can change, group IDs are recommended for stable identification.
ActorGroupDisplayName	String	False			Cloud	The human-readable display name of the group that acts in the role.
ActorGroupGroupId	String	False	Groups.Id		Cloud	The stable ID of the group associated with the project role.
Name	String	False			Server	The name of the Jira role that this actor is assigned to, such as 'Developers' or 'Administrators'.
RoleId	Long	False	Roles.Id	=,IN	Common	The unique identifier for the Jira role that the actor is assigned to.

Pseudocolumns

Pseudocolumn fields are used in the WHERE clause of SELECT statements and offer more granular control over the data returned from the data source.

Name	Type	Description
UserKey	String	Used only on Jira Server. The key of the user to be inserted or removed from the project role.
GroupName	String	Used only on Jira Server. The name of the group to be inserted or removed from the project role.

Lists all roles configured in Jira, such as 'Service Desk Team' or 'Project Manager'.

Table-Specific Information

SELECT

The Cloud uses the Jira API to process filters based on the Id column. The Cloud processes all other filters client-side.

For example, the following queries are processed server-side:

SELECT * FROM Roles WHERE Id = '10002';

INSERT

Insert a role by specifying the Name column.

INSERT INTO Roles(Name, Description) VALUES ('Custom Role 1', 'My custom role 1.')

UPDATE

Update a role by specifying the Id column and the columns to update.

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 unique identifier of the role within the Jira instance.
Name	String	False			Common	The name of the project role, such as 'Developers', 'Administrators', or 'Users'.
Description	String	False			Common	A textual explanation of the purpose or responsibilities associated with the role.
Actors	String	False			Common	A list of users and groups assigned to this role, either by default or per project.
Scope	String	False			Cloud	Defines the scope at which the role is applicable, such as global or project-specific.
IsAdmin	Boolean	False			Cloud	Indicates whether this role is designated as the administrator role for the associated project.
IsDefault	Boolean	False			Cloud	Indicates whether this role is automatically assigned to users by default for new projects.

Manages Jira sprints. Supports creating, modifying, deleting, and querying sprint data from agile boards.

Table-Specific Information

SELECT

To retrieve a list of sprints in your Jira account, you can query the BoardSprints view.

The Cloud uses the Jira API to process filters based on the Id column. The Cloud processes all other filters client-side.

For example, the following queries are processed server-side:

SELECT * FROM Sprints WHERE Id = 122;
SELECT * FROM Sprints WHERE Id IN ('1', '2', '3');

INSERT

Insert a sprint by specifying the OriginBoardId and Name columns.

INSERT INTO Sprints (OriginBoardId, Name, Goal, StartDate, EndDate) VALUES ('1', 'Inserted Sprint', 'Complete target', '2018-02-02', '2018-04-04');

UPDATE

Update a sprint by specifying the Id and the columns to update.

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

DELETE

Delete 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	A unique numeric identifier assigned to the sprint. Used internally to reference the sprint in Jira and API calls.
Name	String	False			Common	The name given to the sprint, typically reflecting its time frame or theme. Helps teams identify and organize sprints easily.
State	String	False		=	Common	The current state of the sprint. Valid states include 'future' (not started), 'active' (in progress), and 'closed' (completed). States transition in this strict order. The allowed values are future, active, closed.
Goal	String	False			Common	A short statement describing the objective of the sprint. Helps align the team on what the sprint is intended to achieve.
OriginBoardId	Integer	False	Boards.Id		Common	[DEPRECATED] The ID of the agile board from which the sprint was created. Use BoardId instead. This column reports the same board and is scheduled for removal in a future release.
StartDate	Datetime	False			Common	The date and time when the sprint was officially started. Typically marks the beginning of the development cycle.
EndDate	Datetime	False			Common	The planned date and time when the sprint is expected to end. This can be used to calculate sprint duration and track timelines.
CompleteDate	Datetime	True			Common	The actual date and time when the sprint was marked as completed in Jira. Useful for reporting and retrospective analysis.
BoardId	Integer	False	Boards.Id	=	Common	The ID of the agile board the sprint originated from. Required when creating a sprint. This value is set at creation and cannot be changed. Filtering on this column restricts the query to a single board.

Creates, updates, deletes, and queries Statuses in Jira. On Cloud, uses the Status API which supports full CRUD. SELECT without filters returns only GLOBAL-scoped statuses; PROJECT-scoped statuses must be queried by Id or Name. Requires Administer Projects and Administer Jira permissions on Cloud. On Server, queries the read-only legacy endpoint; INSERT, UPDATE, and DELETE are not supported.

View-Specific Information

SELECT

The Cloud uses the Jira API to process filters based on the Id and Name columns. Searches on the Name column are case sensitive. The Cloud processes all other filters client-side.

For example, the following queries are processed server-side:

SELECT * FROM Statuses WHERE Id = '10003';

Columns

Name	Type	ReadOnly	References	SupportedOperators	Platform	Description
Id [KEY]	String	False		=,IN	Cloud	The unique integer identifier for the status. Required for UPDATE and DELETE; automatically assigned by Jira on INSERT.
Id [KEY]	Integer	False		=,IN	Server	The unique integer identifier for the status.
Name	String	False		=,IN	Common	The display name of the status, such as 'To Do', 'In Progress', or 'Done'. Shown in issue views and workflows. Required for INSERT. Supports filtering with = and IN operators.
Description	String	False			Common	A description of the status that provides more detail about its intended use or purpose in the workflow.
CategoryId	Integer	False			Server	The ID of the status category that this status belongs to. Categories group similar statuses, such as all done states.
CategoryKey	String	False			Cloud	The status category key. Use TODO, IN_PROGRESS, or DONE when inserting or updating. Required for INSERT.
CategoryKey	String	False			Server	The status category key. Common values are 'new', 'indeterminate', or 'done'.
Scope	String	False			Cloud	The scope of the status. Possible values are GLOBAL for instance-wide statuses or PROJECT for project-specific statuses. Required for INSERT. Read-only after creation.
ScopeProjectId	String	False			Cloud	The Id of the project that the status is scoped to. Only applicable when Scope is PROJECT. Read-only after creation.

Creates, modifies, deletes, and queries the available users in Jira.

Table-Specific Information

SELECT

The Cloud applies filters server-side only when supported for the specific column and operator, as documented in the Columns section. All other filters are processed client-side.

Jira Cloud

The Jira Cloud API has no support for filtering by DisplayName and EmailAddress. These filters are processed client-side.

The only server-side supported filter is filtering by AccountId.

For example, the following queries are processed server-side:

SELECT * FROM Users WHERE AccountId = '712020:fcd95543-59da-42bc-a0f2-4ab4265f07d4';

Jira Data Center

The Jira Data Center API supports filtering by DisplayName and EmailAddress.

For example, the following queries are processed server-side:

SELECT * FROM Users WHERE DisplayName = 'JohnDoe';
SELECT * FROM Users WHERE EmailAddress = '[email protected]';

Filtering by Key is also supported server-side.

For example, the following queries are processed server-side:

SELECT * FROM Users WHERE Key = 'JIRAUSER10472';

NOTE: In Jira Data Center version 10, due to an API limitation, it is not possible to retrieve more than the first 100 users returned by the Jira Data Center API.

INSERT

Insert a user by specifying the EmailAddress column.

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

UPDATE

Update a user by specifying the Key when connected to a Data Center instance.

UPDATE Users SET DisplayName = 'TestInsertUpdated' WHERE Key = 'USR1';

NOTE: This statement is supported only for Jira Data Center instances.

DELETE

Delete a user by specifying the Key when connected to a Data Center instance, or the AccountId when connected to a Cloud instance.

DELETE FROM Users WHERE Key = 'USR1';
DELETE FROM Users WHERE AccountId = 'USR1';

Columns

Name	Type	ReadOnly	References	SupportedOperators	Platform	Description
AccountId [KEY]	String	True		=	Cloud	The accountId of the user, which uniquely identifies the user across all Atlassian products.
Key [KEY]	String	True			Server	The key of the user.
Name	String	False			Server	The name of the user.
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.
AccountType	String	False			Server	The account type of the user.
Products	String	False			Server	A delimited string of Jira products to which the user has access, for example, jira-core,jira-servicedesk,jira-product-discovery,jira-software.

Manages the available issue watchers in Jira. Supports querying, inserting, and deleting watchers.

Table-Specific Information

SELECT

The Cloud uses the Jira API to process filters based on the IssueId and IssueKey columns. The Cloud processes all other filters client-side.

You must specify an IssueId or IssueKey to retrieve results.

For example, the following queries are processed server-side:

SELECT * FROM Watchers WHERE IssueId = '10100';
SELECT * FROM Watchers WHERE IssueKey = 'PRJ-1';

INSERT

Adds a new watcher to an existing issue.

Jira Cloud

To add a new watcher to a specific issue, you must specify the AccountId of the user and either the IssueId or IssueKey of the issue.

INSERT INTO Watchers (IssueId, AccountId) VALUES (19393, '63acbf336f068efec8f6e0c3');
INSERT INTO Watchers (IssueKey, AccountId) VALUES ('CD-352', '63acbf336f068efec8f6e0c3');

Jira Data Center

To add a new watcher to a specific issue, you must specify the Name of the user and either the IssueId or IssueKey of the issue.

INSERT INTO Watchers (IssueId, Name) VALUES (19393, 'CDataUser');
INSERT INTO Watchers (IssueKey, Name) VALUES ('CD-352', 'CDataUser');

DELETE

Removes a watcher from an existing issue.

Jira Cloud

To remove a watcher from a specific issue, you must specify the AccountId of the user and either the IssueId or IssueKey of the issue.

DELETE FROM Watchers WHERE IssueId = 19393 AND AccountId = '63acbf336f068efec8f6e0c3';
DELETE FROM Watchers WHERE IssueKey = 'CD-352' AND AccountId = '63acbf336f068efec8f6e0c3';

Jira Data Center

To remove a watcher from a specific issue, you must specify the Name of the user and either the IssueId or IssueKey of the issue.

DELETE FROM Watchers WHERE IssueId = 19393 AND Name = 'CDataUser';
DELETE FROM Watchers WHERE IssueKey = 'CD-352' AND Name = 'CDataUser';

Columns

Name	Type	ReadOnly	References	SupportedOperators	Platform	Description
DisplayName	String	True			Common	The displayed name of the watcher.
EmailAddress	String	True			Common	The email address of the watcher.
AccountId [KEY]	String	False	Users.AccountId		Cloud	The accountId of the watcher.
Key [KEY]	String	True			Server	The account key of the watcher.
Name	String	False			Server	The name of the watcher.
IssueId [KEY]	String	False		=,EMP_EQ	Common	The issue ID of the watcher.
IssueKey	String	False		=,EMP_EQ	Common	The issue key of the watcher.

Manages the available workflows in Jira, which define the sequence of statuses and transitions for issues.

Table-Specific Information

SELECT

The Cloud uses the Jira API to process filters based on the Name, IsActive, and Query columns. The Cloud processes all other filters client-side.

For example, the following queries are processed 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';

INSERT

The Scope, Statuses, and Workflows columns accept aggregate values and are only used for inserting into 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":[]}]}]');

NOTE: This statement is supported only for Jira Cloud instances.

UPDATE

Update a workflow by specifying the Id and the columns to update. The Description and CanEdit columns can be updated directly. The Scope, Statuses, and Workflows aggregate columns can also be used to update the workflow configuration.

UPDATE Workflows SET Description = 'Updated workflow description' WHERE Id = 'e07175e4-f1c0-482f-a02c-50e966985eb8';

NOTE: This statement is supported only for Jira Cloud instances.

DELETE

Delete a workflow by specifying the Id.

DELETE FROM Workflows WHERE Id = 'e07175e4-f1c0-482f-a02c-50e966985eb8';

NOTE: This statement is supported only for Jira Cloud instances.

Columns

Name	Type	ReadOnly	References	SupportedOperators	Platform	Description
Id	String	False			Cloud	The unique identifier of the workflow within Jira. This ID is used internally and in API operations to reference the workflow.
Name	String	False		=,LIKE	Common	The name assigned to the workflow, which typically reflects the process or team it supports (for example, 'Software Development Workflow').
Description	String	False			Common	A user-defined explanation of what the workflow represents or how it should be used. Helpful for distinguishing between similar workflows.
Created	String	True			Cloud	The timestamp indicating when the workflow was initially created.
Updated	String	True			Common	The date when the workflow was last modified. On the Server platform, this value is returned as a human-readable string (for example, '20 minutes ago' or '10/Oct/25 9:53 AM'). On the Cloud platform, this value is returned as a standard date string.
UpdatedBy	String	True			Server	The display name of the user who most recently updated the workflow. Useful for auditing purposes to identify who last made changes to the workflow configuration or metadata.
IsDefault	Boolean	False			Server	Indicates whether the workflow is currently designated as the default workflow for its associated issue type or project.
Steps	Integer	True			Server	The total number of steps defined in the workflow. Each step represents a status that an issue can occupy, and transitions between steps represent actions that move issues through the process.
CanEdit	Boolean	False			Cloud	If the value is 'true', the workflow can be edited. This can be restricted based on permissions or if the workflow is active.
Version	String	False			Cloud	A JavaScript Object Notation (JSON) object containing the version of the workflow, used for optimistic locking during updates. This field is required when submitting workflow updates.
Statuses	String	False			Cloud	A JavaScript Object Notation (JSON) array of statuses associated with the workflow. This value is returned in SELECT results. On INSERT, it specifies the statuses to include in the new workflow. On UPDATE, it specifies the top-level statuses submitted alongside the workflow update.
Scope	String	False		=	Cloud	A JavaScript Object Notation (JSON) object defining the scope of the workflow, such as whether it is global or limited to specific projects. This value is returned in SELECT results and can be set on INSERT. The update endpoint does not accept a new scope. Accepted filter values are GLOBAL and PROJECT.
Transitions	String	False			Cloud	A JavaScript Object Notation (JSON) array describing the transitions defined in the workflow. Each entry specifies the source status, target status, and conditions that govern the transition. This value is returned in SELECT results and is required when submitting workflow updates.

Pseudocolumns

Pseudocolumn fields are used in the WHERE clause of SELECT statements and offer more granular control over the data returned from the data source.

Name	Type	Description
IsActive	Boolean	If the value is 'true', the workflow is currently in use by one or more projects. Active workflows can be subject to restrictions for modification or deletion.
Workflows	String	A JSON object containing the full workflow configuration. Used for bulk creation or update of workflows and not returned in SELECT queries.

Provides full access to Jira worklogs. Supports creating, updating, deleting, and querying logged work hours on issues.

Table-Specific Information

SELECT

The Cloud uses the Jira API to process filters based on the IssueId, IssueKey, ProjectId, ProjectName, and Started columns. The Cloud processes all other filters client-side.

For example, the following queries are processed server-side:

SELECT * FROM Worklogs WHERE IssueId = 123;
SELECT * FROM Worklogs WHERE ProjectId = 10100;
SELECT * FROM Worklogs WHERE Started >= '2024-01-04' AND Started < '2024-02-03';

INSERT

Insert a worklog by specifying the following fields:

• IssueId
• Started
• TimeSpentSeconds

INSERT INTO Worklogs (IssueId, Started, TimeSpentSeconds) VALUES (10003, '2017-03-20 20:00:00', 10000);

UPDATE

Update a worklog by specifying the Id and IssueId.

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

ADF Fields (REST_V3)

When the APIVersion connection property is set to REST_V3, the Comment_ADF column is available alongside Comment. Use Comment to insert or update a plain-text value, which the driver wraps into ADF format automatically. Use Comment_ADF to supply raw ADF JSON directly.

Insert a worklog with a plain-text comment (wrapped as ADF automatically):

INSERT INTO Worklogs (IssueId, Started, TimeSpentSeconds, Comment) VALUES (10003, '2024-01-15 09:00:00', 3600, 'Plain text comment');

Insert a worklog with a raw ADF JSON comment:

INSERT INTO Worklogs (IssueId, Started, TimeSpentSeconds, Comment_ADF) VALUES (10003, '2024-01-15 09:00:00', 3600, '{"type":"doc","version":1,"content":[{"type":"paragraph","content":[{"type":"text","text":"Raw ADF comment"}]}]}');

Update a worklog comment using plain text:

UPDATE Worklogs SET Comment = 'Updated comment' WHERE Id = 10602 AND IssueId = 10003;

Update a worklog comment using raw ADF JSON:

UPDATE Worklogs SET Comment_ADF = '{"type":"doc","version":1,"content":[{"type":"paragraph","content":[{"type":"text","text":"Updated ADF comment"}]}]}' WHERE Id = 10602 AND IssueId = 10003;

NOTE: Specifying both Comment and Comment_ADF in the same statement results in an error. Use one or the other.

DELETE

Delete a worklog by specifying 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 unique identifier of the worklog entry used to distinguish it from all other worklogs in the system.
IssueId [KEY]	Integer	False	Issues.Id	=,!=,IN,NOT IN	Common	The internal numeric ID of the Jira issue associated with this worklog entry. This links the worklog to a specific issue.
IssueKey	String	False	Issues.Key	=,IN,NOT IN	Common	The human-readable key of the Jira issue (such as 'ABC-123') that this worklog entry pertains to.
ProjectId	Integer	True	Projects.Id	=,IS,IS NOT,IN,NOT IN	Common	The numeric ID of the project to which the issue belongs. This helps associate the worklog with a broader context.
ProjectName	String	True		=,IS,IS NOT,IN,NOT IN	Common	The name of the project that the associated issue and worklog entry belong to.
IssueCreatedDate	Datetime	True		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The date and time when the issue associated with this worklog was originally created.
IssueUpdatedDate	Datetime	True		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The date and time when the issue associated with this worklog was last updated.
AuthorDisplayName	String	True	Users.DisplayName	=,!=,IS,IS NOT,IN,NOT IN	Common	The display name of the user who initially created the worklog entry.
UpdateAuthorDisplayName	String	True	Users.DisplayName		Common	The display name of the user who most recently updated or modified the worklog entry.
Comment	String	False		=,LIKE	Common	The free-text comment or description added by the user to describe the work performed during this logged time.
Comment_ADF	String	False			Cloud	Raw Atlassian Document Format (ADF) JSON value of 'Comment'. Available only when the APIVersion connection property is set to REST_V3.
Created	Datetime	True			Common	The date and time when the worklog entry was created in Jira.
Updated	Datetime	True			Common	The date and time when the worklog entry was last modified.
VisibilityType	String	False			Common	The category of restriction that controls who can view this worklog entry, such as role-based or group-based visibility.
VisibilityValue	String	False			Common	The specific value used to enforce visibility restrictions, such as the name of the user role or group allowed to view the entry.
Started	Datetime	False		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The exact date and time when the user began working, according to this logged work entry.
TimeSpent	String	False			Common	The total time spent on the issue, as logged in this worklog entry, formatted as a string (such as '3h 15m').
TimeSpentSeconds	Long	False			Common	The total time spent on the issue in seconds, useful for precise calculations and aggregations.
AuthorAccountId	String	True	Users.AccountId	=,!=,IS,IS NOT,IN,NOT IN	Cloud	The Atlassian account ID of the user who created the worklog, used for cross-product user identification.
UpdateAuthorAccountId	String	True	Users.AccountId		Cloud	The Atlassian account ID of the user who last updated the worklog, used for consistent tracking of changes.
AuthorAccountKey	String	True	Users.Key		Server	The legacy user key of the person who created the worklog entry. This field cannot be supported in newer Jira versions.
AuthorAccountName	String	True	Users.Name	=,!=,IS,IS NOT,IN,NOT IN	Server	The legacy username of the worklog creator. This value can be deprecated in favor of accountId.
UpdateAuthorAccountKey	String	True	Users.Key		Server	The legacy user key of the person who last updated the worklog entry. This value can be deprecated.
UpdateAuthorAccountName	String	True	Users.Name		Server	The legacy username of the user who last modified the worklog. This field cannot be supported in newer Jira versions.
ItemURL	String	True				A direct URL to view the specific worklog entry in the Jira UI, useful for navigation or reporting.