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!
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.