Tracking the original status change of a ticket

Question

Good morning,

I am trying to create a Dashboard which will show me the amount of tickets that have been set to Done each week for 2020. I have the following query, which I know works:

* project in (xx, xx) AND status changed to Done AND updatedDate >= "2020-01-01" AND updatedDate <= "2020-01-31"* With the date changing for each week in 2020

However, what I am finding is that because of bulk status changes completed by our system admin, some tickets that were actually Done in January 2020, are now only appearing as Done when they were stamped again in April 2020.

For example:

* Originally Done: 16/01/20
* Stamped as Done by system admin: 29/04/20

Is there anyway that I can use SQL to pick up the original Done date?

Mariana_Pryshliak_Saasjet_qysy8m · Answer

Hello @Ben Epps

I've understood that you're looking for a JQL solution, but as an alternative, you can try the Issue History add-on.

It displays the history of all the changes that have been made in each issue. Get all the necessary data: who have made changes,  when they have been made, and to which issues they have been made.

To filter this data only by statuses, you just need to configure settings in the column manager.

So you have the ability to monitor all the changes just by choosing the period.

If you have any additional questions, I'm here to help you ;)

Nic Brough -Adaptavist- · Answer

No, because you can't use SQL on Cloud.

I'm afraid I cannot see a way to do this directly.  The changes of status are, of course, stamped into the issue history, so you have not lost them, but I can't work out a way to get at that data with a JQL query.

On server, I'd add Scriptrunner or one of the other scripting/automation tools, and either write a JQL function to allow search by original change, or a scripted field that is able to extract the date from the history, but you can't do functions on Cloud and the way scripted fields work in Cloud means you'd need some way to trigger a rebuild of the data for it.

I think you might need an external reporting tool to get to this data.