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

Andreas Gounaris
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.
November 12, 2012

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.

8 answers

1 accepted

1 vote
Answer accepted
Andreas Gounaris
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.
November 12, 2012

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
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.
November 12, 2012

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

1 vote
JamieA
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.
November 12, 2012

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
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.
November 12, 2012

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
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.
November 12, 2012

sounds good.

0 votes
Norman Abramovitz
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.
November 12, 2012

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.

0 votes
Andreas Gounaris
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.
November 12, 2012

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!

0 votes
Norman Abramovitz
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.
November 12, 2012

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

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

0 votes
Norman Abramovitz
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.
November 12, 2012

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.

0 votes
Andreas Gounaris
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.
November 12, 2012

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!

0 votes
Norman Abramovitz
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.
November 12, 2012

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?

Suggest an answer

Log in or Sign up to answer