Database tables related to SLA

[Pooja]

Hi Team,

I was trying to get information like Deadline, Days of Breach, etc. for the SLA report.

Created below query, I need some clarification

  *   Value is less than Target Duration but then it is coming under slaExceededThresholds
  *   How can we decode Event_time to get the actual action date
  *   I understand that the duration like value and Target duration are in milliseconds, can e anyone confirm.

Regards,

Pooja

[Andy Heinzer · Accepted solution]

Hi

I see that you are using Jira Service Desk Server and have some questions about the database tables in use here for SLAs.  As you have likely already found, there are two such tables in the database by the names of

• AO_54307E_SLAAUDITLOG
• AO_54307E_SLAAUDITLOGDATA

The EVENT_TIME field there is in milliseconds, but technically this is unix epoch time.  Converting between unix time and a human readable format is something I recently figured out how to do in postgresl using the to_timestamp() function.  If you're using a different database such as MySQL, Oracle, or MS SQL this might not work or it might require a different syntax.  For example if using Postgresql, you could use a query such as,

SELECT to_timestamp(("AO_54307E_SLAAUDITLOG"."EVENT_TIME")/1000) as ReadableDate, * from "AO_54307E_SLAAUDITLOG"

in order to see a readable date and time value in that 'ReadableDate' field next to the rest of the table and its values.

As for your first point, I'm not sure I have an explanation for that behavior yet.  If I understand correctly, you have an request in Service Desk that has exceeded SLA, even though the duration was less than the interval set of that event.  If that is the case, I'd be interested to learn more about what this SLA is triggered upon (what action such as create, edit, specific transition, etc), and whether or not this issue has been moved within Jira.  Perhaps with some more details about this example we can provide a better explanation of the behavior here.

Cheers,

Andy