Reference

Endpoint Read Issues

Name

get_issues

Description

No description available

Related Tables

Issues

Parameters

Parameter Required Options
Name: SearchBy

Label: Search by
Option Value
None
Project Key(s) Project
JQL Expression JQL
Issue Id(s) or Key(s) Key
Name: Project

Label: Project

Specify a project to search issues for
Name: JQL

Label: JQL

Use JQL to use an advanced mode of searching. Few links are https://www.atlassian.com/blog/jira/jql-the-most-flexible-way-to-search-jira-14 and als check https://wac-cdn.atlassian.com/dam/jcr:241d9c9b-475f-411f-90a2-97ca344442b3/atlassian-jql-cheat-sheet-2.pdf?cdnVersion=2169
Option Value
Search Ids (IN) key IN(10001, 10002, 10003)
Search Keys (IN) key IN(CS-1, CS-2, CS-3)
Search Projects (IN) project IN(PROJ1, PROJ2, PROJ3)
Search Status (EQUAL) status='Done'
Search Date (Expression 1) created >=-5d
Search Date (Expression 2) created >=startOfMonth() AND created <=now()
Search Date (Expression 3) created >=startOfYear() AND created <=startOfDay()
Search Date (Static) created >= "2008-12-31"
Search Date (Static with time) created >= '2008/12/31 23:59'
Search Date (Issue Update Date) updated >=-5d
Search Project and Status (AND + IN) project=CS AND status NOT IN ('Done', 'Open', 'Closed')
Search Assignee and Created Date assignee is NOT EMPTY and created < -1d
Search Text (Contains - Fuzzy) Summary ~ 'some words' OR description ~ 'some words'
Search Text (Contains - Fuzzy Wildcard) Summary ~ 'some*' OR description ~ 'some*'
Search Text (Contains - Exact) Summary ~ '\"exact words\"' OR description ~ '\"exact words\"'
Search Text (Does Not Contain - Fuzzy) Summary !~ 'some words' OR description !~ 'some words'
Search Empty OR Null fixVersion is empty OR fixVersion is null
Search Is Not Empty OR Is Not Null fixVersion is not empty OR fixVersion is not null
Search WAS Operator (previous value) status WAS "Resolved" BY (jsmith,srogen) BEFORE "2019/02/02"
Search WAS IN Operator status WAS IN ("Resolved", "In Progress")
Search WAS NOT IN Operator status WAS NOT IN ("Resolved", "In Progress")
Search WAS + BY + DURING (date range) status WAS "Resolved" BY (jsmith,srogen) DURING("2019/02/02", "2020/02/02")
Search CHANGED operator assignee CHANGED
Search CHANGED operator (multiple) status CHANGED FROM "In Progress" TO "Open"
Name: Key

Label: Issue Key(s) or Ids - Comma Separated (PRJA-10,PRJA-13)

Use comma separated IDs or Keys for Issues. 1000 ids max or JIRA internal limit for Payload.
Name: CustomColumnsRegex

Label: CustomColumnsRegex

Enter regular expression for custom columns which you like to output. Use Pipe to type multiple column names (must use key - internal names for columns e.g. customfield_10004). You can use Regex too (e.g. field_100\d+ ). This filter is only applied for those fields which has flag custom=true.

Output Columns

Label Data Type (SSIS) Data Type (SQL) Length Description
Id DT_I8 bigint
Key DT_WSTR nvarchar(50) 50
ProjectKey DT_WSTR nvarchar(50) 50
ProjectName DT_WSTR nvarchar(500) 500
StatusName DT_WSTR nvarchar(500) 500
Summary DT_WSTR nvarchar(2000) 2000
Created DT_DBTIMESTAMP datetime
Updated DT_DBTIMESTAMP datetime
Description DT_NTEXT nvarchar(MAX) Join formatted text blocks using space separator (new lines will be lost)
DescriptionFormatted DT_NTEXT nvarchar(MAX)
CreatorAccountId DT_WSTR nvarchar(100) 100
CreatorEmailAddress DT_WSTR nvarchar(100) 100
ReporterAccountId DT_WSTR nvarchar(100) 100
ReporterEmailAddress DT_WSTR nvarchar(100) 100
AssigneeAccountId DT_WSTR nvarchar(100) 100
AssigneeEmailAddress DT_WSTR nvarchar(100) 100
ResolutionId DT_I8 bigint
ResolutionDescription DT_WSTR nvarchar(4000) 4000
ResolutionName DT_WSTR nvarchar(500) 500
Resolution DT_NTEXT nvarchar(MAX)
TimespentInSeconds DT_I8 bigint
DueDate DT_DBDATE date
Expand DT_WSTR nvarchar(150) 150
StatusCategoryChangeDate DT_DBTIMESTAMP datetime
Self DT_WSTR nvarchar(150) 150
IssueTypeSelf DT_WSTR nvarchar(150) 150
IssueTypeId DT_I8 bigint
IssueTypeDescription DT_NTEXT nvarchar(MAX)
IssueTypeIconUrl DT_WSTR nvarchar(500) 500
IssueTypeName DT_WSTR nvarchar(100) 100
IssueTypeSubtask DT_BOOL bit
IssueTypeAvatarId DT_I4 int
IssueTypeEntityId DT_GUID uniqueidentifier
IssueTypeHierarchyLevel DT_I4 int
ProjectSelf DT_WSTR nvarchar(150) 150
ProjectId DT_I8 bigint
ProjectTypeKey DT_WSTR nvarchar(100) 100
ProjectSimplified DT_BOOL bit
ProjectAvatarUrls48x48 DT_WSTR nvarchar(500) 500
ProjectAvatarUrls24x24 DT_WSTR nvarchar(500) 500
ProjectAvatarUrls16x16 DT_WSTR nvarchar(500) 500
ProjectAvatarUrls32x32 DT_WSTR nvarchar(500) 500
FixVersions DT_NTEXT nvarchar(MAX)
AggregateTimespentInSeconds DT_I8 bigint
ResolutionDate DT_DBTIMESTAMP datetime
WorkRatio DT_WSTR nvarchar(100) 100
WatchesSelf DT_WSTR nvarchar(150) 150
WatchesWatchCount DT_I4 int
WatchesIsWatching DT_BOOL bit
LastViewed DT_DBTIMESTAMP datetime
PrioritySelf DT_WSTR nvarchar(150) 150
PriorityIconUrl DT_WSTR nvarchar(500) 500
PriorityName DT_WSTR nvarchar(100) 100
PriorityId DT_I4 int
Labels DT_NTEXT nvarchar(MAX)
AggregateTimeOriginalEstimate DT_WSTR nvarchar(50) 50
TimeEstimate DT_WSTR nvarchar(50) 50
Versions DT_NTEXT nvarchar(MAX)
IssueLinks DT_NTEXT nvarchar(MAX)
AssigneeSelf DT_WSTR nvarchar(150) 150
AssigneeAvatarUrls48x48 DT_WSTR nvarchar(500) 500
AssigneeAvatarUrls24x24 DT_WSTR nvarchar(500) 500
AssigneeAvatarUrls16x16 DT_WSTR nvarchar(500) 500
AssigneeAvatarUrls32x32 DT_WSTR nvarchar(500) 500
AssigneeDisplayName DT_WSTR nvarchar(500) 500
AssigneeActive DT_BOOL bit 32
AssigneeTimeZone DT_WSTR nvarchar(100) 100
AssigneeAccountType DT_WSTR nvarchar(100) 100
StatusSelf DT_WSTR nvarchar(150) 150
StatusDescription DT_NTEXT nvarchar(MAX)
StatusIconUrl DT_WSTR nvarchar(500) 500
StatusId DT_I4 int
StatusCategorySelf DT_WSTR nvarchar(150) 150
StatusCategoryId DT_I4 int
StatusCategoryKey DT_WSTR nvarchar(100) 100
StatusCategoryColorName DT_WSTR nvarchar(100) 100
StatusCategoryName DT_WSTR nvarchar(100) 100
Components DT_NTEXT nvarchar(MAX)
TimeOriginalEstimate DT_WSTR nvarchar(50) 50
DescriptionVersion DT_I4 int
DescriptionType DT_WSTR nvarchar(50) 50
Security DT_WSTR nvarchar(150) 150
AggregateTimeEstimate DT_I8 bigint
CreatorSelf DT_WSTR nvarchar(150) 150
CreatorAvatarUrls48x48 DT_WSTR nvarchar(500) 500
CreatorAvatarUrls24x24 DT_WSTR nvarchar(500) 500
CreatorAvatarUrls16x16 DT_WSTR nvarchar(500) 500
CreatorAvatarUrls32x32 DT_WSTR nvarchar(500) 500
CreatorDisplayName DT_WSTR nvarchar(200) 200
CreatorActive DT_BOOL bit
CreatorTimeZone DT_WSTR nvarchar(100) 100
CreatorAccountType DT_WSTR nvarchar(100) 100
Subtasks DT_NTEXT nvarchar(MAX)
ReporterSelf DT_WSTR nvarchar(150) 150
ReporterAvatarUrls48x48 DT_WSTR nvarchar(500) 500
ReporterAvatarUrls24x24 DT_WSTR nvarchar(500) 500
ReporterAvatarUrls16x16 DT_WSTR nvarchar(500) 500
ReporterAvatarUrls32x32 DT_WSTR nvarchar(500) 500
ReporterDisplayName DT_WSTR nvarchar(100) 100
ReporterActive DT_BOOL bit
ReporterTimeZone DT_WSTR nvarchar(100) 100
ReporterAccountType DT_WSTR nvarchar(100) 100
AggregateProgressPercent DT_I4 int
AggregateProgressInSeconds DT_I8 bigint
AggregateProgressTotalInSeconds DT_I8 bigint
Environment DT_NTEXT nvarchar(MAX) Join formatted text blocks using space separator (new lines will be lost)
EnvironmentFormatted DT_NTEXT nvarchar(MAX)
ProgressInSeconds DT_I8 bigint
ProgressTotalInSeconds DT_I8 bigint
VotesSelf DT_WSTR nvarchar(150) 150
Votes DT_I4 int
HasVoted DT_BOOL bit
ParentId DT_I4 int
ParentKey DT_WSTR nvarchar(50) 50
ParentSelf DT_WSTR nvarchar(150) 150
ParentSummary DT_WSTR nvarchar(2000) 2000
ParentStatusSelf DT_WSTR nvarchar(150) 150
ParentStatusDescription DT_WSTR nvarchar(2000) 2000
ParentStatusIconUrl DT_WSTR nvarchar(500) 500
ParentStatusName DT_WSTR nvarchar(100) 100
ParentStatusId DT_I8 bigint
ParentStatusCategorySelf DT_WSTR nvarchar(150) 150
ParentStatusCategoryId DT_I4 int
ParentStatusCategoryKey DT_WSTR nvarchar(50) 50
ParentStatusCategoryColorName DT_WSTR nvarchar(50) 50
ParentStatusCategoryName DT_WSTR nvarchar(50) 50
ParentprioritySelf DT_WSTR nvarchar(150) 150
ParentpriorityIconUrl DT_WSTR nvarchar(500) 500
ParentpriorityName DT_WSTR nvarchar(50) 50
ParentpriorityId DT_I4 int
ParentIssueTypeSelf DT_WSTR nvarchar(150) 150
ParentIssueTypeId DT_I8 bigint
ParentIssueTypeDescription DT_WSTR nvarchar(2000) 2000
ParentIssueTypeIconUrl DT_WSTR nvarchar(500) 500
ParentIssueTypeName DT_WSTR nvarchar(50) 50
ParentIssueTypeSubtask DT_BOOL bit
ParentIssueTypeAvatarId DT_I4 int
ParentIssueTypeEntityId DT_GUID uniqueidentifier
ParentIssueTypeHierarchyLevel DT_I4 int
Assignee DT_WSTR nvarchar(150) 150
ResolutionSelf DT_WSTR nvarchar(150) 150
[$parent.key$] DT_WSTR nvarchar(2000) 2000 [Dynamic Column]
[$parent.key$]_value DT_WSTR nvarchar(2000) 2000 [Dynamic Column]_value
[$parent.key$]_id DT_WSTR nvarchar(20) 20 [Dynamic Column]_id
[$parent.key$]_child_value DT_WSTR nvarchar(2000) 2000 [Dynamic Column]_child_value
[$parent.key$]_child_id DT_WSTR nvarchar(20) 20 [Dynamic Column]_child_id
[$parent.key$]_values DT_WSTR nvarchar(2000) 2000 [Dynamic Column]_values
[$parent.key$]_text DT_NTEXT nvarchar(MAX) [Dynamic Column]_text Join formatted text blocks using space separator (new lines will be lost)
[$parent.key$]_accountId DT_WSTR nvarchar(100) 100 [Dynamic Column]_accountId
[$parent.key$]_email DT_WSTR nvarchar(300) 300 [Dynamic Column]_email
[$parent.key$]_name DT_WSTR nvarchar(300) 300 [Dynamic Column]_name
If the column you are looking for is missing, consider customizing Jira Connector.

Input Columns

Label Data Type (SSIS) Data Type (SQL) Length Description
There are no Static columns defined for this endpoint. This endpoint detects columns dynamically at runtime.

Examples

SSIS

Use Jira Connector in API Source or in API Destination SSIS Data Flow components to read or write data.

API Source

This Endpoint belongs to the Issues table, therefore it is better to use it, instead of accessing the endpoint directly:

API Source - Jira
Jira connector can be used to read, write, delete Issues, Users, Worklogs, Comments, Projects, Custom fileds and many other details
Jira
Issues
There are no parameters to configure.
SSIS API Source - Read from table or endpoint

API Destination

This Endpoint belongs to the Issues table, therefore it is better to use it, instead of accessing the endpoint directly. Use this table and table-operation pair to read issues:

API Destination - Jira
Jira connector can be used to read, write, delete Issues, Users, Worklogs, Comments, Projects, Custom fileds and many other details
Jira
Issues
Select, Lookup
There are no parameters to configure.
SSIS API Destination - Access table operation

ODBC application

Use these SQL queries in your ODBC application data source:

List issues

Lists all issues 

SELECT * FROM Issues

List a single issue by Id

List a single issue by Key (e.g. CS-123) or Numeric Id (e.g. 10001). 

SELECT * FROM Issues WITH(SearchBy='Key', Key='10001')

List a single issue by Id - Continue on a specific error message

By default if issue is not found or search condition is bad you may get an error but you can continue by setting ContineOnErrorForMessage=1 and message you like to ignore in ErrorSubstringToMatch. 

SELECT * FROM Issues WITH(SearchBy='Key', Key='10001', ContineOnErrorForMessage=1, ErrorSubstringToMatch='Issue does not exist')

List a single issue by Key

List a single issue by Key (e.g. CS-123) or Numeric Id (e.g. 10001). 

SELECT * FROM Issues WITH(SearchBy='Key', Key='CS-1')

List multiple issues by Id or Key

List multiple issues by comma separated Key(s) or Numeric Id(s). 

SELECT * FROM Issues WITH(SearchBy='Key', Key='CS-1, CS-2, 10003, 10004')

List all issues for a specific project

List all issues for a specified project code. 

SELECT * FROM Issues WITH(Project='CS')

Search issues using Advanced JQL query expression

List issues using JQL query expression 

SELECT * FROM Issues WITH (Jql='status IN (Done, Closed) AND created > -5d' )
/*
Useful links:
  https://support.atlassian.com/jira-work-management/docs/jql-fields/
  https://www.atlassian.com/software/jira/guides/jql/tutorials#advanced-search
  https://www.atlassian.com/blog/jira/jql-the-most-flexible-way-to-search-jira-14
  
Other Possible JQL expressions:

Ids (IN):       key IN(10001, 10002, 10003);
Keys (IN):       key IN(CS-1, CS-2, CS-3);
Projects (IN):       project IN(PROJ1, PROJ2, PROJ3);
Status (EQUAL):       status='Done';
Date (Expression 1):       created >=-5d;
Date (Expression 2):       created >=startOfMonth() AND created <=now();
Date (Expression 3):       created >=startOfYear() AND created <=startOfDay();
Date (Static):       created >= '2008/12/31';
Date (Static with time):       created >= '2008/12/31 23:59';
Project and Status (AND + IN):       project=CS AND status NOT IN ('Done', 'Open', 'Closed');
Assignee and Created Date:    assignee is NOT EMPTY and created < -1d;
Text (Contains - Fuzzy):       Summary ~ 'some words' OR description ~ 'some words';
Text (Contains - Fuzzy Wildcard):       Summary ~ 'some*' OR description ~ 'some*';
Text (Contains - Exact):       Summary ~ '\"exact words\"' OR description ~ '\"exact words\"';
Text (Does Not Contain - Fuzzy):       Summary !~ 'some words' OR description !~ 'some words';
Empty OR Null:   fixVersion is empty OR fixVersion is null;
Is Not Empty OR Is Not Null:    fixVersion is not empty OR fixVersion is not null;
WAS Operator (previous value):       status WAS "Resolved" BY (jsmith,srogen) BEFORE "2019/02/02";
WAS IN Operator:   status WAS IN ("Resolved", "In Progress");
WAS NOT IN Operator:    status WAS NOT IN ("Resolved", "In Progress");
WAS + BY + DURING (date range):       status WAS "Resolved" BY (jsmith,srogen) DURING("2019/02/02", "2020/02/02");
CHANGED operator:    assignee CHANGED;
CHANGED operator (multiple):       status CHANGED FROM "In Progress" TO "Open"

*/

get_issues endpoint belongs to Issues table(s), and can therefore be used via those table(s).

SQL Server

Use these SQL queries in SQL Server after you create a data source in Data Gateway:

List issues

Lists all issues 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Issues';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

List a single issue by Id

List a single issue by Key (e.g. CS-123) or Numeric Id (e.g. 10001). 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Issues WITH(SearchBy=''Key'', Key=''10001'')';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

List a single issue by Id - Continue on a specific error message

By default if issue is not found or search condition is bad you may get an error but you can continue by setting ContineOnErrorForMessage=1 and message you like to ignore in ErrorSubstringToMatch. 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Issues WITH(SearchBy=''Key'', Key=''10001'', ContineOnErrorForMessage=1, ErrorSubstringToMatch=''Issue does not exist'')';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

List a single issue by Key

List a single issue by Key (e.g. CS-123) or Numeric Id (e.g. 10001). 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Issues WITH(SearchBy=''Key'', Key=''CS-1'')';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

List multiple issues by Id or Key

List multiple issues by comma separated Key(s) or Numeric Id(s). 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Issues WITH(SearchBy=''Key'', Key=''CS-1, CS-2, 10003, 10004'')';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

List all issues for a specific project

List all issues for a specified project code. 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Issues WITH(Project=''CS'')';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

Search issues using Advanced JQL query expression

List issues using JQL query expression 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Issues WITH (Jql=''status IN (Done, Closed) AND created > -5d'' )
/*
Useful links:
  https://support.atlassian.com/jira-work-management/docs/jql-fields/
  https://www.atlassian.com/software/jira/guides/jql/tutorials#advanced-search
  https://www.atlassian.com/blog/jira/jql-the-most-flexible-way-to-search-jira-14
  
Other Possible JQL expressions:

Ids (IN):       key IN(10001, 10002, 10003);
Keys (IN):       key IN(CS-1, CS-2, CS-3);
Projects (IN):       project IN(PROJ1, PROJ2, PROJ3);
Status (EQUAL):       status=''Done'';
Date (Expression 1):       created >=-5d;
Date (Expression 2):       created >=startOfMonth() AND created <=now();
Date (Expression 3):       created >=startOfYear() AND created <=startOfDay();
Date (Static):       created >= ''2008/12/31'';
Date (Static with time):       created >= ''2008/12/31 23:59'';
Project and Status (AND + IN):       project=CS AND status NOT IN (''Done'', ''Open'', ''Closed'');
Assignee and Created Date:    assignee is NOT EMPTY and created < -1d;
Text (Contains - Fuzzy):       Summary ~ ''some words'' OR description ~ ''some words'';
Text (Contains - Fuzzy Wildcard):       Summary ~ ''some*'' OR description ~ ''some*'';
Text (Contains - Exact):       Summary ~ ''\"exact words\"'' OR description ~ ''\"exact words\"'';
Text (Does Not Contain - Fuzzy):       Summary !~ ''some words'' OR description !~ ''some words'';
Empty OR Null:   fixVersion is empty OR fixVersion is null;
Is Not Empty OR Is Not Null:    fixVersion is not empty OR fixVersion is not null;
WAS Operator (previous value):       status WAS "Resolved" BY (jsmith,srogen) BEFORE "2019/02/02";
WAS IN Operator:   status WAS IN ("Resolved", "In Progress");
WAS NOT IN Operator:    status WAS NOT IN ("Resolved", "In Progress");
WAS + BY + DURING (date range):       status WAS "Resolved" BY (jsmith,srogen) DURING("2019/02/02", "2020/02/02");
CHANGED operator:    assignee CHANGED;
CHANGED operator (multiple):       status CHANGED FROM "In Progress" TO "Open"

*/';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

get_issues endpoint belongs to Issues table(s), and can therefore be used via those table(s).