SQL query for JIRA saved filter

Hi,

I would like to know if there is any way of retrieving the information from my saved filter using a query on a direct connection to mysql db.

If I want to generate the information and result is below 1000 rows, this is the process:

query - > search for issues -> export button -> choose "Excel (current fields)"

The information will automatically be saved on my local folder.

Currently, we have an issue on exporting the information from my save filter if the result set is beyond 1000 rows.


To be more clear, details are as follows:

JQL: project = projectxyz AND issuetype = "Customer Incident" AND created >= -52w

Information I would like to retrieve based on the JQL:

  1. Key (Jira Issue ID)
  2. Issue Type
  3. Summary
  4. Found In
  5. Status
  6. Resolution
  7. Created
  8. Date of First Response
  9. Resolved
  10. Custom Field1
  11. Custom Field2
  12. Severity


Please advise if there is a way to retrieve the same result from my save filter. Otherwise, kindly let me guide me on creating the sql-query to be able to get the data.

By the way. I'm using JIRA 6.1.1

4 answers

1 accepted

This widget could not be displayed.

Jean Paul,

I agree with Nic that the SQL will not necessarily be straightforward (although I believe that the resolution date actually is available in the jiraissue table).

However, if you want to increase the max limit of 1,000 rows in your export, this may save you from having to use SQL at all:

Scott

Hi Scott,

Thank you for the references, we did try to increase the max limit but executing it overloads and affects the performance of JIRA and is not good for a production environment where we have a lot of users.

This widget could not be displayed.

Your JQL is fine, you're limited in the Excel download because it's resource heavy and can overload your server if you use it with a large data set

You should be thinking about why you want thousands of records (a human simply can't process that much data, so you must be looking for summaries or data slices and a far better option that random downloads of already obsolete information is to do the reporting in Jira)

The SQL is complex - Key, issue type, created data, summary, status, and resolution are all on the main table jiraissue, but

to get first response and resolved, you'll need to read the history changeitem and changegroup to establish them, as they're not necessarily stored in the database at all

for custom field 1 and 2you need to read customfieldvalue and customfield

found-in and severity are odd. They are probably custom fields (in which case, see above). Unless you meant "priority" and/or "affects version", in which case, you can read jiraissue for the priority again, but you'll be mucking around with nodeassociation and versions to get affects version

Hi Nic,

Thank you very much for the detailed response. I will map the fields and hopefully everything can be retrieved easily. Currently, I've seen the table jiraissue for the other fields. I will check the history change item and change group for the other custom fields.

One thing, before 6.1, pkey is being ised for JIRA issue ID. Any advise on how it can be extracted for JIRA 6.1 and above?

Try:

select p.pkey || '-' || ji.issuenum as issuekey,summary from jiraissue ji left join project p on p.id=ji.project limit 15;

You may need to use a different string concatenation operator/function, depending on your specific SQL dialect.

This widget could not be displayed.

Thanks scott.

This widget could not be displayed.

I hope you will take some time to learn alittle SQL. It is a very important skill to have to understanding the various systems you might work with now and in the future. Copying code without a minimal understanding can be very dangerous to projects. i watch this 

https://bluestacks.vip/ , https://textnow.vip/ , https://photomath.vip/

 

Thank you for still taking time replying on this old inquiry.  I agree with you with knowledge on SQL and copy & paste of codes being not a good practice. 

Have a good day. :)

Suggest an answer

Log in or Sign up to answer
Atlassian Summit 2018

Meet the community IRL

Atlassian Summit is an excellent opportunity for in-person support, training, and networking.

Learn more
Community showcase
Posted Wednesday in New to Jira

Are you planning to trial, or are currently trialling Jira Software? - We want to talk to you!

Hello! I'm Rayen, a product manager at Atlassian. My team and I are working hard to improve the trial experience for Jira Software Cloud. We are interested in   talking to 20 people planning t...

196 views 3 0
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