Getting time in status using API

[#1]

Ever since I came out with jiraone, I have always asked myself what extra functions can I make that will help those users who are trying to get meaningful data out of their Jira instances. Today, I will be discussing how you can extract the time in status within your Jira project.

This article focuses on using python v3.6.x and above and a package called jiraone which can be downloaded using pip install jiraone if you’re in a virtual environment or you can make the call from python3 -m pip install jiraone

To start with, time in status is something that every user would want a report on. If you’re an IT team, you will want to have a report that lets you know how long a ticket has been on a status. You can be able to conveniently get this information from any Atlassian marketplace addon available to display this information via the UI. However, if you’re keen on using API and generating a report that you can use to work with your little team or push to another application then this article is right for you.

We can begin by writing a small script in python that will make the request for us and we can generate the report either in CSV or JSON file format all in UTF-8 character encoding.

For this use case, we’re using a configuration file in JSON format to house our login credentials. eg. config.json

{
"user": "prince.nyeche@example.com",
"password": "apitoken",
"url": "https://your-instance.atlassian.net"
}

The above file should be saved as a config.json extension in the same directory as our python file. Which can be given any name. eg. time_status.py

from jiraone import LOGIN, PROJECT, file_reader
from jiraone.module import time_in_status
import json

config = json.load(open('config.json'))
LOGIN(**config)
key = ["COM-12", "COM-14"]

if __name__ == "__main__":     
    time_in_status(PROJECT, key, file_reader, pprint=True, is_printable=False,     
    output_format="json", report_folder="STATUSPAGE", report_file="time.csv",     
    status="In progress", login=LOGIN, output_filename="result")

The function we’re using to output the time in status is literally called “time_in_status”. This function has the ability to generate the time an issue has stayed in a particular status or it can generate all the time it stays in each and every status that exists within a Jira issue. I’ll explain what each argument within the function does, so you can get a clear picture of how to use it. The standard way to call this function is the way it is shown above. First, the PROJECT alias is used as a required positional argument and within the function calls the change_log() method. The second argument requires an issue key. Now you can be able to pass the issue key in various formats such as below

key = "COM-12" # as a string
key = "COM-12,COM-14" # a string separated by comma
key = 10034 # an integer denoting the issueid
key = ["COM-12", "COM-114", "TPS-14", 10024] # a list of issue keys or issue ids
key = {"jql": "project = COM ORDER BY created DESC"} # a dict using JQL

The third argument is file_reader function which you will need to pass or you can pass as a keyword argument as reader=file_reader. The remaining arguments can be passed as keyword arguments, pprint enables you to print out the time in status in Jira’s pretty format e.g. 13d 11h 22m 15s if it is set to True otherwise if it is not set at all, you will get the DateTime output as 13 days, 11:22:15.913 which is a time delta string of the DateTime string collected from the issue history. The output_format argument enables you to generate a report file either in CSV or JSON format. The words have to be strings and are case insensitive. E.g cSV or JsoN will output the correct file. The output_file argument basically just allows you to name the file, avoid using any extension as this will be automatically added based on the output_format. The status argument allows you to only output statuses that have that status name. For example, you want a report of only “In progress” status, then you should write the name "In progress" as the value to the status argument. If left blank, the result will be all the statuses within the issues being searched. Therefore, if you want the time in status for all the statuses that exist within the Jira issues, do not set the status argument. The login argument is essential to the function as it is required for authenticating your API to the Jira issues. The report_file basically helps within the history generation, you do not have to set this as it is optional. The same goes for report_folder you do not have to set this as it is optional.

Once you run the script, you will end up with a report that looks like the one below as the output

[
 {        
  "author": "Prince Nyeche",        
  "issueKey": "COM-12",        
  "status": "To Do",        
  "summary": "Workflow test 3",        
  "timeStatus": "0h 00m 19s"    
 },    
 {        
  "author": "Prince Nyeche",        
  "issueKey": "COM-14",        
  "status": "In Progress",        
  "summary": "Workflow test 3",        
  "timeStatus": "8d 6h 32m 52s"    
 }
]

That’s how easy you can to generate a report of the time in status of a Jira issue using API.

[BPAA]

1. Is there a way to retrieve time in status along with the rest of the details of an issue? i.e through /rest/api/latest/issue/issue_id?

2. Also, if we need to retrieve TIS directly from the api (eg: say we don't wish to use the aforementioned Python library), the following 

   1. /rest/tis/report/1.0/api/issue?issueKey=KEY-3209&&columnsBy=statusDuration&calendar=normalHours 

   2. returns the following. What's the formula for calculating the Time in status as it appears to show the time in milliseconds (probably a standard unix time from epoch) 

   3. dfdf
      {
      dateTimeFormat: "dd/MMM/yy h:mm a",
      dateFormat: "dd/MMM/yy",
      timeZone: "America/New_York",
      locale: "en-US",
      viewFormat: "minutes",
      isComposite: false,
      columnsBy: "Status Duration",
      query: "key in (ubsuatconv-3209)",
      reportDate: "08/Mar/22 11:23 AM",
      version: "4.15.0.697",
      isAggregationType: false,
      isOverall: false,
      isAverage: false,
      isSum: false,
      table: {
      header: {
      headerColumns: [
      {
      id: "issuekey",
      value: "Key"
      },
      {
      id: "summary",
      value: "Summary"
      }
      ],
      groupByColumns: [ ],
      fieldColumns: [ ],
      valueColumns: [
      {
      id: "10006",
      value: "To Do",
      isConsolidated: false
      },
      {
      id: "10027",
      value: "In Development",
      isConsolidated: false
      },
      {
      id: "10208",
      value: "In Analysis",
      isConsolidated: false
      }
      ]
      },
      body: {
      rows: [
      {
      headerColumns: [
      {
      id: "issuekey",
      value: "UBSUATCONV-3209"
      },
      {
      id: "summary",
      value: "Position Recon (Account) - decimal place mismatch for 529 accounts"
      }
      ],
      groupByColumns: [ ],
      fieldColumns: [ ],
      valueColumns: [
      {
      id: "10006",
      value: "1318.7902666667",
      raw: "79127416"
      },
      {
      id: "10027",
      value: "80612.15345",
      raw: "4836729207"
      },
      {
      id: "10208",
      value: "55970.5323833333",
      raw: "3358231943"
      }
      ],
      currentState: [
      {
      id: "10027",
      value: "80612.15345",
      raw: "4836729207"
      }
      ]
      }
      ]
      }
      },
      calendar: {
      id: null,
      name: "normalHours",
      monday: true,
      tuesday: true,
      wednesday: true,
      thursday: true,
      friday: true,
      saturday: true,
      sunday: true,
      timeZone: "America/New_York",
      holidaysWithoutDesc: [ ],
      endTime: "00:00",
      startTime: "00:00",
      holidays: { }
      }
      }

sdfsdf

[Prince Nyeche]

You can always use the get issue endpoint to retrieve an issue detail. Knowing the time_in_status is simply doing a difference between the time that an issue is in a particular status versus when it is not in that particular status. That's how you can determine how long it stayed in a status. I'm not sure I understand exactly what you're trying to achieve with that report endpoint but probably you can look into Atlassian's provided API to get the data you require. The example given here is following a particular scripting language which is python. As it provides an already curated means to extract this time_in_status from Jira.

[jean-fabrice_detard]

Hello - I am struggling with the 'file_reader' argument. What do you expect here? I get this error: 

for singleIssue in jira.search_issues(jql_str='project = RPM AND key = RPM-5257'):
print(time_in_status('RPM',singleIssue.key,file_reader=rreader,pprint=True,login=jira))
Traceback (most recent call last):

File "<ipython-input-109-445fe396a371>", line 4, in <module>
print(time_in_status('RPM',singleIssue.key,file_reader=rreader,pprint=True,login=jira))

File "C:\ProgramData\Anaconda3\lib\site-packages\jiraone\module.py", line 158, in time_in_status
raise JiraOneErrors("value", "You need to pass the `file_reader` function, so the data can be read.")

[Prince Nyeche]

Hi, if you want to combine this time_in_status function with the pyjira module. You will need to instantiate the LOGIN alias of jira as well before calling that function. Also, you will need to import "PROJECT" alias and "file_reader" function into your script file.

from jiraone import LOGIN, PROJECT, file_reader
from jira import JIRA

The "PROJECT" keyword is an alias to an in-built class provided by jiraone and "file_reader" literally is a reader function that helps to read text files. So if you're authenticating with pyjira (i.e. assuming you're on Jira cloud), you should collect the below

email = "email@address.com"
token = "apitoken"
url = "https://jira.atlassian.net"
data = email, token
jira = JIRA(server=url, basic_auth=data)

attributes from pyjira then you can do something like

LOGIN(email, token, url)

Although, you can't simply pass the authentication to jiraone but you can authenticate both and use it independently, Before calling

# other expression
print(time_in_status(PROJECT,singleIssue.key,reader=file_reader,pprint=True,login=LOGIN, is_printable=True))

However, I'll suggest you go through the docs https://jiraone.readthedocs.io for a much easier approach with the time_in_status() function

[Hector Eduardo Calzada Tenorio]

Hi Prince!

The use of Jiraone has been very helpful in our organization and I want to thank you for this contribution. Additionally, I would like to know if it's possible to obtain a time_in_status report of subtasks in Jiraone that indicates the ID of the parent issue. If it's not possible with time_in_status, then a report that shows the subtasks of a workspace with their parent issue would suffice.

Thank you somuch for your help.
Kind Regards.

[Prince Nyeche]

Hey @Hector Eduardo Calzada Tenorio 

The time_in_status would only output the information of the statuses only. So if you only want sub_task information of a specific parent issue, you must do that with JQL. However, the fields available with this function are few and the primary outcome is the difference of time on a particular status only.

[Arqam Nisar]

Hey there, while trying to fetch the time_in_status details, I get the error as 'No data to read. Quiting' although I have the data available for that particular issue.

Kindly give me some suggestions accordingly.

Regards.

[Prince Nyeche]

The time_in_status uses Jira's history to get data. If there are no historical data then you will get that error.