JIRA time in status notifications

Hi all,

I have a user who wants to find a mechanism where information can be captured on an issue if it has been in a status too long.  A notification, chart or filter would work.  

I have looked at the time tacking tools https://confluence.atlassian.com/jira/tracking-the-time-taken-for-each-workflow-transition-126910724.html  But could not find anything that says, if, for example, an issues has been in a state called "investigation" for more than 1 week, then it something happens.  I have the field "Time in status", but I could not use that in my JQL.  

The only thing I can think of doing it to parse the database:

select Issue, textvalue from customfieldvalue where CUSTOMFIELD in (select id from customfield where cfname = 'Time in Status') limit 1000;

dump that to a file and parse the file for the values I wanted.   That is doable, but I was hoping to stay within JIRA and not go out to the database directly.

Thanks in advance,



2 answers

1 accepted

0 votes
Accepted answer

FYI With using some hard-coding for ID's and Values this command gave me what I wanted (I believe)

select customfieldvalue.textvalue, jiraissue.project, jiraissue.issuestatus, jiraissue.SUMMARY, jiraissue.issuenum from customfieldvalue inner join jiraissue on customfieldvalue.issue=jiraissue.id where customfieldvalue.CUSTOMFIELD = '12600' and jiraissue.project = '10001' limit 10;


How about using a JIRA Subscriptions which sends email regularly (Project = ABC Status = In Progress and Updated > 2015-10-1)

Reference: https://confluence.atlassian.com/jira/advanced-searching-179442050.html#AdvancedSearching-Updated

Yes I am aware of that, but I am not sure how to get time in status > 5d. That will notify based upon updated date not time in status

That's why you need to put "Status = "In Progress and Updated" so that it will return all issues in that status with no updates within a specific period of time, that's the closest thing. You can also check the "WAS" operation.

Yeah that gives time in status with no updates. I am sure there is a way to just get time in status. I have a time in status field that give the data Issue#_*:*_statusID_*:*timeInStatusInSeconds_*| for each status that an issue has gone into, and I was wanting to see what was out there for using that field. Thanks though. Here is a sample database view of time in status: 10315_*:*_1_*:*_15800000_*|*_10416_*:*_1_*:*_5000_*|*_3_*:*_1_*:*_216000_*|*_6_*:*_1_*:*_0

Suggest an answer

Log in or Sign up to answer
Community showcase
Posted Oct 09, 2018 in Jira Core

How to manage many similar workflows?

I have multiple projects that use variations of the same base workflow. The variations depend on the requirements of the project or issue type. The variations mostly come in the form of new statuses ...

247 views 6 0
Join discussion

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