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.

Subversion – Building a Complex Release Structure Using Externals

I was thinking about how to structure a Subversion repository to cope with multiple internal components which have to be associated with a release as intermediate components (for example plugins).

The Problem

Let’s say you have a project which has multiple libraries which are developed by teams on individual release schedules. Normally you would go about this with the usual repository structure with each component having its own trunk, tags & branches combinations as shown below.

my-app-repo----+-> component_1
               |        +-> trunk
               |        +-> tags
               |        +-> branches
               +-> component_2
               |        +-> trunk
               |        +-> tags
               |        +-> branches
               +-> ...

For example you might have an application which is made up of individual sub-applications which are being worked upon by separate development teams:

  • Central Core Component
  • User Interface Component
  • Database Interface Component
  • etc.

Now each team has sat down and designed the interfaces which they’re going to expose to the other groups and have agreed with them that the API’s are suitable for the other groups to use. So each team now goes away and carries out the development of their subsystem within their part of the repository and merrily releases these periodically on their own determined release cycle. Each time they carry out one of these releases, like all good developers they *tag* the release within their application’s tags directory. You might end up with something like:

my-app-repo ----+-> core_app
	       |	+-> branches
	       |	+-> tags
	       |	|     +-> release_1.0
	       |	|     +-> release_1.0.1
	       |	|     +-> release_1.0.2
	       |	+-> trunk
	       +-> DB_app
	       |	+-> branches
	       |	+-> tags
	       |	|     +-> REL-0.1
	       |	|     +-> REL-0.2
	       |	+-> trunk
               +-> user_app
	        	+-> branches
	        	+-> tags
	        	|     +-> R1
	        	|     +-> R2
	        	+-> trunk

A problem arises when you need to perform some kind of release of a bundle of the applications together as a bundled release. How do you identify which releases of each of the components is part of the bundle?

A Potential Solution

ell one way of resolving this could be to have a separate bundled-releases tag directory at the top level of the repository and then to copy each of the separate tags into this directly. The obvious problem with this is that each copy operation would then be another revision within the repository and could result in changes being introduced to the tags as they get copied across.

A Better Solution

Another solution to the problem would be to make use of {{svn:externals}} properties within a top level directory to identify the revisions and paths of each of the tags which make up the bundled release.

    1. First of all we need to create the directory in which the tags will exist:

svn mkdir -m "Tag directory for bundled releases" $REPO_ROOT/bundled-tags
Committed revision 15.

    1. Now update our working copy with the new directory:

$ cd my-working-copy
$ svn update
A bundled-tags
Updated to revision 15.

    1. We need to create a directory which will contain the tags and add an svn:externals property upon it:

$ cd bundled-tags
$ svn mkdir bundled-1.0
A bundled-1.0

    1. The property should look something like:
core_app        $REPO_ROOT/core_app/tags/release_1.0
DB_app          $REPO_ROOT/DB_app/tags/REL-0.1
user_app        $REPO_ROOT/user_app/tags/R1
    1. Then finally of course commit the change back to the repository.

$ svn commit -m "Added bundled-tag for release 1.0"
Adding bundled-tags\bundled-1.0
Committed revision 16.

So now if someone checks out the tag which corresponds to the bundled release 1.0 they will get the following:

$ svn co $REPO_ROOT/bundled-tags/bundled-1.0
 U bundled-1.0

Fetching external item into 'bundled-1.0/core_app'
 A bundled-1.0\core_app\core-app.c
 ... core_app files ...
 Checked out external at revision 16.

Fetching external item into 'bundled-1.0\DB_app'
 A bundled-1.0\DB_app\db-app.pl
 ... DB_app files ...
 Checked out external at revision 16.

Fetching external item into 'bundled-1.0\user_app'
 A bundled-1.0\user_app\user-app.c
 ... user_app files ...
 Checked out external at revision 16.
Checked out revision 16.