nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Field value duration for BI purpose

slucchesini

Hello,

This is basically the classic "Time in status" question, except that I want to extend it to any other field.

Here's my context: we've created an Incident workflow that's meant to be very fluid. There's no differentiation between teams, and escalating to a new team is simply a matter of changing the value of the Team field, not the status or the request type. This was done for two main reasons:
- We wanted to avoid the classic "Support N1", "N2", "N3"... statuses because the organization can change and an N4 team could appear or an N3 could disappear
- We also wanted to be able to transfer the ticket to another team (e.g if the problem seems to be coming from a depandency) without creating a new ticket

The big problem is that we would like to get data on how long each team takes to manage a ticket, and we're unable to do so with this architecture. If each team had a given status or request type, we would be able to create a SLA per team, but there's no "Team field has changed" trigger in the SLA rule definitions (in any case, one SLA per team is not necessarily the best solution)... I don't want to create a field per team either and populate it through Automation.
So if I want to stick to my architecture, the only solution I see is probing every ticket's history through the APIs in a BI app, which would be very heavy.

What do you think? Is there a magical "Field value duration" add-on I could use? Should I change my architecture? Can you think of some kind of in-between solution that would keep my workflow flexible while allowing me to get my stats?

Accepted answers

Alexandre Pezzini

Since you are on a Premium license (which does not include native Atlassian Analytics), your best path forward involves using specialized Marketplace tools or targeted automation to structure the data for your BI stack.

1. Recommended Solution for Premium: Marketplace Apps

For a "Field value duration" requirement that extends beyond just status, there are established apps that specialize in "Any Field" tracking. These are much lighter than manual API probing because they index the history for you:

Timepiece (Time in Status): This app includes an "Any Field Duration" report. It can specifically track how long a ticket held a certain value in your "Team" field. Crucially for BI, it provides a JSON Data Feed or CSV export, allowing you to pipe structured duration data directly into Power BI or Tableau without manual calculations.
Issue History for Jira (SaaSJet): This app offers a "Field Changes Duration" report. It is designed to calculate exactly how long any system or custom field value remained the same, providing an audit-ready history that can be exported for external analysis.

2. The "In-Between" Automation Approach

If you want to avoid additional apps, you can use Jira Automation to "pre-calculate" these durations so your BI tool doesn't have to.

The Logic: Create a hidden Long Text custom field (e.g., "Team Duration Log").
The Flow: Use a trigger for "Field value changed" (Team). Use an action to calculate the time elapsed since the last change and append a string to your log field: Team A: 120m; Team B: 45m;.
Result: Your BI tool only needs to read one field per ticket rather than the entire history.

3. Future Recommendation: Atlassian Analytics (Enterprise)

While not available on your current Premium license, it is worth noting how an upgrade to Jira Cloud Enterprise would simplify this via Atlassian Analytics:

Direct SQL Access to History: Atlassian Analytics provides a Data Lake that includes a table called issue_field_history. Instead of using APIs or apps, you could write a single SQL query to calculate the time between "Team" field changes across your entire site.
Cross-Product Insights: You could join this "Team duration" data with other sources (like Opsgenie or Confluence) to see how team response times impact overall service health.
No-Code Visualization: It includes pre-built ITSM templates that can be easily modified to track "Flow Metrics" for any custom field, not just status.

Verified Documentation

Atlassian Analytics Overview: Atlassian Analytics | Atlassian
Getting Started with Analytics: Get started with the Analytics app | Analytics | Atlassian Support
Jira Automation Smart Values: Automation smart values - date and time | Cloud automation Cloud | Atlassian Support

I hope this helps you maintain your flexible architecture while getting the precise BI data you need!

All comments

Alexandre Pezzini

1. Recommended Solution for Premium: Marketplace Apps

Timepiece (Time in Status): This app includes an "Any Field Duration" report. It can specifically track how long a ticket held a certain value in your "Team" field. Crucially for BI, it provides a JSON Data Feed or CSV export, allowing you to pipe structured duration data directly into Power BI or Tableau without manual calculations.
Issue History for Jira (SaaSJet): This app offers a "Field Changes Duration" report. It is designed to calculate exactly how long any system or custom field value remained the same, providing an audit-ready history that can be exported for external analysis.

2. The "In-Between" Automation Approach

If you want to avoid additional apps, you can use Jira Automation to "pre-calculate" these durations so your BI tool doesn't have to.

The Logic: Create a hidden Long Text custom field (e.g., "Team Duration Log").
The Flow: Use a trigger for "Field value changed" (Team). Use an action to calculate the time elapsed since the last change and append a string to your log field: Team A: 120m; Team B: 45m;.
Result: Your BI tool only needs to read one field per ticket rather than the entire history.

3. Future Recommendation: Atlassian Analytics (Enterprise)

While not available on your current Premium license, it is worth noting how an upgrade to Jira Cloud Enterprise would simplify this via Atlassian Analytics:

Direct SQL Access to History: Atlassian Analytics provides a Data Lake that includes a table called issue_field_history. Instead of using APIs or apps, you could write a single SQL query to calculate the time between "Team" field changes across your entire site.
Cross-Product Insights: You could join this "Team duration" data with other sources (like Opsgenie or Confluence) to see how team response times impact overall service health.
No-Code Visualization: It includes pre-built ITSM templates that can be easily modified to track "Flow Metrics" for any custom field, not just status.

Verified Documentation

Atlassian Analytics Overview: Atlassian Analytics | Atlassian
Getting Started with Analytics: Get started with the Analytics app | Analytics | Atlassian Support
Jira Automation Smart Values: Automation smart values - date and time | Cloud automation Cloud | Atlassian Support

I hope this helps you maintain your flexible architecture while getting the precise BI data you need!

Natalia_Kovalchuk_SaaSJet_

Hi!

Alexandre Pezzini, thanks for recommending Issue History for Jira (SaaSJet).

slucchesini, really, the app provides Field changes duration report that automatically calculates the time difference between the selected field changes available when downloading reports using the Excel (Advanced) export. Here is an example:

Also, the app has Time in field values report that shows how long work items stayed in each field value. An example of the report:

Also, the Issue History for Jira app provides an API that can be used to automatically export complete Jira work item history to various BI tools. More info can be found here: Jira API for History Export

In addition to Issue History for Jira, you may also consider SLA Time and Report for Jira by SaaSJet if you need to track this time against specific service targets.

For example, you can configure an SLA to run while the Team field has a particular value and stop or restart when the team changes. With multi-cycle tracking, the app can also calculate the total time spent by the same team if the issue is assigned to it more than once.

Beyond time tracking, the app allows you to set SLA goals, apply working calendars, monitor remaining time directly in Jira issues, receive alerts before a breach, and analyze results through reports, dashboard gadgets, and exports.

Rahul_RVS

Hi @slucchesini

You can use Jira Rest API's to extract the historical data or if you would be interested in a mktplace app for this requirement, you can try out

Time in Status Reports

With this app you generate time in each workflow status for multiple issues with multiple filter and grouping options.

The app also has "Time in Field" report which can help the total time your issues spend in a particular field value.

There is an Export to JSON feature as well which can help you to port this data very easily to a BI tool like Power BI.

Also the app has 20+ reports to meet a variety of use cases.

More details here.

Disclaimer : I am part of the app team for this add-on

TIS - Time in Field.PNG

slucchesini

Hi everyone,

Thanks for your answers! It's good to know that add-ons exist just for that, I'll try to review them but in any case I'm confident that my requirements can be met without changing my architecture!

For those who can't rely on add-ons, I have built something based on native features. Basically:

1. Build 2 types of SLAs: time for resolution and time to assign; you want them to reset when a Team is changed (in my case)

2. Each time a SLA is reseted, add a worklog with the SLA duration; the description of the worklog will contain the type of SLA and the team involved

3. The BI only needs to get the worklogs and read them

Cheers,

Simon

Monika Ambrozowicz_Seibert_

Hi @slucchesini, you've got quite a lot of great recommendations already, but I'd like to add one more for you to compare with the others It's called Awesome Custom Fields (full disclosure: I'm affiliated with the team behind the app). It includes a "Time in Status" field (next to 20+ other super useful fields, like a multi-level cascading select, color labels, or t-shirt size) type that surfaces status-duration data directly on the issue. So even if for now the setup you built works well, I'd still recommend having a look at Awesome Custom Fields because you will likely find other fields from the package useful, as they all fill the gaps in native Jira.

Birkan Yildiz _OBSS_

Hey @slucchesini

As Alexandre mentioned, there is indeed a magical "Field value duration" add-on, so you do not need to change your flexible architecture or build heavy API scripts to parse issue histories!

You can easily track these exact metrics using Timepiece - Time in Status for Jira.

Here is how you can get your team stats effortlessly:

Use the Any Field Duration Report: This advanced report type is designed exactly for this use case, allowing you to see how long any specific issue field held each value without needing to map them to statuses.

Select your 'Team' field: By selecting your "Team" field as the history field, the report will list your tickets in rows and dynamically create columns for each team value (Support N1, N2, etc.), showing the exact time the ticket spent with each team.

Easy BI Integration: If you still want to bring this data into your BI app, Timepiece has an extensive REST API that provides these pre-calculated reports directly as JSON or CSV files.

This saves your system from the heavy lifting of probing raw Jira changelogs.

To learn more, you can find Timepiece - Time in Status for Jira on the Atlassian Marketplace.
Full disclosure, I'm on the team that makes Timepiece. Hope this helps!

Best,
Birkan

Ilze Mezite

Hi, @slucchesini

Just to add another option — eazyBI for Jira can handle this use case natively without requiring heavy API scripting on your side.

eazyBI imports Jira issue changelog data during its regular import process. Using JavaScript calculated custom fields, you can track how long any custom field (such as your "Team" field) held each value, and import those durations directly into eazyBI as measures. From there, you can build reports showing time per team, per issue, per time period, and much more — all without probing Jira's API at query time.

Hope this helps as an alternative worth considering!

Kindly,

Ilze

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

jira

cloud

jira-cloud

service

sla