JQL - Time in particular state

[morrist1]

Hi. I'm looking at this article which is supremely helpful overall...
https://community.atlassian.com/t5/Jira-articles/Becoming-a-JIRA-Search-Ninja-History-searches-in-JIRA/ba-p/461943

What I'm wondering is, is there some JQL method for determining how long an item was in a particular Status state? For example, if I've got some JQL like this...

project = "SomeProj" AND resolution = Unresolved AND createdDate  < "0d" AND createdDate  >= "-30d" AND (TBD ) ORDER BY createdDate ASC   

I'm attempting to search "SomeProj" and find all unresolved issues that are <= 30 days old AND determine how long each issue was blocked / not blocked over that 30 day period.

So, for clarification, I want to figure out (for all open issues within the last n days) what percent of the time we were stuck waiting on someone else (i.e. blocked).

I attempted doing a "where status changed to/from Blocked" but that will only return a single instance of each issue / record (i.e. any record that changed states to or from blocked over that time period). What I'm really looking for is a table where the same record can appear multiple times (i.e. one entry when it was not blocked w/ timestamp, the next entry when it moved to blocked with timestamp, the next entry with it moving out of blocked with timestamp, etc.).

The only way I can think of to do this is to literally have a query for each day over the last n days where you check if (at any point during that day) the item was blocked.

Any ideas on a better way to do this?

Many thanks!

[Alexey Matveev · Accepted solution]

Hello,

You can not do it with out of the box JQL. 

You would need a plugin. Have a look at this plugin:

https://marketplace.atlassian.com/apps/1211499/issue-history-collector?hosting=cloud&tab=overview

[morrist1]

I'll check it out. Thanks for the quick reply!

[morrist1]

Looks like exactly what we need. Thanks.

[Michael Boumansour]

I have a tool I developed to do this among other things if you are interested.  Here a blog post describing it.

[Lime Trees]

Hi,

you can also have a look at our free add-on Status History which shows changes on statuses on particular issues. More details can be found here.

If you need reports or analysis for whole project, please also check Status History PRO, its Reports and Viewer.

Regards,

Lime Trees Support Team