JIRA – Epics all the way down …

Epics within Epics within …

One of the issues which I’ve encountered with JIRA is how to organise programmes of work which then contain sub-programmes, which might contain sub-sub-programmes … all the way down until you eventually reach the stories.

Within our JIRA service we have an issue link for an is implemented byimplements relationship between issues which can be used to construct this kind of hierarchy of relationship.

For example, within the SDLC Tools team we had a programme of work associated with the deprecation of the CQ2SVN integration between ClearQuest and Subversion (a programme of work necessary to deprecate ClearQuest). This work consisted of (items in blue are Epics):

  • Developing Subversion hooks to integrate with JIRA
    • Stories associated with the development work for the hooks
  • Developing a Release Note generation tool
    • Stories associated with the development work for the generation tool
  • Migration of each team with CQ2SVN integration to the new mechanism
    • Stories associated with the activity to migrate teams off CQ2SVN
  • Scheduling the shutdown of CQ2SVN
    • Testing and validation of the shutdown
    • Execution of the shutdown

This was done as a hierarchy of Epics shown below:



Pulling back all of the stories

Within the structure there are two separate sets of links:

  • is implemented byimplements
  • is Epic ofhas Epic

To find the full scope of the CQ2SVN Deprecation above, we need to pull back all of the stories from all of the Epics which are linked at any level to the top level Epic – we do this in two stages:

Finding Every Implementing Epic

The ScriptRunner plugin which we have installed on JIRA includes the function linkedIssuesOfRecursive() which we can use to perform this. This takes two arguments:

  • Subquery
  • Link Type

In our example above the top level “CQ2SVN Deprecation” Epic was CBST-689 – so to find every Epic implemented by this we can use the following JQL query:

issueFunction in linkedIssuesOfRecursive("issue = CBST-689", "is implemented by")

This is broken down into two parts:

We then saved this as a filter (we named it CQ2SVN – is implemented by) because we’re going to come back and re-use this later on.

Finding Every Story

Filters can be specified as part of a JQL query, therefore they can also be used in the subquery part of the linkedIssuesOfRecursive() function.

Making use of this function again to come up with to find every story which is part of these Epics we have the following JQL query:

issueFunction in linkedIssuesOfRecursive("filter = \"CQ2SVN - is implemented by\"", "is Epic of") OR issueFunction in linkedIssuesOfRecursive("issue = CBST-689", "is Epic of")

This is broken down into two parts:

  • Find all of the Epics returned by the CQ2SVN – is implemented by filter
  • Recursively return all of the issues linked to any of these which are linked via the is Epic of link


  • Recursively return all of the issues linked to the top level CQ2SVN Deprecation Epic via the is Epic of link

This returns a list of all 104 stories which contributed to the implementation of the CQ2SVN Deprecation Epic.

In order to re-use this query (e.g. on dashboards, etc.), we saved this as a different filter (we named it CQ2SVN Items).

For example, the filter could then be extended to only show the outstanding items:

filter="CQ2SVN Items" AND resolution IS EMPTY

jira-maven-plugin 0.4

One of the features which I’ve been playing with in my day job has been to do with the use of JIRA‘s versions. Historically, these haven’t been used very effectively in our organisation and it wasn’t until we carried out a migration of development teams from ClearQuest to JIRA that teams had to start using these with greater rigour.

As a personal side project to this I spotted that there was a plugin for Apache Maven which allowed the creation/updating of JIRA’s versions from within a project’s Maven Project Object Model (pom.xml).

The original version which was available made use of JIRA’s SOAP API to perform the updates to versions within a JIRA project. The deprecation of the SOAP API had been announced back in May 2015 – so there was a definite need to look at porting this to JIRA’s REST API instead.

Since then I’ve knocked together some changes to the code which have been pushed upstream to move it across and we’ve reached the point that a version 0.4 release is possible.

This is still definitely version 0.x quality software but it now makes use of Maven’s encrypted credentials and JIRA’s REST API so that’s pretty neat.

To make use of it make sure your repository service is proxying the Sonatype OSS public repository at https://oss.sonatype.org/content/groups/public/ and then add the following dependency to your POM:


Counting the number of JIRA and Confluence users

Unless you have one of the “unlimited” user licenses for Confluence & JIRA it’s important to keep a track on your user growth. The Atlassian applications don’t provide historic tracking of the user base growth so this is something which you need to introduce yourself.


Within JIRA, seeing how many licenses are in use is relatively straightforward. By accessing the License Information Admin page (this is located at http://JIRA_ROOT/secure/admin/ViewLicense!default.jspa) at gives you the following information:

license info

So you can see here that we have 785 active users out of a limit of 2000, so nice an healthy.

Now I could gather this information on a weekly basis manually but that would be a PIA so how can I get the same information automatically?

Well there’s quite a simple database query for figuring out the number of active users:

SELECT COUNT(user_name) AS num_users
      cwd_user, cwd_membership
      cwd_user.id = cwd_membership.child_id AND
      cwd_membership.membership_type='GROUP_USER' AND


So how about the same for Confluence?

Well within Confluence (4.3.x anyway) there’s no straightforward way through the GUI to identify the number of active users. The easiest way is therefore to go direct to the database.

In our case we’re using an MS-SQL database for our Confluence data storage so the query looks like:

SELECT count(DISTINCT cwd_user.lower_user_name) AS num_users
     cwd_user INNER JOIN cwd_membership cwd_membership ON cwd_user.id = cwd_membership.child_user_id
     INNER JOIN cwd_group cwd_group ON cwd_membership.parent_id = cwd_group.id
     INNER JOIN spacepermissions spacepermissions ON cwd_group.group_name = spacepermissions.permgroupname
      spacepermissions.permtype = 'USECONFLUENCE' AND cwd_user.active = 'T'




JIRA – Finding Outstanding Issues and Their Outstanding Blockers

Say you have three projects in JIRA (let’s call them AAA, BBB and CCC). Now there might be a situation where you have issues in project AAA which are blocked by issues in project BBB or CCC.

So what would the necessary JQL query look like which would return all of the issues which are outstanding across all of the projects necessary to deliver project A.

Fortunately this kind of thing can be done with the freely available Script Runner plugin. This provides a number of scripted JQL functions which can be used to construct more complicated queries for returning more detailed information.

So the query which I came up with looks like this:

(Project = AAA AND Status NOT IN (Closed, Resolved)) OR (Project IN (BBB, CCC) AND Status NOT IN (Closed, Resolved) AND issueFunction IN linkedIssuesOf("Project = AAA", "is blocked by")) ORDER BY status


This breaks down into two parts separated by the OR clause. This first one returning all of the issues in project AAA which are outstanding:

Project = AAA AND Status NOT IN (Closed, Resolved)
The second part:
Project IN (BBB, CCC) AND Status NOT IN (Closed, Resolved) AND issueFunction IN linkedIssuesOf("Project = AAA", "is blocked by")
Is made up itself of three components:
  1. The first part limits the search of blockers to only those within the BBB and CCC projects:
    Project = AAA AND Status NOT IN (Closed, Resolved)
  2. The second part takes those issues and only looks at those which are not Closed or Resolved
    AND Status NOT IN (Closed, Resolved)
  3. The third part searches Project AAA for issues which have the is blocked by link and returns the issue which is linked to:
AND issueFunction IN linkedIssuesOf("Project = AAA", "is blocked by")
Combining these three together gives the open issues which are blocking issues in project AAA.
Of course you could also extend this part of the query to also specify the various aspects of the search for blocker issues.