JIRA Data Model

Connection String Options

  1. API Token
  2. Auto Cache
  3. Batch Size
  4. Cache Connection
  5. Cache Driver
  6. Cache Location
  7. Cache Metadata
  8. Cache Tolerance
  9. Callback URL
  10. Certificate Store
  11. Certificate Store Password
  12. Certificate Store Type
  13. Certificate Subject
  14. Connection Life Time
  15. Connect On Open
  16. Firewall Password
  17. Firewall Port
  18. Firewall Server
  19. Firewall Type
  20. Firewall User
  21. Include Custom Fields
  22. Initiate OAuth
  23. Location
  24. Logfile
  25. Max Log File Size
  26. Max Rows
  27. OAuth Access Token
  28. OAuth Access Token Secret
  29. OAuth Client Id
  30. OAuth Client Secret
  31. OAuth Refresh Token
  32. OAuth Settings Location
  33. OAuth Verifier
  34. Offline
  35. Other
  36. Pagesize
  37. Pool Idle Timeout
  38. Pool Max Size
  39. Pool Min Size
  40. Pool Wait Time
  41. Proxy Auth Scheme
  42. Proxy Auto Detect
  43. Proxy Exceptions
  44. Proxy Password
  45. Proxy Port
  46. Proxy Server
  47. Proxy SSL Type
  48. Proxy User
  49. Pseudo Columns
  50. RTK
  51. SSL Client Cert
  52. SSL Client Cert Password
  53. SSL Client Cert Subject
  54. SSL Client Cert Type
  55. SSL Server Cert
  56. Support Enhanced SQL
  57. Tables
  58. Timeout
  59. URL
  60. Use Connection Pooling
  61. User
  62. Verbosity
  63. Views

API Token

APIToken of the currently authenticated user.
Data Type

string

Default Value

""

Remarks

APIToken of the currently authenticated user.

 

Auto Cache

Automatically caches the results of SELECT queries into a cache database specified by either CacheLocation or both of CacheConnection and CacheProvider .
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:

 

 

See Also

 

 

  • CacheMetadata: This property reduces the amount of metadata that crosses the network by persisting table schemas retrieved from the JIRA metadata. Metadata then needs to be retrieved only once instead of every connection.
  • Explicitly Caching Data: This section provides more examples of using AutoCache in Offline mode.
  • CACHE Statements: You can use the CACHE statement to persist any SELECT query, as well as manage the cache; for example, refreshing schemas.

 

 

 

Batch Size

The maximum size of each batch operation to submit.
Data Type

int

Default Value

0

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.

 

Cache Connection

The connection string for the cache database. This property is always used in conjunction with CacheProvider . Setting both properties will override the value set for CacheLocation for caching data.
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 CacheProvider 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.

 

 

Cache Driver

The database driver to be used to cache data.
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 based on the CacheDriver and CacheConnection properties. The CacheDriver is the name of the JDBC driver class that you want to use to cache data.

Note that you must also add the CacheDriver JAR file to the classpath.

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;Password=123abc;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;Password=123abc;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;Password=123abc;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;Password=123abc;Url=https://yoursitename.atlassian.net
  
The CData JDBC Driver for MySQL is located in the lib subfolder of the CData JDBC Driver for JIRA 2019 installation directory.
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;Password=123abc;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;Password=123abc;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=org.postgresql.Driver;CacheConnection='jdbc:postgresql://localhost:5433/postgres?user=postgres&password=admin';User=admin;Password=123abc;Url=https://yoursitename.atlassian.net

 

 

Cache Location

Specifies the path to the cache when caching to a file.
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.

CacheLocation defaults to the directory specified by the Location setting.

See Also

 

 

  • AutoCache: Set to implicitly create and maintain a cache for later offline use.
  • CacheMetadata: Set to persist the JIRA catalog in CacheLocation.

 

 

Cache Metadata

This property determines whether or not to cache the table metadata to a file store.
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.

 

 

Cache Tolerance

The tolerance for stale data in the cache specified in seconds when using AutoCache .
Data Type

string

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.

 

Callback URL

The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings.
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.

 

Certificate Store

The certificate store used for JIRA authentication.
Data Type

string

Default Value

""

Remarks

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

 

Certificate Store Password

The password of the certificate store used with Jira authentication.
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.

 

Certificate Store Type

The type of certificate store used with Jira Private Application authentication.
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.
P7BFile The certificate store is the name of a PKCS7 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.

 

 

Certificate Subject

The subject of the certificate used with Jira Private Application authentication.
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:

 

 

Connection Life Time

The maximum lifetime of a connection in seconds. Once the time has elapsed, the connection object is disposed.
Data Type

string

Default Value

"0"

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.

 

Connect On Open

This property species whether to connect to the JIRA when the connection is opened.
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).

 

Firewall Password

A password used to authenticate to a proxy-based firewall.
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.

 

Firewall Port

The TCP port for a proxy-based firewall.
Data Type

string

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.

 

Firewall Server

The name or IP address of a proxy-based firewall.
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.

 

Firewall Type

The protocol used by a proxy-based firewall.
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.

 

 

Firewall User

The user name to use to authenticate with a proxy-based firewall.
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.

 

Include Custom Fields

Set whether to include custom fields in queries to the Issues table. When off, performance will be higher, but the custom fields of Issues cannot be retrieved or modified.
Data Type

bool

Default Value

false

Remarks

If set to TRUE, the custom fields of Issues will be retrieved.

 

Initiate OAuth

Set this property to initiate the process to obtain or refresh the OAuth access token when you connect.
Data Type

string

Default Value

"OFF"

Remarks

The following options are available:

  1. OFF: Indicates that the OAuth flow will be handled entirely by the user. An OAuthAccessToken will be required to authenticate.
  2. 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.
  3. 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.

 

 

Location

A path to the directory that contains the schema files defining tables, views, and stored procedures.
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 the same folder where the CData driver is loaded from.

 

Logfile

A path to the log file.
Data Type

string

Default Value

""

Remarks

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

 

Max Log File Size

A string specifying the maximum size in bytes for a log file (for example, 10 MB). When the limit is hit, a new log is created in the same folder with the date and time appended to the end.
Data Type

string

Default Value

"100MB"

Remarks

A string specifying the maximum size in bytes for a log file (for example, 10 MB). 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.

 

Max Rows

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

string

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.

 

OAuth Access Token

The access token for connecting using OAuth.
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.

 

OAuth Access Token Secret

The OAuth access token secret for connecting using OAuth.
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.

 

OAuth Client Id

The client ID assigned when you register your application with an OAuth authorization server.
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.

 

OAuth Client Secret

The client secret assigned when you register your application with an OAuth authorization server.
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.

 

OAuth Refresh Token

The OAuth refresh token for the corresponding OAuth access token.
Data Type

string

Default Value

""

Remarks

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

 

OAuth Settings Location

The location of the settings file where OAuth values are saved when InitiateOAuth is set to GETANDREFRESH or REFRESH. Alternatively, this can be held in memory by specifying a value starting with memory://.
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. The default OAuthSettingsLocation is a settings file located in the %AppData%\CData folder.

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.

 

OAuth Verifier

The verifier code returned from the OAuth authorization URL.
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 Getting Started 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.

 

 

Offline

Use offline mode to get the data from the cache instead of the live source.
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.

 

Other

These hidden properties are used only in specific use cases.
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

Specifies how many issues the driver will retrieve for each request. Applicable only for the Issues, Attachments, Worklogs, Comments, IssueSubtasks, IssueChangelogs, IssueTransitions, IssueComponents, IssueFixVersions, Boards, Users tables. The max value for a JIRA server instance is 1000. The max value for a JIRA Cloud account is 100.
Data Type

string

Default Value

"100"

Remarks

Number of Issue entries retrieved in each page.

 

Pool Idle Timeout

The allowed idle time for a connection before it is closed.
Data Type

string

Default Value

""

Remarks

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

 

Pool Max Size

The maximum connections in the pool.
Data Type

string

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.

 

Pool Min Size

The minimum number of connections in the pool.
Data Type

string

Default Value

"1"

Remarks

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

 

Pool Wait Time

The max seconds to wait for an available connection.
Data Type

string

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.

 

Proxy Auth Scheme

The authentication type to use to authenticate to the ProxyServer proxy.
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.

 

Proxy Auto Detect

This indicates whether to use the system proxy settings or not. Set ProxyAutoDetect to FALSE to use custom proxy settings. This takes precedence over other proxy settings.
Data Type

bool

Default Value

true

Remarks

By default, the driver uses the system HTTP proxy. Set this to false if you want to connect to another proxy.

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

 

Proxy Exceptions

A semicolon separated list of hosts or IPs that are exempt from connecting through the ProxyServer .
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.

 

Proxy Password

A password to be used to authenticate to the ProxyServer proxy.
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.

 

Proxy Port

The TCP port the ProxyServer proxy is running on.
Data Type

string

Default Value

"80"

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.

 

Proxy Server

The hostname or IP address of a proxy to route HTTP traffic through.
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.

 

Proxy SSL Type

The SSL type to use when connecting to the ProxyServer proxy.
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.

 

 

Proxy User

A user name to be used to authenticate to the ProxyServer proxy.
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

 

 

Pseudo Columns

This property indicates whether or not to include pseudo columns as columns to the table.
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, "*=*".

 

RTK

The runtime key used for licensing.
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.

 

SSL Client Cert

The TLS/SSL client certificate store for SSL Client Authentication (2-way SSL).
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).

 

SSL Client Cert Password

The password for the TLS/SSL client certificate.
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.

 

SSL Client Cert Subject

The subject of the TLS/SSL client certificate.
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.

 

SSL Client Cert Type

The type of key store containing the TLS/SSL client certificate.
Data Type

string

Default Value

""

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.

 

 

SSL Server Cert

The certificate to be accepted from the server when connecting using TLS/SSL.
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.

 

Support Enhanced SQL

This property enhances SQL functionality beyond what can be supported through the API directly, by enabling in-memory client-side processing.
Data Type

bool

Default Value

true

Remarks

When SupportEnhancedSQL = true, the driver offloads as much of the SELECT statement processing as possible to JIRA and then processes the rest of the query in memory. In this way, the driver can execute unsupported predicates, joins, and aggregation.

When SupportEnhancedSQL = false, the driver limits SQL execution to what is supported by the JIRA API.

Execution of Predicates

 

The driver determines which of the clauses are supported by the data source and then pushes them to the source to get the smallest superset of rows that would satisfy the query. It then filters the rest of the rows locally. The filter operation is streamed, which enables the driver to filter effectively for even very large datasets.

Execution of Joins

 

The driver uses various techniques to join in memory. The driver trades off memory utilization against the requirement of reading the same table more than once.

Execution of Aggregates

 

The driver retrieves all rows necessary to process the aggregation in memory.

 

Tables

This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC.
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. For example, Tables=TableA,TableB,TableC.

 

Timeout

The value in seconds until the timeout error is thrown, canceling the operation.
Data Type

string

Default Value

"60"

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.

 

URL

The URL to your JIRA endpoint.
Data Type

string

Default Value

""

Remarks

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

 

Use Connection Pooling

This property enables connection pooling.
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.

 

User

Username of the currently authenticated user.
Data Type

string

Default Value

""

Remarks

 

Username of the currently authenticated user.

 

Verbosity

The verbosity level that determines the amount of detail included in the log file.
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 described in the following list:

 

   
1 Setting Verbosity to 1 will log the query, the number of rows returned by it, the start of execution and the time taken, and any errors.
2 Setting Verbosity to 2 will log everything included in Verbosity 1, cache queries, and additional information about the request, if applicable, such as HTTP headers.
3 Setting Verbosity to 3 will additionally log the body of the request and the response.
4 Setting Verbosity to 4 will additionally log transport-level communication with the data source. This includes SSL negotiation.
5 Setting Verbosity to 5 will additionally log communication with the data source and additional details that may be helpful in troubleshooting problems. This includes interface commands.

 

The Verbosity should not be set to greater than 1 for normal operation. Substantial amounts of data can be logged at higher verbosities, which can delay execution times.

 

Views

Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC.
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. For example, Views=ViewA,ViewB,ViewC.




 

Tables

  1. Attachments
  2. Boards
  3. Comments
  4. Issues
  5. IssueTypes
  6. ProjectComponents
  7. Projects
  8. ProjectVersions
  9. Sprints
  10. Worklogs

Attachments

Delete, insert and query 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 Description
Id [KEY] Integer False   =

The Id of the attachment.

IssueId Integer False

Issues.Id

=,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN

The issue Id of the attachment.

IssueKey String False

Issues.Key

=,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN

The key of the issue.

IssueCreatedDate Datetime False   =,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN

The date the attachment's issue was created.

IssueUpdatedDate Datetime False   =,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN

The date the attachment's issue was last updated.

Filename String False    

The filename of the attachment.

AuthorName String False

Users.Name

 

The name of the author of the attachment.

AuthorKey String False

Users.Key

 

The key of the author.

AuthorDisplayName String False

Users.DisplayName

 

The display name of the author of the attachment.

Created Datetime False    

The creation date of the attachment.

Size Integer False    

The size of the attachment.

MimeType String False    

The MIME type of the attachment.

Content String False    

The URI of the actual attached file.

Thumbnail String False    

The thumbnail of the attachment.

FilePath String False    

The path of the file to insert.

ContentEncoded String False    

BASE64 encoded content of the file to insert.

Name String False    

The name of the file to upload. Also the file extension should be specified.

 

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.

Rows@Next String

This is used to page through multiple pages of results and should not be set manually.

 

 

 

Boards

Query 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. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false.

 

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 Description
Id [KEY] Integer True   =

The Id of the board.

Name String False   =

The name of the board.

Type String False   =

The type of the board.

The allowed values are scrum, kanban, simple.

FilterId Integer False    

ID of a filter that the user has permissions to view.

 

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.

Rows@Next String

This is used to page through multiple pages of results and should not be set manually.

 

 

 

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 Description
Id [KEY] Integer True   =

The Id of the comment.

IssueId [KEY] Integer False

Issues.Id

=,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN

The Id of the issue.

IssueKey String False

Issues.Key

=,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN

The key of the issue.

IssueCreatedDate Datetime True   =,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN

The date the comment's issue was created.

IssueUpdatedDate Datetime True   =,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN

The date the comment's issue was last updated.

AuthorName String True

Users.Name

 

The name of the author who made the comment.

AuthorKey String True

Users.Key

 

The key of the author who made the comment.

AuthorDisplayName String True

Users.DisplayName

 

The display name of the author who made the comment.

AuthorEmail String True

Users.EmailAddress

 

The email address of the author who made the comment.

UpdateAuthorKey String True

Users.Key

 

The Id of the author who edited the comment.

UpdateAuthorName String True

Users.Name

 

The name of the author who edited the comment.

UpdateDisplayName String True

Users.DisplayName

 

The display name of the author who changed the comment.

UpdateAuthorEmail String True

Users.EmailAddress

 

The email address of the author who edited the comment.

Body String False    

The body of the comment.

RenderedBody String True    

The rendered body of the comment.

Created Datetime True    

The date the comment was created.

Updated Datetime True    

The date the comment was updated.

VisibilityType String False    

The visibility type of the comment.

VisibilityValue String False    

The visibility value of the comment.

 

Pseudo-Columns

 

 

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

 

 

   
Name Type Description
JQL String

JQL (JIRA Query Language) allows you to build structured queries.

Rows@Next String

This is used to page through multiple pages of results and should not be set manually.

 

 

 

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. ProjectId, Summary and IssueTypeId are required for an Insert.

INSERT INTO Issues(ProjectId,Summary,IssueTypeId,CustomField1) VALUES(10000,'Desc from prod',10100,'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 Description
Id [KEY] Int True   =,!=,>,>=,<,<=,IN,NOT IN

The Id of the issue.

Key String True   =,!=,>,>=,<,<=,IN,NOT IN

The key of the issue.

IssueTypeId String False   =,!=,IS,IS NOT,IN,NOT IN

The issue type Id.

IssueTypeName String True   =,!=,IS,IS NOT,IN,NOT IN

The issue type name.

ProjectId String False   =,!=,IS,IS NOT,IN,NOT IN

The project Id of the issue.

ProjectName String True   =,!=,IS,IS NOT,IN,NOT IN

The project name of the issue.

ProjectKey String False   =,!=,IS,IS NOT,IN,NOT IN

The project key of the issue.

ParentId Int False   =,!=,IN,NOT IN

The Id of the issue's parent if the issue is a subtask.

ParentKey String True   =,!=,IN,NOT IN

The key of the issue's parent if the issue is a subtask.

ResolutionId String True   =,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN

The resolution Id of the issue.

ResolutionName String True   =,!=,IS,IS NOT,IN,NOT IN

The resolution name of the issue.

ResolutionDescription String True    

The resolution description of the issue.

ResolutionDate Datetime True   =,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN

The resolution date of the issue.

Workratio Long True   =,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN

The work ratio of the issue.

LastViewed Datetime True   =,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN

The last time that the issue was viewed.

WatchCount Int True   =,!=,>,>=,<,<=,IN,NOT IN

The number of watches of the issue.

IsWatching Bool True    

Whether the currently authenticated user is watching the issue.

Created Datetime True   =,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN

The creation date of the issue.

PriorityId String False   =,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN

The priority Id of the issue.

PriorityName String True   =,!=,IS,IS NOT,IN,NOT IN

The priority name of the issue.

TimeSpentSeconds Long True    

The time spent in seconds on the issue.

TimeSpent String True   =,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN

The time spent on the issue.

RemainingEstimateSeconds Long True    

The time estimate in seconds of the issue.

RemainingEstimate String False   =,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN

The time estimate of the issue.

OriginalEstimateSeconds Long True    

The original time estimate in seconds of the issue.

OriginalEstimate String False   =,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN

The original time estimate of the issue.

AggregateTimeSpent Long True    

The aggregate time spent of the issue.

AggregateTimeOriginalEstimate Long True    

The original aggregate time estimate of the issue.

AggregateTimeEstimate Long True    

The aggregate time estimate of the issue.

AssigneeDisplayName String True    

Assignee display name.

AssigneeKey String True   =,!=,IS,IS NOT,IN,NOT IN

The assignee key of the issue.

AssigneeName String False   =,!=,IS,IS NOT,IN,NOT IN

The assignee name of the issue.

AssigneeEmail String True   =,!=,IS,IS NOT,IN,NOT IN

The assignee email of the issue.

Updated Datetime True   =,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN

The updated date of the issue.

StatusId String True   =,!=,IS,IS NOT,IN,NOT IN

The status Id of the issue.

StatusName String True   =,!=,IS,IS NOT,IN,NOT IN

The status name of the issue.

Description String False   IS,IS NOT

The description of the issue.

Summary String False   IS,IS NOT

The summary of the issue.

CreatorDisplayName String True    

Issue creator display name.

CreatorName String True   =,!=,IS,IS NOT,IN,NOT IN

The creator name of the issue.

CreatorKey String True   =,!=,IS,IS NOT,IN,NOT IN

The creator key of the issue.

CreatorEmail String True   =,!=,IS,IS NOT,IN,NOT IN

The creator email of the issue.

ReporterDisplayName String True    

Issue reporter display name.

ReporterName String False   =,!=,IS,IS NOT,IN,NOT IN

The reporter name of the issue.

ReporterKey String True   =,!=,IS,IS NOT,IN,NOT IN

The reporter key of the issue.

ReporterEmail String True   =,!=,IS,IS NOT,IN,NOT IN

The reporter email of the issue.

AggregateProgress Long True    

The aggregate progress of the issue.

TotalProgress Long True    

The aggregate total progress of the issue.

Votes Int True   =,!=,>,>=,<,<=,IN,NOT IN

Votes of the issue.

HasVotes Bool True    

The vote status of the issue.

DueDate Date False   =,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN

The due date of the issue.

Labels String False   =,!=,IS,IS NOT,IN,NOT IN

The labels of an issue.

Environment String False   LIKE,NOT LIKE,IS,IS NOT

The environment of an issue.

SecurityLevel String False   =,!=,IS,IS NOT,IN, NOT IN

The security level of an issue.

FixVersionsAggregate String False    

The fix versions of the issue

ComponentsAggregate String False    

Aggregate list of components included in the issue.

IssueLinksAggregate String False    

The issue links of the issue.

AffectedVersionsAggregate String False    

The affected versions of the issue.

SubtasksAggregate String True    

The subtasks of the issue.

CustomField1 Any False    

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    

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.

Rows@Next String

Identifier for the next page of results. Do not set this value manually.

 

 

 

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

SELECT * FROM IssueTypes WHERE Id=3


 
Columns

 

 

 

   
Name Type ReadOnly References SupportedOperators Description
Id [KEY] String True   =

The Id of the issue type.

Name String False    

The name of the issue type.

Description String False    

The description of the issue type.

Subtask Boolean False    

The subtask of the issue type. Not applicable for update.

IconUrl String True    

The icon URL of the issue 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
Rows@Next String

This is used to page through multiple pages of results and should not be set manually.

 

 

 

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 Description
Id [KEY] Integer True   =

The Id of the component.

ProjectId Integer True

Projects.Id

=

The selected project's id.

ProjectKey String False   =

The selected project's key.

Name String False    

The name of the component.

Description String False    

The description of the component.

LeadDisplayName String True    

The display name of the component's lead.

LeadKey String False    

The key of the component's lead.

AssigneeType String False    

The type of the component's default assignee.

The allowed values are PROJECT_DEFAULT, COMPONENT_LEAD, PROJECT_LEAD, UNASSIGNED.

AssigneeDisplayName String True    

The display name of the component's default assignee.

AssigneeKey String True    

The key of the component's default assignee.

IsAssigneeTypeValid Boolean True    

Whether the assignee type is valid.

 

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.

 

 

 

Projects

Query 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. 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 Projects WHERE Id=10000


 
Columns

 

 

 

   
Name Type ReadOnly References SupportedOperators Description
Id [KEY] Integer True   =

The Id of the project.

Key [KEY] String False   =

The key of the project.

Name String False    

The name of the project.

Description String False    

The description of the project.

LeadKey String True

Users.Key

 

The key of the project lead.

LeadName String False

Users.Name

 

The name of the project lead.

LeadEmailAddress String True

Users.EmailAddress

 

The email address of the project lead.

LeadDisplayName String True

Users.DisplayName

 

The display name of the project lead.

ComponentsAggregate String True    

The componens of the project.

IssueTypesAggregate String True    

The issue types of the project.

Url String True    

The URL of the project.

Email String True    

The email of the project.

AssigneeType String False    

The assignee type of the project.

The allowed values are PROJECT_LEAD, UNASSIGNED.

VersionsAggregate String True    

The versions of the project.

RolesAggregate String True    

The roles of the project.

ProjectKeysAggregate String True    

The project keys of the project.

ProjectCategoryId String False    

The Id of the project category.

ProjectCategoryName String True    

The name of the project category.

ProjectCategoryDescription String True    

The description of the project category.

ProjectTypeKey String False    

The key of the project type. Not applicable for update.

The allowed values are software, service_desk, business.

 

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
Recent String

The recent project.

Rows@Next String

This is used to page through multiple pages of results and should not be set manually.

 

 

 

ProjectVersions

Query,create, modify and delete the available Versions 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 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 Description
Id [KEY] Integer True   =

The Id of the version.

ProjectId Integer False

Projects.Id

=

The selected project's id.

ProjectKey String False   =

The selected project's key.

Name String False    

The name of the version.

Description String False    

The description of the version.

Released Boolean False    

Whether the version has been released.

ReleaseDate Date False    

Release date of the version.

StartDate Date False    

Optional start date of the version.

Overdue Boolean True    

Whether the version is overdue for release.

Archived Boolean False    

Whether the version has been archived. When a new version is created, this field is always set to False.

 

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.

 

 

 

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 Description
Id [KEY] Integer True   =

The Id of the sprint.

Name String False    

The name of the sprint.

State String False   =

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    

The goal assigned for the sprint.

OriginBoardId Integer False

Boards.Id

=

The board Id the sprint originated from. This field cannot be updated

StartDate Datetime False    

The date when the sprint was started.

EndDate Datetime False    

The date when the sprint has ended.

CompleteDate Datetime True    

The date when the sprint was completed.

 

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.

 

 

 

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 Description
Id [KEY] String True   =

The Id of the worklog.

IssueId [KEY] Integer False

Issues.Id

=,<>,>,>=,<,<=,IN,NOT IN

The issue Id of the worklog.

IssueKey String False

Issues.Key

=,<>,>,>=,<,<=,IN,NOT IN

The key of the issue.

ProjectId Integer True

Projects.Id

=,IS,IS NOT,IN,NOT IN

The project Id of the worklog's issue.

ProjectName String True   =,IS,IS NOT,IN,NOT IN

The project name of the worklog's issue.

IssueCreatedDate Datetime True   =,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN

The date the worklogs's issue was created.

IssueUpdatedDate Datetime True   =,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN

The date the worklogs's issue was last updated.

AuthorName String True

Users.Name

=,<>=,IS,IS NOT,IN,NOT IN

The name of the author of the worklog.

AuthorDisplayName String True

Users.DisplayName

=,<>=,IS,IS NOT,IN,NOT IN

The display name of the author of the worklog.

UpdateAuthorName String True

Users.Name

=,<>=,IS,IS NOT,IN,NOT IN

The name of the author who has updated the worklog.

UpdateAuthorDisplayName String True

Users.DisplayName

=,<>=,IS,IS NOT,IN,NOT IN

The display name of the author who has updated the worklog.

Comment String False   =,LIKE,NOT LIKE

The comment of the worklog.

Created Datetime True   =,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN

The date the worklog was created.

Updated Datetime True    

The update date of the worklog.

VisibilityType String False    

The visibility type of the worklog.

VisibilityValue String False    

The visibility value of the worklog.

Started Datetime False    

The start date of the worklog.

TimeSpent String False    

The spent time of the worklog.

TimeSpentSeconds Long False    

The time spent in seconds for the worklog.

 

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.

 

 




 

Views

  1. Audit
  2. BoardIssues
  3. BoardSprints
  4. Configuration
  5. Dashboards
  6. Epics
  7. Fields
  8. Filters
  9. Groups
  10. IssueAffectedVersions
  11. IssueChangelogs
  12. IssueComponents
  13. IssueFixVersions
  14. IssueLinks
  15. IssueLinkTypes
  16. IssueSubtasks
  17. IssueTransitions
  18. MyPermissions
  19. ProjectCategories
  20. RoleDetails
  21. Roles
  22. SecurityLevels
  23. SprintIssues
  24. Statuses
  25. Users
  26. Votes
  27. Watchers
  28. Workflows

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 Description
RecordId Integer     The Id of the audit record.
Summary String     Summary of the change.
RemoteAddres String     The remote address of the record.
AuthorKey String     Key of the author that made the change.
Created Datetime     Date on when the change was made.
Category String     Category of the change.
EventSource String     What triggered the event.
Description String     Description of the change.
ItemId String     Unique identifier of the object item.
ItemName String     Name of the object item.
ItemTypeName String     Typename of the object item.
ItemParentId String     Identifier of the parent of the object item.
ItemParentName String     Parent name of the object item.
FieldName String     The name of the changed field.
ChangedFrom String     The updated value.
ChangedTo String     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.
Rows@Next String   This is used to page through multiple pages of results and should not be set manually.

 

 

 

BoardIssues

Query the agile Board Issues 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 BoardIssues WHERE BoardId = '1' 
SELECT * FROM BoardIssues WHERE BoardId IN (70,215) 
Columns

 

 

 

   
Name Type References SupportedOperators Description
Id [KEY] Integer

Issues.Id

= The Id of the issue.
Key String

Issues.Key

= The key of the issue.
BoardId [KEY] Integer

Boards.Id

= The board the issue is included in.
IssueTypeId Integer

IssueTypes.Id

=,!=,IS,IS NOT,IN,NOT IN The issue type Id.
IssueTypeName String   =,!=,IS,IS NOT,IN,NOT IN The issue type name.
StatusId Integer

Statuses.Id

=,!=,IS,IS NOT,IN,NOT IN The issue status Id.
StatusName String   =,!=,IS,IS NOT,IN,NOT IN The issue status name.
ProjectId Integer

Projects.Id

=,!=,IS,IS NOT,IN,NOT IN The project Id of the issue.
ProjectKey String

Projects.Key

=,!=,IS,IS NOT,IN,NOT IN The project key of the issue.
ProjectName String   =,!=,IS,IS NOT,IN,NOT IN The project name of the issue.
ClosedSprintsAggregate String     The issue's previous sprints that have been closed.
Created Datetime   =,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN The creation date of the issue.
Updated Datetime   =,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN The updated date of the issue.

 

Pseudo-Columns

 

 

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

 

 

   
Name Type Description
Rows@Next String   This is used to page through multiple pages of results and should not be set manually.

 

 

 

BoardSprints

Query the agile Sprints related to a Jira Board.

 

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 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 Description
Id [KEY] Integer     The Id of the sprint.
BoardId [KEY] Integer

Boards.Id

= The board Id the sprint is in.
Name String     The name of the sprint.
State String   = 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     The goal assigned for the sprint.
OriginBoardId Integer

Boards.Id

= The board Id the sprint originated from. This field cannot be updated
StartDate Datetime     The date when the sprint was started.
EndDate Datetime     The date when the sprint has ended.
CompleteDate Datetime     The date when the sprint was completed.

 

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.

 

 

 

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 Description
VotingEnabled Boolean     Configuration for voting.
WatchingEnabled Boolean     Configuration for watching.
UnassignedIssuesAllowed Boolean     Configuration to allow unassigned issues.
SubTasksEnabled Boolean     Configuration to enable subtasks.
IssueLinkingEnabled Boolean     Configuration to enable issue linking.
TimeTrackingEnabled Boolean     Configuration to enable time tracking.
AttachmentsEnabled Boolean     Configuration to enable attachments.
WorkingHoursPerDay Double     Configuration of the working hours per day.
WorkingDaysPerWeek Double     Configuration of the working days per week.
TimeFormat String     Configuration of the time format.
DefaultUnit String     Configuration of the default unit.

 

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.

 

 

 

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 Description
Id [KEY] String   = The Id of the dashboard.
Name String     The name of the dashboard
View String     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.

Rows@Next String   This is used to page through multiple pages of results and should not be set manually.

 

 

 

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 Description
Id [KEY] Integer   = The Id of the Epic.
Key [KEY] String   = The key of the Epic.
BoardId [KEY] Integer

Boards.Id

= The board Id the Epic is in.
Name String     The name of the Epic.
Done Boolean     Whether or not the Epic is done.

The allowed values are true, false.

ColorKey String     The key of the Epic's color.
Summary String     A brief summary for the Epic.

 

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.

 

 

 

Fields

Query the available System and Custom Fields in JIRA.

 

Columns

 

 

 

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

 

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.

 

 

 

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 Description
Id [KEY] Integer   = The Id of the filter.
Name String     The name assigned to the filter.
JQLExpression String     The JQL by which the filter will search issues by.
Description String     The description given to the filter.
OwnerDisplayName String   = The board Id the sprint originated from.
Favourite Boolean     Whether you have set this filter as a favourite.
FavouritedCount Integer     How many users have this filter as a favourite.
SubscriptionsAggregate String     Collection of user subscriptions to the filter .
SharePermissionsAggregate String     Aggregate of share permissions to the filter.
SharedUsersAggregate String     Aggregate of users this filter is shared to.

 

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.

 

 

 

Groups

Query the available Groups 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.

The available columns for Groups are UserName and Name. UserName supports only the = operator. The Name column has limited support for the LIKE operator: The format %abc% can be used to get all Groups that contain 'abc' and the <> operator can be used to exclude Groups. For example:

SELECT * FROM Groups WHERE Name <> 'administrator' AND UserName='John' AND Name LIKE %jira%
Columns

 

 

 

   
Name Type References SupportedOperators Description
Name String   =,LIKE,<> The name of the group.
Html String     The HTML of the group.
LabelsAggregate String     The labels of the group.

 

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.

 

 

 

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 Description
VersionId [KEY] String     The Id of the affected version.
IssueId [KEY] Integer

Issues.Id

=,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN The issue Id of the affected version.
IssueKey String

Issues.Key

=,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN The issue key of the affected version.
IssueCreatedDate Datetime   =,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN The date the affected version's issue was created.
IssueUpdatedDate Datetime   =,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN The date the affected version's issue was last updated.
Name String     The name of the affected version.
Archived Boolean     A boolean indicating if the affected is archived.
Released Boolean     A boolean indicating if the affected is released.
ReleaseDate Date     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.
Rows@Next String   This is used to page through multiple pages of results and should not be set manually.

 

 

 

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 Description
HistoryId Integer     The Id of the change.
IssueId Integer

Issues.Id

=,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN The Id of the issue.
IssueKey String

Issues.Key

=,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN The key of the issue.
Created Datetime     The creation date of the change.
IssueCreatedDate Datetime   =,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN The date the change's issue was created.
IssueUpdatedDate Datetime   =,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN The date the change's issue was last updated.
AuthorName String     The author name of the change.
AuthorKey String     The author key of the change.
ItemField String     The item field of the change.
ItemFieldType String     The item field type of the change.
ItemFrom String     The item the issue changed from.
ItemFromString String     The description of the item the issue changed from.
ItemTo String     The item the issue changed to.
ItemToString String     The description of the item the issue changed to.

 

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.
Rows@Next String   This is used to page through multiple pages of results and should not be set manually.

 

 

 

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 Description
Id [KEY] String     The Id of the component.
Name String     The name of the component.
IssueId [KEY] Integer

Issues.Id

=,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN The issue Id of the component.
IssueKey String

Issues.Key

=,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN The issue key of the component.
IssueCreatedDate Datetime   =,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN The date the component's issue was created.
IssueUpdatedDate Datetime   =,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN The date the component's issue was last updated.
Description String     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.
Rows@Next String   This is used to page through multiple pages of results and should not be set manually.

 

 

 

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 Description
VersionId [KEY] String     The Id of the fix version.
IssueId [KEY] Integer

Issues.Id

=,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN The issue Id of the fix version.
IssueKey String

Issues.Key

=,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN The issue key of the fix version.
IssueCreatedDate Datetime   =,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN The date the fix version's issue was created.
IssueUpdatedDate Datetime   =,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN The date the fix version's issue was last updated.
Name String     The name of the fix version.
Archived Boolean     A boolean indicating if the fix is archived.
Released Boolean     A boolean indicating if the fix is released.
ReleaseDate Datetime     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.
Rows@Next String   This is used to page through multiple pages of results and should not be set manually.

 

 

 

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 Description
Id [KEY] String     The Id of the link.
InwardIssueId Integer

Issues.Id

  The Id of the issue targeting this issue in the link.
InwardIssueKey String

Issues.Key

  The Key of the issue targeting this issue in the link.
OutWardIssueId Integer

Issues.Id

  The Id of the issue this link is targeting.
OutwardIssueKey String

Issues.Key

  The Key of the issue this link is targeting.
IssueId [KEY] Integer

Issues.Id

=,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN The link's original issue Id.
IssueKey String

Issues.Key

=,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN The link's original issue Key.
IssueCreatedDate Datetime   =,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN The date the link's issue was created.
IssueUpdatedDate Datetime   =,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN The date the link's issue was last updated.
TypeId Id     The Id of the link type.
TypeName String     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.
Rows@Next String   This is used to page through multiple pages of results and should not be set manually.

 

 

 

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 Description
Id [KEY] String   =,IN Unique identifier of the issue link type.
Name String     The name of the issue link type.
Inward String     The description of the issue link type inward link.
Outward String     The description of the issue link type outward link.

 

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.

 

 

 

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 Description
SubtaskId [KEY] String     The Id of the subtask.
SubtaskKey String     The key of the subtask.
IssueId Integer

Issues.Id

=,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN The Id of the parent issue.
IssueKey String

Issues.Key

=,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN The key of the parent issue.
IssueCreatedDate Datetime   =,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN The date the subtask's issue was created.
IssueUpdatedDate Datetime   =,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN The date the subtask's issue was last updated.
Summary String     The summary of the subtask.
TypeId String

IssueTypes.Id

  The issue type Id of the subtask.
TypeName String

IssueTypes.Name

  The issue type name of the subtask.
TypeDescription String

IssueTypes.Description

  The issue type description of the subtask.
TypeSubtask Boolean

IssueTypes.Subtask

  A boolean indicating if the issue is a subtask.
PriorityId String     The priority Id of the subtask.
PriorityName String     The priority name of the subtask.
StatusId String     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.
Rows@Next String   This is used to page through multiple pages of results and should not be set manually.

 

 

 

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 Description
Id [KEY] String     The Id of the transition.
IssueId [KEY] Integer

Issues.Id

=,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN The Id of the issue.
IssueKey String

Issues.Key

=,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN The key of the issue.
IssueCreatedDate Datetime   =,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN The date the transition's issue was created.
IssueUpdatedDate Datetime   =,<>,>,>=,<,<=,IS,IS NOT,IN,NOT IN The date the transition's issue was last updated.
HasScreen String     Whether the transition has a screen.
Name String     The name of the transition.
ToDescription String     The description of the status the issue is transitioning to.
ToId String     The Id of the status the issue is transitioning to.
ToName String     The name of the status the issue is transitioning to.
StatusCategoryId String     The status category Id of the transition.
StatusCategoryKey String     The status category key of the transition.
StatusCategoryName String     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.
Rows@Next String   This is used to page through multiple pages of results and should not be set manually.

 

 

 

MyPermissions

Query the available Permissions 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 Description
Id [KEY] Integer     The Id of the permission.
Key [KEY] String     The key of the permission.
Name String     The name of the permission.
Type String     The type of the permission.
Description String     The description of the permission.
HavePermission Boolean     Boolean indicating if the permission is set or not.
DeprecatedKey Boolean     Boolean indicating if the permission is deprecated.

 

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.
Rows@Next String   This is used to page through multiple pages of results and should not be set manually.

 

 

 

ProjectCategories

Query all the project categories.

 

Columns

 

 

 

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

 

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.

 

 

 

RoleDetails

Query Role Details in JIRA.

 

Select

 

The following sections describe the querying capabilities of the JIRA Cloud and Server APIs. If the driver cannot process a filter through the API, it processes the filter within itself. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false.

 

Querying JIRA Cloud

 

 

If you are using a JIRA Cloud account there are no required parameters. The following query can return all roles:

SELECT * FROM RoleDetails

 

Server-side processing is supported for ProjectKey or ProjectId:

SELECT * FROM RoleDetails WHERE ProjectId=10036

SELECT * FROM RoleDetails WHERE ProjectKey='PK-1'
Querying JIRA Server

 

If you are using a JIRA Server account, you need to specify the Id of the role and the ProjectId or ProjectKey:

SELECT * FROM RoleDetails WHERE Id=7 AND ProjectID=10022
 
SELECT * FROM RoleDetails WHERE Id=7 AND ProjectKey='PK-1'
Columns

 

 

 

   
Name Type References SupportedOperators Description
Id [KEY] Integer

Roles.Id

= The Id of the details of the role.
ProjectId [KEY] Integer

Projects.Id

=,IN The Id of the project associated with the details of the role.
ProjectKey [KEY] String

Projects.Key

=,IN The key of the project associated with the details of the role.
Name String     The name of the details of the role.
Description String     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.

 

 

 

Roles

Query the available Roles 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 (=) operator with ProjectId and ProjectKey.

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 roles for every project:

SELECT * FROM Roles 

 

Or filter them by project:

SELECT * FROM Roles WHERE ProjectId=10003
 
SELECT * FROM Roles WHERE ProjectKey='PK-1'
Columns

 

 

 

   
Name Type References SupportedOperators Description
Id [KEY] String     The Id of the role.
ProjectId [KEY] Integer

Projects.Id

=,IN The project Id of the role.
ProjectKey [KEY] String

Projects.Key

=,IN The project Key of the role.
Name String     The name of the role.
Uri String     The URI 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 Description
Id [KEY] Integer     Unique identifier of the security level.
Name String     Name of the security level.
Description String     Description of the security level.
Link String     Link to this security level.

 

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.

 

 

 

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 Description
Id [KEY] Integer

Issues.Id

  The Id of the issue.
Key String

Issues.Key

  The key of the issue.
SprintId [KEY] Integer

Sprints.Id

= The sprint the issue is assigned to.
IssueTypeId Integer

IssueTypes.Id

  The issue type Id.
IssueTypeName String     The issue type name.
StatusId Integer     The issue status Id.
StatusName String     The issue status name.
ProjectId Integer

Projects.Id

  The project Id of the issue.
ProjectKey String

Projects.Key

  The project key of the issue.
ProjectName String     The project name of the issue.
ClosedSprintsAggregate String     The issue's previous sprints that have been closed.
Created Datetime     The creation date of the issue.
Updated Datetime     The updated date of the issue.

 

Pseudo-Columns

 

 

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

 

 

   
Name Type Description
Rows@Next String   This is used to page through multiple pages of results and should not be set manually.

 

 

 

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 Description
Id [KEY] Integer   = The Id of the status.
Name String   = The name of the status.
Description String     The description of the status.
CategoryId Integer     The Category Id of the status.
CategoryKey String     The Category Key of the status.

 

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.

 

 

 

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 the GroupName:

SELECT * FROM Users WHERE GroupName = 'Testing team'

 

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 References SupportedOperators Description
Key [KEY] String   = The key of the user.
GroupName [KEY] String   =,IN The group the user is part of.
Name String   =,LIKE The name of the user.
DisplayName String   = The display name of the user.
EmailAddress String   = The email address of the user.
Active Boolean   = Boolean indicating if the user is active.

The allowed values are true, false.

The default value is false.

TimeZone String     The timezone of the user.
Locale String     The locale of the user.

 

Pseudo-Columns

 

 

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

 

 

   
Name Type Description
IncludeInactive String   If true, then inactive users are included in the results.

The allowed values are true, false.

The default value is true.

Rows@Next String   This is used to page through multiple pages of results and should not be set manually.

 

 

 

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 Description
Key [KEY] String     The key of the vote.
Name String     The name of the vote.
DisplayName String     The display name of the vote.
Active Boolean     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.
Rows@Next String   This is used to page through multiple pages of results and should not be set manually.

 

 

 

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 Description
Key [KEY] String     The key of the watcher.
Name String     The name of the watcher.
DisplayName String     The displayed name of the watcher.
EmailAddress String     The email address 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.
Rows@Next String   This is used to page through multiple pages of results and should not be set manually.

 

 

 

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 Description
Name String   = The name of the workflow.
Description String     The description of the workflow.
LastModifiedDate String     The last modified date of the workflow.
LastModifiedUser String     The user who last modified the workflow.
Steps Integer     The steps of the workflow.
IsDefault Boolean     Boolean indicating if the workflow is set as the default.

 

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.