Open issuesSwitch filter

Jose Luis Gonzalez added a comment - 31/Jan/2023 9:34 PM 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: # 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: //id.atlassian.com/manage-profile/security/api-tokens 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: //www.gstatic.com/charts/loader.js' ></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\ // build tooltip\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>&nbsp;' + 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); // <-- use data view to draw chart\n\ }); </script>\n\ <div id= 'timeline' style= 'height: 600px;' ></div>" template = template.replace( "%DATA%" ,data) f = open( "timeline.html" , "w" ) f.write(template) f.close()   The output is a CSV with the timestamps and statuses: time0 time1 status deltaSec key 2022-10-18 17:03:59.448000-05:00 2023-01-31 14:29:35.583039-06:00 Approved 9066336.135039 SCRUM-1487 2022-10-18 16:49:53.196000-05:00 2022-10-18 17:03:59.448000-05:00 To Do 846.252 SCRUM-1487 2022-10-18 17:05:42.310000-05:00 2023-01-31 14:29:35.583163-06:00 Cancelled 9066233.273163 SCRUM-1486 2022-10-18 17:05:22.638000-05:00 2022-10-18 17:05:42.310000-05:00 To Do 19.672 SCRUM-1486 And an HTML file with a timeline chart     Cheers!