Trying to get Time in status

Robert G. Nadon
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 24, 2015

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

0 votes
Answer accepted
Robert G. Nadon
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 25, 2015

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

Robert G. Nadon
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.
December 8, 2015

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

1 vote
Bob Swift OSS (Bob Swift Atlassian Apps)
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 24, 2015

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.

Robert G. Nadon
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 24, 2015

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.

Robert G. Nadon
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 24, 2015

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

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events