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

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

2 votes
Accepted answer

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.

+1ed your solution smile

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

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

0 votes

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 Community Champion Jan 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
Community showcase
Published 10 hours ago in Confluence Cloud

Happy holidays from our team to yours!

Hi Community!  2018 was filled with changes for our team, both big and small, and we've taken a lot of time to both celebrate our wins and recognize areas of improvement. One thing that we're a...

87 views 0 12
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you