Calculate the time a common ticket spent on a given team hands

[Esteban Borja Lopez]

Hi all, 

Im dealing with the challenge to identify the time each team spend on a given common incident ticket through automation.

This is, a unique ticket might be going through different specific teams on the seek of resolving the issue, and the only way to identify who was working on it is by using a custom field that is being manually set (based on a dropdown list of teams such as "NOC L1", "NOC L2", "IT", "Development", "Quality Assurance", etc...).

Idea is to measure the end-to-end issue resolution time (this is covered already), at the same time being able to tell for how long each team has worked it out, either by time or by percentage. Something like this will work:

• Incident Response Time: 30m
  • "NOC L1": 100%
• Incident Resolution Time: 3h 15m
  • "NOC L1": 10%
  • "NOC L2": 30%
  • "Development":40%
  • "Quality Assurance": 20%

At this stage im mainly thinking on the approach to be taken other than coding... nevertheless, any hint on how the automation expressions might look like is welcome!

Thanks team,

Esteban

[Emre Toptancı _OBSS_]

Hello @Esteban Borja Lopez

I know you are looking for a solution with automation. But if you are OK with using a marketplace app for this, our team at OBSS built Time in Status for this exact need. It is available for Jira Server, Cloud, and Data Center.  

Time in Status mainly allows you to see how much time each issue spent on each status and on each assignee.

       

The app also has Any Field Duration report that can show how long your Team field held each value. So if your issue jumps from team to team and each time the value of the Team field is changed, you can see the total time for each value of Team field. (The screenshot below shows a sample Any Field Duration report for Status and Flagged fields combined but you can do the same for just your Team field.)

Additionally, Time in Status has Group Duration report. This one lets you pick some user groups and shows the total time each issue was assigned to a member of those groups.

For all numeric report types, you can calculate averages and sums of those durations grouped by the issue fields you select. For example total in-progress time per customer (organization) or average resolution time per sprint, week, month, issuetype, request type, etc. The ability to group by parts of dates (year, month, week, day, hour) or sprints is particularly useful here since it allows you to compare different time periods or see the trend.

The app calculates its reports using already existing Jira issue histories so when you install the app, you don't need to add anything to your issue workflows and you can get reports on your past issues as well. It supports both Company Managed and Team Managed projects.

Time in Status reports can be accessed through its own reporting page, dashboard gadgets, and issue view screen tabs. All these options can provide both calculated data tables and charts.

And the app has a REST API so you can get the reports from Jira UI or via REST.

  

Using Time in Status you can:

• See how much time each issue spent on each status, assignee, user group and also see dates of status transitions.
• Calculate averages and sums of those durations grouped by issue fields you select. (For example, see average InProgress time per project and per issue type.)
• Export your data as XLS, XLSX, or CSV.
• Access data via REST API. (for integrations)
• Visualize data with various chart types.
• See Time in Status reports on Jira Dashboard gadgets

https://marketplace.atlassian.com/apps/1211756/

EmreT

[Esteban Borja Lopez]

Hi @Emre Toptancı _OBSS_ , will have a look to the app. Thanks for the reply!

Cheers,

Esteban

[Bill Sheboy]

Hi @Esteban Borja Lopez 

The information you need to answer the questions in your use case with custom fields are in the issue history, and that information is neither easily consumable by automation nor easily reportable by Jira with out-of-the-box features.

The specifics of your use case will determine how complex this is to solve, such as: can an issue be worked on by a  specific team multiple times, will people make mistakes of assignment, etc.

Something like this may be better solved with child work items, created just-in-time and specific for a team doing work.  Then measure around the child items for a given parent to report.

Kind regards,
Bill

[Esteban Borja Lopez]

Hello @Bill Sheboy

Thanks for the response!

I've been playing around with some ideas and I think I get something working, not the best solution but a decent one that drops what Im after. Approach is as follow:

• Created some custom fields for lead time calculation purpose
• Created an automation rule that prepare the above fields upon ticket creation (timestamp, initial team allocation, ...)
• Created 3 additional rules to:
  • Calculate the handling time for a given team (based on "Impacted Teams" value, which triggers the rule), which set the start timestamp since the specific team is assigned... the rule has only 2 teams by now for simplification purposes. The result if an integer that is saved number-type field that represents the minutes the ticket was under that team
  • Calculate the handling time as above, but the trigger is a transition event to final status
  • Calculate the total ticket lead time (since creation till final status), in minutes

• Once I got the 3 numbers (in the future could be as many as I want based on the need), Im able to consume it on Confluence via nested-macros, which can calculated to represent the time (min) and the percentage (%) each team is working on a given ticket type, in this case, incidents

Rules

Rule sample

Custom Fields (fulfilled)

Confluence fields/values consumption

SQL

SELECT

SUM (T1.'Incident Lead Time (min)') AS 'Total Lead Time (min)',
SUM (T1.'L2 QA Lead Time (min)') AS 'QA Lead Time (min)',
SUM (T1.'L2 OC Lead Time (min)') AS 'OC Lead Time (min)',

CONCAT(ROUND((SUM (T1.'L2 QA Lead Time (min)')/SUM (T1.'Incident Lead Time (min)'))*100,2),"%") AS 'QA Lead Time (%)',
CONCAT(ROUND((SUM (T1.'L2 OC Lead Time (min)')/SUM (T1.'Incident Lead Time (min)'))*100,2),"%") AS 'OC Lead Time (%)'

FROM T1

Result

I will keep debugging it to find corner cases not being covered but so far results are ok. Any idea/comment/proposal to simplify this would be more than welcome :)

Thanks and regards,

Esteban 

[Bill Sheboy]

Hi, Esteban.

Looks like you have a working solution!  Some edge cases to check are:

• team assignment errors,
• back-and-forth assignments between teams (non-assignment errors), and
• confirming that all of the dates are in the same time zone prior to performing any math/date diffs.

We have some similar rules in place, with the primary one to measure Age of WIP.  There are no built-in functions to do that...and the days-in-column Jira feature only works for very simple boards where teams have no backwards flow.

Kind regards,
Bill

[Esteban Borja Lopez]

Hi, @Bill Sheboy 

• team assignment errors, ---> As this is a troubleshooting ticket (incidents/problems issue-type), the issue can travel along multiple teams, back and forth... so at least some exceptional cases that requires manual review, I hope this is not an issue to consider
• back-and-forth assignments between teams (non-assignment errors), and ---> No a problem, as the rules are set in such way that it counts (and accumulate) the allocation time
• confirming that all of the dates are in the same time zone prior to performing any math/date diffs. ---> this it's only applicable (so far) to a single internal project, which is set on the right timezone. Let's see once I've extend it further :)

Something I need to still give a think, and perhaps change a bit the approach, is the key field im using here: "Impacted Teams", as it's a "Select List (multiple choices)" type so I might need to either restrict it 1 team at a time (don't think it's the right solution as 1 incident can impact multiple areas) or use a different field.

Thanks again for taking the time in reviewing/responding to my post, kindly appreciated. Hope the approach explained helps someone else to overcome the needs, as I've seen multiple questions about same thing, and I know (by fact) that this is a head-ache!

Regards,

Esteban

[Esteban Borja Lopez]

FYI - just an update on this matter... after several adjustments (following the described approach), this is how the dashboard looks like in Confluence:

All is automatically calculated/rendered... only manual task is team allocation as per the incident troubleshooting.

With this, Im closing this Question as "solved".

Thanks all for the contribution,

Esteban