Fields that change over time - getting historic info

[Graham.Wilson]

Hi all. Is there a way to get historic data about a field? Specifically, when we put something into "Waiting for Customer" we have a dropdown field with numerous reasons we can enter so we know why a request is sitting with the customer (waiting for clarification, change not ready, etc etc).

If the customer responds and puts it back into "Waiting for Support", and we send it back again (for whatever reason), the field gets overwritten with the new reasons. That means we can only see the latest return reason, so we're not getting a true reflection of where our bottlenecks are.

SO - how can we get this historic information (without going down the "raise a brand new request every single time" route)?

[Antoine Berry]

Hi,

Depending on what you want to achieve, you should be able to get that information in the issue's history. If you need the last value for automation, you could store it in a custom field.

[Graham.Wilson]

Thanks for responding - any idea how that could be achieved?

[Antoine Berry]

This is doable with an add-on such as script runner. May you please explain simply what your need is ? So maybe I can help further and suggest an appropriate answer.

[Graham.Wilson]

The situation - we're moving a single request through dev, test, "go live" and "post live" stages. These stages would be in a "current status" field but obviously they'd change as the request moves through the stages. We want to be able to see a) how long a request spends in each status and b) how many returns there were in each stage (i.e. how many times the status changes from "waiting for support" to "waiting for customer").

We also want to know why we're returning things to customers (this info is in a drop down selection when we change the status, but this info is currently lost when it changes - only the most recent is changed).

Hope that clarifies things.

[Antoine Berry]

Hi, could you please attach a screenshot of your workflow ? 

[Graham.Wilson]

The workflow is the standard "Service Request Fulfilment Without Approvals."

Basically once created, the request will usually be in "Waiting for customer" status when they're developing or testing, or "Waiting for support" when they've asked us to promote a change into a test or live environment. The custom field "current stage" will say either DEVELOPMENT, TEST, PRELIVE or LIVE.

[Antoine Berry]

I see clearer now. I do not have a service desk instance right now but if I remember correctly you cannot achieve time spent in each stage with a SLA (since it is a custom field).

You could have custom fields "time spent in dev", "time spent in test" etc. as well as "Waiting for customer counter" and "Waiting for support counter" to keep track of how many times you have been in these statuses. 

Using script runner you could setup post functions and script listeners to update these fields.

Also you could consider a workflow that includes Dev, test, prelive and live as statuses. 

[Graham.Wilson]

Ok - are there any examples of this I can use or look at?

I'd like to avoid changing the workflow as the ability to extract information based on historic data is much more useful to me.

[Antoine Berry]

This is tricky. I will come back with a detailed answer. Are you using scriptunner ?

[Graham.Wilson]

Hi.  Yes, using Scriptrunner.   Thanks for all your assistance so far.

[Antoine Berry]

Ok so this is the first draft I came up with. Add this as a script listener. This is assuming a few points : 

• your field "time spent in dev" is a number field.
• your field "time spent in dev" will be stored as total minutes spent in dev.
• the select list "current stage" is None when created. If it is already valued, the script needs to be improved.
• When a user updates the field "Current Stage", it triggers the listener. So I guess you would use "Issue updated" but you need to check if users update it in transitions as well.

Keep in mind I did not test this and you need to adapt it. 

Update the custom field IDs and the field name ("Current Stage") must match exactly. Complete the switch with the right cases (must match exact values in the select list).

I will come with the next script later, but this was the hardest one.

import com.atlassian.jira.issue.history.ChangeItemBean
import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.issue.ModifiedValue
import com.atlassian.jira.issue.util.DefaultIssueChangeHolder
import java.sql.Timestamp
import groovy.time.TimeCategory 
import groovy.time.TimeDuration

def currentStageChanged = event?.getChangeLog()?.getRelated("ChildChangeItem")?.find {it.field == "Current Stage"}

if (currentStageChanged){ 
def changeHistoryManager = com.atlassian.jira.component.ComponentAccessor.getChangeHistoryManager()
def ch = changeHistoryManager.getChangeHistories(issue)

//get the date when Current stage was set to the previous value (startDate) and when it was modified again (which should be about when this listener is triggered - endDate) 
Timestamp startDate
Timestamp endDate
String previousValue
for(int i=ch.size() - 1; (i >= 0) && (startDate == null); i--){
for (ChangeItemBean bean:ch.get(i).getChangeItemBeans()){
if (bean.getField() == "Current stage"){
if (endDate == null){
endDate = bean.getChanged()
}
else {
startDate = bean.getChanged()
previousValue = bean.toString()
}
}
}
}

switch (previousValue){
case "DEV" : 
//time spent in dev custom field ID 
int cfDevTimeSpentId = 10101
def cfDevTimeSpent = customFieldManager.getCustomFieldObject(cfDevTimeSpentId)
def cfDevTimeSpentValue = issue.getCustomFieldValue(cfDevTimeSpent)
cfDevTimeSpentValueNumber = (cfDevTimeSpentValue == null) ? 0 : cfDevTimeSpentValue

//Get the date difference in minutes
int diff = endDate.getTime() - startDate.getTime()
int minuteInMillis = 60000
Double elapsedMinutes = Double.parseDouble(diff / minuteInMillis);

cfDevTimeSpent.updateValue(null, issue, new ModifiedValue(cfDevTimeSpentValue, cfDevTimeSpentValueNumber + elapsedMinutes), new DefaultIssueChangeHolder())
break
default:
break
}
}

Update me with what goes wrong.

Antoine

[Antoine Berry]

Hi, 

To update the counters (how many times the issue has been in "Waiting for customer" status), it is pretty straight forward. Add a scripted postfunction in each transition that goes to "Waiting for customer" with this code : 

import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.issue.ModifiedValue
import com.atlassian.jira.issue.util.DefaultIssueChangeHolder

def customFieldManager = ComponentAccessor.getCustomFieldManager()

//Jira User picker ID 
int waitingForCustomerCounterId = 10010
def cfWaitingForCustomerCounter = customFieldManager.getCustomFieldObject(waitingForCustomerCounterId)
def cfWaitingForCustomerCounterValue = issue.getCustomFieldValue(cfWaitingForCustomerCounter)

cfWaitingForCustomerCounterValue = (cfWaitingForCustomerCounterValue == null) ? 0 : cfWaitingForCustomerCounterValue

cfWaitingForCustomerCounter.updateValue(null, issue, new ModifiedValue(cfWaitingForCustomerCounterValue, cfWaitingForCustomerCounterValue + 1), new DefaultIssueChangeHolder())

Antoine

[Graham.Wilson]

Hi. Thanks for this.  I was out of the office yesterday so didn't get an opportunity to respond - I'll take a look at this today!