Trying to get Time in status

Hi all,

Does anyone (@Bob Swift [Bob Swift Atlassian Add-ons]?) know of anyway to use the JIRA cli to get the Time in Status field for issues in a given project?

If not does any know the SQL needed to get a list of output that has:

 Issue key (JIRA-123 or what have you) along with Time in status.  I can get ID and Time in status and ID and Issue Key I just need to know how to join them.  

 This sort of works but I believe gives me all issues and all custom fields, I only need then in one project and time in status custom field value.

select customfieldvalue.textvalue, jiraissue.project, jiraissue.issuestatus, jiraissue.SUMMARY, jiraissue.issuenum from customfieldvalue inner join jiraissue  on customfieldvalue.issue=jiraissue.issuenum 

I I believe I need to somehow add

where CUSTOMFIELD in (select id from customfield where cfname = 'Time in Status') 

and

where jiraissue.project='10001'

(Please excuse my poor SQL skills, it has been 2 decades since I used them)

OR anyway to get a text file with the Time in status field that I could parse and create nice custom graphs such as eash status with a bar showing both how long it has been open and how long it has been in the current status.

I know Enhancer plugin for JIRA get real close, but not good enough for my customer

Thanks in advanced, 

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

Turns out that the time in status only updates after close which does not work.

CLI doesn't do anything special with a time in status field. It should be available just like any other custom field with getIssueList, getIssue, or getFieldValue. 

Another alternative is to do some scripting together with the getIssueHistoryList action. It gives you rows that document the time a transition took place and source and target status. The rows you would need are the rows where the field is status.

Thanks. I think I do not know enough about tine in status as I run: jira.sh --action getFieldValue --issue "DGAGENT-103" --field "Time in Status" Issue DGAGENT-103 has field 'Time in Status' with value: And nothing for a value.

I will play with getIssueHistoryList and see if I can get the data somehow.

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
Fadoua M. Boualem
Published Monday in Trello

Using Trello to manage events

As a Jira power user, I was at first doubtful that Trello could benefit my workflow. Jira already uses boards (ones you can customize!), so why would I even need to use Trello?! In this post you will...

513 views 8 9
Read article

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