Filter issues based on transition state execution date in JIRA

[Sonal Onkar]

Hello - I have installed the Jira Suite Utilities plugin. So now I get a nifty tab called "Transitions" in addition to the standard tabs in an issue. This tab records all workflow transition executions, users, duration in a particular step of the workflow and the date and time when the transition was executed.

I have a need where I need to filter on issues that were put in one of the intermediate (not open, close, resolved) states on a certain date or a date range.

I reckon that all the information is being captured in the system anyway. How would I use these additional fields in the "Search for Issues" panel to filter out on a select set?

[Matheus Fernandes · Accepted solution]

Hi Sonalo,

What you could is create a "Date Time" custom field, and on the "Operations" column, click "configure" and set the default value to "use the current date as the default", and then add it to the screen of the operation that you want to keep track. Doing this, every time you do the transition the "DateTime" will be updated, so you can search it.

Let me know if you need more details.

p.s.: You will need a screen in the transition in order to make it to work.

[Sonal Onkar]

Thank you Matheus! So if I want to track transition from step 3-4 and from Step 6-7, then do I need to create 2 date time fields and put them in the transition screens for those respective transitions?

[Matheus Fernandes]

Exactly! :)

[Sonal Onkar]

Thank you! I will try this out and see if I have more questions.

[Matheus Fernandes]

Please, contact me if you do.

[Sonal Onkar]

Thank you. This solution worked for our current needs. Your help is much appreciated.

[Sonal Onkar]

Mattheus - I have a bug with the above implemented solution. The date fields do not pick up the date the transition was executed. Instead it takes on the date that the issue was opened on. How can I fix this? I added the field to the transition screen of the step that I want the date recorded for and set the default value to "use current date" like you advised. Please help!

[Matheus Fernandes]

Hum, I m sorry to hear that, it wasn't supposed to be like this. But it seems that the DatePicker has some bugs :/

What you could do is instead of using the current date as default, is manually set the date every time you pass that screen. I hope it helps.

[Matheus Fernandes]

Hum, I m sorry to hear that, it wasn't supposed to be like this. But it seems that the DatePicker has some bugs :/
What you could do is instead of using the current date as default, is manually set the date every time you pass that screen. I hope it helps.

[Jyotiprakash Nayak]

This doesnot seem to work the way suggested , kindly suggest a method to track the transition details of states in a workflow with proper date & time details 

[Matheus Fernandes]

Hey,

I know this is probably dead by now and you have possibly already figured how to retrieve this information, but I believe you could use the WAS or CHANGED operator in JIRA, e.g.:

Project = TEST AND Status CHANGED TO "Intermediate" DURING ("2012/08/01","2012/10/05")

Cheers,
Matheus Fernandes

[Brian Rafferty]

Here's an interesting anomaly which I hope someone can shed some light on.

I have two filters. One shows all issues which have been in "Status A" for more than 14 days. The other shows all issues which have been in "Status A" 14 days or less.

One issue was in "Status A" more than 14 days ago; was resolved, then reopened, and a week or so later made it back to "Status A" again.

So now, this issue shows up in both filters. What I need is for the issue to appear only in the latest applicable filter (in this case, the 14 days or less filter).

The relevant filters are:

project=XXX and status changed to "Status A" before startofday(-14) and status = "Status A"

and

project=XXX and status changed to "Status A" after startofday(-14) and status = "Status A"

Any insights?

Thanks,

Brian

[Kathryn Allison]

Ran into similar issue. The problem is the query is pulling if the status was changed to "Status A" more than 14 days ago. If you go to the status twice, because of reopening or otherwise, the query isn't considering only the latest time it changed to "Status A". It's consider if it has ever in the lifetime of the ticket changed th "Status A" 14 or more days ago.

You need to adjust the first filter to add the word 'not' as a parameter. This worked for me:

project=XXX and not status changed to "Status A" after startofday(-14) and status = "Status A"

[Brian Rafferty]

Thank you, Kathryn--that's the solution I needed! Guess I need to engage more in "negative" thinking... ;-)

[Sonal Onkar]

Thank you, but how do I use with the existing plugin? I have the fields that capture this information, but they are not custom fields so they don't appear in the search options. Any insight on that?

[Renjith Pillai]

Refer

https://answers.atlassian.com/questions/4073/how-do-i-get-issues-that-have-had-a-certain-status-within-a-certain-period?page=1#4104

[Sonal Onkar]

Thank you, but how do I use with the existing plugin? I have the fields that capture this information, but they are not custom fields so they don't appear in the search options. Any insight on that?

[Renjith Pillai]

Not sure, but a direct SQL query into the Jira database will help you mostly.

And if you suceed in getting an SQL query, you could administer a jndi entry to this SQL in confluence and easily publish the results.