Connection String OptionsBack To Top

AuthScheme
URL
User
Password
APIToken
SSOLoginURL
SSOProperties
SSOExchangeUrl
SSOAppName
SSOAppPassword
InitiateOAuth
OAuthVersion
OAuthClientId
OAuthClientSecret
OAuthAccessToken
OAuthAccessTokenSecret
Scope
OAuthSettingsLocation
CallbackURL
CloudId
OAuthVerifier
AuthToken
AuthKey
OAuthRefreshToken
OAuthExpiresIn
OAuthTokenTimestamp
CertificateStoreType
CertificateStore
CertificateSubject
CertificateStorePassword
SSLClientCert
SSLClientCertType
SSLClientCertPassword
SSLClientCertSubject
SSLServerCert
FirewallType
FirewallServer
FirewallPort
FirewallUser
FirewallPassword
ProxyAutoDetect
ProxyServer
ProxyPort
ProxyAuthScheme
ProxyUser
ProxyPassword
ProxySSLType
ProxyExceptions
Logfile
Verbosity
LogModules
MaxLogFileSize
MaxLogFileCount
Location
BrowsableSchemas
Tables
Views
AutoCache
CacheDriver
CacheConnection
CacheLocation
CacheTolerance
Offline
CacheMetadata
BatchSize
ConnectionLifeTime
ConnectOnOpen
IncludeCustomFields
MaxRows
MaxThreads
Other
Pagesize
PoolIdleTimeout
PoolMaxSize
PoolMinSize
PoolWaitTime
PseudoColumns
Readonly
RTK
Timeout
Timezone
UseConnectionPooling
UseDefaultOrderBy
UseDisplayNames
UserDefinedViews
UseSimpleNames

AuthScheme

Data Type

string

Default Value

"Auto"

Remarks

Auto: Lets the driver decide automatically based on the other connection properties you have set.
OAuth: Set this to perform OAuth authentication.
APIToken: Set this to use basic User/APIToken authentication for Cloud instances.
Basic: Set this to use basic User/Password authentication for Server instances.
Crowd: Set this to perform Crowd SSO authentication.
OKTA: Set this to perform OKTA SSO authentication.
LDAP: Set this to perform LDAP authentication.

URL

Data Type

string

Default Value

Remarks

The URL to your JIRA endpoint; for example, https://yoursitename.atlassian.net.

User

Data Type

string

Default Value

Remarks

Together with Password, this field is used to authenticate against the Jira server.

Password

Data Type

string

Default Value

Remarks

The User and Password are together used to authenticate with the server.

APIToken

Data Type

string

Default Value

Remarks

APIToken of the currently authenticated user.

SSOLoginURL

Data Type

string

Default Value

Remarks

The identity provider's login URL.

SSOProperties

Data Type

string

Default Value

Remarks

Additional properties required to connect to the identity provider in a semicolon-separated list. is used in conjunction with the SSOLoginURL.

SSO configuration is discussed further in Establishing a Connection.

The CData JDBC Driver for Jira will use the url specified here to consume a SAML response and retrieve Jira specific credentials. The retrieved credentials are the final piece during the SSO connection that are used to communicate with Jira.

SSOAppName

Data Type

string

Default Value

Remarks

Along with SSOAppPassword, may be be specified to identify and authenticate to your app configured in the SSO IdP. Currently only Crowd supports it.

SSOAppPassword

Data Type

string

Default Value

Remarks

Along with SSOAppName, may be be specified to identify and authenticate to your app configured in the SSO IdP. Currently only Crowd supports it.

InitiateOAuth

Data Type

string

Default Value

"OFF"

Remarks

The following options are available:

OFF: Indicates that the OAuth flow will be handled entirely by the user. An OAuthAccessToken will be required to authenticate.
GETANDREFRESH: Indicates that the entire OAuth Flow will be handled by the driver. If no token currently exists, it will be obtained by prompting the user via the browser. If a token exists, it will be refreshed when applicable.
REFRESH: Indicates that the driver will only handle refreshing the OAuthAccessToken. The user will never be prompted by the driver to authenticate via the browser. The user must handle obtaining the OAuthAccessToken and OAuthRefreshToken initially.

OAuthVersion

Data Type

string

Default Value

"2.0"

Remarks

The version of OAuth being used. The following options are available: 1.0,2.0

OAuthClientId

Data Type

string

Default Value

Remarks

As part of registering an OAuth application, you will receive the OAuthClientId value, sometimes also called a consumer key, and a client secret, the OAuthClientSecret.

OAuthClientSecret

Data Type

string

Default Value

Remarks

As part of registering an OAuth application, you will receive the OAuthClientId, also called a consumer key. You will also receive a client secret, also called a consumer secret. Set the client secret in the OAuthClientSecret property.

OAuthAccessToken

Data Type

string

Default Value

Remarks

The OAuthAccessToken property is used to connect using OAuth. The OAuthAccessToken is retrieved from the OAuth server as part of the authentication process. It has a server-dependent timeout and can be reused between requests.

The access token is used in place of your user name and password. The access token protects your credentials by keeping them on the server.

OAuthAccessTokenSecret

Data Type

string

Default Value

Remarks

The OAuthAccessTokenSecret property is used to connect and authenticate using OAuth. The OAuthAccessTokenSecret is retrieved from the OAuth server as part of the authentication process. It is used with the OAuthAccessToken and can be used for multiple requests until it times out.

Scope

Data Type

string

Default Value

Remarks

Specify scope to obtain the initial access and refresh token.

By default the driver will request that the user authorize all available scopes. If you want to override this, you can set this property to a space-separated list of OAuth scopes.

OAuthSettingsLocation

Data Type

string

Default Value

"%APPDATA%\\CData\\JIRA Data Provider\\OAuthSettings.txt"

Remarks

When InitiateOAuth is set to GETANDREFRESH or REFRESH, the driver saves OAuth values to avoid requiring the user to manually enter OAuth connection properties and allowing the credentials to be shared across connections or processes.

Alternatively to specifying a file path, memory storage can be used instead. Memory locations are specified by using a value starting with 'memory://' followed by a unique identifier for that set of credentials (ex: memory://user1). The identifier can be anything you choose but should be unique to the user. Unlike with the file based storage, you must manually store the credentials when closing the connection with memory storage to be able to set them in the connection when the process is started again. The OAuth property values can be retrieved with a query to the sys_connection_props system table. If there are multiple connections using the same credentials, the properties should be read from the last connection to be closed.

If left unspecified, the default location is "%APPDATA%\\CData\\JIRA Data Provider\\OAuthSettings.txt" with %APPDATA% being set to the user's configuration directory:


Platform	%APPDATA%
Windows	The value of the APPDATA environment variable
Mac	~/Library/Application Support
Linux	~/.config

CallbackURL

Data Type

string

Default Value

Remarks

During the authentication process, the OAuth authorization server redirects the user to this URL. This value must match the callback URL you specify in your app settings.

CloudId

Data Type

string

Default Value

Remarks

This will be determined automatically during OAuth 2.0 authentication and returned from the GetOAuthAccessToken stored procedure. Do not set a value for CloudId when using InitiateOAuth.

OAuthVerifier

Data Type

string

Default Value

Remarks

The verifier code returned from the OAuth authorization URL. This can be used on systems where a browser cannot be launched such as headless systems.

Authentication on Headless Machines

See Establishing a Connection to obtain the OAuthVerifier value.

Set OAuthSettingsLocation along with OAuthVerifier. When you connect, the driver exchanges the OAuthVerifier for the OAuth authentication tokens and saves them, encrypted, to the specified file. Set InitiateOAuth to GETANDREFRESH automate the exchange.

Once the OAuth settings file has been generated, you can remove OAuthVerifier from the connection properties and connect with OAuthSettingsLocation set.

To automatically refresh the OAuth token values, set OAuthSettingsLocation and additionally set InitiateOAuth to REFRESH.

AuthToken

Data Type

string

Default Value

Remarks

This property is required only when performing headless authentication in OAuth 1.0. It can be obtained from the GetOAuthAuthorizationUrl stored procedure.

It can be supplied alongside the AuthKey in the GetOAuthAccessToken stored procedure to obtain the OAuthAccessToken.

AuthKey

Data Type

string

Default Value

Remarks

This property is required only when performing headless authentication in OAuth 1.0. It can be obtained from the GetOAuthAuthorizationUrl stored procedure.

It can be supplied alongside the AuthToken in the GetOAuthAccessToken stored procedure to obtain the OAuthAccessToken.

OAuthRefreshToken

Data Type

string

Default Value

Remarks

The OAuthRefreshToken property is used to refresh the OAuthAccessToken when using OAuth authentication.

OAuthExpiresIn

Data Type

string

Default Value

Remarks

Pair with OAuthTokenTimestamp to determine when the AccessToken will expire.

OAuthTokenTimestamp

Data Type

string

Default Value

Remarks

Pair with OAuthExpiresIn to determine when the AccessToken will expire.

CertificateStoreType

Data Type

string

Default Value

"USER"

Remarks

The name of the certificate store for the client certificate used in Jira private application authentication. Jira private application authentication uses SSL client authentication with digital certificates.

Specifying the Certificate Store Type

The CertificateStoreType property can take one of the following values:


User - default	For Windows, this specifies that the certificate store is a certificate store owned by the current user. Note: This store type is not available in Java.
Machine	For Windows, this specifies that the certificate store is a machine store. Note: this store type is not available in Java.
PFXFile	The certificate store is the name of a PFX (PKCS12) file containing certificates.
PFXBlob	The certificate store is a string (base-64-encoded) representing a certificate store in PFX (PKCS12) format.
JKSFile	The certificate store is the name of a Java key store (JKS) file containing certificates. Note: this store type is only available in Java.
JKSBlob	The certificate store is a string (base-64-encoded) representing a certificate store in Java key store (JKS) format. Note: this store type is only available in Java.
PEMKeyFile	The certificate store is the name of a PEM-encoded file that contains a private key and an optional certificate.
PublicKeyFile	The certificate store is the name of a file that contains a PEM- or DER-encoded public key certificate.
PublicKeyBlob	The certificate store is a string (base-64-encoded) that contains a PEM- or DER-encoded public key certificate.
SSHPublicKeyBlob	The certificate store is a string (base-64-encoded) that contains an SSH-style public key.
P10BFile	The certificate store is the name of a PKCS10 file containing certificates.
SSHPublicKeyFile	The certificate store is the name of a file that contains an SSH-style public key.
PPKFile	The certificate store is the name of a file that contains a PPK (PuTTY Private Key).
XMLFile	The certificate store is the name of a file that contains a certificate in XML format.
XMLBlob	The certificate store is a string that contains a certificate in XML format.

Related Connection Properties

To specify the private certificate, you may need to set the following properties in addition to CertificateStoreType:

CertificateStore: Set this to the name of the certificate store. For example, "MY" for a Windows certificate store or the file name of a file store.
CertificateStorePassword: If the store is password-protected, provide the password here.
CertificateSubject: Set this to the subject of the certificate. To locate the certificate in the store, the driver executes a search based on the specified subject. Refer to the CertificateSubject field for details.

CertificateStore

Data Type

string

Default Value

Remarks

The location of the certificate store for the client certificate used in Jira application authentication.

CertificateSubject

Data Type

string

Default Value

Remarks

The subject of the client certificate used in Jira private application authentication.

About the Jira Client SSL Auth Flow

Jira private application authentication uses SSL client authentication with digital certificates.

Loading a Certificate

When loading a certificate the subject is used to locate the certificate in the store.

If an exact match is not found, the store is searched for subjects containing the value of the property.
If a match is still not found, no certificate is selected.
The special value "*" picks the first certificate in the certificate store.

Specifying the Subject

The certificate subject is a comma-separated list of distinguished name fields and values. For instance "CN=www.server.com, OU=test, C=US, E=support@cdata.com". Common fields and their meanings are displayed below.


Field	Meaning
CN	Common Name. This is commonly a host name like www.server.com.
O	Organization
OU	Organizational Unit
L	Locality
S	State
C	Country
E	Email Address

Note: If a field value contains a comma it must be quoted.

Additional Connection Properties

To specify the private certificate, you need to set CertificateSubject and the following additional properties:

CertificateStore
CertificateStoreType
CertificateStorePassword

CertificateStorePassword

Data Type

string

Default Value

Remarks

If the certificate store is of a type that requires a password, this property is used to specify that password in order to open the certificate store.

SSLClientCert

Data Type

string

Default Value

Remarks

The name of the certificate store for the client certificate.

The SSLClientCertType field specifies the type of the certificate store specified by SSLClientCert. If the store is password protected, specify the password in SSLClientCertPassword.

SSLClientCert is used in conjunction with the SSLClientCertSubject field in order to specify client certificates. If SSLClientCert has a value, and SSLClientCertSubject is set, a search for a certificate is initiated. See SSLClientCertSubject for more information.

Designations of certificate stores are platform-dependent.

The following are designations of the most common User and Machine certificate stores in Windows:


MY	A certificate store holding personal certificates with their associated private keys.
CA	Certifying authority certificates.
ROOT	Root certificates.
SPC	Software publisher certificates.

In Java, the certificate store normally is a file containing certificates and optional private keys.

When the certificate store type is PFXFile, this property must be set to the name of the file. When the type is PFXBlob, the property must be set to the binary contents of a PFX file (for example, PKCS12 certificate store).

SSLClientCertType

Data Type

string

Default Value

"USER"

Remarks

This property can take one of the following values:


USER - default	For Windows, this specifies that the certificate store is a certificate store owned by the current user. Note that this store type is not available in Java.
MACHINE	For Windows, this specifies that the certificate store is a machine store. Note that this store type is not available in Java.
PFXFILE	The certificate store is the name of a PFX (PKCS12) file containing certificates.
PFXBLOB	The certificate store is a string (base-64-encoded) representing a certificate store in PFX (PKCS12) format.
JKSFILE	The certificate store is the name of a Java key store (JKS) file containing certificates. Note that this store type is only available in Java.
JKSBLOB	The certificate store is a string (base-64-encoded) representing a certificate store in JKS format. Note that this store type is only available in Java.
PEMKEY_FILE	The certificate store is the name of a PEM-encoded file that contains a private key and an optional certificate.
PEMKEY_BLOB	The certificate store is a string (base64-encoded) that contains a private key and an optional certificate.
PUBLIC_KEY_FILE	The certificate store is the name of a file that contains a PEM- or DER-encoded public key certificate.
PUBLIC_KEY_BLOB	The certificate store is a string (base-64-encoded) that contains a PEM- or DER-encoded public key certificate.
SSHPUBLIC_KEY_FILE	The certificate store is the name of a file that contains an SSH-style public key.
SSHPUBLIC_KEY_BLOB	The certificate store is a string (base-64-encoded) that contains an SSH-style public key.
P7BFILE	The certificate store is the name of a PKCS7 file containing certificates.
PPKFILE	The certificate store is the name of a file that contains a PuTTY Private Key (PPK).
XMLFILE	The certificate store is the name of a file that contains a certificate in XML format.
XMLBLOB	The certificate store is a string that contains a certificate in XML format.

SSLClientCertPassword

Data Type

string

Default Value

Remarks

If the certificate store is of a type that requires a password, this property is used to specify that password to open the certificate store.

SSLClientCertSubject

Data Type

string

Default Value

"*"

Remarks

When loading a certificate the subject is used to locate the certificate in the store.

If an exact match is not found, the store is searched for subjects containing the value of the property. If a match is still not found, the property is set to an empty string, and no certificate is selected.

The special value "*" picks the first certificate in the certificate store.

The certificate subject is a comma separated list of distinguished name fields and values. For example, "CN=www.server.com, OU=test, C=US, E=support@company.com". The common fields and their meanings are shown below.


Field	Meaning
CN	Common Name. This is commonly a host name like www.server.com.
O	Organization
OU	Organizational Unit
L	Locality
S	State
C	Country
E	Email Address

If a field value contains a comma, it must be quoted.

SSLServerCert

Data Type

string

Default Value

Remarks

If using a TLS/SSL connection, this property can be used to specify the TLS/SSL certificate to be accepted from the server. Any other certificate that is not trusted by the machine is rejected.

This property can take the following forms:


Description	Example
A full PEM Certificate (example shortened for brevity)	-----BEGIN CERTIFICATE----- MIIChTCCAe4CAQAwDQYJKoZIhv......Qw== -----END CERTIFICATE-----
A path to a local file containing the certificate	C:\cert.cer
The public key (example shortened for brevity)	-----BEGIN RSA PUBLIC KEY----- MIGfMA0GCSq......AQAB -----END RSA PUBLIC KEY-----
The MD5 Thumbprint (hex values can also be either space or colon separated)	ecadbdda5a1529c58a1e9e09828d70e4
The SHA1 Thumbprint (hex values can also be either space or colon separated)	34a929226ae0819f2ec14b4a3d904f801cbb150d

If not specified, any certificate trusted by the machine is accepted.

Certificates are validated as trusted by the machine based on the System's trust store. The trust store used is the 'javax.net.ssl.trustStore' value specified for the system. If no value is specified for this property, Java's default trust store is used (for example, JAVA_HOME\lib\security\cacerts).

Use '*' to signify to accept all certificates. Note that this is not recommended due to security concerns.

FirewallType

Data Type

string

Default Value

"NONE"

Remarks

This property specifies the protocol that the driver will use to tunnel traffic through the FirewallServer proxy. Note that by default, the driver connects to the system proxy; to disable this behavior and connect to one of the following proxy types, set ProxyAutoDetect to false.


Type	Default Port	Description
TUNNEL	80	When this is set, the driver opens a connection to Jira and traffic flows back and forth through the proxy.
SOCKS4	1080	When this is set, the driver sends data through the SOCKS 4 proxy specified by FirewallServer and FirewallPort and passes the FirewallUser value to the proxy, which determines if the connection request should be granted.
SOCKS5	1080	When this is set, the driver sends data through the SOCKS 5 proxy specified by FirewallServer and FirewallPort. If your proxy requires authentication, set FirewallUser and FirewallPassword to credentials the proxy recognizes.

To connect to HTTP proxies, use ProxyServer and ProxyPort. To authenticate to HTTP proxies, use ProxyAuthScheme, ProxyUser, and ProxyPassword.

FirewallServer

Data Type

string

Default Value

Remarks

This property specifies the IP address, DNS name, or host name of a proxy allowing traversal of a firewall. The protocol is specified by FirewallType: Use FirewallServer with this property to connect through SOCKS or do tunneling. Use ProxyServer to connect to an HTTP proxy.

Note that the driver uses the system proxy by default. To use a different proxy, set ProxyAutoDetect to false.

FirewallPort

Data Type

int

Default Value

Remarks

This specifies the TCP port for a proxy allowing traversal of a firewall. Use FirewallServer to specify the name or IP address. Specify the protocol with FirewallType.

FirewallUser

Data Type

string

Default Value

Remarks

The FirewallUser and FirewallPassword properties are used to authenticate against the proxy specified in FirewallServer and FirewallPort, following the authentication method specified in FirewallType.

FirewallPassword

Data Type

string

Default Value

Remarks

This property is passed to the proxy specified by FirewallServer and FirewallPort, following the authentication method specified by FirewallType.

ProxyAutoDetect

Data Type

bool

Default Value

false

Remarks

This takes precedence over other proxy settings, so you'll need to set ProxyAutoDetect to FALSE in order use custom proxy settings.

NOTE: When this property is set to True, the proxy used is determined as follows:

A search from the JVM properties (http.proxy, https.proxy, socksProxy, etc.) is performed.
In the case that the JVM properties don't exist, a search from java.home/lib/net.properties is performed.
In the case that java.net.useSystemProxies is set to True, a search from the SystemProxy is performed.
In Windows only, an attempt is made to retrieve these properties from the Internet Options in the registry.

To connect to an HTTP proxy, see ProxyServer. For other proxies, such as SOCKS or tunneling, see FirewallType.

ProxyServer

Data Type

string

Default Value

Remarks

The hostname or IP address of a proxy to route HTTP traffic through. The driver can use the HTTP, Windows (NTLM), or Kerberos authentication types to authenticate to an HTTP proxy.

If you need to connect through a SOCKS proxy or tunnel the connection, see FirewallType.

By default, the driver uses the system proxy. If you need to use another proxy, set ProxyAutoDetect to false.

ProxyPort

Data Type

int

Default Value

Remarks

The port the HTTP proxy is running on that you want to redirect HTTP traffic through. Specify the HTTP proxy in ProxyServer. For other proxy types, see FirewallType.

ProxyAuthScheme

Data Type

string

Default Value

"BASIC"

Remarks

This value specifies the authentication type to use to authenticate to the HTTP proxy specified by ProxyServer and ProxyPort.

Note that the driver will use the system proxy settings by default, without further configuration needed; if you want to connect to another proxy, you will need to set ProxyAutoDetect to false, in addition to ProxyServer and ProxyPort. To authenticate, set ProxyAuthScheme and set ProxyUser and ProxyPassword, if needed.

The authentication type can be one of the following:

BASIC: The driver performs HTTP BASIC authentication.
DIGEST: The driver performs HTTP DIGEST authentication.
NEGOTIATE: The driver retrieves an NTLM or Kerberos token based on the applicable protocol for authentication.
PROPRIETARY: The driver does not generate an NTLM or Kerberos token. You must supply this token in the Authorization header of the HTTP request.

If you need to use another authentication type, such as SOCKS 5 authentication, see FirewallType.

ProxyUser

Data Type

string

Default Value

Remarks

The ProxyUser and ProxyPassword options are used to connect and authenticate against the HTTP proxy specified in ProxyServer.

You can select one of the available authentication types in ProxyAuthScheme. If you are using HTTP authentication, set this to the user name of a user recognized by the HTTP proxy. If you are using Windows or Kerberos authentication, set this property to a user name in one of the following formats:

user@domain

domain\user

ProxyPassword

Data Type

string

Default Value

Remarks

This property is used to authenticate to an HTTP proxy server that supports NTLM (Windows), Kerberos, or HTTP authentication. To specify the HTTP proxy, you can set ProxyServer and ProxyPort. To specify the authentication type, set ProxyAuthScheme.

If you are using HTTP authentication, additionally set ProxyUser and ProxyPassword to HTTP proxy.

If you are using NTLM authentication, set ProxyUser and ProxyPassword to your Windows password. You may also need these to complete Kerberos authentication.

For SOCKS 5 authentication or tunneling, see FirewallType.

By default, the driver uses the system proxy. If you want to connect to another proxy, set ProxyAutoDetect to false.

ProxySSLType

Data Type

string

Default Value

"AUTO"

Remarks

This property determines when to use SSL for the connection to an HTTP proxy specified by ProxyServer. This value can be AUTO, ALWAYS, NEVER, or TUNNEL. The applicable values are the following:


AUTO	Default setting. If the URL is an HTTPS URL, the driver will use the TUNNEL option. If the URL is an HTTP URL, the component will use the NEVER option.
ALWAYS	The connection is always SSL enabled.
NEVER	The connection is not SSL enabled.
TUNNEL	The connection is through a tunneling proxy. The proxy server opens a connection to the remote host and traffic flows back and forth through the proxy.

ProxyExceptions

Data Type

string

Default Value

Remarks

The ProxyServer is used for all addresses, except for addresses defined in this property. Use semicolons to separate entries.

Note that the driver uses the system proxy settings by default, without further configuration needed; if you want to explicitly configure proxy exceptions for this connection, you need to set ProxyAutoDetect = false, and configure ProxyServer and ProxyPort. To authenticate, set ProxyAuthScheme and set ProxyUser and ProxyPassword, if needed.

Logfile

Data Type

string

Default Value

Remarks

Once this property is set, the driver will populate the log file as it carries out various tasks, such as when authentication is performed or queries are executed. If the specified file doesn't already exist, it will be created.

Connection strings and version information are also logged, though connection properties containing sensitive information are masked automatically.

If a relative filepath is supplied, the location of the log file will be resolved based on the path found in the Location connection property.

For more control over what is written to the log file, you can adjust the Verbosity property.

Log contents are categorized into several modules. You can show/hide individual modules using the LogModules property.

To edit the maximum size of a single logfile before a new one is created, see MaxLogFileSize.

If you would like to place a cap on the number of logfiles generated, use MaxLogFileCount.

Java Logging

Java logging is also supported. To enable Java logging, set Logfile to:

Logfile=JAVALOG://myloggername

As in the above sample, JAVALOG:// is a required prefix to use Java logging, and you will substitute your own Logger.

The supplied Logger's getLogger method is then called, using the supplied value to create the Logger instance. If a logging instance already exists, it will reference the existing instance.

When Java logging is enabled, the Verbosity will now correspond to specific logging levels.

Verbosity

Data Type

string

Default Value

"1"

Remarks

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

LogModules

Data Type

string

Default Value

Remarks

Only the modules specified (separated by ';') will be included in the log file. By default all modules are included.

See the Logging page for an overview.

MaxLogFileSize

Data Type

string

Default Value

"100MB"

Remarks

When the limit is hit, a new log is created in the same folder with the date and time appended to the end. The default limit is 100 MB. Values lower than 100 kB will use 100 kB as the value instead.

Adjust the maximum number of logfiles generated with MaxLogFileCount.

MaxLogFileCount

Data Type

int

Default Value

-1

Remarks

When the limit is hit, a new log is created in the same folder with the date and time appended to the end and the oldest log file will be deleted.

The minimum supported value is 2. A value of 0 or a negative value indicates no limit on the count.

Adjust the maximum size of the logfiles generated with MaxLogFileSize.

Location

Data Type

string

Default Value

"%APPDATA%\\CData\\JIRA Data Provider\\Schema"

Remarks

The path to a directory which contains the schema files for the driver (.rsd files for tables and views, .rsb files for stored procedures). The folder location can be a relative path from the location of the executable. The Location property is only needed if you want to customize definitions (for example, change a column name, ignore a column, and so on) or extend the data model with new tables, views, or stored procedures.

If left unspecified, the default location is "%APPDATA%\\CData\\JIRA Data Provider\\Schema" with %APPDATA% being set to the user's configuration directory:


Platform	%APPDATA%
Windows	The value of the APPDATA environment variable
Mac	~/Library/Application Support
Linux	~/.config

BrowsableSchemas

Data Type

string

Default Value

Remarks

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

Tables

Data Type

string

Default Value

Remarks

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

This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.

Specify the tables you want in a comma-separated list. Each table should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Tables=TableA,[TableB/WithSlash],WithCatalog.WithSchema.`TableC With Space`.

Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.

Views

Data Type

string

Default Value

Remarks

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

This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.

Specify the views you want in a comma-separated list. Each view should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Views=ViewA,[ViewB/WithSlash],WithCatalog.WithSchema.`ViewC With Space`.

AutoCache

Data Type

bool

Default Value

false

Remarks

When AutoCache = true, the driver automatically maintains a cache of your table's data in the database of your choice.

Setting the Caching Database

When AutoCache = true, the driver caches to a simple, file-based cache. You can configure its location or cache to a different database with the following properties:

CacheLocation: Specifies the path to the file store.
CacheDriver and CacheConnection: Specifies a driver to a database and the connection string.

CacheDriver

Data Type

string

Default Value

Remarks

You can cache to any database for which you have a JDBC driver, including CData JDBC drivers.

The cache database is determined by the CacheDriver and CacheConnection properties. The CacheDriver is the name of the JDBC driver class that you want to use to cache data.

Note: you must add the CacheDriver JAR file to the classpath.

Examples

The following examples show how to cache to several major databases. Refer to CacheConnection for more information on the JDBC URL syntax and typical connection properties.

Derby and Java DB

The driver simplifies Derby configuration. Java DB is the Oracle distribution of Derby. The JAR file is shipped in the JDK. You can find the JAR file, derby.jar, in the db subfolder of the JDK installation. In most caching scenarios, you need to specify only the following, after adding derby.jar to the classpath:

jdbc:jira:CacheLocation='c:/Temp/cachedir';User=admin;APIToken=myApiToken;Url=https://yoursitename.atlassian.net

To customize the Derby JDBC URL, use CacheDriver and CacheConnection. For example, to cache to an in-memory database, use a JDBC URL like the following:

jdbc:jira:CacheDriver=org.apache.derby.jdbc.EmbeddedDriver;CacheConnection='jdbc:derby:memory';User=admin;APIToken=myApiToken;Url=https://yoursitename.atlassian.net

SQLite

The following is a JDBC URL for the SQLite JDBC driver:

jdbc:jira:CacheDriver=org.sqlite.JDBC;CacheConnection='jdbc:sqlite:C:/Temp/sqlite.db';User=admin;APIToken=myApiToken;Url=https://yoursitename.atlassian.net

MySQL

The following is a JDBC URL for the included CData JDBC Driver for MySQL:

  jdbc:jira:Cache Driver=cdata.jdbc.mysql.MySQLDriver;Cache Connection='jdbc:mysql:Server=localhost;Port=3306;Database=cache;User=root;Password=123456';User=admin;APIToken=myApiToken;Url=https://yoursitename.atlassian.net

SQL Server

The following JDBC URL uses the Microsoft JDBC Driver for SQL Server:

jdbc:jira:Cache Driver=com.microsoft.sqlserver.jdbc.SQLServerDriver;Cache Connection='jdbc:sqlserver://localhost\sqlexpress:7437;user=sa;password=123456;databaseName=Cache';User=admin;APIToken=myApiToken;Url=https://yoursitename.atlassian.net

Oracle

The following is a JDBC URL for the Oracle Thin Client:

jdbc:jira:Cache Driver=oracle.jdbc.OracleDriver;CacheConnection='jdbc:oracle:thin:scott/tiger@localhost:1521:orcldb';User=admin;APIToken=myApiToken;Url=https://yoursitename.atlassian.net

NOTE: If using a version of Oracle older than 9i, the cache driver will instead be oracle.jdbc.driver.OracleDriver .

PostgreSQL

The following JDBC URL uses the official PostgreSQL JDBC driver:

jdbc:jira:CacheDriver=cdata.jdbc.postgresql.PostgreSQLDriver;CacheConnection='jdbc:postgresql:User=postgres;Password=admin;Database=postgres;Server=localhost;Port=5432;';User=admin;APIToken=myApiToken;Url=https://yoursitename.atlassian.net

CacheConnection

Data Type

string

Default Value

Remarks

The cache database is determined based on the CacheDriver and CacheConnection properties. Both properties are required to use the cache database. Examples of common cache database settings can be found below. For more information on setting the caching database's driver, refer to CacheDriver.

The connection string specified in the CacheConnection property is passed directly to the underlying CacheDriver. Consult the documentation for the specific JDBC driver for more information on the available properties. Make sure to include the JDBC driver in your application's classpath.

Derby and Java DB

The driver simplifies caching to Derby, only requiring you to set the CacheLocation property to make a basic connection.

Alternatively, you can configure the connection to Derby manually using CacheDriver and CacheConnection. The following is the Derby JDBC URL syntax:

jdbc:derby:[subsubprotocol:][databaseName][;attribute=value[;attribute=value] ... ]

For example, to cache to an in-memory database, use the following:

jdbc:derby:memory

SQLite

To cache to SQLite, you can use the SQLite JDBC driver. The following is the syntax of the JDBC URL:

jdbc:sqlite:dataSource

Data Source: The path to an SQLite database file. Or, use a value of :memory to cache in memory.

MySQL

The installation includes the CData JDBC Driver for MySQL. The following is an example JDBC URL:

jdbc:mysql:User=root;Password=root;Server=localhost;Port=3306;Database=cache

The following are typical connection properties:

Server: The IP address or domain name of the server you want to connect to.
Port: The port that the server is running on.
User: The user name provided for authentication to the database.
Password: The password provided for authentication to the database.
Database: The name of the database.

SQL Server

The JDBC URL for the Microsoft JDBC Driver for SQL Server has the following syntax:

jdbc:sqlserver://[serverName[\instance][:port]][;database=databaseName][;property=value[;property=value] ... ]

For example:

jdbc:sqlserver://localhost\sqlexpress:1433;integratedSecurity=true

The following are typical SQL Server connection properties:

Server: The name or network address of the computer running SQL Server. To connect to a named instance instead of the default instance, this property can be used to specify the host name and the instance, separated by a backslash.
Port: The port SQL Server is running on.
Database: The name of the SQL Server database.
Integrated Security: Set this option to true to use the current Windows account for authentication. Set this option to false if you are setting the User and Password in the connection.
To use integrated security, you will also need to add sqljdbc_auth.dll to a folder on the Windows system path. This file is located in the auth subfolder of the Microsoft JDBC Driver for SQL Server installation. The bitness of the assembly must match the bitness of your JVM.
User Id: The user name provided for authentication with SQL Server. This property is only needed if you are not using integrated security.
Password: The password provided for authentication with SQL Server. This property is only needed if you are not using integrated security.

Oracle

The following is the conventional JDBC URL syntax for the Oracle JDBC Thin driver:

jdbc:oracle:thin:[userId/password]@[//]host[[:port][:sid]]

For example:

jdbc:oracle:thin:scott/tiger@myhost:1521:orcl

The following are typical connection properties:

Data Source: The connect descriptor that identifies the Oracle database. This can be a TNS connect descriptor, an Oracle Net Services name that resolves to a connect descriptor, or, after version 11g, an Easy Connect naming (the host name of the Oracle server with an optional port and service name).
Password: The password provided for authentication with the Oracle database.
User Id: The user Id provided for authentication with the Oracle database.

PostgreSQL

The following is the JDBC URL syntax for the official PostgreSQL JDBC driver:

jdbc:postgresql:[//[host[:port]]/]database[[?option=value][[&option=value][&option=value] ... ]]

For example, the following connection string connects to a database on the default host (localhost) and port (5432):

jdbc:postgresql:postgres

The following are typical connection properties:

Host: The address of the server hosting the PostgreSQL database.
Port: The port used to connect to the server hosting the PostgreSQL database.
Database: The name of the database.
User name: The user Id provided for authentication with the PostgreSQL database. You can specify this in the JDBC URL with the "user" parameter.
Password: The password provided for authentication with the PostgreSQL database.

CacheLocation

Data Type

string

Default Value

"%APPDATA%\\CData\\JIRA Data Provider"

Remarks

The CacheLocation is a simple, file-based cache. The driver uses Java DB, Oracle's distribution of the Derby database. To cache to Java DB, you will need to add the Java DB JAR file to the classpath. The JAR file, derby.jar, is shipped in the JDK and located in the db subfolder of the JDK installation.

If left unspecified, the default location is "%APPDATA%\\CData\\JIRA Data Provider" with %APPDATA% being set to the user's configuration directory:


Platform	%APPDATA%
Windows	The value of the APPDATA environment variable
Mac	~/Library/Application Support
Linux	~/.config

CacheTolerance

Data Type

int

Default Value

600

Remarks

The tolerance for stale data in the cache specified in seconds. This only applies when AutoCache is used. The driver checks with the data source for newer records after the tolerance interval has expired. Otherwise, it returns the data directly from the cache.

Offline

Data Type

bool

Default Value

false

Remarks

When Offline = true, all queries execute against the cache as opposed to the live data source. In this mode, certain queries like INSERT, UPDATE, DELETE, and CACHE are not allowed.

CacheMetadata

Data Type

bool

Default Value

false

Remarks

As you execute queries with this property set, table metadata in the Jira catalog are cached to the file store specified by CacheLocation if set or the user's home directory otherwise. A table's metadata will be retrieved only once, when the table is queried for the first time.

When to Use CacheMetadata

The driver automatically persists metadata in memory for up to two hours when you first discover the metadata for a table or view and therefore, CacheMetadata is generally not required. CacheMetadata becomes useful when metadata operations are expensive such as when you are working with large amounts of metadata or when you have many short-lived connections.

When Not to Use CacheMetadata

When you are working with volatile metadata: Metadata for a table is only retrieved the first time the connection to the table is made. To pick up new, changed, or deleted columns, you would need to delete and rebuild the metadata cache. Therefore, it is best to rely on the in-memory caching for cases where metadata changes often.
When you are caching to a database: CacheMetadata can only be used with CacheLocation. If you are caching to another database with the CacheDriver and CacheConnection properties, use AutoCache to cache implicitly. Or, use CACHE Statements to cache explicitly.

BatchSize

Data Type

int

Default Value

Remarks

When BatchSize is set to a value greater than 0, the batch operation will split the entire batch into separate batches of size BatchSize. The split batches will then be submitted to the server individually. This is useful when the server has limitations on the size of the request that can be submitted.

Setting BatchSize to 0 will submit the entire batch as specified.

ConnectionLifeTime

Data Type

int

Default Value

Remarks

The maximum lifetime of a connection in seconds. Once the time has elapsed, the connection object is disposed. The default is 0 which indicates there is no limit to the connection lifetime.

ConnectOnOpen

Data Type

bool

Default Value

false

Remarks

When set to true, a connection will be made to Jira when the connection is opened. This property enables the Test Connection feature available in various database tools.

This feature acts as a NOOP command as it is used to verify a connection can be made to Jira and nothing from this initial connection is maintained.

Setting this property to false may provide performance improvements (depending upon the number of times a connection is opened).

IncludeCustomFields

Data Type

bool

Default Value

false

Remarks

Setting this to true will cause custom fields to be included in the column listing, but may cause poor performance when listing metadata.

MaxRows

Data Type

int

Default Value

-1

Remarks

Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.

MaxThreads

Data Type

string

Default Value

"20"

Remarks

This property allows you to issue multiple requests simultaneously, thereby improving performance.

Other

Data Type

string

Default Value

Remarks

The properties listed below are available for specific use cases. Normal driver use cases and functionality should not require these properties.

Specify multiple properties in a semicolon-separated list.

Caching Configuration


CachePartial=True	Caches only a subset of columns, which you can specify in your query.
QueryPassthrough=True	Passes the specified query to the cache database instead of using the SQL parser of the driver.

Integration and Formatting


DefaultColumnSize	Sets the default length of string fields when the data source does not provide column length in the metadata. The default value is 2000.
ConvertDateTimeToGMT	Determines whether to convert date-time values to GMT, instead of the local time of the machine.
RecordToFile=filename	Records the underlying socket data transfer to the specified file.

Pagesize

Data Type

int

Default Value

100

Remarks

The Pagesize property affects the maximum number of results to return per page from Jira. Setting a higher value may result in better performance at the cost of additional memory allocated per page consumed.

PoolIdleTimeout

Data Type

int

Default Value

Remarks

The allowed idle time a connection can remain in the pool until the connection is closed. The default is 60 seconds.

PoolMaxSize

Data Type

int

Default Value

100

Remarks

The maximum connections in the pool. The default is 100. To disable this property, set the property value to 0 or less.

PoolMinSize

Data Type

int

Default Value

Remarks

The minimum number of connections in the pool. The default is 1.

PoolWaitTime

Data Type

int

Default Value

Remarks

The max seconds to wait for a connection to become available. If a new connection request is waiting for an available connection and exceeds this time, an error is thrown. By default, new requests wait forever for an available connection.

PseudoColumns

Data Type

string

Default Value

Remarks

This setting is particularly helpful in Entity Framework, which does not allow you to set a value for a pseudo column unless it is a table column. The value of this connection setting is of the format "Table1=Column1, Table1=Column2, Table2=Column3". You can use the "*" character to include all tables and all columns; for example, "*=*".

Readonly

Data Type

bool

Default Value

false

Remarks

If this property is set to true, the driver will allow only SELECT queries. INSERT, UPDATE, DELETE, and stored procedure queries will cause an error to be thrown.

RTK

Data Type

string

Default Value

Remarks

The RTK property may be used to license a build. See the included licensing file to see how to set this property. The runtime key is only available if you purchased an OEM license.

Timeout

Data Type

int

Default Value

Remarks

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

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

Timezone

Data Type

string

Default Value

Remarks

Note: You can find the timezone in your Jira Cloud by navigating to Settings -> System -> General configuration -> Default user time zone

UseConnectionPooling

Data Type

bool

Default Value

false

Remarks

This property enables connection pooling. The default is false. See Connection Pooling for information on using connection pools.

UseDefaultOrderBy

Data Type

bool

Default Value

true

Remarks

By default, the CData JDBC Driver for Jira applies an order by when none is specified. This ensures that duplicate records do not get output when returning data. However, this will have a significant impact on performance for very large tables. If your use case can handle the possibility of duplicate primary keys, it is recommended to turn this off if you would like to have better performance.

UseDisplayNames

Data Type

bool

Default Value

true

Remarks

Boolean determining if the display names for custom fields should be used instead of the API names.

UserDefinedViews

Data Type

string

Default Value

Remarks

User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The driver automatically detects the views specified in this file.

You can also have multiple view definitions and control them using the UserDefinedViews connection property. When you use this property, only the specified views are seen by the driver.

This User Defined View configuration file is formatted as follows:

Each root element defines the name of a view.
Each root element contains a child element, called query, which contains the custom SQL query for the view.

For example:

{

	"MyView": {

		"query": "SELECT * FROM Projects WHERE MyColumn = 'value'"

	},

	"MyView2": {

		"query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"

	}

}

Use the UserDefinedViews connection property to specify the location of your JSON configuration file. For example:

"UserDefinedViews", "C:\\Users\\yourusername\\Desktop\\tmp\\UserDefinedViews.json"

UseSimpleNames

Data Type

bool

Default Value

false

Remarks

Jira tables and columns can use special characters in names that are normally not allowed in standard databases. UseSimpleNames makes the driver easier to use with traditional database tools.

Setting UseSimpleNames to true will simplify the names of tables and columns returned. It will enforce a naming scheme such that only alphanumeric characters and the underscore are valid for the displayed table and column names. Any nonalphanumeric characters will be converted to an underscore.

Attachments

Query, insert and delete the available Attachments in JIRA.

Table Specific Information

Select

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

The driver processes other filters within the driver. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false.

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'

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.

Boards

Query, insert and delete the agile Boards in Jira.

Table Specific Information

Select

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

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'

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.

Comments

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 driver uses the Jira API to process some of the filters.

The driver processes other filters within the driver. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false.

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

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.

Issues

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 driver uses the Jira API to process some of the filters. The driver processes other filters client-side within the driver. 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.

IssueTypes

Query the available Issue Types in JIRA.

Select

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

The driver processes other filters within the driver.

SELECT * FROM IssueTypes WHERE Id = 3

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.

ProjectComponents

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

Table Specific Information

Select

The driver uses the Jira API to process some of the filters. The driver processes other filters within the driver. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false.

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'

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.

Projects

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

Select

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

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

SELECT * FROM Projects WHERE Id = 10000

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.

ProjectVersions

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

Table Specific Information

Select

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'

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

Roles

Query the available roles in JIRA.

Select

The driver will use the Jira API to process filters that refer to Id while the rest of the filter is executed client side within the driver. 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.

Sprints

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 driver uses the Jira API to process some of the filters. The driver 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')

You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false.

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.

Users

Query the available Users in JIRA.

Select

The driver will execute filters other than the following client-side within the driver. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false. 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)

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.
Key [KEY]	String	True			Server	The key of the user.
Name	String	False			Server	The name of the user.

Worklogs

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 driver will use the Jira APIs to process filters based on the following columns and operators. The driver will execute other filters client-side. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false.

For example, the following query is processed server side:

SELECT * FROM Worklogs WHERE IssueId=10001

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.

ViewsBack To Top

AdvancedSettings
ApplicationRoles
Audit
BoardConfiguration
BoardIssues
BoardSprints
Configuration
Dashboards
Epics
FavouriteFilters
Fields
Filters
Groups
IssueAffectedVersions
IssueChangelogs
IssueComponents
IssueCustomFieldOptions
IssueCustomFields
IssueFixVersions
IssueLinks
IssueLinkTypes
IssueNavigatorDefaultColumns
IssuePriorities
IssueResolutions
IssueSubtasks
IssueTransitions
MyPermissions
Permissions
ProjectCategories
ProjectRoles
ProjectsIssueTypes
ProjectTypes
RoleDetails
SecurityLevels
SecuritySchemes
SprintIssues
Statuses
TimeTrackingProviders
Votes
Watchers
Workflows
WorkflowStatusCategories
WorkflowStatuses

AdvancedSettings

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.

ApplicationRoles

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

Table Specific Information

Select

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

The driver processes other filters within the driver. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false.

For example, the following queries are processed server side:

SELECT * FROM ApplicationRoles

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

Audit

Query the audit log of your JIRA account.

Table Specific Information

Select

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

The driver processes other filters within the driver. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false.

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.

BoardConfiguration

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

BoardIssues

Query the agile Board Issues in Jira.

Table Specific Information

Select

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.

BoardSprints

Query the agile Sprints related to a Jira Board.

Table Specific Information

Select

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.

Configuration

Query the available Configurations in JIRA.

Table Specific Information

Select

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

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.

Dashboards

Query the available Dashboards in JIRA.

Table Specific Information

Select

The driver 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 driver processes other filters within the driver. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false.

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.

Epics

Query all the Epics.

Table Specific Information

Select

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

The driver processes other filters within the driver. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false.

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.

FavouriteFilters

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.

Fields

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.

Filters

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

Table Specific Information

Select

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

The driver processes other filters within the driver. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false.

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.

Groups

Query the available Groups in JIRA.

Table Specific Information

Select

SELECT * FROM GROUPS WHERE Name <> 'administrator' AND  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.

IssueAffectedVersions

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 driver uses the Jira API to process some of the filters.

The driver processes other filters within the driver. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false.

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.

IssueChangelogs

Query the available IssueChangelogs in JIRA.

Table Specific Information

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

Select

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

The driver processes other filters within the driver. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false.

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

SELECT * FROM IssueChangelogs WHERE IssueId<>10022

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.

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.

IssueComponents

Query the available IssueComponents in JIRA.

Table Specific Information

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

Select

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

The driver processes other filters within the driver. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false.

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.

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.

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.

IssueCustomFields

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.

IssueFixVersions

Query the available IssueFixVersions in JIRA.

Table Specific Information

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

Select

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

The driver processes other filters within the driver. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false.

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.

IssueLinks

Query the available IssueLinks in JIRA.

Table Specific Information

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

Select

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

The driver processes other filters within the driver. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false.

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.

IssueLinkTypes

Lists all the issue link types.

Select

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

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

IssueNavigatorDefaultColumns

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.

IssuePriorities

Query the list of all issue priorities.

Table Specific Information

Select

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

The driver processes other filters within the driver. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false.

For example, the following queries are processed server side:

SELECT * FROM IssuePriorities

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.

IssueResolutions

Query the list of all issue resolution values.

Table Specific Information

Select

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

The driver processes other filters within the driver. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false.

For example, the following queries are processed server side:

SELECT * FROM IssueResolutions

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.

IssueSubtasks

Query the available IssueSubtasks in JIRA.

Table Specific Information

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

Select

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

The driver processes other filters within the driver. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false.

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.

IssueTransitions

Query the available IssueTransitions in JIRA.

Table Specific Information

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

Select

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

The driver processes other filters within the driver. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false.

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.

MyPermissions

Query the available Permissions of the current user in JIRA.

Table Specific Information

Select

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

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.

Permissions

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.

ProjectCategories

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.

ProjectRoles

Query roles for all projects in JIRA.

Select

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

For example, the following queries are processed server side:

Retrieve all roles for each project:

SELECT * FROM ProjectRoles

Retrieve all roles for a specific project:

SELECT * FROM ProjectRoles WHERE ProjectId = '10000'

Columns


Name	Type	References	SupportedOperators	Platform	Description
RoleId [KEY]	Long	Roles.Id		Common	The Id of the role.
RoleName	String	Roles.Name		Common	The name of the role.
ProjectId [KEY]	Integer	Projects.Id	=,IN	Common	The Id of the project associated with the role.
ProjectKey [KEY]	String	Projects.Key	=,IN	Common	The key of the project associated with the role.

ProjectsIssueTypes

Query the available Issue Types of projects.

Columns


Name	Type	References	SupportedOperators	Platform	Description
Id [KEY]	String				The Id of the issue type.
ProjectId [KEY]	Int	Projects.Id			The Id of the project.
ProjectName	String	Projects.Name			The name of the project
Name	String				The name of the issue type.
Description	String				The description of the issue type.
Subtask	Boolean				The subtask of the issue type. Not applicable for update.
IconUrl	String				The icon URL of the issue type.
AvatarId	String				The id of the avatar

ProjectTypes

Query the list of all issue priorities.

Table Specific Information

Select

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

The driver processes other filters within the driver. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false.

For example, the following queries are processed server side:

SELECT * FROM ProjectTypes

SELECT * FROM ProjectTypes WHERE Key='software'

Columns


Name	Type	References	SupportedOperators	Platform	Description
Key [KEY]	String		=	Common	The key of the project type.
DescriptionKey	String			Common	The key of the project type's description.
Icon	String			Common	The icon of the project type.
Color	String			Common	The color of the project type.

RoleDetails

Query Role Details in JIRA.

Columns


Name	Type	References	SupportedOperators	Platform	Description
Id [KEY]	Integer	Roles.Id		Common	The Id of the details of the role.
ProjectId [KEY]	Integer	Projects.Id	=,IN	Common	The Id of the project associated with the details of the role.
ProjectKey [KEY]	String	Projects.Key	=,IN	Common	The key of the project associated with the details of the role.
Name	String			Common	The name of the details of the role.
Description	String			Common	The description of the details 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
Rows@Next	String		This is used to page through multiple pages of results and should not be set manually.

SecurityLevels

Query all the created security levels.

Columns


Name	Type	References	SupportedOperators	Platform	Description
Id [KEY]	Integer			Common	The ID of the issue level security item.
SecuritySchemeId	Long	SecuritySchemes.Id	=,IN	Common	Link to this security level.
Name	String			Common	The name of the issue level security item.
Description	String			Common	The description of the issue level security item.

SecuritySchemes

Query all the created security schemes.

Columns


Name	Type	References	SupportedOperators	Platform	Description
Id [KEY]	Long		=	Common	The ID of the issue security scheme.
Name	String			Common	The name of the issue security scheme.
Description	String			Common	The description of the issue security scheme.
DefaultSecurityLevelId	Long			Common	The ID of the default security level.

SprintIssues

Query the agile Sprint Issues in Jira.

Table Specific Information

Select

The Jira APIs support the IN operator in filters on SprintId; the driver executes the rest of the filter client side within itself. For example, following query's filter is processed server side:

SELECT * FROM SprintIssues WHERE SprintId = '1'

Using the IN operator:

SELECT * FROM SprintIssues WHERE SprintId IN (70,215)

Note that 'kanban' type boards do not support sprints. You can retrieve all sprint issues faster if you use a filter like the following:

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

Columns


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

Statuses

Query the possible Statuses in Jira.

Select

The driver will use the Jira APIs to process filters that refer to the Id or Name columns. Searches on the Name column are case sensitive.

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

SELECT * FROM Statuses WHERE Id = '10003'



SELECT * FROM Statuses WHERE Name = 'Closed'

Columns


Name	Type	References	SupportedOperators	Platform	Description
Id [KEY]	Integer		=	Common	The Id of the status.
Name	String		=	Common	The name of the status.
Description	String			Common	The description of the status.
CategoryId	Integer			Common	The Category Id of the status.
CategoryKey	String			Common	The Category Key of the status.

TimeTrackingProviders

Query the list of all time tracking providers.

Table Specific Information

Select

SELECT * FROM TimeTrackingProviders

Columns


Name	Type	References	SupportedOperators	Platform	Description
Key [KEY]	String		=	Cloud	The key for the time tracking provider. For example, JIRA.
Name	String			Cloud	The name of the time tracking provider. For example, JIRA provided time tracking.
URL	String			Cloud	The URL of the configuration page for the time tracking provider app. For example, /example/config/url. This property is only returned if the adminPageKey property is set in the module descriptor of the time tracking provider app.

Votes

Query the available Votes in JIRA.

Select

The driver will use the Jira APIs to process filters that refer to IssueId or IssueKey and one of them is required to retrieve results. The Jira APIs support only the equals (=) operator to filter on these columns.

The driver will execute filters other than the following client-side within itself. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false.

For example, the following queries are processed server side:

SELECT * FROM Votes WHERE IssueId=10100

SELECT * FROM Votes WHERE IssueKey='MKY-1'

Columns


Name	Type	References	SupportedOperators	Platform	Description
Key [KEY]	String			Common	The key of the vote.
Name	String			Common	The name of the vote.
DisplayName	String			Common	The display name of the vote.
Active	Boolean			Common	Boolean indicating if the vote is active.

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
IssueId	String		The issue Id of the vote.
IssueKey	String		The issue key of the vote.

Watchers

Query the available Watchers in JIRA.

Select

The driver will use the Jira APIs to process filters based on IssueId or IssueKey. One of these is required to retrieve results. IssueId and IssueKey can be used only with the equal (=) operator.

The driver will execute other filters client-side within itself. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false.

For example, the following query is processed server side:

SELECT * FROM Watchers WHERE IssueId='MKY-1'

Columns


Name	Type	References	SupportedOperators	Platform	Description
DisplayName	String			Common	The displayed name of the watcher.
EmailAddress	String			Common	The email address of the watcher.
AccountId [KEY]	String			Cloud	The accountId of the watcher.
Key [KEY]	String			Server	The key of the watcher.
Name	String			Server	The name of the watcher.

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
IssueId	String		The issue Id of the watcher.
IssueKey	String		The issue key of the watcher.

Workflows

Query the available Workflows in Jira.

Select

The driver will use the Jira APIs to process filters that refer to Name and use the equals (=) operator only.

The driver will execute filters other than the following client-side within itself. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false.

For example, the following query is executed server side:

SELECT * FROM Workflows WHERE Name='abc'

Columns


Name	Type	References	SupportedOperators	Platform	Description
Name	String			Common	The name of the workflow.
Description	String			Common	The description of the workflow.
LastModifiedDate	String			Common	The last modified date of the workflow.
LastModifiedUser	String			Common	The user who last modified the workflow.
Steps	Integer			Common	The steps of the workflow.
IsDefault	Boolean			Common	Boolean indicating if the workflow is set as the default.

WorkflowStatusCategories

Query the list of all status categories.

Table Specific Information

Select

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

The driver processes other filters within the driver. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false.

For example, the following queries are processed server side:

SELECT * FROM WorkflowStatusCategories

SELECT * FROM WorkflowStatusCategories WHERE Id=1

SELECT * FROM WorkflowStatusCategories WHERE Key='undefined'

Columns


Name	Type	References	SupportedOperators	Platform	Description
Id [KEY]	Long		=	Common	The ID of the status category.
Key	String		=	Common	The key of the status category.
Name	String			Common	The name of the status category.
ColorName	String			Common	The name of the color used to represent the status category.
URL	String			Common	The URL of the status category.

WorkflowStatuses

Query the list of all statuses associated with workflows.

Table Specific Information

Select

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

The driver processes other filters within the driver. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false.

For example, the following queries are processed server side:

SELECT * FROM WorkflowStatuses

SELECT * FROM WorkflowStatuses WHERE Id=1

SELECT * FROM WorkflowStatuses WHERE Name='Open'

Columns


Name	Type	References	SupportedOperators	Platform	Description
Id [KEY]	String		=	Common	The ID of the status.
StatusCategoryId	Long	WorkflowStatusCategories.Id		Common	The ID of the status category.
Name	String		=	Common	The name of the status.
Description	String			Common	The description of the status.
URL	String			Common	The description of the status.
IconUrl	String			Common	The URL of the icon used to represent the status.

Matillion ETL Data Model for JiraVersion - 22.0.8322.0

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Authentication on Headless Machines

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Matillion ETL Data Model for Jira
Version - 22.0.8322.0