このページでは、CData Cloud でのJira への接続の確立 のガイド、利用可能なリソースに関する情報、および使用可能な接続プロパティのリファレンスについて説明します。

Jira への接続

接続の確立 は、CData Cloud にデータベースを作成するためのJira への認証方法と必要な接続プロパティの設定方法について示します。

CData Cloud サービスからデータにアクセス

利用可能な標準サービスを経由してJira からデータにアクセスする方法と、CData Cloud の管理については、CData Cloud ドキュメント で詳しく説明します。

Database タブで対応するアイコンを選択して、Jira に接続します。必須プロパティはSettings にリストされています。Advanced タブには、通常は必要ない接続プロパティが表示されます。

Jira への接続

接続するには、URL をJira のエンドポイントに設定します。例えば、https://yoursitename.atlassian.net です。

カスタムフィールドへのアクセス

デフォルトでは、Cloud はシステムフィールドのみ表示します。Issues のカスタムフィールドにアクセスするには、IncludeCustomFields をtrue に設定するか、Cloud スキーマを拡張してカスタムフィールドへのアクセスを設定します。（カスタムフィールド を参照してください。）カスタムフィールドが含まれる場合、サーバーの応答時間が著しく遅くなることがありますのでご注意ください。

Jira への認証

Jira は、個人用アクセストークン、またはOAuth 2.0 による認証をサポートします。

個人用アクセストークン（PAT）

PAT は、Jira を認証するセキュアな方法であり、ユーザー名とパスワードによる認証に代わる安全な代替手段として機能します。PAT による認証の利点は、万が一システムが侵害された場合、管理者が既存のトークンを即座に取り消せることです。これにより、すべてのスクリプトおよびインテグレーションでアクセスパスワードを変更する必要がなくなります。

PAT は各ユーザーによって作成されます。作成されると、自動的にユーザーと同じパーミッションレベルになります。ユーザーはAtlassian Settings メニューから、またはREST API を使用してPAT を作成できます。

Atlassian アプリケーションから：

1. 画面右上のプロフィール画像を選択します。Jira はドロップダウンリストを表示します。
2. Settings ->Profile を選択します。
3. 左パネルで、Personal Access Tokens を選択します。
4. Create token をクリックします。
5. 新しいトークンに名前を付けます。
6. 必要であれば、トークンを一定日数後に自動的に失効するように設定します。（Atlassian はトークンの有効期限が切れる5日前にリマインダーメッセージを表示し、ユーザーに新しいトークンを作成する時間を与えます。）
7. Create をクリックします。

個人用トークンが表示されます。トークンは作成後に閲覧や検索ができないため、必ずその場でコピーし、安全な場所に保管してください。

次に、以下のJira Cloud プロパティを設定します。

• AuthScheme = OAuth
• OAuthAccessToken = 生成された個人用アクセストークンの値。
• InitiateOAuth = OFF

Note： PAT はREST API を使用して作成することもできます。詳しくは、https://confluence.atlassian.com/enterprise/using-personal-access-tokens-1026032365.html でJira のPAT 作成についてのドキュメントを参照してください。

OAuth 2.0

Jira の"3本足の" OAuth 2.0 サポート（3LO）を活用して、ログインクレデンシャルを提供せずにデータに接続できます。

以下の手順は、すべてのOAuth フローでAuthScheme がOAuth に設定されていることを前提としています。

API トークン

AuthScheme をAPIToken に設定し、User およびAPIToken を設定することで、任意のJira Cloud アカウントへの接続を確立できます。
Cloud インスタンスへのベーシック認証には、API トークンが必要です。API トークンを生成するには、Atlassian アカウントにログインして［セキュリティ設定］->［API トークン］->［API トークンを作成する］をクリックします。生成されたトークンが表示されます。

Basic

AuthScheme をBasic に設定することで、任意のJira Server インスタンスへの接続を確立できます。Server インスタンスに接続するには、User およびPassword を指定します。
（Note：パスワードはCloud アカウントへの接続には非推奨となり、Server インスタンスへの接続にのみ使用されるようになりました。）

LDAP

AuthScheme をLDAP に設定することで、任意のJira Server インスタンスへの接続を確立できます。追加で、Jira インスタンスのURL、User、およびPassword を指定します。 （Note：LDAP 認証は現在、Cloud アカウントではサポートされていません。）

Crowd

AuthScheme をCrowd に設定します。Crowd への接続には、次の接続プロパティを使用します。

• User：Crowd ユーザーアカウント。
• Password：Crowd アカウントに関連付けられたパスワード。
• SSOLoginURL：Crowd アカウントに関連付けられたログインURL。IDP URL は、自身のアプリケーション->［SSO］->［SSO information］->［Identity provider single sign-on URL］にあります。
• SSOAppName：SSO を有効にするアプリケーションの名前。
• SSOAppPassword：SSO を有効にするアプリケーションのパスワード。
• SSOExchangeUrl: The URL used used to exchange the SAML token for Jira cookies. This URL may have the following formats:
  • https://<authority of Jira instance>/plugins/servlet/samlconsumer
  • https://<authority of Jira instance>/plugins/servlet/samlsso

次は接続文字列の例です。

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

Okta

Okta に接続するには、AuthScheme をOkta に設定し、次のプロパティを設定します。

• User：Okta ユーザー。
• Password：Okta ユーザーのパスワード。
• SSOLoginURL：SSO プロバイダーのログインURL。
• SSOExchangeUrl: The URL used used to exchange the SAML token for Jira cookies. This URL may have the following formats:
  • https://<authority of Jira instance>/plugins/servlet/samlconsumer
  • https://<authority of Jira instance>/plugins/servlet/samlsso

Okta クライアントリクエストコンテキストをオーバーライドする信頼されたアプリケーションまたはプロキシを使用する場合、またはMFA を設定している場合は、Okta を使用して認証するためにSSOProperties を組み合わせて使用する必要があります。必要に応じて、以下のいずれかを設定します。

• APIToken：Okta クライアントリクエストコンテキストをオーバーライドする、信頼されたアプリケーションまたはプロキシ経由でユーザーを認証する場合、これを顧客がOkta 組織で作成したAPI Token に設定します。
• MFAType：MFA フローを設定した場合、次の対応するタイプのいずれかに設定します：OktaVerify、Email、またはSMS。
• MFAPassCode：MFA フローを設定した場合は、有効なパスコードに設定します。
  これを空欄または無効な値に設定した場合、Cloud はユーザーのデバイスまたはE メールにワンタイムパスワードチャレンジを発行します。パスコードを受信後、取得したワンタイムパスワードをMFAPassCode 接続プロパティに設定する接続を再度開きます。
• MFARememberDevice：デフォルトはTrue です。Okta は、MFA が必要な場合にデバイスを記憶させることをサポートします。設定された認証ポリシーに従ってデバイスの記憶が許可されている場合、Cloud はMFA 認証の有効期間を延長するデバイストークンを送信します。MFA を記憶させない場合は、この 変数をFalse に設定してください。

接続文字列の例：

AuthScheme=Okta;Url=https://yoursitename.atlassian.net;SSOLoginURL='https://example.okta.com/home/appType/0bg4ivz6cJRZgCz5d6/46';User=oktaUserName;Password=oktaPassword;SSOExchangeUrl=https://<authority of Jira instance>/plugins/servlet/samlconsumer;

SSL 設定のカスタマイズ

デフォルトでは、Cloud はサーバーの証明書をシステムの信頼できる証明書ストアと照合してSSL / TLS のネゴシエーションを試みます。

別の証明書を指定するには、利用可能なフォーマットについてSSLServerCert プロパティを参照してください。

クライアントSSL 証明書

Jira Cloud はクライアント証明書の設定もサポートしています。次を設定すれば、クライアント証明書を使って接続できます。

• SSLClientCert：クライアント証明書のための証明書ストア名。
• SSLClientCertType：TLS / SSL クライアント証明書を格納するキーストアの種類。
• SSLClientCertPassword：TLS / SSL クライアント証明書のパスワード。
• SSLClientCertSubject：TLS / SSL クライアント証明書のサブジェクト。

Firewall またはProxy 経由の接続

HTTP プロキシ

Windows のシステムプロキシ経由の接続では、接続プロパティを追加で設定する必要はありません。他のプロキシに接続するには、ProxyAutoDetect をfalse に設定します。

さらにHTTP プロキシへの認証には、ProxyServer とProxyPort に加えてProxyAuthScheme、ProxyUser、およびProxyPassword を設定します。

その他のプロキシ

次のプロパティを設定します。

• プロキシベースのファイヤーウォールを使用するには、FirewallType、FirewallServer、およびFirewallPort を設定します。
• 接続をトンネルするには、FirewallType をTUNNEL に設定します。
• 認証するには、FirewallUser とFirewallPassword を設定します。
• SOCKS プロキシへの認証には、さらにFirewallType をSOCKS5 に設定します。

Cloud は、Jira API をリレーショナルテーブル、ビュー、およびストアドプロシージャとしてモデル化します。これにより、Jira Cloud アカウントやJira Server インスタンスへのデータベースライクな接続が可能になります。

カスタムフィールドへのアクセス

デフォルトでは、テーブルスキーマおよびビュースキーマは、Jira エンティティのシステムフィールドのカラム動作を記述します。システムフィールド同様、Issues のカスタムフィールド にアクセスするには、接続文字列のIncludeCustomFields を設定できます。

カラム名の制御

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

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

Cloud はJira のデータを、標準のSQL ステートメントを使用してクエリできるリレーショナルデータベースのテーブルのリストとしてモデル化します。

CData Cloud - Jira テーブル

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

Query, insert and delete the available Attachments in Jira.

テーブル固有の情報

Select

Cloud はJira API を使用して一部のフィルタを処理します。

Cloud は他のフィルタをCloud 内で処理します。

例えば、次のクエリはサーバー側で処理されます。

SELECT * FROM Attachments WHERE Id = 10003

課題の添付ファイルを取得します。

SELECT * FROM Attachments WHERE IssueId = '123' 

添付ファイルを取得する課題のより複雑なフィルタリングには、JQL 疑似カラムを使用します。例えば、以下は1月7日以降に作成された課題の添付ファイルを取得します。

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

Insert

課題に添付ファイルをアップロードします。

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

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

Delete

添付ファイルのID を指定して、課題にアップロードされた添付ファイルを削除します。

DELETE FROM Attachments WHERE Id = 10003

Columns

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

Pseudo-Columns

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。

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

Query, insert and delete the agile Boards in Jira.

テーブル固有の情報

Select

Cloud はJira API を使用して一部のフィルタを処理します。Cloud は他のフィルタをCloud 内で処理します。

サーバー側のフィルタを使用したクエリ例を次に示します。

SELECT * FROM Boards WHERE Id = 10022

その他のフィルタの使用：

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

Insert

新しいボードを挿入するには、Name、Type、およびFilterId を指定する必要があります。

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

Delete

ボードを削除するには、Id を指定する必要があります。

DELETE FROM Boards WHERE Id = 130

Columns

Name	Type	ReadOnly	References	SupportedOperators	Platform	Description
Id [KEY]	Integer	True		=	Common	The Id of the board.
Name	String	False		=	Common	The name of the board.
Type	String	False		=	Common	The type of the board. 使用できる値は次のとおりです。scrum, kanban, simple

Pseudo-Columns

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。

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

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

テーブル固有の情報

Select

このテーブルをクエリして、すべての課題にポストされたすべてのコメントを取得できます。

Cloud はJira API を使用して一部のフィルタを処理します。

Cloud は他のフィルタをCloud 内で処理します。

例えば、次のクエリはサーバー側で処理されます。

SELECT * FROM Comments

特定の課題にポストされたコメントを取得することもできます。

SELECT * FROM Comments WHERE IssueId = 10003

さらに、ORDER BY 句でCreated カラムを使用できます。

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

Insert

ログインユーザーとしてコメントをポストします。IssueId およびBody が必須です。

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

Update

コメントの内容のみが更新可能です。UPDATE にはIssueId も必要です。

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

Delete

Id およびIssueId を入力して、課題にポストされたコメントを削除します。

DELETE FROM Comments WHERE Id = 10202 AND IssueId = 10003

Columns

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

Pseudo-Columns

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。

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

Create, modify, delete and query Issues in Jira.

テーブル固有の情報

このテーブルをクエリするときにカスタムフィールドのカラムと値（Sprint、Epic など）を動的に取得するためには、IncludeCustomFields 接続プロパティを'True' に設定する必要があります。

SQL とJQL (Jira Query Language) を使用して、Issues を検索できます。

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'

Cloud はJira API を使用して一部のフィルタを処理します。Cloud はクライアント側でCloud 内で他のフィルタを処理します。SupportEnhancedSQL をfalse に設定することで、クエリのクライアント側の実行をオフにできます。その場合、その他のカラムを参照する検索条件は一致しないデータを生成します。 例えば、次のクエリはサーバー側で処理されます。

SQL およびJQL

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

時間、日、週、分などを指定して、TimeSpent、OriginalEstimate、RemainingEstimate などの時間間隔のフィールドをフィルタリングします。

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

Insert

プロジェクトで新しい課題を作成します。挿入には、Project（ProjectId またはProjectKey を指定）、IssueType（IssueTypeId またはIssueTypeName を指定）、およびSummary が必要です。ProjectName を指定することもできますが、ProjectKey が属する値を見つけるためにサーバーへの追加リクエストが発生します。Project やIssueType によっては、さらに多くのフィールドが必要な場合があります。

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

Update

ID を入力して既存の課題の情報を更新します。

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

また、特定の課題の集計フィールドを変更することもできます。FixVersionsAggregate、AffectedVersionsAggregate、ComponentsAggregate、およびIssueLinksAggregate を変更できます。これらのカラムの特別な入力を使用して、課題の適切なフィールドを割り当て、削除、および更新できます。 プロバイダークエリで使用するには、Jira アプリケーションで先にオブジェクトを作成しておく必要があります。NOTE：IssuelinksAggregate フィールドは入力の要件がわずかに異なります。以下で概要を説明します。

修正バージョンを追加する構造：

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

or

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

サンプルクエリ：

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

一度に追加できるオブジェクトは1つのみですが、set コマンドを使用すると、複数のオブジェクトを割り当てることができます。 重要：set コマンドは、以前のすべてのオブジェクトを上書きします。つまり、以前のエントリは破棄され、 set コマンドで指定されたオブジェクトのみが課題に保存されます。set コマンドの後ろには配列が必要です。

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

or 

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

サンプルクエリは次のようになります。

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

remove コマンドを使用して、エントリを一度に1つずつ削除することもできます。

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

or

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

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

これらのコマンドは、新しい課題を挿入するときにも適用されます。

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

IssueLinksAggregate では'add' 操作のみがサポートされており、リンクのタイプおよびリンクの方向（'outwardIssue' か'inwardIssue' のいずれか1つ）を指定する必要があります。 使用可能なリンクタイプはBlocks、RootCause、Relates、Duplicate、および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

ID を指定して課題を削除します。完了していないサブタスクも削除されることに注意してください。

DELETE FROM Issues WHERE Id = '10003'

Columns

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

Pseudo-Columns

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。

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

Query the available Issue Types in Jira.

Select

Cloud はJira API を使用して一部のフィルタを処理します。

Cloud は他のフィルタをCloud 内で処理します。

SELECT * FROM IssueTypes WHERE Id = 3

Insert

挿入するにはName が必要です。次のクエリは、新しい課題タイプを作成します。

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

Update

課題タイプId を指定して更新できます。

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

Delete

課題タイプId を指定して課題タイプを削除します。

DELETE FROM IssueTypes WHERE Id = 10306

Columns

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

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

テーブル固有の情報

Select

Cloud はJira API を使用して一部のフィルタを処理します。Cloud は他のフィルタをCloud 内で処理します。

例えば、次のクエリはサーバー側で処理されます。

各プロジェクトのコンポーネントを取得

SELECT * FROM ProjectComponents

コンポーネントのId 、またはコンポーネントが含まれているプロジェクトでフィルタリングすることもできます。

SELECT * FROM ProjectComponents WHERE Id = 10022 

ProjectId の使用：

SELECT * FROM ProjectComponents WHERE ProjectId = 10027 

ProjectKey の使用：

SELECT * FROM ProjectComponents WHERE ProjectKey = 'QMTH' 

Insert

プロジェクトの新しいコンポーネントを作成します。ProjectKey およびName カラムが必須です。

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

Update

変更するコンポーネントのId カラムを指定して、プロジェクトコンポーネントを変更します。

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

Delete

Id を指定して、プロジェクトのコンポーネントを削除します。

DELETE FROM ProjectComponents WHERE Id = 10065

Columns

Name	Type	ReadOnly	References	SupportedOperators	Platform	Description
Id [KEY]	Integer	True		=	Common	The Id of the component.
ProjectId	Integer	True	Projects.Id	=,IN	Common	The selected project's id.
ProjectKey	String	False		=,IN	Common	The selected project's key.
Name	String	False			Common	The name of the component.
Description	String	False			Common	The description of the component.
LeadDisplayName	String	True			Common	The display name of the component's lead.
LeadKey	String	False			Common	The key of the component's lead.
AssigneeType	String	False			Common	The type of the component's default assignee. 使用できる値は次のとおりです。PROJECT_DEFAULT, COMPONENT_LEAD, PROJECT_LEAD, UNASSIGNED
AssigneeDisplayName	String	True			Common	The display name of the component's default assignee.
AssigneeKey	String	True			Common	The key of the component's default assignee.
IsAssigneeTypeValid	Boolean	True			Common	Whether the assignee type is valid.

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

Select

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

For example, the following queries are processed server side:

Retrieve all actors for a specific project:

SELECT * FROM ProjectRoleActors WHERE ProjectId = '10650'

SELECT * FROM ProjectRoleActors WHERE ProjectKey = 'MYP'

Retrieve all actors for a specific role in all projects:

SELECT * FROM ProjectRoleActors WHERE RoleId = '10329'

Retrieve all actors for a specific project:

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

Retrieve all actors for specific projects:

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

Retrieve all actors for specific roles in a project:

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

Insert

Cloud

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

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

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

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

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

Server

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

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

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

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

Delete

Cloud

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

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

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

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

Server

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

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

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

Columns

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

Pseudo-Columns

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。

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

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

Select

Cloud はJira API を使用して一部のフィルタを処理します。

Cloud は他のフィルタをCloud 内で処理します。 例えば、次のクエリはサーバー側で処理されます。

SELECT * FROM Projects WHERE Id = 10000

INSERT

挿入には、Key、Name、LeadName、およびProjectTypeKey が必要です。次のクエリは、新しいプロジェクトを作成します。

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

Update

Id かKey のいずれかを指定して更新できます。

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

Delete

Id かKey のいずれかを指定してプロジェクトを削除できます。

DELETE FROM Projects WHERE Key = 'EXE4'

Columns

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

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

テーブル固有の情報

Select

Cloud はJira API を使用して一部のフィルタを処理します。Cloud は他のフィルタをCloud 内で処理します。 例えば、次のクエリはサーバー側で処理されます。

すべてのプロジェクトのバージョンを取得：

SELECT * FROM ProjectVersions 

Id、ProjectKey、またはProjectId でフィルタをかけることもできます。次に例を示します。

SELECT * FROM ProjectVersions WHERE Id = 10022 

ProjectId の使用：

SELECT * FROM ProjectVersions WHERE ProjectId = 10022 

ProjectKey の使用：

SELECT * FROM ProjectVersions WHERE ProjectKey = 'QMTH' 

Insert

プロジェクト全体で使用する新しいバージョンを作成します。Name と、ProjectId またはProjectKey いずれかのカラムが必須です。

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

Update

変更するバージョンのId カラムを指定して、プロジェクトバージョンを変更します。

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

Delete

Id カラムを指定して、プロジェクトのバージョンを削除します。

DELETE FROM ProjectVersions WHERE Id = 10065

Columns

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

Query the default actors for project roles in Jira.

Select

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

For example, the following queries are processed server side:

Retrieve all the default actors for all roles:

SELECT * FROM RoleActors

Retrieve all the default actors for a specific role:

SELECT * FROM RoleActors WHERE RoleId = '10002'

Insert

Cloud

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

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

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

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

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

Server

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

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

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

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

Delete

Cloud

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

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

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

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

Server

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

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

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

Columns

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

Pseudo-Columns

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。

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

Query the available roles in Jira.

Select

Cloud はJira API を使用してId を参照するフィルタを処理します。その他のフィルタはクライアント側でCloud 内部で処理されます。Jira API は、Id でイコール（=）演算子を使用するフィルタをサポートしています。

例えば、次のクエリはサーバー側で処理されます。

すべての利用可能なロールを取得する：

SELECT * FROM Roles

特定のロールを取得する：

SELECT * FROM Roles WHERE Id = '10002'

Update

ロールId を指定して、ロールの名前と説明を更新します。

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

Delete

Id を指定して、ロールを削除します。

DELETE FROM Roles WHERE Id = '10002'

Columns

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

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

テーブル固有の情報

Select

Jira アカウントのSprints のリストを取得するには、BoardSprints ビューをクエリできます。

Cloud はJira API を使用して一部のフィルタを処理します。Cloud はその他のフィルタをクライアント側で内部で実行します。例えば、次のクエリはサーバー側で処理されます。

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

INSERT

新しいスプリントを作成して、ボードにリンクします。新しいスプリントの挿入には、OriginBoardId およびName カラムが必須です。

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

Update

スプリントのId を指定してスプリントを変更します。

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

Delete

Id を指定して、スプリントを削除します。

DELETE FROM Sprints WHERE Id = 305

Columns

Name	Type	ReadOnly	References	SupportedOperators	Platform	Description
Id [KEY]	Integer	True		=,IN	Common	The Id of the sprint.
Name	String	False			Common	The name of the sprint.
State	String	False			Common	The state of the sprint. A newly created sprint starts in the 'future' state. The state can only transition from 'future' to 'active', and from 'active' to 'closed' 使用できる値は次のとおりです。future, active, closed
Goal	String	False			Common	The goal assigned for the sprint.
OriginBoardId	Integer	False	Boards.Id	=	Common	The board Id the sprint originated from. This field cannot be updated
StartDate	Datetime	False			Common	The date when the sprint was started.
EndDate	Datetime	False			Common	The date when the sprint has ended.
CompleteDate	Datetime	True			Common	The date when the sprint was completed.

Query the available Users in Jira.

Select

Cloud は、次のフィルタ以外のフィルタを、クライアント側でCloud 内部で実行します。 Jira API では、LIKE 演算子を使ったName、DisplayName、およびEmailAddress カラムのフィルタのサポートに制限があります。

Name、DisplayName、およびEmailAddress を一度に1つずつ指定します。

Jira はこれらのカラムに基づいたフィルタをActive またはIncludeInactive カラムと組み合わせてサポートします。例えば、次のクエリはサーバー側で処理されます。

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

GroupName もしくはGroupId を指定して、特定のグループに属するユーザーをクエリします。

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

すべてのグループとそのメンバーのリストを取得することもできます。

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

IssueKey およびProjectKey 疑似カラムを使用して、特定の課題またはプロジェクト内の任意の課題を閲覧できるユーザーを検索します。

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

Insert

INSERT への入力はE メールアドレスである必要があります。

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

Columns

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

Pseudo-Columns

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。

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

Query the available Workflows in Jira.

Select

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

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

For example, the following queries are executed server side:

SELECT * FROM Workflows WHERE Name = 'abc'

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

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

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

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

Columns

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

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

テーブル固有の情報

Select

このテーブルをクエリして、Jira プロジェクトの作業ログを取得できます。Cloud は、Jira API を使用して次のカラムおよび演算子に基づいてフィルタを処理します。 Cloud は他のフィルタをクライアント側で実行します。

例えば、次のクエリはサーバーサイドで処理されます。

SELECT * FROM Worklogs WHERE IssueId = 10001

Insert

挿入には、IssueId、Started およびTimeSpentSeconds が必須です。次のクエリは、課題の作業完了を記録します。

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

Update

作業ログを変更するには、worklog Id とIssueId の両方が必要です。

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

Delete

Id およびIssueId を入力して、課題の作業ログを削除します。

DELETE FROM Worklogs WHERE Id = 10602 AND IssueId = 10003

GetDeleted

削除された作業ログのId を取得します。

GETDELETED FROM Worklogs

Columns

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

ビューは、データを示すという点でテーブルに似ていますが、ビューは読み取り専用です。

クエリは、ビューに対して通常のテーブルと同様に実行することができます。

CData Cloud - Jira ビュー

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

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

Table Specific Information

Select

SELECT * FROM AdvancedSettings

Columns

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

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

Table Specific Information

Select

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

The Cloud processes other filters within the Cloud.

For example, the following queries are processed server side:

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

Columns

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

Query the audit log of your Jira account.

テーブル固有の情報

Select

Cloud はJira API を使用して一部のフィルタを処理します。

Cloud は他のフィルタをCloud 内で処理します。

例えば、次のクエリはサーバー側で処理されます。

SELECT * FROM Audit WHERE Filter = 'ab'

Columns

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

Pseudo-Columns

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。

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

Query the agile Board Configuration in Jira.

Columns

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

Query the agile Board Issues in Jira.

テーブル固有の情報

Select

Cloud はJira API を使用して一部のフィルタを処理します。 Cloud は他のフィルタをCloud 内で処理します。

例えば、次のクエリはサーバー側で処理されます。

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

Columns

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

Query the agile Sprints related to a Jira Board.

テーブル固有の情報

Select

Cloud はJira API を使用して一部のフィルタを処理します。Cloud は他のフィルタをCloud 内で処理します。

例えば、次のクエリはサーバー側で処理されます。

SELECT * FROM BoardSprints WHERE BoardId = 122 

BoardId の使用：

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

かんばんタイプのボードはスプリントをサポートしていないため、次のようなクエリを使用すると、すべてのスプリントをより速く取得できます。

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

Columns

Name	Type	References	SupportedOperators	Platform	Description
Id [KEY]	Integer			Common	The Id of the sprint.
BoardId [KEY]	Integer	Boards.Id	=,IN	Common	The board Id the sprint is in.
Name	String			Common	The name of the sprint.
State	String		=	Common	The state of the sprint. The sprint state can only transition from 'future' to 'active', and from 'active' to 'close' 使用できる値は次のとおりです。future, active, closed
Goal	String			Common	The goal assigned for the sprint.
OriginBoardId	Integer	Boards.Id	=	Common	The board Id the sprint originated from. This field cannot be updated
StartDate	Datetime			Common	The date when the sprint was started.
EndDate	Datetime			Common	The date when the sprint has ended.
CompleteDate	Datetime			Common	The date when the sprint was completed.

Query the available Configurations in Jira.

テーブル固有の情報

Select

Cloud は、このテーブルで指定されたすべてのフィルタをクライアント側でCloud 内で処理します。

SELECT * FROM Configuration

Columns

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

Query the available Dashboards in Jira.

テーブル固有の情報

Select

Cloud はJira API を使用して一部のフィルタを処理します。Jira はId およびFilter カラムをサポートしますが、両方同時にはサポートしません。

Cloud は他のフィルタをCloud 内で処理します。

例えば、次のクエリはサーバー側で処理されます。

SELECT * FROM Dashboards WHERE Id = 10001

疑似カラムFilter でフィルタをかけることもできます。

SELECT * FROM Dashboards WHERE Filter = 'favourite'

Columns

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

Pseudo-Columns

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。

Name	Type	Description
Filter	String		This is used to filter data. Valid values include favourite and my. 使用できる値は次のとおりです。favourite, my

Query all the Epics.

テーブル固有の情報

Select

Cloud はJira API を使用して一部のフィルタを処理します。

Cloud は他のフィルタをCloud 内で処理します。

例えば、次のクエリはサーバー側でフィルタリングされます。

SELECT * FROM Epics WHERE BoardId = 1

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

特定のエピックのId またはKey を指定：

SELECT * FROM Epics WHERE Id = 10000 

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

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

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

Columns

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

Query the favourite Filters in Jira.

Columns

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

Query the available System and Custom Fields in Jira.

Columns

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

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

テーブル固有の情報

Select

Cloud はJira API を使用して一部のフィルタを処理します。

Cloud は他のフィルタをCloud 内で処理します。

すべてのフィルタを取得：

SELECT * FROM Filters 

特定のフィルタのId を指定：

SELECT * FROM Filters WHERE Id = 10001 

Columns

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

Query the available Groups in Jira.

テーブル固有の情報

Select

Cloud はJira API を使用して一部のフィルタを処理します。Cloud は他のフィルタをCloud 内で処理します。

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

Columns

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

Query the Affected Versions of an issue in Jira.

テーブル固有の情報

SQL とJQL (Jira Query Language) を使用して、IssueAffectedVersions を検索できます。

Select

Cloud はJira API を使用して一部のフィルタを処理します。

Cloud は他のフィルタをCloud 内で処理します。

IssueAffectedVersions で使用可能なカラムは、IssueId、IssueKey、およびJQL です次に例を示します。

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

Columns

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

Pseudo-Columns

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。

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

Query the available IssueChangelogs in Jira.

テーブル固有の情報

SQL とJQL (Jira Query Language) を使用して、IssueChangelogs を検索できます。

Select

Cloud はJira API を使用して一部のフィルタを処理します。

Cloud は他のフィルタをCloud 内で処理します。

IssueChangelogs のサーバー側のフィルタリングで使用可能なカラムは、IssueId、IssueKey、ProjectId、ProjectKey、ProjectName およびJQL です。次に例を示します。

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

Columns

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

Pseudo-Columns

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。

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

Query the available IssueComponents in Jira.

テーブル固有の情報

SQL とJQL (Jira Query Language) を使用して、IssueComponents を検索できます。

Select

Cloud はJira API を使用して一部のフィルタを処理します。

Cloud は他のフィルタをCloud 内で処理します。

IssueComponents で使用可能なカラムは、IssueId、IssueKey、およびJQL です。次に例を示します。

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

Columns

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

Pseudo-Columns

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。

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

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

Columns

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

Query the available Custom Fields in Jira.

Columns

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

Query the available IssueFixVersions in Jira.

テーブル固有の情報

SQL とJQL (Jira Query Language) を使用して、IssueFixVersions を検索できます。

Select

Cloud はJira API を使用して一部のフィルタを処理します。

Cloud は他のフィルタをCloud 内で処理します。

IssueFixVersions で使用可能なカラムは、IssueId、IssueKey、およびJQL です。次に例を示します。

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

Columns

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

Pseudo-Columns

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。

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

Query the available IssueLinks in Jira.

テーブル固有の情報

SQL とJQL (Jira Query Language) を使用して、IssueLinks を検索できます。

Select

Cloud はJira API を使用して一部のフィルタを処理します。

Cloud は他のフィルタをCloud 内で処理します。

IssueLinks で使用可能なカラムは、IssueId、IssueKey、およびJQL です。次に例を示します。

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

Columns

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

Pseudo-Columns

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。

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

Lists all the issue link types.

Select

Cloud はJira API を使用して、Id カラムを参照するフィルタを処理します。

Cloud はその他のフィルタをクライアント側で内部で実行します。例えば、次のクエリはサーバー側で処理されます。

SELECT * FROM IssueLinkTypes WHERE Id = '10003'

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

Columns

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

Query the default issue navigator columns.

Table Specific Information

Select

SELECT * FROM IssueNavigatorDefaultColumns

Columns

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

Query the list of all issue priorities.

Table Specific Information

Select

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

The Cloud processes other filters within the Cloud.

For example, the following queries are processed server side:

SELECT * FROM IssuePriorities WHERE Id = 1

Columns

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

Query the list of all issue resolution values.

Table Specific Information

Select

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

The Cloud processes other filters within the Cloud.

For example, the following queries are processed server side:

SELECT * FROM IssueResolutions WHERE Id = 10000

Columns

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

Query the available IssueSubtasks in Jira.

テーブル固有の情報

SQL とJQL (Jira Query Language) を使用して、IssueSubtasks を検索できます。

Select

Cloud はJira API を使用して一部のフィルタを処理します。

Cloud は他のフィルタをCloud 内で処理します。

IssueSubtasks で使用可能なカラムは、IssueId、IssueKey、およびJQL です。次に例を示します。

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

Columns

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

Pseudo-Columns

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。

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

Query the available IssueTransitions in Jira.

テーブル固有の情報

SQL とJQL (Jira Query Language) を使用して、IssueTransitions を検索できます。

Select

Cloud はJira API を使用して一部のフィルタを処理します。

Cloud は他のフィルタをCloud 内で処理します。

IssueTransitions で使用可能なカラムは、IssueId、IssueKey、およびJQL です。次に例を示します。

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

Columns

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

Pseudo-Columns

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。

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

Query the available Permissions of the current user in Jira.

テーブル固有の情報

Select

Cloud は、このテーブルで指定されたすべてのフィルタをクライアント側でCloud 内で処理します。

SELECT * FROM MyPermissions

Columns

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

Pseudo-Columns

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。

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

Query the available Permissions in Jira.

Columns

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

Query all the project categories.

Columns

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