Query related to subtask.

Hi all,

We extensively use subtask feature of JIRA and now have couple of where Standard Issue type is around 6k and if i include subtask it is more than 50K. I can get subtask info in excel easily howver the view is limited to 1k in JIRA so it would be difficult to have all the subtask and you can imgine that i have export couple of time to get all 50k subtask in excel and these may lead to human error.

Is this data somewhere stored in sql server DB where i can fetch data directly.

Like for comment info in JIRA there is JIRAaction table which has all this info. I'm able to retrive all the info driectly by sql server which has simplified by task.

2 answers

1 accepted

0 vote

Sub-tasks are still issues in their own right.

I avoid the use of SQL because you have to build an understanding of the data structure for it to be useful, and you shouldn't really run it on an active Jira database - there's the risk of constructing queries that cause overloads and take your Jira out. (and it also makes "clever" developers think they can write to it - which is wrong because it breaks it)

Anyway, your query starts with jiraissue - you'll need to select by issue type (join to the issue type table to read the type names), then join that to the other tables you need if you want versions, components, custom fields, user names or the parent issue id (that's 9 other tables to start with - you can see why I say it's easy to construct overloading SQL statements)

You might find it easier to question why you are exporting? Then come up with a better solution.

Thanks NIc. Your advice always helps me a lot.

We have a SQL team who could assit me wirting sql quries if i get the table name. As per my understanding subtask info is also saved in jiraissue table- correct me if I'm wrong.

Yes, you are absolutely correct

I said it in the first sentence, but re-reading it, I can see how it's unclear. I fall into odd English ways of phrasing things sometimes, and it makes my prose unclear. Sorry.

Still curious as to why you need to export by the way. There may be a better solution.

Oh, and tables, you'll want to look at https://developer.atlassian.com/display/JIRADEV/Database+Schemafor the full schema and how some of the bigger stuff hooks together. Bear in mind the schema varies between versions of Jira (but not hugely)

So the reason is we are migrating data from JIRA to our new tool Salesforce so i need to be ready with all the data in JIRA so we can import it in sales force.

Those tools do two very different things, I probably woudn't bother.

0 vote

Hi Neeta,

Instead of trying to retrieve the information directly from the database, you could try incresing the Excel limit to enable all the 50k issues into the exported file

Take a look at this page to increase the limit

Hope this helps

It is unlikely that you will have enough memory installed on your server for this to work.

Suggest an answer

Log in or Join to answer
Community showcase
Sarah Schuster
Posted Jan 29, 2018 in Jira

What are common themes you've seen across successful & failed Jira Software implementations?

Hey everyone! My name is Sarah Schuster, and I'm a Customer Success Manager in Atlassian specializing in Jira Software Cloud. Over the next few weeks I will be posting discussion topics (8 total) to ...

3,308 views 14 20
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot