JQL query (or other way) to create filters with issue relative dates

[Andreas Gounaris]

Hi,

I have a request to create reports based on the time an issue remains in a specific state.

The request is the following:

* List of issues that took longer than 30 minutes to assign (both by

week and by day) (new issues remain unassigned by default)

* Percentage of issues that took longer that 30 minutes to assign (both

by week and by day)

Since operators 'was' and 'changed' operate only against string values or date offsets from the current date, I can't use them.

What I'm thinking is to create a custom scripted field that will return the difference of createdDate-dateAssigned and use in in JQL to check the idle time (30mins) against it.

This question pops up now, how can I get the issue history from the API? Could you direct me please?

Thanks in advance.

[Andreas Gounaris · Accepted solution]

Ok, managed to get what I wanted.

Here it is, the groovy scripted field:

/*
 * Calculate the time in minutes an issue remained unassigned.
 * Since scripted fields are updated only when the issue is updated, the initial value
 * of the custom field will be -1.
*/
import com.atlassian.jira.ComponentManager

def componentManager = ComponentManager.getInstance()
def changeHistoryManager = componentManager.getChangeHistoryManager()
def eventTime = -1

def assignee = issue.assignee

if (assignee) {
    //eventTime= changeHistoryManager.getChangeItemsForField(issue, "assignee").find {it.toString}?.getCreated().getTime()
    changeHistoryManager.getChangeItemsForField(issue, "assignee").find {
        eventTime = it.getCreated().getTime()
    }
    return ((eventTime - issue.getCreated().getTime())/60000).toBigInteger().toDouble()
} else {
    return ((new Date().getTime() - issue.getCreated().getTime())/60000).toBigInteger().toDouble()
}

Thank you @Jamie

[JamieA]

Cool. FWIW I agree with your points about external dependencies and hitting the db directly...

[JamieA]

It's easy enough with a scripted field. Check the creation date, easy.

If the assignee is set on create then the time difference is zero.

If the assignee is set, then get the assigned datetime:

def componentManager = ComponentManager.getInstance()
def changeHistoryManager = componentManager.getChangeHistoryManager()
changeHistoryManager.getChangeItemsForField(issue, "assignee").find {it.toString}?.getCreated()

If the assignee is not set then the time difference is... either null, or now - creation time. Which leads you to a problem, in that calculated fields only get updated when the issue changes.

So you might be looking at a custom report, a new jql function, or modifying the "time to first response" report/gadget.

In short, not that easy after all.

[Andreas Gounaris]

I was thinking of you actually, trying to create this field. Thanks for the tips - great help - I'll dig into it and try to set the field based on its creation conditions.

Couldn't I have a condition in the script to store, lets say a -1 value to issues with no assignee? I could then exclude those created 30 mins before from the JQL filter.

[JamieA]

sounds good.

[Norman Abramovitz]

I am to until I figured out the queries management wanted was easier to do by setting up a website and have their reports generated hourly and daily to html and pdf formats all through database tools. Less than a days work. Now, I am only asked about adding new reports (rarely now) and I saved a few licenses since some managers did not need Jira accounts. I also did not need to "customize" Jira just for reporting reasons.

[Andreas Gounaris]

Thanks Norman, your tips are welcome.

I'm using MySQL but I'm not a big fan of solving an issue by creating external dependencies. If it can't be solved on the spot, then it won't be solved at all!

[Norman Abramovitz]

Here is an url about current Jira reporting capabilities and integrations that might assist you.

https://confluence.atlassian.com/display/ATLAS/Reporting+in+JIRA

[Norman Abramovitz]

Actually, your points raised are generally easier to do and maintain with database tools than with Jira customizations. Also you already paid for your database tools if you are using a non open source database. There are also open source reporting tools as well.

[Andreas Gounaris]

Unfortunatelly not. Although I do have access to the DB and the query is more or less easy to create, this will be my last resort as it comes along with other kinds of effort (schedule auto execution, format the results send an email, maintenance blabla...) - OVERKILL!

[Norman Abramovitz]

If you have access to the database through SQL, it would be easier to get the results you want. Would that type of solution work for you?