It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

JEP Reopen reporting


I am trying to run an audit trail on all of my companies historical jiras by making a table via sql server. I was wondering there was a table in the jira database that tracks how many time each jira has been reopened and the date a jira was reopened. I can find the a change of status to reopen in CHANGEITEM, but can't find an associated date to each reopen time. Any help or tips would be great. Thanks!


3 answers

1 accepted

0 votes
Answer accepted

I can't speak for JEP, but if you need to do this with SQL, then you should join Changeitem to Changegroup.

Each changeitem line records "field X changed from Y to Z" (and you've found the status changes). If you look at changeitem, it's also got a "group id" field. Use that to read the id in changegroup and you'll find changegroup records the person who made the change, the issue ID and the date/time they did it.

0 votes
Tuncay Senturk Community Leader Mar 05, 2014
Hi Aaron Since JEP reopening counter is a calculated custom field and does not store any value on database, you can not get the results via Sql. If you need to get them via Sql, you should do what Nic says. Join changeitem and changegroup tables and get the attributes properly. However it will be difficult to get all fields like resolving/reopening users and dates in a single sql.

Thank you both. I will join those tables to find the dates. Have a great weekend.


Suggest an answer

Log in or Sign up to answer
This widget could not be displayed.
This widget could not be displayed.
Community showcase
Published Apr 03, 2019 in Marketplace Apps

How to estimate projects more accurately?

Gaining insight into your team’s effectiveness and choosing the right estimation technique.   Estimating projects is hard. It is because people have the tendency to underestimate the time thin...

315 views 2 10
Read article

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