How to get a list of tickets excluding those with 0 elapsed time ?
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
While using Jira Service Management reports, average of SLA is calculated based on total elapsed time of all tickets divided by the number of tickets. However there could be possibility where some tickets have 0 elapsed time, it could be because of different request type. Thus these tickets can impact the report and users might not want to include these tickets with 0 elapsed time. In this article we are going to discuss how we can get a list of tickets excluding 0 elapsed time.
Environment
JSM 4.x
Diagnosis
We can easily get elapsed time of an SLA field in millisecond for any Jira Service Management ticket with these two steps.
- With follow SQL query we would be able to identify associated CUSTOMFIELD for the specific SLA field.
SELECT * FROM CUSTOMFIELD c WHERE c.CFNAME = 'Time to first response';
ID |CFKEY|CUSTOMFIELDTYPEKEY |CUSTOMFIELDSEARCHERKEY |CFNAME |DESCRIPTION |DEFAULTVALUE|FIELDTYPE|PROJECT|ISSUETYPE|ISSUESWITHVALUE|LASTVALUEUPDATE|
-----+-----+--------------------------------------+-----------------------------------------------+----------------------+---------------------------------------------------------+------------+---------+-------+---------+---------------+---------------+
10225| |com.atlassian.servicedesk:sd-sla-field|com.atlassian.servicedesk:sd-sla-field-searcher|Time to first response|This custom field was created by Jira Service Management.| | | | | | |- With following REST API, we can get the elpasedTime in millisecond for the particular ticket. Here you would have to replace ITSM1-6 with ticket-id and customfield_10225 with the ID of customfield, we got in last SQL query.
curl -su "username:password" "http://IP:PORT/rest/api/2/issue/ITSM1-6?fields=customfield_10225" | jq '.fields.customfield_10225.completedCycles[].elapsedTime.millis'
37743
Solution
- This query will provide the list of tickets in project ITSM1 having SLA 'Time to first response' with elapsedTime not 0. User would have to replace ITSM1 with their project-key.
SELECT ( p.pkey
|| '-'
|| i.issuenum ) AS issuekey,
cf.cfname AS FIELD ,
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.CFNAME = 'Time to first response'
AND p.pkey = 'ITSM1'
AND cv.TEXTVALUE NOT LIKE '%"elapsedTime":0%'
AND cv.TEXTVALUE LIKE '%elapsedTime%';