In which table is the value of time to resolution(SLA field) stored and in which column?

Nidhi Shah_7oog7k

I am trying to find that how much time it takes for an issue to resolve.. for that i need the resolution time.. I want to compare resolution times of issues per week..But I am not able to find the value of this field in Database.. I am using SQL server.. 

somethingblue

Hi Nidhi,

Take a look at Troubleshooting SLA in JIRA Service Desk.  From that article it looks like it is stored in

<span>AO_54307E_TIMEMETRIC</span>

It provides some SQL statements to get additional data from the SLA table:

 

<span>select</span> <span>(</span>p<span>.</span>pkey <span>||</span> <span>'-'</span> <span>||</span> i<span>.</span>issuenum<span>)</span> <span>as</span> issuekey<span>,</span> <span>cf</span><span>.</span>cfname<span>,</span> cv<span>.</span>textvalue
<span>from</span> customfield <span>cf</span><span>,</span> customfieldvalue cv<span>,</span> jiraissue i<span>,</span> project p
<span>where</span> i<span>.</span>project <span>=</span> p<span>.</span>id
<span>and</span> cv<span>.</span>issue <span>=</span> i<span>.</span>id
<span>and</span> cv<span>.</span>customfield <span>=</span> <span>cf</span><span>.</span>id
<span>and</span> <span>cf</span><span>.</span>customfieldtypekey <span>=</span> <span>'com.atlassian.servicedesk:sd-sla-field'</span>
<span>and</span> p<span>.</span>pkey <span>=</span> <span>'TEST'</span>
<span>and</span> i<span>.</span>issuenum <span>in</span> <span>(</span><span>1</span><span>,</span><span>2</span><span>)</span><span>;</span>

 

• The query above will return the SLA information (for all SLA fields types) for ticket TEST-1 and TEST-2. If you remove the p.pkey = 'TEST' and i.issuenum in (1,2) ; it will return all issues from all projects.

• Alternatively you can also cross check it directly via REST API call. This will return SLA information from the database.

  To Query For A Specific SLA

  https://<ATLASSIAN_DOMAIN>/rest/servicedesk/1/servicedesk/<PROJECT_KEY>/sla/debug/issue/<TICKET_ID>/metric/<SLA_ID>/data

 

Since you're using SQL server you may need to modify the SQL a bit since we usually test with Postgres.  Take a look at Troubleshooting SLA in JIRA Service Desk for more SQL examples and further explanations of how the table works with other related SQL tables to provide information.

Cheers,

Branden