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

[Boris LINDAUER]

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

[Valeriia_Havrylenko_SaaSJet]

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.

Chart view of report:

The Transition Count report shows how many times an issue has moved between all statuses in the workflow.

Chart view of report:

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

[RVS_Support]

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

[Mehmet A _Bloompeak_]

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.

[Laura De Vita]

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

[Mohamed Benziane]

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