Query related to subtask.

[Neeta Dubey]

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.

[Nic Brough -Adaptavist- · Accepted solution]

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.

[Neeta Dubey]

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.

[Nic Brough -Adaptavist-]

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.

[Nic Brough -Adaptavist-]

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)

[Neeta Dubey]

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.

[Nic Brough -Adaptavist-]

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

[Marcus Silveira]

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

[Nic Brough -Adaptavist-]

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