How to get SLA Elapsed time from DB in Jira Server and Data Center

Summary

How to find the "Elapsed time" for SLAs in issues of the Service Desk.

Environment

4.20.2

Solution

Jira by default does not provide a feature to get "Elapsed time" for SLAs on screen. However, once the SLA is achieved if you hover on the SLA, you could see SLA Goal and 'Actual' time. Here, 'Actual' time would be the 'Elapsed time'.

• You can get the information about "Elapsed Time" for an SLA through the REST URL for any issues irrespective of their SLA status (achieved/waiting/breached).

  1. Go to Project Settings > SLAs > Click on the SLA and notice the URL. The URL would end with a number. This number is SLA_ID for that SLA. In the below example, 7 is SLA ID.http://localhost:8020/servicedesk/admin/TRAIN/sla/custom/7

Use the below REST URL to add details of the ProjKey-Issue ID and SLA ID of the issue and SLA for which you need to get "Elapsed time".

<BASE URL>/

rest

/servicedeskapi/request/<ProjKey-IssueNumber>/sla/<SLA ID> ------------------------------------ example: http:

//localhost:8020/

rest

/servicedeskapi/request/TRAIN-15/sla/7

This would provide you with details of that SLA and issue number in JSON format. You could search for "elapsedTime" field which would contain information on Elapsed time in milliseconds.

DB method

Note: Please note as per our support offerings we don't support providing DB queries.

• You can get the information about "Elapsed TIme" for any SLAs through the below query.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 = '<Project Key>'AND i.issuenum IN (<Issue Number>);

  ℹ️ Replace <Project Key> and <Issue Number> accordingly.

This will get you the output for SLAs for the given ProjectKey and IssueNumber. In the DB output, column cfname will be the SLA and textvalue will be the data for that SLA in JSON format.

• Here is the example of the output for an SLA from textvalue column. Elapsed time is stored as "elapsedTime":511715, which will be in milliseconds.{"timeline":{"events":[{"date":1594807955256,"types":["START"]},{"date":1594808466971,"types":["STOP"]},{"date":1594808505333,"types":["START"]}]},"ongoingSLAData":{"goalId":14,"startTime":1594808505333,"paused":false,"thresholdData":{"calculatedAt":1594894905342,"remainingTime":-9,"thresholdsConfigChangeDate":1594797355307,"thresholdsConfigChangeMsEpoch":1594797355307}},"completeSLAData":[{"succeeded":true,"goalTime":28800000,"goalTimeUnits":{"weeks":0,"remainingDaysWithinWeek":0,"remainingMillisWithinDay":28800000,"breached":false},"elapsedTime":511715,"remainingTime":28288285,"remainingTimeInDaysAndMillis":{"weeks":0,"remainingDaysWithinWeek":0,"remainingMillisWithinDay":28288285,"breached":false},"calendarName":"Sample 9-5 Calendar","startTime":1594807955256,"stopTime":1594808466971}],"metricId":6,"definitionChangeDate":0,"definitionChangeMsEpoch":0,"goalsChangeDate":1594797355341,"goalsChangeMsEpoch":1594797355341,"goalTimeUpdatedDate":1594797355337,"goalTimeUpdatedMsEpoch":1594797355337,"metricCreatedDate":1594797355307,"updatedDate":1594894905350}

  ⚠️ However, it is important to note that the Elapsed Time value in the database will only be stored for those SLAs that have been 'realised/achieved'. So, if you have any SLA for an issue that is not realized, Elapsed time for that SLA will not be stored in the database. In the REST method, you will get Elapsed time for all SLAs (even if it is realized or not).