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.

Accessing Custom Fields

By default the Cloud surfaces only system fields. To access the custom fields for Issues, either set IncludeCustomFields to true, or extend the Cloud schemas to configure access to custom fields. (See Custom Fields.) Note that the server response time can be significantly slower when custom fields are included.

Authenticating to Jira

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

Personal Access Token (PAT)

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

PATs are created by each individual user. When created, they automatically have the same permissions level as the user. Users can create a PAT either via their Atlassian Settings menu, or using a REST API.

From the Atlassian application:

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

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

Now set the following Jira Cloud properties:

• AuthScheme = OAuth
• OAuthAccessToken = the value of the generated Personal Access Token.
• InitiateOAuth = OFF

Note: PATs can also be created using the REST API. For further information, see Jira's documentation on Creating PATs at https://confluence.atlassian.com/enterprise/using-personal-access-tokens-1026032365.html.

OAuth 2.0

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

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

API Token

You can establish a connection to any Jira Cloud account by setting the AuthScheme to APIToken and providing the User and APIToken.
An API token is necessary for basic authentication to Cloud instances. To generate one, log in to your Atlassian account and navigate to Security > Create and manage API tokens > Create API token. The generated token will be displayed.

Basic

You can establish a connection to any Jira Server instance by setting the AuthScheme to Basic. To connect to a Server Instance provide the User and Password.
(Note: Password has been deprecated for connecting to a Cloud Account and is now used only to connect to a Server Instance.)

LDAP

You can establish a connection to any Jira Server instance by setting the AuthScheme to LDAP. Additionally provide the URL, User and Password of the Jira instance. (Note: LDAP Authentication is not currently supported for Cloud accounts.)

Crowd

Set the AuthScheme to Crowd. The following connection properties are used to connect to 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 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

The following is an example connection string:

AuthScheme=Crowd;Url=https://yoursitename.atlassian.net;SSOLoginURL='https://<authority>/crowd/console/secure/saml/sso.action';User=crowdUserName;Password=crowdPassword;SSOExchangeUrl=https://<authority of Jira instance>/plugins/servlet/samlconsumer;SSOAppName=CrowdAppName;SSOAppPassword=CrowdAppPassword;

Okta

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

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

If you are using a trusted application or proxy that overrides the Okta client request OR configuring MFA, you must use combinations of SSOProperties to authenticate using Okta. Set any of the following, as applicable:

• APIToken: When authenticating a user via a trusted application or proxy that overrides the Okta client request context, set this to the API Token the customer created from the Okta organization.
• MFAType: If you have configured the MFA flow, set this to one of the following supported types: OktaVerify, Email, or SMS.
• MFAPassCode: If you have configured the MFA flow, set this to a valid passcode.
  If you set this to empty or an invalid value, the 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;

Customizing the SSL Configuration

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

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

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 connect through the Windows system proxy, you do not need to set any additional connection properties. To connect to other proxies, set ProxyAutoDetect to false.

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

Other Proxies

Set the following properties:

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

The Cloud models the Jira API as relational tables, views, and stored procedures, allowing database-like connectivity to Jira Cloud accounts or Jira Server instances.

Accessing Custom Fields

By default, the table and view schemas describe the column behavior of system fields for Jira entities. To access Custom Fields for Issues, as well as system fields, you can set IncludeCustomFields in the connection string.

Controlling Column Names

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

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

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	Query, insert and delete the available Attachments in Jira.
Boards	Query, insert and delete the agile Boards in Jira.
Comments	Create, modify, delete and query the available Comment types in Jira.
Issues	Create, modify, delete and query Issues in Jira.
IssueTypes	Query the available Issue Types in Jira.
ProjectComponents	Query, create, modify, and delete the available Components of a project in Jira.
ProjectRoleActors	Query the actors who act in a role for specific projects in Jira.
Projects	Query, update, insert and delete the available Projects in Jira.
ProjectVersions	Query,create, modify and delete the available Versions of a project in Jira.
RoleActors	Query the default actors for project roles in Jira.
Roles	Query the available roles in Jira.
Sprints	Query,create, modify and delete the agile Sprints in Jira.
Users	Query the available Users in Jira.
Workflows	Query the available Workflows in Jira.
Worklogs	Create, modify, delete and query the available Worklogs in Jira.

Query, insert and delete the available Attachments in Jira.

Table Specific Information

Select

The Cloud uses the Jira API to process some of the filters.

The Cloud processes other filters within the Cloud.

For example, the following queries are processed server side:

SELECT * FROM Attachments WHERE Id = 10003

Retrieve attachments of an issue:

SELECT * FROM Attachments WHERE IssueId = '123' 

Use the JQL pseudocolumn for more complex filters on the issues to retrieve attachments from. For example, retrieve attachments of issues that were created after January 7th:

SELECT * FROM Attachments WHERE JQL = 'created > 2018-01-07' 

Insert

Upload an attachment to an issue.

INSERT INTO Attachments (FilePath, IssueKey) VALUES ('C:/Users/User/Desktop/info.txt', 'SSFSF-1')

INSERT INTO Attachments (ContentEncoded, Name, IssueKey) VALUES ('U29tZSBjb250ZW50IGhlcmU=', 'Uploaded File', 'SSFSF-1')

Delete

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

DELETE FROM Attachments WHERE Id = 10003

Columns

Name	Type	ReadOnly	References	SupportedOperators	Platform	Description
Id [KEY]	Integer	False			Common	The Id of the attachment.
IssueId [KEY]	Integer	False	Issues.Id	=,<>,>,>=,<,<=,IN,NOT IN	Common	The Id of the issue.
IssueKey	String	False	Issues.Key	=,<>,>,>=,<,<=,IN,NOT IN	Common	The key of the issue.
IssueCreatedDate	Datetime	False		=,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The date the attachment's issue was created.
IssueUpdatedDate	Datetime	False		=,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The date the attachment's issue was last updated.
Filename	String	False			Common	The filename of the attachment.
AuthorName	String	False			Common	The name of the author of the attachment.
AuthorDisplayName	String	False	Users.DisplayName		Common	The display name of the author of the attachment.
Created	Datetime	False			Common	The creation date of the attachment.
Size	Integer	False			Common	The size of the attachment.
MimeType	String	False			Common	The MIME type of the attachment.
Content	String	False			Common	The URI of the actual attached file.
Thumbnail	String	False			Common	The thumbnail of the attachment.
AuthorAccountId	String	False	Users.AccountId		Cloud	The account Id of the author of the attachment.
AuthorKey	String	False			Server	The author key of the attachment.

Pseudo-Columns

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

Name	Type	Description
JQL	String	JQL (Jira Query Language) allows you to build structured queries.
FilePath	String	The path of the file to insert.
ContentEncoded	String	BASE64 encoded content of the file to insert.
Name	String	The name of the file to upload. Also the file extension should be specified.

Query, insert and delete the agile Boards in Jira.

Table Specific Information

Select

The Cloud uses the Jira API to process some of the filters. The Cloud processes other filters within the Cloud.

The following is an example of a query with server-side filters

SELECT * FROM Boards WHERE Id = 10022

Using other filters:

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

Insert

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

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

Delete

To delete a board you must specify its Id.

DELETE FROM Boards WHERE Id = 130

Columns

Name	Type	ReadOnly	References	SupportedOperators	Platform	Description
Id [KEY]	Integer	True		=	Common	The Id of the board.
Name	String	False		=	Common	The name of the board.
Type	String	False		=	Common	The type of the board. The allowed values are scrum, kanban, simple.

Pseudo-Columns

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

Name	Type	Description
ProjectKeyOrId	String	Filter the agile boards based on the project they are located in.
FilterId	String	ID of a filter that the user has permissions to view. Not supported for next-gen boards.

Create, modify, delete and query the available Comment types in Jira.

Table Specific Information

Select

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

The Cloud uses the Jira API to process some of the filters.

The Cloud processes other filters within the Cloud.

For example, the following queries are processed server side:

SELECT * FROM Comments

You can also retrieve comments posted on a specific issue.

SELECT * FROM Comments WHERE IssueId = 10003

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

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

Insert

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

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

Update

Only the body of the comment can be updated. The IssueId is required for an UPDATE as well.

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

Delete

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

DELETE FROM Comments WHERE Id = 10202 AND IssueId = 10003

Columns

Name	Type	ReadOnly	References	SupportedOperators	Platform	Description
Id [KEY]	Integer	True		=	Common	The Id of the comment.
IssueId [KEY]	Integer	False	Issues.Id	=,<>,>,>=,<,<=,IN,NOT IN	Common	The Id of the issue.
IssueKey	String	False	Issues.Key	=,<>,>,>=,<,<=,IN,NOT IN	Common	The key of the issue.
IssueCreatedDate	Datetime	True		=,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The date the comment's issue was created.
IssueUpdatedDate	Datetime	True		=,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The date the comment's issue was last updated.
AuthorDisplayName	String	True	Users.DisplayName		Common	The display name of the author who made the comment.
AuthorEmail	String	True	Users.EmailAddress		Common	The email address of the author who made the comment.
UpdateDisplayName	String	True	Users.DisplayName		Common	The display name of the author who changed the comment.
UpdateAuthorEmail	String	True	Users.EmailAddress		Common	The email address of the author who edited the comment.
Body	String	False			Common	The body of the comment.
RenderedBody	String	True			Common	The rendered body of the comment.
Created	Datetime	True			Common	The date the comment was created.
Updated	Datetime	True			Common	The date the comment was updated.
VisibilityType	String	False			Common	The visibility type of the comment.
VisibilityValue	String	False			Common	The visibility value of the comment.
AuthorAccountName	String	True			Server	The name of the author who made the comment.
AuthorAccountKey	String	True			Server	The key of the author who made the comment.
UpdateAuthorAccountKey	String	True			Server	The Id of the author who edited the comment.
UpdateAuthorAccountName	String	True			Server	The name of the author who edited the comment.
AuthorAccountId	String	True	Users.AccountId		Cloud	The name of the author who made the comment.
UpdateAuthorAccountId	String	True	Users.AccountId		Cloud	The Id of the author who edited the comment.

Pseudo-Columns

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

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

Create, modify, delete and query Issues in Jira.

Table Specific Information

You should set the IncludeCustomFields connection property to 'True' in order to dynamically retrieve the Custom Fields columns and values (like Sprint, Epic, ...) when querying this table.

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

Select

JQL

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

SQL

SELECT * FROM Issues WHERE ProjectId > 10001 OR Updated <= '2016/03/29 15:00'

The Cloud uses the Jira API to process some of the filters. The Cloud processes other filters client-side within the Cloud. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria that refers to other columns will cause inconsistent data. For example, the following queries are processed server side.

SQL and JQL

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

Filter time interval fields like TimeSpent, OriginalEstimate and RemainingEstimate by specifying hours, days, weeks, minutes and so on.

SELECT * FROM Issues WHERE RemainingEstimate = '1m 2d' AND TimeSpent = '3m 2w'

Insert

Create a new issue in the Project. Project (specify either ProjectId or ProjectKey), IssueType (specify either IssueTypeId or IssueTypeName), and Summary are required for Inserts. ProjectName may also be specified instead, but it will cost an additional request to the server to find the ProjectKey value that it belongs to. Depending on the Project and the IssueType more fields may be required.

INSERT INTO Issues (ProjectId, Summary, IssueTypeId, CustomField1) VALUES (10000, 'Desc from prod', 10100, 'test')
INSERT INTO Issues (ProjectKey, Summary, IssueTypeName, CustomField1) VALUES ('PROD', 'Desc from prod', 'Task', 'test')

Update

Update info of existing issues by providing the Id

UPDATE Issues SET Summary = 'Desc FROM prod', CustomField1 = 'ValidValue' WHERE Id = '10003'

You can also modify some aggregate fields for a specific issue. You can modify FixVersionsAggregate, AffectedVersionsAggregate, ComponentsAggregate and IssueLinksAggregate. Using the special inputs for these columns, you can assign, remove and update the appropriate field for an issue. Note that the object must first be created in the Jira application to use it with the provider queries. NOTE: The IssuelinksAggregate field has slightly different input requirements, outlined further below.

Structure for adding a fix version:

{ "add": {"name":"example"} }

or

{ "add": {"id":"1234"} }

An example query:

UPDATE Issues SET FixVersionsAggregate = '{"add":{"name":"extra"}}' WHERE Id = 19683

You can only add one object at a time, but using the set command you can assign multiple objects. IMPORTANT NOTE: The set command will overwrite all the previous objects, meaning that previous entries will be discarded, and only the objects specified in the set command will be saved to the issue. The set command must be followed by an array:

{ "set":  [ {"name":"New Component 1"}, {"name":"Old Component 2"} ] }

or 

{ "set":  [ {"id":"1234"}, {"name":"Old Component 2"} ] }

And an example query would be:

UPDATE Issues SET ComponentsAggregate = '{ "set":  [ {"id":"1234"}, {"name":"Old Components 2"} ] }' WHERE Id = 19683

You can also remove an entry one at a time using the remove command:

{ "remove": {"name":"example"} }

or

{ "remove": {"id":"1234"} }

UPDATE Issues SET AffectedVersionsAggregate = '{"remove":{"name":"extra"}}' WHERE id = 19683

These commands are also applicable when inserting a new issue:

INSERT INTO Issues (ProjectId, Summary, IssueTypeId, FixVersionsAggregate) VALUES (10000, 'Desc from prod', 10100, '{"add":{"name":"Example1"}}')

INSERT INTO Issues (ProjectId, Summary, IssueTypeId, FixVersionsAggregate) VALUES (10000, 'Desc from prod', 10100, '{"set":[{"name":"Example1"}]}')

IssueLinksAggregate

Only the 'add' operation is supported with IssueLinksAggregate, and you must specify the type of the link and the direction of the link, either 'outwardIssue' or 'inwardIssue', but not both. The valid link types are Blocks, RootCause, Relates, Duplicate and Cloners

{ "add":
  { "outwardIssue":
      {"key":"COB-15"},
    "type":
      {"name":"Cloners"}
  }
}

or

{ "add":
  { "inwardIssue":
    {"key":"FIL-78"},
    "type":
    {"name":"Blocks"}
  }
}

INSERT INTO Issues (ProjectId, Summary, IssueTypeId, FixVersionsAggregate) VALUES (10000, 'Desc from prod', 10100, '{"add":{"outwardIssue":{"key":"COB-15"},"type":{"name":"Cloners"}}}')

"UPDATE Issues SET IssuelinksAggregate = '{"add":{"inwardIssue":{"key":"FIL-1"},"type":{"name":"Blocks"}}}' WHERE Id = '22204'"

Delete

Delete an issue by providing the Id. Note that subtasks that have not been completed will be deleted as well.

DELETE FROM Issues WHERE Id = '10003'

Columns

Name	Type	ReadOnly	References	SupportedOperators	Platform	Description
Id [KEY]	Int	True		=,!=,>,>=,<,<=,IN,NOT IN	Common	The Id of the issue.
Key	String	True		=,!=,>,>=,<,<=,IN,NOT IN	Common	The key of the issue.
IssueTypeId	String	False		=,!=,IS,IS NOT,IN,NOT IN	Common	The issue type Id.
IssueTypeName	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The issue type name.
ProjectId	String	False		=,!=,IS,IS NOT,IN,NOT IN	Common	The project Id of the issue.
ProjectName	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The project name of the issue.
ProjectKey	String	False		=,!=,IS,IS NOT,IN,NOT IN	Common	The project key of the issue.
ParentId	Int	False		=,!=,IN,NOT IN	Common	The Id of the issue's parent if the issue is a subtask.
ParentKey	String	True		=,!=,IN,NOT IN	Common	The key of the issue's parent if the issue is a subtask.
ParentIssueTypeId	String	True			Common	The issue type id of the issue's parent.
ParentIssueTypeName	String	True			Common	The issue type name of the issue's parent.
ParentStatusId	String	True			Common	The status id of the issue's parent.
ParentStatusName	String	True			Common	The status name of the issue's parent.
ResolutionId	String	True		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The resolution Id of the issue.
ResolutionName	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The resolution name of the issue.
ResolutionDescription	String	True			Common	The resolution description of the issue.
ResolutionDate	Datetime	True		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The resolution date of the issue.
Workratio	Long	True		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The work ratio of the issue.
LastViewed	Datetime	True		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The last time that the issue was viewed.
WatchCount	Int	True		=,!=,>,>=,<,<=,IN,NOT IN	Common	The number of watches of the issue.
IsWatching	Bool	True			Common	Whether the currently authenticated user is watching the issue.
Created	Datetime	True		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The creation date of the issue.
PriorityId	String	False		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The priority Id of the issue.
PriorityName	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The priority name of the issue.
TimeSpentSeconds	Long	True			Common	The time spent in seconds on the issue.
TimeSpent	String	True		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The time spent on the issue.
RemainingEstimateSeconds	Long	True			Common	The time estimate in seconds of the issue.
RemainingEstimate	String	False		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The time estimate of the issue.
OriginalEstimateSeconds	Long	True			Common	The original time estimate in seconds of the issue.
OriginalEstimate	String	False		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The original time estimate of the issue.
AggregateTimeSpent	Long	True			Common	The aggregate time spent of the issue.
AggregateTimeOriginalEstimate	Long	True			Common	The original aggregate time estimate of the issue.
AggregateTimeEstimate	Long	True			Common	The aggregate time estimate of the issue.
AssigneeDisplayName	String	True			Common	Assignee display name.
AssigneeKey	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	[DEPRECATED] The assignee key of the issue.
AssigneeAccountId	String	False		=,!=,IS,IS NOT,IN,NOT IN	Common	The assignee account id.
AssigneeName	String	False		=,!=,IS,IS NOT,IN,NOT IN	Common	The assignee name of the issue.
AssigneeEmail	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The assignee email of the issue.
Updated	Datetime	True		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The updated date of the issue.
StatusId	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The status Id of the issue.
StatusName	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The status name of the issue.
StatusCategoryId	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The status category id of the issue.
StatusCategoryKey	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The status category key of the issue.
StatusCategoryName	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The status category name of the issue.
Description	String	False		IS,IS NOT	Common	The description of the issue.
Summary	String	False		IS,IS NOT	Common	The summary of the issue.
CreatorDisplayName	String	True			Common	Issue creator display name.
CreatorName	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The creator name of the issue.
CreatorAccountId	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The creator account id.
CreatorKey	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	[DEPRECATED] The creator key of the issue.
CreatorEmail	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The creator email of the issue.
ReporterDisplayName	String	True			Common	Issue reporter display name.
ReporterName	String	False		=,!=,IS,IS NOT,IN,NOT IN	Common	The reporter name of the issue.
ReporterKey	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	[DEPRECATED]The reporter key of the issue.
ReporterAccountId	String	False		=,!=,IS,IS NOT,IN,NOT IN	Common	The reporter account id.
ReporterEmail	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The reporter email of the issue.
AggregateProgress	Long	True			Common	The aggregate progress of the issue.
TotalProgress	Long	True			Common	The aggregate total progress of the issue.
Votes	Int	True		=,!=,>,>=,<,<=,IN,NOT IN	Common	Votes of the issue.
HasVotes	Bool	True			Common	The vote status of the issue.
DueDate	Date	False		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The due date of the issue.
Labels	String	False		=,!=,IS,IS NOT,IN,NOT IN	Common	The labels of an issue.
Environment	String	False		LIKE,NOT LIKE,IS,IS NOT	Common	The environment of an issue.
SecurityLevel	String	False		=,!=,IS,IS NOT,IN, NOT IN	Common	The security level of an issue.
FixVersionsAggregate	String	False			Common	The fix versions of the issue
ComponentsAggregate	String	False			Common	Aggregate list of components included in the issue.
IssueLinksAggregate	String	False			Common	The issue links of the issue.
AffectedVersionsAggregate	String	False			Common	The affected versions of the issue.
SubtasksAggregate	String	True			Common	The subtasks of the issue.
CustomField1	Any	False			Common	A custom field created for the Issues. The custom fields will be listed if the 'IncludeCustomFields' connection property is set to true.
CustomField2	Any	False			Common	A custom field created for the Issues. The custom fields will be listed if the 'IncludeCustomFields' connection property is set to true.

Pseudo-Columns

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

Name	Type	Description
JQL	String	JQL (Jira Query Language) allows you to build structured queries.
Attachments	String	Search for issues that have or do not have attachments. This column can be queried with IS EMPTY or IS NOT EMPTY.
Comment	String	Search for issues that have a comment that contains particular text.
Category	String	Search for issues that belong to projects in a particular category.
SprintId	Integer	Search for issues that belongs to a specific sprint, by specifying the Id of the Sprint.
SprintName	String	Search for issues that belongs to a specific sprint, by specifying the name of the Sprint.
Filter	String	Search for issues of saved filters. This column can be queried with filter name or filter id.

Query the available Issue Types in Jira.

Select

The Cloud uses the Jira API to process some of the filters.

The Cloud processes other filters within the Cloud.

SELECT * FROM IssueTypes WHERE Id = 3

Insert

Name is required to insert. The following query creates a new issue type:

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

Update

You can update by specifying the issue type Id:

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

Delete

Delete an issue type by specifying the issue type Id:

DELETE FROM IssueTypes WHERE Id = 10306

Columns

Name	Type	ReadOnly	References	SupportedOperators	Platform	Description
Id [KEY]	String	True		=	Common	The Id of the issue type.
Name	String	False			Common	The name of the issue type.
Description	String	False			Common	The description of the issue type.
Subtask	Boolean	False			Common	The subtask of the issue type. Not applicable for update.
IconUrl	String	True			Common	The icon URL of the issue type.

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

Table Specific Information

Select

The Cloud uses the Jira API to process some of the filters. The Cloud processes other filters within the Cloud.

For example, the following queries are processed server side:

Retrieve the components of each project

SELECT * FROM ProjectComponents

You can also filter the components by their Id or the Project they are part of:

SELECT * FROM ProjectComponents WHERE Id = 10022 

Using ProjectId:

SELECT * FROM ProjectComponents WHERE ProjectId = 10027 

Using ProjectKey:

SELECT * FROM ProjectComponents WHERE ProjectKey = 'QMTH' 

Insert

Create a new Component for the project. The ProjectKey and Name columns are required.

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

Update

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

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

Delete

Remove a component of the project by specifying the Id.

DELETE FROM ProjectComponents WHERE Id = 10065

Columns

Name	Type	ReadOnly	References	SupportedOperators	Platform	Description
Id [KEY]	Integer	True		=	Common	The Id of the component.
ProjectId	Integer	True	Projects.Id	=,IN	Common	The selected project's id.
ProjectKey	String	False		=,IN	Common	The selected project's key.
Name	String	False			Common	The name of the component.
Description	String	False			Common	The description of the component.
LeadDisplayName	String	True			Common	The display name of the component's lead.
LeadKey	String	False			Common	The key of the component's lead.
AssigneeType	String	False			Common	The type of the component's default assignee. The allowed values are PROJECT_DEFAULT, COMPONENT_LEAD, PROJECT_LEAD, UNASSIGNED.
AssigneeDisplayName	String	True			Common	The display name of the component's default assignee.
AssigneeKey	String	True			Common	The key of the component's default assignee.
IsAssigneeTypeValid	Boolean	True			Common	Whether the assignee type is valid.

Query the actors who act in a role for specific projects in Jira.

Select

The Cloud will use the Jira API to process filters that refer to ProjectId, ProjectKey and RoleId while the rest of the filter is executed client side within the Cloud. The Jira APIs support filters using the equals (=) and IN operator with ProjectId, ProjectKey and RoleId. Note that either ProjectId, ProjectKey or RoleId must be specified with a comparison type of equals (=) for SELECT SQL statement.

For example, the following queries are processed server side:

Retrieve all actors for a specific project:

SELECT * FROM ProjectRoleActors WHERE ProjectId = '10650'

SELECT * FROM ProjectRoleActors WHERE ProjectKey = 'MYP'

Retrieve all actors for a specific role in all projects:

SELECT * FROM ProjectRoleActors WHERE RoleId = '10329'

Retrieve all actors for a specific project:

SELECT * FROM ProjectRoleActors WHERE RoleId = '10329' AND ProjectId = '10650'

Retrieve all actors for specific projects:

SELECT * FROM ProjectRoleActors WHERE RoleId = '10329' AND ProjectId IN ('10650', '10651', '10652')

Retrieve all actors for specific roles in a project:

SELECT * FROM ProjectRoleActors WHERE RoleId IN ('10329', '10330', '10331') AND ProjectId = '10650'

Insert

Cloud

Add a user as an actor to a role in a specific project by specifying the user's account Id.

INSERT INTO ProjectRoleActors (ProjectId, RoleId, ActorUserAccountId) VALUES ('10650', '10329', '63acbf336f068efec8f6e0c3')

Add a group as an actor to a role in a specific project by either specifying the group's Id or name.

INSERT INTO ProjectRoleActors (ProjectId, RoleId, ActorGroupGroupId) VALUES ('10650', '10329', '0eed15e3-80cc-41d0-ba1a-b408658caf4d')

INSERT INTO ProjectRoleActors (ProjectId, RoleId, ActorGroupName) VALUES ('10650', '10329', 'JohnTest1')

Server

Add a user as an actor to a role by specifying the project, role and user's name.

INSERT INTO ProjectRoleActors (ProjectId, RoleId, UserName) VALUES ('10000', '10101', 'myuser')

Add a group as an actor to a role by specifying the project, role and group's name.

INSERT INTO ProjectRoleActors (ProjectId, RoleId, GroupName) VALUES ('10000', '10101', 'test-group')

Delete

Cloud

Delete an actor from a role by specifying the project, role and the user's account Id, the group's name or the group's Id.

DELETE FROM ProjectRoleActors WHERE ProjectId = '10650' AND RoleId = '10329' AND ActorUserAccountId = '63acbf336f068efec8f6e0c3'

DELETE FROM ProjectRoleActors WHERE ActorGroupGroupId = '0eed15e3-80cc-41d0-ba1a-b408658caf4d' AND ProjectId = '10650' AND RoleId = '10330'

DELETE FROM ProjectRoleActors WHERE ActorGroupName = 'JohnTest1' AND ProjectId = '10650' AND RoleId = '10330'

Server

Delete a default actor from a role by specifying the user's name or the group's name.

DELETE FROM ProjectRoleActors WHERE ProjectId = '10000' AND RoleId = '10101' AND UserName = 'myuser'

DELETE FROM ProjectRoleActors WHERE ProjectId = '10000' AND RoleId = '10101' AND GroupName = 'test-group'

Columns

Name	Type	ReadOnly	References	SupportedOperators	Platform	Description
Id [KEY]	Long	False			Common	The ID of the role actor.
DisplayName	String	False			Common	The display name of the role actor. For users, depending on the user?s privacy setting, this may return an alternative value for the user's name.
Type	String	False			Common	The type of role actor.
Name	String	False			Server	The name of the role.
AvatarUrl	String	False			Common	The avatar of the role actor.
ActorUserAccountId	String	False			Cloud	Details of the user associated with the role. The account ID of the user, which uniquely identifies the user across all Atlassian products.
ActorGroupName	String	False			Cloud	Details of the group associated with the role. The name of the group. As a group's name can change, use of groupId is recommended to identify the group.
ActorGroupDisplayName	String	False			Cloud	Details of the group associated with the role. The display name of the group.
ActorGroupGroupId	String	False			Cloud	Details of the group associated with the role. The ID of the group.
RoleId	Long	False	Roles.Id	=,IN	Common	The Id of the role.
ProjectId	Integer	False	Projects.Id	=,IN	Common	The Id of the project associated with the role.
ProjectKey	String	False	Projects.Key	=,IN	Common	The key of the project associated with the role.

Pseudo-Columns

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

Name	Type	Description
UserName	String	Server platform only. Input for Insert and Delete statements. Details of the user associated with the role. The name of the user.
GroupName	String	Server platform only. Input for Insert and Delete statements. Details of the group associated with the role. The name of the group.

Query, update, insert and delete the available Projects in Jira.

Select

The Cloud uses the Jira API to process some of the filters.

The Cloud processes other filters within the Cloud. For example, the following queries are processed server side:

SELECT * FROM Projects WHERE Id = 10000

INSERT

Key, Name, LeadName and ProjectTypeKey are required to insert. The following query creates a new project:

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

Update

You can update by specifying either Id or Key:

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

Delete

Delete a project by specifying either Id or Key:

DELETE FROM Projects WHERE Key = 'EXE4'

Columns

Name	Type	ReadOnly	References	SupportedOperators	Platform	Description
Id [KEY]	Int	True		=	Common	The Id of the project.
Key	String	False		=	Common	The key of the project.
Name	String	False			Common	The name of the project.
Description	String	False			Common	The description of the project.
LeadEmailAddress	String	True	Users.EmailAddress		Common	The email address of the project lead.
LeadDisplayName	String	True	Users.DisplayName		Common	The display name of the project lead.
ComponentsAggregate	String	True			Common	The components of the project.
IssueTypesAggregate	String	True			Common	The issue types of the project.
Url	String	True			Common	The URL of the project.
Email	String	True			Common	The email of the project.
AssigneeType	String	False			Common	The assignee type of the project.
VersionsAggregate	String	True			Common	The versions of the project.
RolesAggregate	String	True			Common	The roles of the project.
ProjectKeysAggregate	String	True			Common	The project keys of the project.
ProjectCategoryId	String	False			Common	The Id of the project category.
ProjectCategoryName	String	True			Common	The name of the project category.
ProjectCategoryDescription	String	True			Common	The description of the project category.
ProjectTypeKey	String	False			Common	The key of the project type. Not applicable for update.
LeadAccountId	String	False	Users.AccountId		Cloud	The Id of the project lead.
LeadAccountKey	String	True	Users.Key		Server	The Key of the project lead.
LeadAccountName	String	False	Users.Name		Server	The user name of the project lead.
Simplified	Boolean	True			Cloud	Whether the project is team-managed or not.

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

Table Specific Information

Select

The Cloud uses the Jira API to process some of the filters. The Cloud processes other filters within the Cloud. For example, the following queries are processed server side:

Retrieve the versions for all projects:

SELECT * FROM ProjectVersions 

You can also filter by the Id, ProjectKey, or ProjectId. For example:

SELECT * FROM ProjectVersions WHERE Id = 10022 

Using ProjectId:

SELECT * FROM ProjectVersions WHERE ProjectId = 10022 

Using ProjectKey:

SELECT * FROM ProjectVersions WHERE ProjectKey = 'QMTH' 

Insert

Create a new version to be used throughout the project. The Name and either ProjectId or ProjectKey columns are required.

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

Update

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

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

Delete

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

DELETE FROM ProjectVersions WHERE Id = 10065

Columns

Name	Type	ReadOnly	References	SupportedOperators	Platform	Description
Id [KEY]	Integer	True		=	Common	The Id of the version.
ProjectId	Integer	False	Projects.Id	=,IN	Common	The selected project's id.
ProjectKey	String	False		=,IN	Common	The selected project's key.
Name	String	False			Common	The name of the version.
Description	String	False			Common	The description of the version.
Released	Boolean	False			Common	Whether the version has been released.
ReleaseDate	Date	False			Common	Release date of the version.
StartDate	Date	False			Common	Optional start date of the version.
UserStartDate	Date	False			Common	The date on which work on this version is expected to start.
UserReleaseDate	Date	False			Common	The date on which work on this version is expected to finish.
Overdue	Boolean	True			Common	Whether the version is overdue for release.
Archived	Boolean	False			Common	Whether the version has been archived. When a new version is created, this field is always set to False.

Query the default actors for project roles in Jira.

Select

The Cloud will use the Jira API to process filters that refer to RoleId while the rest of the filter is executed client side within the Cloud. The Jira APIs support filters using the equals (=) and IN operator with RoleId.

For example, the following queries are processed server side:

Retrieve all the default actors for all roles:

SELECT * FROM RoleActors

Retrieve all the default actors for a specific role:

SELECT * FROM RoleActors WHERE RoleId = '10002'

Insert

Cloud

Add a user as a default actor to a role by specifying the user's account Id.

INSERT INTO RoleActors (ActorUserAccountId, RoleId) VALUES ('63acbf336f068efec8f6e0c3', '10326')

Add a group as a default actor to a role by either specifying the group's Id or name.

INSERT INTO RoleActors (ActorGroupGroupId, RoleId) VALUES ('ad8da7e1-7b66-408e-a61c-c4c8383ca5d1', '10326')

INSERT INTO RoleActors (ActorGroupName, RoleId) VALUES ('testGroupName', '10326')

Server

Add a user as a default actor to a role by specifying the user's key.

INSERT INTO RoleActors (RoleId, UserKey) VALUES ('10102', 'JIRAUSER10001')

Add a group as a default actor to a role by specifying the group's name.

INSERT INTO RoleActors (RoleId, GroupName) VALUES ('10102', 'test-group')

Delete

Cloud

Delete a default actor from a role by specifying the RoleId and the user's account Id, the group's name or the group's Id.

DELETE FROM RoleActors WHERE RoleId = '10326' AND ActorUserAccountId = '63acbf336f068efec8f6e0c3' 

DELETE FROM RoleActors WHERE RoleId = '10325' AND ActorGroupName = 'Developers' 

DELETE FROM RoleActors WHERE RoleId = '10325' AND ActorGroupGroupId = '6dd7f7e1-77f0-4cd1-b8e1-a069830a8f1d' 

Server

Delete a default actor from a role by specifying the user's key or the group's name.

DELETE FROM RoleActors WHERE RoleId = '10102' AND UserKey = 'JIRAUSER10001'

DELETE FROM RoleActors WHERE RoleId = '10102' AND GroupName = 'test-group'

Columns

Name	Type	ReadOnly	References	SupportedOperators	Platform	Description
Id [KEY]	Long	False			Common	The ID of the role actor.
DisplayName	String	False			Common	The display name of the role actor. For users, depending on the user?s privacy setting, this may return an alternative value for the user's name.
Type	String	False			Common	The type of role actor.
AvatarUrl	String	False			Common	The avatar of the role actor.
ActorUserAccountId	String	False	Users.AccountId		Cloud	Details of the user associated with the role. The account ID of the user, which uniquely identifies the user across all Atlassian products.
ActorGroupName	String	False	Groups.Name		Cloud	Details of the group associated with the role. The name of the group. As a group's name can change, use of groupId is recommended to identify the group.
ActorGroupDisplayName	String	False			Cloud	Details of the group associated with the role. The display name of the group.
ActorGroupGroupId	String	False	Groups.Id		Cloud	Details of the group associated with the role. The ID of the group.
Name	String	False			Server	The name of the role.
RoleId	Long	False	Roles.Id	=,IN	Common	The Id of the role.

Pseudo-Columns

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

Name	Type	Description
UserKey	String	Server platform only. Input for Insert and Delete statements. Details of the user associated with the role. The key of the user.
GroupName	String	Server platform only. Input for Insert and Delete statements. Details of the group associated with the role. The name of the group.

Query the available roles in Jira.

Select

The Cloud will use the Jira API to process filters that refer to Id while the rest of the filter is executed client side within the Cloud. The Jira APIs support filters using the equals (=) operator with Id.

For example, the following queries are processed server side:

Retrieve all the available Roles:

SELECT * FROM Roles

Retrieve a specific Role:

SELECT * FROM Roles WHERE Id = '10002'

Update

Update the role name and description by specifying the role Id

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

Delete

Delete a role by specifying the Id.

DELETE FROM Roles WHERE Id = '10002'

Columns

Name	Type	ReadOnly	References	SupportedOperators	Platform	Description
Id [KEY]	Long	False		=	Common	The Id of the role.
Name	String	False			Common	The name of the role.
Description	String	False			Common	The description of the role.
Actors	String	False			Common	The list of users who act in this role.
Scope	String	False			Cloud	The scope of the role.
IsAdmin	Boolean	False			Cloud	Whether this role is the admin role for the project.
IsDefault	Boolean	False			Cloud	Whether this role is the default role for the project.

Query,create, modify and delete the agile Sprints in Jira.

Table Specific Information

Select

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

The Cloud uses the Jira API to process some of the filters. The Cloud will process the rest of the filter client side within itself. For example, the following query is processed server-side:

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

INSERT

Create a new sprint and link it to a board. The OriginBoardId and the Name columns are required for inserting a new sprint.

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

Update

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

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

Delete

Remove a sprint by specifying the Id.

DELETE FROM Sprints WHERE Id = 305

Columns

Name	Type	ReadOnly	References	SupportedOperators	Platform	Description
Id [KEY]	Integer	True		=,IN	Common	The Id of the sprint.
Name	String	False			Common	The name of the sprint.
State	String	False			Common	The state of the sprint. A newly created sprint starts in the 'future' state. The state can only transition from 'future' to 'active', and from 'active' to 'closed' The allowed values are future, active, closed.
Goal	String	False			Common	The goal assigned for the sprint.
OriginBoardId	Integer	False	Boards.Id	=	Common	The board Id the sprint originated from. This field cannot be updated
StartDate	Datetime	False			Common	The date when the sprint was started.
EndDate	Datetime	False			Common	The date when the sprint has ended.
CompleteDate	Datetime	True			Common	The date when the sprint was completed.

Query the available Users in Jira.

Select

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

Specify Name, DisplayName, and EmailAddress one at a time.

The Jira supports filters based on these columns combined with the Active or IncludeInactive columns. For example the following queries are processed server side:

SELECT * FROM Users WHERE Name LIKE '%John%' AND Active = false AND IncludeInactive = true

Query the Users belonging to a specific group by specifying either the GroupName or the GroupId:

SELECT * FROM Users WHERE GroupName = 'Testing team'
SELECT * FROM Users WHERE GroupId = '3dd7e7a1-77f7-4ac1-b8e1-a069830a7f1d'

You can also retrieve a list of all groups and their members:

SELECT * FROM Users WHERE GroupName IN (SELECT Name FROM Groups)

Use the IssueKey and ProjectKey pseudo-columns to find users who can browse a specific issue or any issue in a project.

SELECT * FROM Users WHERE IssueKey = 'PRJ-1'
SELECT * FROM Users WHERE ProjectKey = 'PRJ'

Insert

The input to INSERT must be an email address:

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

Columns

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

Pseudo-Columns

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

Name	Type	Description
IssueKey	String	Search for users who can browse a specific issue.
ProjectKey	String	Search for users who can browse any issue in a project.

Query the available Workflows in Jira.

Select

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

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

For example, the following queries are executed server side:

SELECT * FROM Workflows WHERE Name = 'abc'

SELECT * FROM Workflows WHERE Name IN ('abc','def','ghi')

SELECT * FROM Workflows WHERE IsActive = true AND Query = 'flow'

The Scope, Statuses, and Workflows columns accept aggregate values and are only used for inserting to and updating Workflows.

INSERT INTO Workflows(Scope, Statuses, Workflows) VALUES ('{"type":"GLOBAL"}', '[{"description":"","name":"To Do #2","statusCategory":"TODO","statusReference":"f0b24de5-25e7-4fab-ab94-63d81db6c0c0"}]', '[{"description":"","name":"Software workflow 1","startPointLayout":{"x":-100.00030899047852,"y":-153.00020599365235},"statuses":[{"layout":{"x":114.99993896484375,"y":-16},"properties":{},"statusReference":"f0b24de5-25e7-4fab-ab94-63d81db6c0c0"}],"transitions":[{"actions":[],"description":"","from":[],"id":"1","name":"Create","properties":{},"to":{"statusReference":"f0b24de5-25e7-4fab-ab94-63d81db6c0c0"},"triggers":[],"type":"INITIAL","validators":[]}]}]')", true)

Columns

Name	Type	ReadOnly	References	SupportedOperators	Platform	Description
Id	String	False				The entity ID of the workflow.
Name	String	False		IN		The name of the workflow.
Description	String	False				The description of the workflow.
Created	String	False				The date when the workflow was created.
Updated	String	False				The date when the workflow was updated.
IsDefault	Boolean	False				Boolean indicating if the workflow is set as the default.
CanDelete	Boolean	False				Boolean indicating whether the workflow can be deleted.
CanEdit	Boolean	False				Boolean indicating whether the workflow can be edited.
IsActive	Boolean	False		=		Filters active and inactive workflows.
Query	String	False		=		String used to perform a case-insensitive partial match with workflow name.
Scope	String	False				A JSON aggregate outlining the scope of the workflow. This column used exclusively for inserting to and updating workflows (does not return in SELECT query results).
Statuses	String	False				A JSON aggregate outlining the statuses to associate with the workflows. This column used exclusively for inserting to and updating workflows (does not return in SELECT query results).
Workflows	String	False				The details of the workflows to create. This column used exclusively for inserting to and updating workflows (does not return in SELECT query results).

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

Table Specific Information

Select

You can query this table to retrieve worklogs of Jira projects. The Cloud will use the Jira APIs to process filters based on the following columns and operators. The Cloud will execute other filters client-side.

For example, the following query is processed server side:

SELECT * FROM Worklogs WHERE IssueId = 10001

Insert

IssueId, Started, and TimeSpentSeconds are required to insert. The following query logs work done for an issue:

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

Update

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

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

Delete

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

DELETE FROM Worklogs WHERE Id = 10602 AND IssueId = 10003

GetDeleted

This retrieves Ids of the deleted worklogs:

GETDELETED FROM Worklogs

Columns

Name	Type	ReadOnly	References	SupportedOperators	Platform	Description
Id [KEY]	String	True		=	Common	The Id of the worklog.
IssueId [KEY]	Integer	False	Issues.Id	=,<>,>,>=,<,<=,IN,NOT IN	Common	The issue Id of the worklog.
IssueKey	String	False	Issues.Key	=,IN,NOT IN	Common	The key of the issue.
ProjectId	Integer	True	Projects.Id	=,IS,IS NOT,IN,NOT IN	Common	The project Id of the worklog's issue.
ProjectName	String	True		=,IS,IS NOT,IN,NOT IN	Common	The project name of the worklog's issue.
IssueCreatedDate	Datetime	True		=,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The date the worklogs's issue was created.
IssueUpdatedDate	Datetime	True		=,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The date the worklogs's issue was last updated.
AuthorDisplayName	String	True	Users.DisplayName	=,!=,IS,IS NOT,IN,NOT IN	Common	The display name of the author of the worklog.
UpdateAuthorDisplayName	String	True	Users.DisplayName	=,!=,IS,IS NOT,IN,NOT IN	Common	The display name of the author who has updated the worklog.
Comment	String	False		=,LIKE,NOT LIKE	Common	The comment of the worklog.
Created	Datetime	True		=,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The date the worklog was created.
Updated	Datetime	True			Common	The update date of the worklog.
VisibilityType	String	False			Common	The visibility type of the worklog.
VisibilityValue	String	False			Common	The visibility value of the worklog.
Started	Datetime	False			Common	The start date of the worklog.
TimeSpent	String	False			Common	The spent time of the worklog.
TimeSpentSeconds	Long	False			Common	The time spent in seconds for the worklog.
AuthorAccountId	String	True	Users.AccountId	=,!=,IS,IS NOT,IN,NOT IN	Cloud	The name of the author of the worklog.
UpdateAuthorAccountId	String	True	Users.AccountId	=,!=,IS,IS NOT,IN,NOT IN	Cloud	The name of the author who has updated the worklog.
AuthorAccountKey	String	True	Users.Key	=,!=,IS,IS NOT,IN,NOT IN	Server	The key of the author of the worklog.
AuthorAccountName	String	True	Users.Name	=,!=,IS,IS NOT,IN,NOT IN	Server	The name of the author of the worklog.
UpdateAuthorAccountKey	String	True	Users.Key	=,!=,IS,IS NOT,IN,NOT IN	Server	The key of the author who has updated the worklog.
UpdateAuthorAccountName	String	True	Users.Name	=,!=,IS,IS NOT,IN,NOT IN	Server	The name of the author who has updated the worklog.

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

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

CData Cloud - Jira Views

Name	Description
AdvancedSettings	Query the application properties that are accessible on the Advanced Settings page.
ApplicationRoles	Query all application roles. In Jira, application roles are managed using the Application access configuration page.
Audit	Query the audit log of your Jira account.
BoardConfiguration	Query the agile Board Configuration in Jira.
BoardIssues	Query the agile Board Issues in Jira.
BoardSprints	Query the agile Sprints related to a Jira Board.
Configuration	Query the available Configurations in Jira.
Dashboards	Query the available Dashboards in Jira.
Epics	Query all the Epics.
FavouriteFilters	Query the favourite Filters in Jira.
Fields	Query the available System and Custom Fields in Jira.
Filters	Query the set up Filters in Jira. This table is not available in Jira Server.
Groups	Query the available Groups in Jira.
IssueAffectedVersions	Query the Affected Versions of an issue in Jira.
IssueChangelogs	Query the available IssueChangelogs in Jira.
IssueComponents	Query the available IssueComponents in Jira.
IssueCustomFieldOptions	Query Issue Custom Field Options in Jira. This resource is not available in Jira Server or when using OAuth authentication. Specifying at least CUSTOMFIELDID, or ID is required.
IssueCustomFields	Query the available Custom Fields in Jira.
IssueFixVersions	Query the available IssueFixVersions in Jira.
IssueLinks	Query the available IssueLinks in Jira.
IssueLinkTypes	Lists all the issue link types.
IssueNavigatorDefaultColumns	Query the default issue navigator columns.
IssuePriorities	Query the list of all issue priorities.
IssueResolutions	Query the list of all issue resolution values.
IssueSubtasks	Query the available IssueSubtasks in Jira.
IssueTransitions	Query the available IssueTransitions in Jira.
MyPermissions	Query the available Permissions of the current user in Jira.
Permissions	Query the available Permissions in Jira.
ProjectCategories	Query all the project categories.
ProjectRoles	Query roles for all projects in Jira.
ProjectsIssueTypes	Query the available Issue Types of projects.
ProjectTypes	Query the list of all issue priorities.
SecurityLevels	Query all the created security levels.
SecuritySchemes	Query all the created security schemes.
SprintIssues	Query the agile Sprint Issues in Jira.
Statuses	Query the possible Statuses in Jira.
TimeTrackingProviders	Query the list of all time tracking providers.
Votes	Query the available Votes in Jira.
Watchers	Query the available Watchers in Jira.
WorkflowStatusCategories	Query the list of all status categories.
WorkflowStatuses	Query the list of all statuses associated with workflows.
WorklogDeleted	Queries the deleted worklogs in your Jira account.

Query the application properties that are accessible on the Advanced Settings page.

Table Specific Information

Select

SELECT * FROM AdvancedSettings

Columns

Name	Type	References	SupportedOperators	Platform	Description
Id [KEY]	String		=	Common	The ID of the application property.
Name	String			Common	The name of the application property.
Description	String			Common	The description of the application property.
Type	String			Common	The data type of the application property.
Value	String			Common	The new value.
DefaultValue	String			Common	The default value of the application property.
AllowedValues	String			Common	The allowed values, if applicable.

Query all application roles. In Jira, application roles are managed using the Application access configuration page.

Table Specific Information

Select

The Cloud uses the Jira API to process some of the filters.

The Cloud processes other filters within the Cloud.

For example, the following queries are processed server side:

SELECT * FROM ApplicationRoles WHERE Key = 'jira-software'

Columns

Name	Type	References	SupportedOperators	Platform	Description
Key [KEY]	String		=	Common	The key of the application role.
Name	String			Common	The display name of the application role.
Groups	String			Common	The groups associated with the application role.
DefaultGroups	String			Common	The groups that are granted default access for this application role.
SelectedByDefault	Boolean			Common	Determines whether this application role should be selected by default on user creation.
NumberOfSeats	Integer			Common	The maximum count of users on your license.
RemainingSeats	Integer			Common	The count of users remaining on your license.
UserCount	Integer			Common	The number of users counting against your license.
UserCountDescription	String			Common	The type of users being counted against your license.
HasUnlimitedSeats	Boolean			Common	Determines whether this application role has unlimited seats.
Platform	Boolean			Common	Indicates if the application role belongs to Jira platform (jira-core).

Query the audit log of your Jira account.

Table Specific Information

Select

The Cloud uses the Jira API to process some of the filters.

The Cloud processes other filters within the Cloud.

For example, the following queries are processed server side:

SELECT * FROM Audit WHERE Filter = 'ab'

Columns

Name	Type	References	SupportedOperators	Platform	Description
RecordId	Integer			Common	The Id of the audit record.
Summary	String			Common	Summary of the change.
RemoteAddress	String			Common	The remote address of the record.
AuthorKey	String			Common	Key of the author that made the change.
Created	Datetime			Common	Date on when the change was made.
Category	String			Common	Category of the change.
EventSource	String			Common	What triggered the event.
Description	String			Common	Description of the change.
ItemId	String			Common	Unique identifier of the object item.
ItemName	String			Common	Name of the object item.
ItemTypeName	String			Common	Typename of the object item.
ItemParentId	String			Common	Identifier of the parent of the object item.
ItemParentName	String			Common	Parent name of the object item.
FieldName	String			Common	The name of the changed field.
ChangedFrom	String			Common	The updated value.
ChangedTo	String			Common	The previous value.

Pseudo-Columns

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

Name	Type	Description
Filter	String		Containing a string in at least one field. For example, providing up will return all audit records where one or more fields contains words such as update.

Query the agile Board Configuration in Jira.

Columns

Name	Type	References	SupportedOperators	Platform	Description
BoardId [KEY]	Integer	Boards.Id	=,IN		The Id of the board.
ColumnConfig	String				The column configuration lists the columns for the board, in the order defined in the column configuration. For each column, it shows the issue status mapping as well as the constraint type (Valid values: none, issueCount, issueCountExclSubs) for the min/max number of issues.
Filter	String				Reference to the filter used by the given board.
Name	String				The Name of the board.
CustomFieldIdRank	Integer				The CustomFieldId ranking.
Self	String				The self/url column.
SubQuery	String				JQL subquery used by the given board.
Type	String				The type of the board.
EstimationDisplayName	String				Display name of the field used for estimation.
EstimationFieldId	String				The Id of the field used for estimation.
EstimationType	String				Contains information about type of estimation used for the board. Valid values: none, issueCount, field. If the estimation type is

Query the agile Board Issues in Jira.

Table Specific Information

Select

The Cloud uses the Jira API to process some of the filters. The Cloud processes other filters within the Cloud.

For example, the following queries are processed server side:

SELECT * FROM BoardIssues WHERE BoardId = '1' 
SELECT * FROM BoardIssues WHERE BoardId IN (70, 215) 

Columns

Name	Type	References	SupportedOperators	Platform	Description
Id [KEY]	Integer	Issues.Id	=,IN	Common	The Id of the issue.
Key	String	Issues.Key	=,IN	Common	The key of the issue.
BoardId [KEY]	Integer	Boards.Id	=,IN	Common	The board the issue is included in.
IssueTypeId	String	IssueTypes.Id	=,!=,IS,IS NOT,IN,NOT IN	Common	The issue type Id.
IssueTypeName	String		=,!=,IS,IS NOT,IN,NOT IN	Common	The issue type name.
StatusId	Integer	Statuses.Id	=,!=,IS,IS NOT,IN,NOT IN	Common	The issue status Id.
StatusName	String		=,!=,IS,IS NOT,IN,NOT IN	Common	The issue status name.
ProjectId	Integer	Projects.Id	=,!=,IS,IS NOT,IN,NOT IN	Common	The project Id of the issue.
ProjectKey	String	Projects.Key	=,!=,IS,IS NOT,IN,NOT IN	Common	The project key of the issue.
ProjectName	String		=,!=,IS,IS NOT,IN,NOT IN	Common	The project name of the issue.
ClosedSprintsAggregate	String			Common	The issue's previous sprints that have been closed.
Created	Datetime		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The creation date of the issue.
Updated	Datetime		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The updated date of the issue.

Query the agile Sprints related to a Jira Board.

Table Specific Information

Select

The Cloud uses the Jira API to process some of the filters. The Cloud processes other filters within the Cloud.

For example, the following queries are processed server side.

SELECT * FROM BoardSprints WHERE BoardId = 122 

Using BoardId:

SELECT * FROM BoardSprints WHERE BoardId IN (12, 42) 

Boards of type 'kanban' do not support sprints, so you can retrieve all the sprints faster if you use a query like the following:

SELECT * FROM BoardSprints WHERE BoardId IN (SELECT Id FROM Boards WHERE Type != 'kanban') 

Columns

Name	Type	References	SupportedOperators	Platform	Description
Id [KEY]	Integer			Common	The Id of the sprint.
BoardId [KEY]	Integer	Boards.Id	=,IN	Common	The board Id the sprint is in.
Name	String			Common	The name of the sprint.
State	String		=	Common	The state of the sprint. The sprint state can only transition from 'future' to 'active', and from 'active' to 'close' The allowed values are future, active, closed.
Goal	String			Common	The goal assigned for the sprint.
OriginBoardId	Integer	Boards.Id	=	Common	The board Id the sprint originated from. This field cannot be updated
StartDate	Datetime			Common	The date when the sprint was started.
EndDate	Datetime			Common	The date when the sprint has ended.
CompleteDate	Datetime			Common	The date when the sprint was completed.

Query the available Configurations in Jira.

Table Specific Information

Select

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

SELECT * FROM Configuration

Columns

Name	Type	References	SupportedOperators	Platform	Description
VotingEnabled	Boolean			Common	Configuration for voting.
WatchingEnabled	Boolean			Common	Configuration for watching.
UnassignedIssuesAllowed	Boolean			Common	Configuration to allow unassigned issues.
SubTasksEnabled	Boolean			Common	Configuration to enable subtasks.
IssueLinkingEnabled	Boolean			Common	Configuration to enable issue linking.
TimeTrackingEnabled	Boolean			Common	Configuration to enable time tracking.
AttachmentsEnabled	Boolean			Common	Configuration to enable attachments.
WorkingHoursPerDay	Double			Common	Configuration of the working hours per day.
WorkingDaysPerWeek	Double			Common	Configuration of the working days per week.
TimeFormat	String			Common	Configuration of the time format.
DefaultUnit	String			Common	Configuration of the default unit.

Query the available Dashboards in Jira.

Table Specific Information

Select

The Cloud uses the Jira API to process some of the filters. Jira supports the Id and Filter columns but not both at the same time.

The Cloud processes other filters within the Cloud.

For example, the following queries are processed server side.

SELECT * FROM Dashboards WHERE Id = 10001

You can also filter on the pseudo column Filter:

SELECT * FROM Dashboards WHERE Filter = 'favourite'

Columns

Name	Type	References	SupportedOperators	Platform	Description
Id [KEY]	String		=	Common	The Id of the dashboard.
Name	String			Common	The name of the dashboard
View	String			Common	The view URL of the dashboard.

Pseudo-Columns

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

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

Query all the Epics.

Table Specific Information

Select

The Cloud uses the Jira API to process some of the filters.

The Cloud processes other filters within the Cloud.

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

SELECT * FROM Epics WHERE BoardId = 1

SELECT * FROM Epics WHERE BoardId IN (1, 2, 3)

By specifying the Id or Key of a certain epic:

SELECT * FROM Epics WHERE Id = 10000 

SELECT * FROM Epics WHERE Id IN (10000, 10001) 

SELECT * FROM Epics WHERE Key = 'TSS-9151' 

SELECT * FROM Epics WHERE Key IN ('TSS-9151', 'TSS-9152') 

Columns

Name	Type	References	SupportedOperators	Platform	Description
Id [KEY]	Integer		=,IN	Common	The Id of the Epic.
Key [KEY]	String		=,IN	Common	The key of the Epic.
BoardId [KEY]	Integer	Boards.Id	=,IN	Common	The board Id the Epic is in.
Name	String			Common	The name of the Epic.
Done	Boolean			Common	Whether or not the Epic is done.
ColorKey	String			Common	The key of the Epic's color.
Summary	String			Common	A brief summary for the Epic.

Query the favourite Filters in Jira.

Columns

Name	Type	References	SupportedOperators	Platform	Description
Id [KEY]	Integer			Common	The Id of the favourite filter.
Name	String			Common	The name assigned to the favourite filter.
JQLExpression	String			Common	The JQL by which the favourite filter will search issues by.
Description	String			Common	The description given to the favourite filter.
OwnerDisplayName	String			Common	The display name of the user who owns the favourite filter.
OwnerAccountId	String			Cloud	The account ID of the user who owns the favourite filter.
Count	Integer			Cloud	How many users have this filter as a favourite.
OwnerKey	String			Server	The key of the user who owns the favourite filter.

Query the available System and Custom Fields in Jira.

Columns

Name	Type	References	SupportedOperators	Platform	Description
Id [KEY]	String			Common	The Id of the field.
Key	String			Common	The key of the field.
Name	String			Common	The name of the field.
Custom	Boolean			Common	A boolean indicating if the field is custom.
Orderable	Boolean			Common	A boolean indicating if the field is orderable.
Navigable	Boolean			Common	A boolean indicating if the field is navigable.
Searchable	Boolean			Common	A boolean indicating if the field is searchable.

Query the set up Filters in Jira. This table is not available in Jira Server.

Table Specific Information

Select

The Cloud uses the Jira API to process some of the filters.

The Cloud processes other filters within the Cloud.

Retrieve all filters:

SELECT * FROM Filters 

By specifying the Id of a certain filter:

SELECT * FROM Filters WHERE Id = 10001 

Columns

Name	Type	References	SupportedOperators	Platform	Description
Id [KEY]	Integer		=	Cloud	The Id of the filter.
Name	String			Cloud	The name assigned to the filter.
JQLExpression	String			Cloud	The JQL by which the filter will search issues by.
Description	String			Cloud	The description given to the filter.
OwnerDisplayName	String		=	Cloud	The board Id the sprint originated from.
Favourite	Boolean			Cloud	Whether you have set this filter as a favourite.
FavouritedCount	Integer			Cloud	How many users have this filter as a favourite.
SubscriptionsAggregate	String			Cloud	Collection of user subscriptions to the filter .
SharePermissionsAggregate	String			Cloud	Aggregate of share permissions to the filter.

Query the available Groups in Jira.

Table Specific Information

Select

The Cloud uses the Jira API to process some of the filters. The Cloud processes other filters within the Cloud.

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

Columns

Name	Type	References	SupportedOperators	Platform	Description
Id [KEY]	String			Common	The Id of the group.
Name	String		=,!=,CONTAINS	Common	The name of the group.
Html	String			Common	The HTML of the group.
LabelsAggregate	String			Common	The labels of the group.

Query the Affected Versions of an issue in Jira.

Table Specific Information

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

Select

The Cloud uses the Jira API to process some of the filters.

The Cloud processes other filters within the Cloud.

The available columns for IssueAffectedVersions are IssueId, IssueKey, and JQL. For example:

SELECT * FROM IssueAffectedVersions WHERE IssueId > 10022 AND IssueId < 10090

Columns

Name	Type	References	SupportedOperators	Platform	Description
VersionId [KEY]	String		=,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The Id of the affected version.
IssueId [KEY]	Integer	Issues.Id	=,<>,>,>=,<,<=,IN,NOT IN	Common	The issue Id of the affected version.
IssueKey	String	Issues.Key	=,<>,>,>=,<,<=,IN,NOT IN	Common	The issue key of the affected version.
IssueCreatedDate	Datetime		=,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The date the affected version's issue was created.
IssueUpdatedDate	Datetime		=,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The date the affected version's issue was last updated.
Name	String			Common	The name of the affected version.
Archived	Boolean			Common	A boolean indicating if the affected is archived.
Released	Boolean			Common	A boolean indicating if the affected is released.
ReleaseDate	Date			Common	The release date.

Pseudo-Columns

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

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

Query the available IssueChangelogs in Jira.

Table Specific Information

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

Select

The Cloud uses the Jira API to process some of the filters.

The Cloud processes other filters within the Cloud.

The available columns for server-side filtering for IssueChangelogs are IssueId, IssueKey, ProjectId, ProjectKey, ProjectName, and JQL. For example:

SELECT * FROM IssueChangelogs WHERE IssueId<>10022
SELECT * FROM IssueChangelogs WHERE ProjectKey = 'PRJ'

Columns

Name	Type	References	SupportedOperators	Platform	Description
HistoryId	Integer			Common	The Id of the change.
IssueId	Integer	Issues.Id	=,<>,>,>=,<,<=,IN,NOT IN	Common	The Id of the issue.
IssueKey	String	Issues.Key	=,<>,>,>=,<,<=,IN,NOT IN	Common	The key of the issue.
Created	Datetime			Common	The creation date of the change.
IssueCreatedDate	Datetime		=,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The date the change's issue was created.
IssueUpdatedDate	Datetime		=,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The date the change's issue was last updated.
ItemField	String			Common	The item field of the change.
ItemFieldType	String			Common	The item field type of the change.
ItemFrom	String			Common	The item the issue changed from.
ItemFromString	String			Common	The description of the item the issue changed from.
ItemTo	String			Common	The item the issue changed to.
ItemToString	String			Common	The description of the item the issue changed to.
AuthorDisplayName	String			Common	The display name of the author who has updated the change.
AuthorAccountId	String			Cloud	The author name of the change.
AuthorName	String			Cloud	The author name of the change.
AuthorAccountKey	String			Server	The key of the author of the change.
AuthorAccountName	String			Server	The name of the author who has updated the change.
ProjectId	Integer	Projects.Id	=,!=,IS,IS NOT,IN,NOT IN	Common	The project Id of the issue.
ProjectKey	String	Projects.Key	=,!=,IS,IS NOT,IN,NOT IN	Common	The project key of the issue.
ProjectName	String		=,!=,IS,IS NOT,IN,NOT IN	Common	The project name of the issue.

Pseudo-Columns

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

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

Query the available IssueComponents in Jira.

Table Specific Information

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

Select

The Cloud uses the Jira API to process some of the filters.

The Cloud processes other filters within the Cloud.

The available columns for IssueComponents are IssueId, IssueKey, and JQL. For example:

SELECT * FROM IssueComponents WHERE IssueId > 10022 AND IssueId < 10090

Columns

Name	Type	References	SupportedOperators	Platform	Description
Id [KEY]	String		=,<>,IS,IS NOT,IN,NOT IN	Common	The Id of the component.
Name	String			Common	The name of the component.
IssueId [KEY]	Integer	Issues.Id	=,<>,>,>=,<,<=,IN,NOT IN	Common	The issue Id of the component.
IssueKey	String	Issues.Key	=,<>,>,>=,<,<=,IN,NOT IN	Common	The issue key of the component.
IssueCreatedDate	Datetime		=,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The date the component's issue was created.
IssueUpdatedDate	Datetime		=,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The date the component's issue was last updated.
Description	String			Common	The description of the component.

Pseudo-Columns

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

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

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

Columns

Name	Type	References	SupportedOperators	Platform	Description
Id [KEY]	Long		=		The ID of the custom field option.
CustomFieldId	Long	IssueCustomFields.CustomId	=		The ID of the custom field.
Name	String				The name of the custom field option.

Query the available Custom Fields in Jira.

Columns

Name	Type	References	SupportedOperators	Platform	Description
Id [KEY]	String			Common	The Id of the custom field.
Key	String			Common	The key of the custom field.
CustomId	Long				The custom ID of the field.
Name	String			Common	The name of the custom field.
CDataName	String			Common	The name of the custom field on the Issues table provided by CData, when IncludeCustomFields=true
Orderable	Boolean			Common	A boolean indicating if the field is orderable.
Navigable	Boolean			Common	A boolean indicating if the field is navigable.
Searchable	Boolean			Common	A boolean indicating if the field is searchable.

Query the available IssueFixVersions in Jira.

Table Specific Information

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

Select

The Cloud uses the Jira API to process some of the filters.

The Cloud processes other filters within the Cloud.

The available columns for IssueFixVersions are IssueId, IssueKey, and JQL. For example:

SELECT * FROM IssueFixVersions WHERE IssueId > 10022 AND IssueId < 10090

Columns

Name	Type	References	SupportedOperators	Platform	Description
VersionId [KEY]	String		=,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The Id of the fix version.
IssueId [KEY]	Integer	Issues.Id	=,<>,>,>=,<,<=,IN,NOT IN	Common	The issue Id of the fix version.
IssueKey	String	Issues.Key	=,<>,>,>=,<,<=,IN,NOT IN	Common	The issue key of the fix version.
IssueCreatedDate	Datetime		=,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The date the fix version's issue was created.
IssueUpdatedDate	Datetime		=,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The date the fix version's issue was last updated.
Name	String			Common	The name of the fix version.
Archived	Boolean			Common	A boolean indicating if the fix is archived.
Released	Boolean			Common	A boolean indicating if the fix is released.
ReleaseDate	Datetime			Common	The release date.

Pseudo-Columns

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

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

Query the available IssueLinks in Jira.

Table Specific Information

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

Select

The Cloud uses the Jira API to process some of the filters.

The Cloud processes other filters within the Cloud.

The available columns for IssueLinks are IssueId, IssueKey, and JQL. For example:

SELECT * FROM IssueLinks WHERE IssueId > 10022 AND IssueId < 10090

Columns

Name	Type	References	SupportedOperators	Platform	Description
Id [KEY]	String			Common	The Id of the link.
InwardIssueId	Integer	Issues.Id		Common	The Id of the issue targeting this issue in the link.
InwardIssueKey	String	Issues.Key		Common	The Key of the issue targeting this issue in the link.
OutWardIssueId	Integer	Issues.Id		Common	The Id of the issue this link is targeting.
OutwardIssueKey	String	Issues.Key		Common	The Key of the issue this link is targeting.
IssueId [KEY]	Integer	Issues.Id	=,<>,>,>=,<,<=,IN,NOT IN	Common	The link's original issue Id.
IssueKey	String	Issues.Key	=,<>,>,>=,<,<=,IN,NOT IN	Common	The link's original issue Key.
IssueCreatedDate	Datetime		=,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The date the link's issue was created.
IssueUpdatedDate	Datetime		=,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The date the links's issue was last updated.
TypeId	Id			Common	The Id of the link type.
TypeName	String			Common	The Name of the link type.

Pseudo-Columns

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

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

Lists all the issue link types.

Select

The Cloud will use the Jira APIs to process filters that refer to the Id column.

The Cloud will process the rest of the filter client side within itself. For example, the following queries are processed server-side:

SELECT * FROM IssueLinkTypes WHERE Id = '10003'

SELECT * FROM IssueLinkTypes WHERE Id IN ('10002', '10000')

Columns

Name	Type	References	SupportedOperators	Platform	Description
Id [KEY]	String		=,IN	Common	Unique identifier of the issue link type.
Name	String			Common	The name of the issue link type.
Inward	String			Common	The description of the issue link type inward link.
Outward	String			Common	The description of the issue link type outward link.

Query the default issue navigator columns.

Table Specific Information

Select

SELECT * FROM IssueNavigatorDefaultColumns

Columns

Name	Type	References	SupportedOperators	Platform	Description
Label	String			Common	The issue navigator column label.
Value	String			Common	The issue navigator column value.

Query the list of all issue priorities.

Table Specific Information

Select

The Cloud uses the Jira API to process some of the filters.

The Cloud processes other filters within the Cloud.

For example, the following queries are processed server side:

SELECT * FROM IssuePriorities WHERE Id = 1

Columns

Name	Type	References	SupportedOperators	Platform	Description
Id [KEY]	String		=	Common	The ID of the issue priority.
Name	String			Common	The name of the issue priority.
Description	String			Common	The description of the issue priority.
URL	String			Common	The URL of the issue priority.
StatusColor	String			Common	The color used to indicate the issue priority.
IconUrl	String			Common	The URL of the icon for the issue priority.

Query the list of all issue resolution values.

Table Specific Information

Select

The Cloud uses the Jira API to process some of the filters.

The Cloud processes other filters within the Cloud.

For example, the following queries are processed server side:

SELECT * FROM IssueResolutions WHERE Id = 10000

Columns

Name	Type	References	SupportedOperators	Platform	Description
Id [KEY]	String		=	Common	The ID of the issue resolution.
Name	String			Common	The name of the issue resolution.
Description	String			Common	The description of the issue resolution.
URL	String			Common	The URL of the issue resolution.

Query the available IssueSubtasks in Jira.

Table Specific Information

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

Select

The Cloud uses the Jira API to process some of the filters.

The Cloud processes other filters within the Cloud.

The available columns for IssueSubtasks are IssueId, IssueKey, and JQL. For example:

SELECT * FROM IssueSubtasks WHERE IssueId = 10009 AND JQL = 'creator = john'

Columns

Name	Type	References	SupportedOperators	Platform	Description
SubtaskId [KEY]	String			Common	The Id of the subtask.
SubtaskKey	String			Common	The key of the subtask.
IssueId	Integer	Issues.Id	=,<>,>,>=,<,<=,IN,NOT IN	Common	The Id of the parent issue.
IssueKey	String	Issues.Key	=,<>,>,>=,<,<=,IN,NOT IN	Common	The key of the parent issue.
IssueCreatedDate	Datetime		=,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The date the subtask's issue was created.
IssueUpdatedDate	Datetime		=,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The date the subtask's issue was last updated.
Summary	String			Common	The summary of the subtask.
TypeId	String	IssueTypes.Id		Common	The issue type Id of the subtask.
TypeName	String	IssueTypes.Name		Common	The issue type name of the subtask.
TypeDescription	String	IssueTypes.Description		Common	The issue type description of the subtask.
TypeSubtask	Boolean	IssueTypes.Subtask		Common	A boolean indicating if the issue is a subtask.
PriorityId	String			Common	The priority Id of the subtask.
PriorityName	String			Common	The priority name of the subtask.
StatusId	String			Common	The status Id of the subtask.

Pseudo-Columns

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

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

Query the available IssueTransitions in Jira.

Table Specific Information

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

Select

The Cloud uses the Jira API to process some of the filters.

The Cloud processes other filters within the Cloud.

The available columns for IssueTransitions are IssueId, IssueKey, and JQL. For example:

SELECT * FROM IssueTransitions WHERE JQL = 'id=10022'

Columns

Name	Type	References	SupportedOperators	Platform	Description
Id [KEY]	String			Common	The Id of the transition.
IssueId [KEY]	Integer	Issues.Id	=,<>,>,>=,<,<=,IN,NOT IN	Common	The Id of the issue.
IssueKey	String	Issues.Key	=,<>,>,>=,<,<=,IN,NOT IN	Common	The key of the issue.
IssueCreatedDate	Datetime		=,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The date the transition's issue was created.
IssueUpdatedDate	Datetime		=,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The date the transition's issue was last updated.
HasScreen	String			Common	Whether the transition has a screen.
Name	String			Common	The name of the transition.
ToDescription	String			Common	The description of the status the issue is transitioning to.
ToId	String			Common	The Id of the status the issue is transitioning to.
ToName	String			Common	The name of the status the issue is transitioning to.
StatusCategoryId	String			Common	The status category Id of the transition.
StatusCategoryKey	String			Common	The status category key of the transition.
StatusCategoryName	String			Common	The status category name of the transition.

Pseudo-Columns

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

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

Query the available Permissions of the current user in Jira.

Table Specific Information

Select

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

SELECT * FROM MyPermissions

Columns

Name	Type	References	SupportedOperators	Platform	Description
Id [KEY]	Integer			Common	The Id of the permission.
Key [KEY]	String			Common	The key of the permission.
Name	String			Common	The name of the permission.
Type	String			Common	The type of the permission.
Description	String			Common	The description of the permission.
HavePermission	Boolean			Common	Boolean indicating if the permission is set or not.
DeprecatedKey	Boolean			Common	Boolean indicating if the permission is deprecated.
Permissions	String		=	Common	Permissions as comma seperated values.

Pseudo-Columns

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

Name	Type	Description
ProjectKey	String		The key of the project associated with the permission.
ProjectId	String		The Id of the project associated with the permission.
IssueKey	String		The key of the issue associated with the permission.
IssueId	String		The Id of the issue associated with the permission.

Query the available Permissions in Jira.

Columns

Name	Type	References	SupportedOperators	Platform	Description
Key [KEY]	String			Common	The key of the permission.
Name	String			Common	The name of the permission.
Type	String			Common	The type of the permission.
Description	String			Common	The description of the permission.

Query all the project categories.

Columns

Name	Type	References	SupportedOperators	Platform	Description
Id [KEY]	Integer		=	Common	Unique identifier of the project category.
Name	String			Common	Name of the project category.
Description	String			Common	Description of the project category.
Link	String			Common	Link to this project category.