How get only the last occurance with JQL status changed from to?

Maik Winter January 26, 2016

I tried to write a JQL to get only the latest occurance of a status change.

Example workflow sequence 1:

Issue Create -> Open -> Classified -> Support Investigating -> Waiting for Customer -> Support Investigating -> Provider Investigating -> Waiting for Customer -> Resolved -> Closed

Example workflow sequence 2:

Issue Create -> Open -> Classified -> Support Investigating -> Provider Investigating -> Waiting for Customer -> Provider Investigating -> Support Investigating -> Waiting for Customer -> Resolved -> Closed

 

The JQL's are should only find issues in the status "Waiting for Customer" with only the latest occurance from "Support Investigating" to "Waiting for Customer" / from "Provider Investigating" to "Waiting for Customer".

 

My actual two JQL's are find the same issue, because the filter does not consider only the last occurance :

project = itsd and status = "Waiting for Customer" and status changed FROM "Support Investigating" TO "Waiting for Customer"

project = itsd and status = "Waiting for Customer" and status changed FROM "Provider Investigating" TO "Waiting for Customer"

Any ideas?

Regards,
Maik

3 answers

1 accepted

3 votes
Answer accepted
Nicolas Bourdages
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
January 26, 2016

I would use workflow post-functions to store a value for the previous status in a Latest Status custom field. In your example, when using the transition from "Provider Investigating" to "Waiting for Customer", the post function would set that custom field to "Provider Investigating".

I think sometimes it's not about bending JQL to your will, but marking issues in a way that can be picked up by JQL.

Ignacio Pulgar
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
January 26, 2016

+1ed your solution smile

Nicolas Bourdages
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
January 26, 2016

Cheers smile

Maik Winter January 29, 2016

Thanks Nicolas, this was the simplest way to cover the requirement wink

Nico n May 30, 2018

thanks for the idea, unfortunatey, i don't find a free way to update a custom field in the postfunction. There is jira suite utilities but it is not free.

Do you know any alternatives? 

thanks

Nico

1 vote
Ignacio Pulgar
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
January 26, 2016

JQLs work like a WHERE clause added on a SQL starting by "SELECT * FROM issues_table WHERE ... <JQL>". So, a JQL will always retrieve just a list of issues that matches the specified conditions.

Therefore, not being able to query the table of historical status changes directly, you will not be able to obtain its last change through built-in JQL resources. (However, look at Nicholas Bourdages answer for a good solution)

Provided the issue has historically changed to "Waiting for Customer" from both statuses, you can just add some extra conditions, with BY <username>, or with BEFORE <date> and AFTER <date>.

For example, this JQL returns all ITSD issues on "Waiting for Customer" status which status you had ever changed from "Support Investigating" to "Waiting for Customer" during the last week:
 

project = ITSD and status = "Waiting for Customer" and status changed FROM "Support Investigating" TO "Waiting for Customer" BY currentUser() AFTER startOfDay(-7)

 

Of course, this approach assumes that you know some relevant things beforehand, like who is the user whose changes you are interested in, or the date range on which the change you are looking for occurred, so this is not a solution, but a workaround.

Hope it helps.

0 votes
Vasiliy Zverev
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
January 26, 2016

There is more hardcore solution: use scripted field via ScriptRunner to analise chnage history. 

Here is some code to get last status:

import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.issue.changehistory.ChangeHistory
import com.atlassian.jira.issue.history.ChangeItemBean


PreviousStatus previousStatus = new PreviousStatus(0, issue.getStatusObject().getName())

for(ChangeHistory changeHistory: ComponentAccessor.getChangeHistoryManager().getChangeHistories(issue)) {
    for(ChangeItemBean changeItemBean: changeHistory.getChangeItemBeans()) {
        if (changeItemBean.getField().equals("status")) {
            previousStatus.setNewStatus(changeItemBean.getCreated().getTime(), changeItemBean.getFromString()) ;
        }
    }
}

return previousStatus.getStatus();

class PreviousStatus{
    private long dateInMills;
    private String value;

    public PreviousStatus(long _dateInMills, String _value){
        dateInMills = _dateInMills;
        value = _value;
    }

    public setNewStatus(long _dateInMills, String _value){
        if(_dateInMills &gt; dateInMills){
            dateInMills = _dateInMills;
            value = _value;
        }
    }

    public String getStatus(){return value}
}

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events