Get the list of SLA Breached / not Breached tickets from jira database

Hi All,


I am looking to get a list of SLA Breached/ Not breached tickets from JIRA database. Its a Json field and we are using Oracle 11. Looks like there is good support for JSON in oracle 12.

I could find the breached/ Not breached for the closed tickets by looking for "completeSLAData":[{"succeeded":false' or "completeSLAData":[{"succeeded":true in the customfieldvalue.

But it is not clear for the open tickets as we also have an option to move tickets between types and some types have sla set up and some not.

Is there a way to get what the breached() function actually does as it seem to get right results in JQL which is exactly what i am looking to do from database?


Many Thanks in Advance


1 answer


This is a little bit more complex than it looks to be sad

For completed (Finished) SLA Metrics you can read data directly from database, but for Active and Paused metrics SLA Metric Searcher uses some logic. To determine if an Active SLA Metric is breached you need to determine: SLA Calendar, Consumed time, SLA Goal Value and current time.

It can't be read directly from database but you may write some code to map entire logic and get actual SLA Metric values. We did it some time ago for VertygoSLA (it took a lot of time but it's feasible).

If the reporting is essential to your company, you can use SLA PowerBox add-on which provides SLA Date custom field type and let you get data directly from database (but it's paid add-on and replaces SLA functionality in JSD).

Suggest an answer

Log in or Join to answer
Community showcase
Sarah Schuster
Posted Jan 29, 2018 in Jira

What are common themes you've seen across successful & failed Jira Software implementations?

Hey everyone! My name is Sarah Schuster, and I'm a Customer Success Manager in Atlassian specializing in Jira Software Cloud. Over the next few weeks I will be posting discussion topics (8 total) to ...

2,755 views 11 18
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot