# file timeInStatus.py
# Requirements: install python3
# How to run: python timeInStatus.py
import requests
from requests.auth import HTTPBasicAuth
import json
import csv
from datetime import datetime,timezone
import re
import urllib.parse
jql="project = KN4" # insert here the jql that you can test in the UI
APItoken="APIToken" # generate it here https:AdminEmail="email@domain.com"
tenant="site-url.atlassian.net"
# First, get the data of the issues via API
auth = HTTPBasicAuth(AdminEmail, APItoken)
headers = {
"Accept": "application/json"
}
url = "https:+tenant+"/rest/api/3/search?jql="+urllib.parse.quote(jql)+"&startAt=0&maxResults=100&expand=changelog"
response = requests.request("GET", url, headers=headers, auth=auth)
jsonResponse = json.loads(response.text)
# Then, process the data of the issues
data = ""
headers = ["time0","time1","status","deltaSec","key"]
# output csv file
with open('logs.csv', 'w') as f:
writer = csv.DictWriter(f, fieldnames=headers)
writer.writeheader()
# process each retrives issue
for issue in jsonResponse["issues"]:
statusChanges = []
# sincethe current status doesn't have history tiem, we add it for calculation
print("working with:" + issue["key"])
statusChange = {}
statusChange["time0"] = datetime.fromisoformat(re.sub(r'00$',':00',issue["fields"]["created"]))
statusChange["time1"] = datetime.now(timezone.utc).astimezone()
statusChange["status"] = issue["fields"]["status"]["name"]
statusChange["key"] = issue["key"]
statusChanges.append(statusChange)
# Process the changelog items from the newest to the oldest
for i in range(len(issue["changelog"]["histories"])):
history = issue["changelog"]["histories"][i]
for item in history["items"]:
if item["field"] == "status":
statusChange = {}
statusChange["key"] = issue["key"]
statusChange["time1"] = datetime.fromisoformat(re.sub(r'00$',':00', history["created"]))
statusChange["status"] = item["fromString"]
## adjust previous status
statusChanges[len(statusChanges)-1]["time0"] = datetime.fromisoformat(re.sub(r'00$',':00',history["created"]))
statusChanges.append(statusChange)
#print(history["created"],history["items"][0]["field"], history["items"][0]["fromString"],history["items"][0]["toString"])
# complete the first history entry, the start date is when the issue was created
statusChanges[len(statusChanges)-1]["time0"] = datetime.fromisoformat(re.sub(r'00$',':00',issue["fields"]["created"]))
for statusChange in statusChanges:
data = data + "['"+issue["key"]+"','"+statusChange["status"]+"', new Date("+ str(int(statusChange["time0"].timestamp()*1000)) + "), new Date("+str(int(statusChange["time1"].timestamp()*1000))+")],\n"
statusChange["deltaSec"] = (statusChange["time1"] - statusChange["time0"]).total_seconds()
writer.writerows(statusChanges)
# Write the html presentation template, it uses google charts api
template = "<script type='text/javascript' src='https:></script>\n\
<script type='text/javascript'> \n\
google.charts.load('current', {\n\
packages: ['timeline']\n\
}).then(function () {\n\
var container = document.getElementById('timeline');\n\
var chart = new google.visualization.Timeline(container);\n\
var dataTable = new google.visualization.DataTable();\n\
dataTable.addColumn({ type: 'string', id: 'Federation' });\n\
dataTable.addColumn({ type: 'string', id: 'Event' });\n\
dataTable.addColumn({ type: 'date', id: 'Start' });\n\
dataTable.addColumn({ type: 'date', id: 'End' });\n\
dataTable.addRows([\n\
%DATA%]);\n\
var options = {\n\
timeline: { colorByRowLabel: true },\n\
tooltip: {isHtml: true},\n\
legend: 'none',\n\
backgroundColor: '#ffd'\n\
};\n\
\n\
var formatTime = new google.visualization.DateFormat({\n\
pattern: 'yyyy-MMM-dd HH:mm:ss a'\n\
});\n\
\n\
var view = new google.visualization.DataView(dataTable);\n\
view.setColumns([0, 1, {\n\
role: 'tooltip',\n\
type: 'string',\n\
calc: function (dt, row) {\n\
var dateBegin = dt.getValue(row, 2);\n\
var dateEnd = dt.getValue(row, 3);\n\
var oneHour = (60 * 60 * 1000);\n\
var duration = (dateEnd.getTime() - dateBegin.getTime()) / oneHour;\n\
\n\
var tooltip = '<div><div class=\"ggl-tooltip\"><span>';\n\
tooltip += dt.getValue(row, 0) + ':</span> ' + dt.getValue(row, 1) + '</div>';\n\
tooltip += '<div class=\"ggl-tooltip\"><div>' + formatTime.formatValue(dateBegin) + ' - ';\n\
tooltip += formatTime.formatValue(dateEnd) + '</div>';\n\
tooltip += '<div><span>Duration: </span>' + duration + ' hours</div></div>';\n\
\n\
return tooltip;\n\
},\n\
p: {html: true}\n\
}, 2, 3]);\n\
\n\
chart.draw(view.toDataTable(), options); }); </script>\n\
<div id='timeline' style='height: 600px;'></div>"
template = template.replace("%DATA%",data)
f = open("timeline.html", "w")
f.write(template)
f.close()
Suggestion
Hello Everyone,
I would like to contribute by adding one workaround for this query for all users can retrieve the report, although the following options are valid:
Option 1: The days in the column feature in agile boards, show the number of days that an issue has been in a column, you can check this document for more information. To enable it, open the board settings, go to card layout and find the option at the bottom.
Option 2: There are some apps in the marketplace that provide this feature, you could take a look at these apps.
This is the python script:
The output is a CSV with the timestamps and statuses:
And an HTML file with a timeline chart
Cheers!