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,

Robert

 

2 answers

1 accepted

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

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
Atlassian Community Anniversary

Happy Anniversary, Atlassian Community!

This community is celebrating its one-year anniversary and Atlassian co-founder Mike Cannon-Brookes has all the feels.

Read more
Community showcase
Julia Dillon
Posted Tuesday in Jira

Tell us how your team runs on Jira!

Hey Atlassian Community! Today we are launching a bunch of customer stories about the amazing work teams, like Dropbox and Twilio, are doing with Jira. You can check out the stories here. The thi...

147 views 1 17
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