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

[Nidhi Shah]

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

AO_54307E_TIMEMETRIC

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

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

• 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