Query to retrieve the number of times a task (or subtask) has been set to a certain status value

Boris LINDAUER
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
May 26, 2023

Hello

For an epic, I would like to have the list of tasks / sub tasks who have changed to a status "TEST KO" over the duration of the project.

For example, it would return something like that

Task 1 , 05/01/2023
Task 1 , 05/04/2023
Task 1 , 05/12/2023
Task 1 , 05/25/2023
Task 2 , 05/03/2023
Task 2 , 05/27/2023
meaning that the task 1 has changed 4 times to "TEST KO", Task 2 has changed twice on those specific dates

OR something like

Task 1 , 4
Task 2 , 2
which would mean that the task 1 has been set 4 times to "TEST KO", and Task 2 twice

Thanks

5 answers

3 votes
Valeriia_Havrylenko_SaaSJet
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
July 24, 2023

Hello @Boris LINDAUER  👋 
Welcome to the community!

As an alternative , I guess you can try Time in Status for Jira Cloud (developed by my SaaSJet team) with 7 types of status time reports

For your exactly need we have Status Count report or Transition Count report

The Status Count report shows how many times an issue has been in each status.

Status count .png

Chart view of report:
Status count  chart view.png
The Transition Count report shows how many times an issue has moved between all statuses in the workflow.Transition Count report.png

Chart view of report:

Transition Count report Chart view.png

Add-on has a 30-day free trial version and free up to 10 users
Please, let me know if you have any questions

Hope it helps 😌
Valeriia

0 votes
RVS_Support
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.
July 24, 2023

Hi @Boris LINDAUER 

Welcome to the community !!

If you are open for a mktplace app, you can try our addon:

Time in Status Reports 

The add-on provides the status transition count for the complete lifecycle of the issues. There are 20+ reports in the app which help meet a variety of use cases.

Do try it out.

Disclaimer : I work for RVS, the vendor for this app

TIS - Count.PNG

0 votes
Bloompeak Support
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
July 24, 2023

Hi @Boris LINDAUER

Welcome to Atlassian Community!

You can try Status Time Reports app developed by our team. It mainly provides reports and gadgets based on how much time passed in each status.

Here is the online demo link, you can see it in action and try without installing the app. For your case, you can have a look at Status Count And Entry Dates report. Entry date(see In Development, Ready for Testing, In Testing, In Development columns.) is status transition date and status count(see #In Development, #Ready for Testing, #In Testing, #In Development columns) is how many times an issue is entered to this status.

  • This app has a dynamic status grouping feature so that you can generate various valuable reports as time in status, time in assignee, status entry dates and status counts, cycle time and lead time, average/sum reports by any field(e.g. average in progress time by project, average cycle time by issue creation month).
  • You can search issues by Project, Issue Type, Status, Assignee, Issue Creation/Resolution Date(and any other Date field) and JQL Query.
  • Status durations are calculated according to the working calendar you define. Once you enter your working calendar into the app, it takes your working schedule into account too. That is, "In Progress" time of an issue opened on Friday at 5 PM and closed on Monday at 9 AM, will be a few hours rather than 3 days.
  • You can set different duration formats.
  • You can export reports in CSV file format and open them in MS Excel.
  • You can also add this app as a gadget to your Jira dashboards and reach “Status Time” from Issue Detail page.
  • You can enable/disable access to Status Time reports&gadgets and Issue Detail page per project, users, groups or project role.

For further details, you can have a look at Status Time Reports How to Videos.

If you are looking for a completely  free solution, you can try the limited version Status Time Free.

Hope it helps.

0 votes
Laura De Vita May 26, 2023

Hi @Boris LINDAUER 

if the epic link field is present in the activity and sub activity the query is this:

issuetype in (Task, Sub-task) and "Epic Link" in ("name epic")and (status changed to "TEST KO"  and status= "TEST KO")

You must insert  in " name epic"  the name of the epic.

if there is script runner  and epic link is not present on task, the possibile  query is:

issueFunction in linkedIssuesOfRecursive("issue = issueKeyEpic") and (status changed to "TEST KO" and status= "TEST KO")

I hope I have been helpful.

Kinds Regards,

Laura

0 votes
Mohamed Benziane
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 26, 2023

Hi,

Welcome to the community

You can achieve this using the REST API and look at the history or look in the marketplace to find a plugin

Suggest an answer

Log in or Sign up to answer