Trying to get Time in status

[Robert G. Nadon]

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

[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

[Robert G. Nadon]

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

[Bob Swift]

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]

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]

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