Capturing link data for JIra issues in CSV import

[rsstorey]

My program heavily uses Jira as a data source within our Model Based System Engineering (MBSE) environment.  We use No Magic's MagicDraw application to import data via csv to support modeling across the entire program enterprise.  One of the key aspects of MBSE is defining relationships between the thousands of model SysML elements that represents every aspect of a program from end to end in a highly dynamic environment.

I came across an issue with a new Jira project I need to integrate into our MBSE environment.  Each JIra issue has numerous links to other issues that represent the associated relationship between these issues.  I query Jira to return the 5000+ issues in a list view.  The link includes the "Links" column with lists each of the issues by Jira Key which is a hyper link. When  I export this list and open it in Microsoft Excel the links for each issue are listed in a single cell separated by comma with no information indicating type type of relationship (input, output, part of, parent, child, etc.).

The first obstacle, is there a way to identify the type of relationship for the link (this will help in identifying the type of SysML associated to be created within the MBSE project)

Second, is there a way to list the data as a separate line for each unique Jira Issue and each link (to the link Jira issue).

[Andy Heinzer]

Hi,

I understand that you are working with with CSV files in order to import/export Jira data and it seems that some of that data appears to be getting mangled in the process.  However I am finding a few differences between what you have stated and what I see in my latest test version of Jira 8.3.2.  You mentioned:

The link includes the "Links" column with lists each of the issues by Jira Key which is a hyper link. When I export this list and open it in Microsoft Excel the links for each issue are listed in a single cell separated by comma with no information indicating type type of relationship

However when I export a search of issues from Jira into the CSV format, I don't see a single Links field.  Nor do I see hyperlinks on these values.  Instead, I get separate column names such as

"Outward issue link (Blocks)" "Outward issue link (Duplicate)" "Outward issue link (Duplicate)"

And in my case, I only see the issue key, without any hyperlink.  This format is compatible with our KB on How to Import Issue Links from a CSV File. 

I'm not sure why you might be seeing this differently in your environment.  It could be the version of Jira you're using here, which would be interesting.  But I'd also want to see if perhaps you might not be using the true CSV exporter in Jira.  I say that because this feature was only really implemented in Jira 7.2 and higher.  Before that time, Jira 7.1.x and earlier, had an Excel exporter, which functioned very similarly, but it did have some Excel specific features that it could implement, like hyperlinking values, something the CSV exporter doesn't do for issue links. If the export is actually creating a .xls file that is directly opened in Excel, then it might be part of the problem here.  But if your export is actually creating a file with an extension of .csv when exported, then maybe not.

So perhaps there is something different about your version of Jira, and/or the export option you are using (Excel, CSV, HTML, XML can all export this issue data, but they could each have slightly different formatting that might be munging the data in an unfavorable way).  Please let me know what version of Jira you are using here, and the specific option that you select when you choose the Export option (ie CSV current fields, CSV all fields, RSS, HTML All fields, HTML current fields, XML, Excel all fields, Excel current fields, word, printable, full content).  I would be interested to see what more we can learn about this.

Please let me know.

Andy

[rsstorey]

Thank you for you response.  In review my initial post I realized I misrepresented my intent.  When I Export CSV (Current field) I do get the links as individual columns for each type of link.  In my case this resulted in 173 individual columns for the links between the 1107 individual jira issues.

What I would like to know is if a tailored csv export is possible in which the data is presented as follows.

Jira Key     Link

Key-1         Key-3, Key-4, Key-5
Key-2        Key-4, Key-5
Key-3        Key-1, Key-5
Key-4        Key-1, Key-2
Key-5        Key-1, Key-2, Key-3

Current Export

Issue Key      Outbound (Type 1)     Outbound (Type 2)     Outbound (Type 3)
Key-1             Key-3                          Key-4                           Key-5
Key-2                                                Key-4                            Key-5
Key-3            Key-1                                                                Key-5

Preferred Export (an individual line for each Jira key and one link.

Key-1     Key-3
Key-1.    Key-4
Key-1.    Key-5
Key-2.    Key-4
Key-2.    Key-5
.... 

[Andy Heinzer]

The problem is that your preferred export method does not capture the link type.  Perhaps you are not concerned with that part.

In short, I don't believe that the CSV export in Jira can currently provide you with a separate entry for each link an issue has.  Jira's issue navigator is designed specifically to return Jira issues and their data.  It's not meant to be able to export those records exactly in that way. 

However since it appears you are using Jira Server, you can obtain this data in the way I think you want with some SQL queries run against the Jira database directly.  For example, you could probably use a query like this one to return the link data in a way I think you are looking for:

SELECT p.pkey || '-' || ji.IssueNum as "Source issuekey", ilt.outward as "Outward link name", ptwo.pkey || '-' || jitwo.IssueNum as "Destination issuekey" 
FROM issuelink il
join jiraissue ji on ji.id = il.source
join project p on ji.project = p.id
join jiraissue jitwo on jitwo.id = il.destination
join project ptwo on jitwo.project = ptwo.id
join issuelinktype ilt on ilt.id = il.linktype;

By using SQL (instead of JQL) and doing a select against the issuelink table directly, you will get a unique record for each issuelink that exists in Jira, and not just a single unique record per issue.  Searching in Jira natively, via JQL limits the results to be bound to the Jira issues themselves.

Perhaps you could obtain the data you want via SQL this way instead of trying to use the CSV export to get the data out.  I hope this helps.

Andy

[rsstorey]

Andy,

Thank you for the response, this may well be the answer I'm looking for.  Yes, the type of link is vital.  I will ask one of our Jira Admins to look at this and see if they can test.

Once again thanks.

[Andy Heinzer]

Glad to help out.   If this does end up being the answer, then please click the Accept Answer button next to my answer.  If it doesn't, just let us know.  And if you find some other alternative method, please let us know about that too.

By having an accepted answer on a thread, it can mark the question itself as solved, which in turn can help with search results for other users that might have the same question to find this same info.

Cheers,

Andy