Issues

Provides full access to Jira issues. Supports creation, updates, deletions, and queries for issue records across all projects.

Table Specific Information

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

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

Select

JQL

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

SQL

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

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

SQL and JQL

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

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

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

Insert

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

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

Update

Update info of existing issues by providing the Id

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

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

Structure for adding a fix version:

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

or

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

An example query:

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

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

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

or 

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

And an example query would be:

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

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

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

or

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

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

These commands are also applicable when inserting a new issue:

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

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

IssueLinksAggregate

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

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

or

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

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

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

Delete

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

DELETE FROM Issues WHERE Id = '10003'

Columns


Name	Type	ReadOnly	References	SupportedOperators	Platform	Description
Id [KEY]	Int	True		=,!=,>,>=,<,<=,IN,NOT IN	Common	The unique numeric identifier assigned to the issue by Jira.
Key	String	True		=,!=,>,>=,<,<=,IN,NOT IN	Common	The unique key used to reference the issue, typically in the format 'PROJ-123'.
IssueTypeId	String	False		=,!=,IS,IS NOT,IN,NOT IN	Common	The internal identifier of the issue type, such as 'Bug', 'Task', or 'Story'.
IssueTypeName	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The human-readable name of the issue type, such as 'Bug' or 'Task'.
ProjectId	Integer	False		=,!=,IS,IS NOT,IN,NOT IN	Common	The unique identifier of the project that contains the issue.
ProjectName	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The name of the project that the issue belongs to.
ProjectKey	String	False		=,!=,IS,IS NOT,IN,NOT IN	Common	The unique key of the project, often used as the prefix in issue keys (for example, 'PROJ').
ParentId	Int	False		=,!=,IN,NOT IN	Common	The ID of the parent issue, used when the issue is a subtask.
ParentKey	String	True		=,!=,IN,NOT IN	Common	The key of the parent issue, used when the issue is a subtask.
ParentIssueTypeId	String	True			Common	The issue type ID of the parent issue, providing insight into the parent issue's classification.
ParentIssueTypeName	String	True			Common	The issue type name of the parent issue, such as 'Story' or 'Task'.
ParentStatusId	String	True			Common	The status ID of the parent issue, indicating the workflow status.
ParentStatusName	String	True			Common	The status name of the parent issue, such as 'In Progress' or 'Done'.
ResolutionId	String	True		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The resolution ID of the issue, representing how the issue was resolved.
ResolutionName	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The human-readable name of the issue's resolution, such as 'Fixed' or 'Duplicate'.
ResolutionDescription	String	True			Common	A detailed explanation of the resolution applied to the issue.
ResolutionDate	Datetime	True		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The date and time the issue was marked as resolved.
Workratio	Long	True		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	A percentage value comparing the amount of time spent versus the estimated effort.
LastViewed	Datetime	True		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The date and time the issue was last viewed by the current user.
WatchCount	Int	True		=,!=,>,>=,<,<=,IN,NOT IN	Common	The number of users who are watching the issue for updates.
IsWatching	Bool	True			Common	If the current authenticated user is watching this issue, the value is 'true'; otherwise 'false'.
Created	Datetime	True		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The timestamp when the issue was initially created in Jira.
PriorityId	String	False		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The ID representing the priority level assigned to the issue.
PriorityName	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The name of the priority level, such as 'High', 'Medium', or 'Low'.
TimeSpentSeconds	Long	True			Common	The total time spent working on the issue, measured in seconds.
TimeSpent	String	True		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The total time spent on the issue, formatted as a human-readable string (for example, '2h 30m').
RemainingEstimateSeconds	Long	True			Common	The remaining estimated effort required to complete the issue, in seconds.
RemainingEstimate	String	False		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The remaining time estimate formatted as a string (for example, '1d 2h').
OriginalEstimateSeconds	Long	True			Common	The original estimated time required to complete the issue, in seconds.
OriginalEstimate	String	False		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The original time estimate displayed as a formatted string (for example, '3d').
AggregateTimeSpent	Long	True			Common	The total time spent across the issue and its subtasks, in seconds.
AggregateTimeOriginalEstimate	Long	True			Common	The total of the original time estimates for the issue and all its subtasks.
AggregateTimeEstimate	Long	True			Common	The current total remaining estimate for the issue and all of its subtasks.
AssigneeDisplayName	String	True			Common	The full display name of the user assigned to the issue.
AssigneeKey	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	[DEPRECATED] The key identifier of the assignee. Use AssigneeAccountId instead.
AssigneeAccountId	String	False		=,!=,IS,IS NOT,IN,NOT IN	Common	The Atlassian account ID of the user assigned to the issue.
AssigneeName	String	False		=,!=,IS,IS NOT,IN,NOT IN	Common	The username of the assignee, used in older Jira instances.
AssigneeEmail	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The email address of the user assigned to the issue.
Updated	Datetime	True		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The timestamp of the last update made to the issue.
StatusId	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The internal identifier of the issue's current workflow status.
StatusName	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The name of the issue's status, such as 'To Do', 'In Progress', or 'Done'.
StatusCategoryId	Integer	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The category ID associated with the issue status, used to group similar workflow statuses.
StatusCategoryKey	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The key used to identify the status category, such as 'new', 'indeterminate', or 'done'.
StatusCategoryName	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The display name of the status category, used to group workflow states.
Description	String	False		IS,IS NOT	Common	A detailed explanation of the issue's problem, task, or request.
Summary	String	False		IS,IS NOT	Common	A brief summary or title that provides an overview of the issue.
CreatorDisplayName	String	True			Common	The full display name of the user who created the issue.
CreatorName	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The username of the issue creator, typically used in legacy Jira systems.
CreatorAccountId	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The Atlassian account ID of the user who created the issue.
CreatorKey	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	[DEPRECATED] The key identifier for the issue creator. Use CreatorAccountId instead.
CreatorEmail	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The email address of the user who created the issue.
ReporterDisplayName	String	True			Common	The full display name of the user who reported the issue.
ReporterName	String	False		=,!=,IS,IS NOT,IN,NOT IN	Common	The username of the reporter, used in legacy Jira systems.
ReporterKey	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	[DEPRECATED] The key identifier for the reporter. Use ReporterAccountId instead.
ReporterAccountId	String	False		=,!=,IS,IS NOT,IN,NOT IN	Common	The Atlassian account ID of the user who reported the issue.
ReporterEmail	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The email address of the user who reported the issue.
AggregateProgress	Long	True			Common	The total progress made on the issue, including progress on all subtasks, measured in seconds.
TotalProgress	Long	True			Common	The total estimated time for the issue and all its subtasks.
Votes	Int	True		=,!=,>,>=,<,<=,IN,NOT IN	Common	The number of users who have voted for this issue as being important or needing attention.
HasVotes	Bool	True			Common	Indicates whether the issue has any user votes. Returns 'true' if votes exist, otherwise 'false'.
DueDate	Date	False		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The due date by which the issue is expected to be completed.
Labels	String	False		=,!=,IS,IS NOT,IN,NOT IN	Common	A comma-separated list of labels applied to the issue for categorization or filtering.
Environment	String	False		LIKE,NOT LIKE,IS,IS NOT	Common	Describes the technical or physical environment relevant to the issue (such as OS, browser, or hardware).
SecurityLevel	String	False		=,!=,IS,IS NOT,IN, NOT IN	Common	The security level applied to the issue, restricting visibility to a defined user group.
FixVersionsAggregate	String	False			Common	A comma-separated list of versions in which the issue is or will be resolved.
ComponentsAggregate	String	False			Common	A list of project components that this issue is associated with.
IssueLinksAggregate	String	False			Common	A list of links to other issues that relate to or are dependent on this issue.
AffectedVersionsAggregate	String	False			Common	A list of versions affected by this issue.
SubtasksAggregate	String	True			Common	A collection of subtasks associated with the issue.
CustomField1	Any	False			Common	A user-defined custom field for the issue. Custom fields appear when 'IncludeCustomFields' is enabled.
CustomField2	Any	False			Common	Another user-defined custom field available when 'IncludeCustomFields' is set to 'true'.
ItemURL	String	True			Common	A browsable URL linking directly to the issue in the Jira web interface.

Pseudo-Columns

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


Name	Type	Description
JQL	String	Used to filter and search issues using Jira Query Language (JQL).
Attachments	String	Used to query issues that have attachments. Can be filtered using IS EMPTY or IS NOT EMPTY.
Comment	String	Filters issues that contain a comment with specified text content.
Category	String	Allows filtering of issues based on the category of the associated project.
SprintId	Integer	Filters issues by the numeric ID of the sprint they belong to.
SprintName	String	Filters issues by the name of the sprint they are part of.
Filter	String	Queries issues using the name or ID of a saved filter.

ADO.NET Provider for Jira