Tip Tuesday | Jira Service Management Cloud – Churn Stats

Mark Cann -

A bit of a departure from our normal tech suggestions, but for any Jira Service Management admins.

I was trying to work out a way of reporting ticket churn – tickets open at the start of a period, new tickets opened during period, tickets closed during period, tickets remaining open at end of period.  The trouble I was having was that, whilst tickets opened a period is reasonably straightforward,

 created >= "yyyy/mm/dd" AND created < "yyyy/mm/dd"

the other categories depend upon the state of tickets as was at a point in time – so unless you check how many tickets are open on the day in question, how do you find out after that day has passed?

The answer is the very handy JQL clause of was…on.  This enables you to interrogate the status as it was at a point in time.  Combining this with time-based functions enables a clear picture of how things stood at a point in time to be reported – so for example, to ascertain tickets that were closed last month:

created <= endOfMonth(-1)
AND status was not in (Resolved, Closed) on startOfMonth(-1) AND status was in (Resolved, Closed) on endOfMonth(-1)
  • The first line considers just those tickets created before the end of last month (ie, ignores anything created from the start of the current month onwards)
  • The second line includes those that were not resolved or closed at the start of the previous month but were resolved or closed at the end of the previous month

Similarly, those tickets that were already open at the start of the preceding month:

created <= endOfMonth(-2) AND status was not in (Resolved, Closed) on endOfMonth(-2)

There are then various ways you can use this.  Perhaps the simplest is to create a queue for each query within each Service Desk, but if you need to report this data – and particularly if you have a number of desks you are responsible for – you can script either the queues or the raw JQL using the REST APIs…for example, running the queue:

curl -s --request GET \
  --url 'https:// mycompany.atlassian.net/rest/servicedeskapi/servicedesk/TESTCO/queue/1198/issue?start=0&limit=50' \
--user 'first.last@mycompany.com:key'

Or running the JQL

export TEMP_PROJECT=TESTCO
### open at start last month
curl \
-X POST \
-H "Content-type: application/json" \
--user 'first.last@mycompany.com:key' \
--url "https://mycompany.atlassian.net/rest/api/2/search" \
--data '{
  "jql":"project = \"'$TEMP_PROJECT'\" and created <= endOfMonth(-2) AND status was not in (Resolved, Closed) on endOfMonth(-2)",
  "startAt":0,
  "maxResults":100,
  "fields": ["key", "summary"]
  }'

Note that in this example the name of the ServiceDesk has been accessed via a variable, which makes it easier to re-use the line in a loop.

You can then combine this with a JSON tool like JQ to extract just the key data you are after and format it in CSV format…

curl \
-X POST \
-H "Content-type: application/json" \
--user 'first.last@mycompany.com:key' \
--url "https://mycompany.atlassian.net/rest/api/2/search" \
--data '{
  "jql":"project = \"'$TEMP_PROJECT'\" and created <= endOfMonth(-2) AND status was not in (Resolved, Closed) on endOfMonth(-2)",
  "startAt":0,
  "maxResults":100,
  "fields": ["key", "summary"]
  }' \
 2>/dev/null | /opt/homebrew/cellar/jq/1.7.1/bin/jq -r '.issues[] | [.key, .fields.summary] | @csv'

which you can then save as a file, store elsewhere, or simply run a line count on to get your stats.