How to Use SLA Data Stored in Database

This page is about Time to SLA for Jira Server users.

The Time to SLA app provides various REST APIs to retrieve SLA data stored in the app. You may want to get that information from the database to use with business information tools.


On this page you will learn the reasons behind limited data access to SLA data in a database, and how to interpret that limited information.

Important Notes and Disclaimer

  • Information stored in the database by the Time to SLA plugin is never meant to be used externally. We do not guarantee the integrity of the information you have collected by those instructions here. Use them at your own risk.
  • Database tables store vital information for Jira, Time to SLA, and many other plugins. Manually working on it might cause irreversible damage or bad application performance due to corrupted table statistics etc.
  • When possible, use REST services instead. If something is missing from REST services, please raise a ticket, and we will do our best to provide related services.

AO_C5D949_TTS_ISSUE_SLA : SLA Data on issues

This table stores intermediate information required to speed-up the calculation of updated SLA values on an issue. Each line represents an SLA instance on a single issue.

Column

Type

Explanation

Reference

IDNumberID of this SLA instance. Note that this ID is subject to change at any time.
SLA_IDNumberID of the SLA definition this SLA instance belongs to.AO_C5D949_TTS_SLA.ID
ISSUE_IDNumberID of the issue this SLA instance belongs to.jiraissue.id
ORIGIN_DATEDateExact instant this SLA instance started to count.
EXPECTED_TARGET_DATEDateDeadline for this SLA instance.
ACTUAL_TARGET_DATEDateActual instant that this SLA has reached its target condition.
RESET_DATEDateLast reset instant for SLA.
INDICATOR *StringCurrent state of SLA (EXCEED, STILL or SUCCESS).
PAUSEDBooleanWhether or not SLA is paused.
FINISHEDBooleanWhether or not SLA is completed.
WORKING_DURATION *IntegerElapsed time from the start of SLA in milliseconds.
PAUSED_DURATION *IntegerTime spent on paused state (when SLA in progress) in milliseconds.
FORMATTED_WORKING_DURATIONStringFormatted working duration, filled only when SLA is completed.
FORMATTED_PAUSED_DURATIONStringFormatted paused duration, filled only when SLA is completed.

* Limitation applies for information stored on those columns please see the following:

  • Data on this table is only updated during issue transitions, so:
    • You may observe inconsistencies between the data and SLA information on the issue view because SLA is an active metric. For example, the remaining duration of an active SLA changes with each second.
    • SLAs are considered as breached once their deadline has passed. Yet this is not persisted on the database until they go through an issue transition after breaching their deadline.
    • One exception to this is paused SLAs. The target dates of paused SLAs will always be kept updated (this is done by a scheduled task, so it is reasonable to observe 2-3 minutes inaccuracies).
  • Completed SLAs (SLAs that reached their target status) always show updated information since there is no actively running clock on them.
    • So if you see FINISHED column true, you can be sure that all values in other columns will be consistent with the actual SLA state.
  • EXPECTED_TARGET_DATE is an excellent reference to check whether or not an SLA is breached. It does not change by time (except when an SLA is paused in that case target date shifts further in time, and this is correctly reflected on database entries with the help of a scheduled task).


Here are some queries to differentiate different SLA states:

Met SLAs
"INDICATOR" = 'MET'
Progress SLAs (Including paused ones)

("INDICATOR" = 'STILL' AND "EXPECTED_TARGET_DATE" > now()) OR "PAUSED" is true

Progress SLAs (Excluding paused ones)
("INDICATOR" = 'STILL' AND "EXPECTED_TARGET_DATE" > now()) OR "PAUSED" is false
Exceeded SLAs
("INDICATOR" = 'EXCEED' OR ("INDICATOR" = 'STILL' AND "EXPECTED_TARGET_DATE" < now())) AND "PAUSED" is false
Completed SLAs
"FINISHED" is true
SLA Indicator custom field displays paused SLAs as PROGRESS