Export all data from Jira

[Eliya Voldman]

Hello,

My company has to decommission server where Jira is installed.

We are not sure if we will use it again soon. Most likely management will decide to use other tracking system or simply keep data in some storage .

We are using Jira since 2013 and have around 3000 issues in database. 

Our version is v4.4.4

Hence here are my questions:

1. how could I export all issues from Jira
2. Not sure which format it better to store ... Excel, css or etc.
3. I suggest that we will need to use this data until we have another tracking system ... hence my question: I don't think it's convenient to search data from spreadsheets ... maybe you'd recommend to import this data in some other tool or database which will provide better experience to search for issue. Maybe MS Access or other tool you could recommend?

Thanks

[Nic Brough -Adaptavist- · Accepted solution]

The only way you can export all your data like this is to create XML backups, which are only suitable for recreating another Jira server to read it (and although XML import can be used to upgrade, you won't be able to import 4.4.4 into 6.x if memory serves)

Three CSV exports (you can only export 1,000 at a time) will lose you data - the history and config of your stuff cannot be exported

Mapping it into a datastore would be a nightmare if you wanted to keep all the data - the Jira database is not a reportable shape, you might have to spend months working out where everything is and how to represent it in a totally different shape in a database

So, you first decide what you want to keep - everything, lossy issues, just the issues, etc, and that will start to tell you what you can do to keep or archive it.

[Eliya Voldman]

Well ... you made a good point. I simply was not aware of those limitations .

Let me reword my request:

I want to save only issues and attached files to it.

My point:

1. imagine someone would need information about some issue we tracked before e.g. some artifacts ID. Then one could open e.g. Excel or etc. and search for that ID ... read 'Description' and check the file attached to it.
2. I am not sure if searching for that issue in e.g. Excel is better than in any database where we could import that Excel.  Maybe having Excel would be fine too as long as it searchable and have 'Description ' and 'Summary' information about that ID

Not sure I expressed my thoughts ... in other words I want to keep somehow all possible information we have now in Jira. If can't save all ... I think at least some valuable would be OK too.

What would you recommend ?

Thanks

[Nic Brough -Adaptavist-]

So you just need the basic Jira issue information, and the attachments.

• I would make four exports - 3 x 1,000 CSV files (you will probably find it easier to break this up by project or category or something, so more than 3 files, but it gets you round the 1000 limit). 
• Export as many fields as you can get on the issue navigator from where you run the CSV exports.
• Next, look at the file system on the server - there's a directory called "attachments" in your "Jira home" directory (check system info in the application to see where that is, and if you've renamed attachments).
• You are going to need to copy that, retaining the structure of the directories, to somewhere people can go to read the attachments.
• Next, create two or three tables in an empty database.
• Take a copy of the jira database's "attachments" table - this enables you to find the right attachment in from the Jira data you are going to import
• Take a copy of the Jiraissues table.  You only need the issue key and issue id
• Import the CSV into the third table.
• You need the first two tables to cross reference the attachments from the Jira issue CSV (Jira saves them with identifiers instead of names, the attachments table maps that)

[Eliya Voldman]

- Nic Brough

Thanks for detailed explanation.

First I checked what we have in our instance for export:

I talked to my team and they would be happy to have it in Excel.

Hence I split everything in 4 Excel files and compiled one of it.

Looking into Excel I realized that it does not have very important parts for us i.e. 'Description' and 'Comments' fields.  Looks that it exported only those columns that were visible in search:

Hence I have to resolve 2 items here:

1. Get 'Description' and 'Comments' in Excel export

2. Handle attachment . Also those attachments should be crosswalks to corresponding issues.

Unfortunately I don't have access to Jira server but I could request our admin to do it.

Could you please advise how to get into export file those fields that we are interested mostly i.e. Description and Comments?

Could you please outline what should I request admin to get attachments and later link it with final Excel file?

Thanks and appreciate your help

[Nic Brough -Adaptavist-]

I mentioned the issue navigator in my last comment, but there was a lot there, and I kept it short!

The CSV/Excel exports have an option for "export via current list of fields shown", so if you use the issue navigator's "change visible fields", you'll be able to add the fields you want, and remove the ones you are less interested in.

You're going to struggle with comments - it's not a field, and I'm not sure you can get it out in version 4.  You might get "last comment" though.

You are going to need to extract the attachments off the disk, that's a server admin job.  I'm not sure what you mean by "crosswalks" - attachments are single entries on single issues.

[Eliya Voldman]

Thanks for explaining 

I was able to add Description field.

I did not find anything related to 'Comments' in the list of fields. Well this is a problem for us since both Description and Comments have valuable information .

I will talk to admin to download attachments and then will think if I can somehow reference corresponding issue. This is what I meant by 'crosswalks' ... sorry about it

[Nic Brough -Adaptavist-]

Don't worry about "crosswalks", languages are variable, and Jira has its own jargon on top of however we humans speak.  I would have said "cross reference", but I could not be sure that's what would translate for me.

I earlier mentioned that if you're going to try to get as much issue data as possible, you are going to need to fetch two database tables as well as the CSV.

But your need for attachments and comments are not going to work in Excel, you need a bespoke database for this.

Imagine a single issue with a couple of attachments.  I will ignore most fields here, it gets complicated.  In the database, this issue will appear in two tables, with three lines:

• JiraIssue
  • ID - unique id for the issue in the database
  • Issue Key - the key for us humans, appears in your CSV
• Attachments
  • ID - unique id for the file
  • Issue ID - the issue ID in JiraIssue the attachment belongs to
  • Path/name - where it is in the file system and the file's physical name
  • Name - the name of the file to display (taken from the name of the file that was uploaded)
  • Version - the version of the file (if you upload a file with the same name as an existing one, Jira keeps the old one)

So when you want to look at the two attachments on issue ABC-123, you need to make some calls into this data.

• Look at the issue data to get the issue ID from the Key
• Read the attachments data for all the attachments on the issue

Now you know which files to serve up to the users from your file store

This is a bit similar to comments.  If you take a raw copy of the "JiraAction" table, you can put that into your database too.  This table contains a bit of history if you've used a really old Jira, but most of the time it's just the comments.

All the comments are stored as lines in JiraAction - join that to the issue ID in JiraIssue to get all the comments that belong to an issue (it includes fields like Isssue ID, the body comment, the author, when it was added, and when edited)

[Hieu]

1. Export all issues to csv file
2. Another lightweight tool is Trello -> save cost
3. Use the new system for tracking issue + spend time try to import

You can skip step 2. Just do steps 1 and 3. Step 1 is mandatory

[Eliya Voldman]

Thanks Hieu for reply.

The only concern is that .csv is not very convenient for user for search ... maybe .csv could be import in Excel .. but sounds like this could be the simplest way. Actually I will need to export 4 times since Jira allow only 1000 records at once ...

Well ... hope that exported .csv will have all Descriptions and Summary data ..

[Eliya Voldman]

Well eventually I was able to export issues in 2 formats: Excel and HTML. 
My team likes Excel because it allows rearrange data but it missing Comments fields. That’s why I did HTML (Full Content) as well which has all data although is not so convenient. Anyway I think I am all set with export. 
I requested admin to download all attachments and when I get it I wil think how to cross reference to issues 

Thanks a lot for help