JIRA time in status notifications

[Robert G. Nadon]

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,

Robert

[Robert G. Nadon · Accepted solution]

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;

Robert

[GabrielleJ]

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

[Robert G. Nadon]

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

[GabrielleJ]

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.

[Robert G. Nadon]

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