In Part 1 of this series, we went through how Atlassian Support uses a shared view to manage our support queues. In this post, we’ll get into how we modeled our goals and came up with the code we use. The focus of this article is to explain how we visualize our queues rather than our support process itself. So, in the article and in the sample code provided, we’re going to be talking about a simple shared queue of incoming issues waiting to be assigned to an engineer, and not the exact process used by Atlassian.

pre.code { border: 1px solid #9999ff; color:#999999; padding:1em; margin:1em; } pre.command { font-style:italic: font-size: .75em; } div.note { background-color:#ffffcc;border:1px solid #999999; padding:2em;}

Before we start on the technical details, let’s talk about our team goals and what questions we need to answer to be able to visualize how well we’re meeting our goals. As we discussed in part one of the series, we have a goal of providing an initial response for each support ticket within a set period of time. You can see the published goals in context on our support policy page, but in general, we have four priority levels, and the highest priority issues are handled more quickly than lower priority issues. Taking that as our goal, we have a few practical follow-up questions.

What Issues Do We Display in Our Shared Queue?

All of our engineers pull from a single shared queue of incoming issues that haven’t yet been assigned to an engineer. When an issue first enters the shared queue, it needs someone to take ownership of it and work on it. Once someone has taken ownership of the case, the issue shouldn’t be visible to the rest of the team.

Each engineer supports one or more products. We need to make sure that the issues displayed to each engineer match their skills and training. So, we need to know what product the issue is related to and what products the engineers viewing the queue can handle.

Here’s a summary of all the questions we have to answer, and the data we’ll use to answer them:

  • Is the issue assigned? We’ll use Jira’s built in “assignee” field to determine this.
  • Is this issue for the right product(s)? We’ll use Jira’s built-in “project” field for this, and for the purposes of this example, we’ll assume we have a single project whose key is “TEST”.
  • Does this issue require any action from the support team? For this example, we’ll assume that we only care about issues whose status is “Open” (incoming issues) or “Waiting for Support” (a custom workflow step we use to distinguish cases that are waiting for us rather than the customer).

That gives us the pool of issues we’re concerned with. Based on this and a working understanding of the Jira database schema, we can retrieve a list of issues using an SQL query like:

ji.pkey as Key,
ist.pname as Priority,
ji.summary as Summary,
ji.assignee as Assignee
from jiraissue ji
join issuestatus ist on (
join project proj on (
where assignee is null
and proj.pkey in ('TEST')
and ist.pname in ('Open','Waiting for Support');

The query above limits the scope of our shared view, but doesn’t bring in all the data we need. To fix that, we need to ask ourselves another question.

What Case Should We Work on Next?

We want to make sure that we take the most urgent case requiring action. We ask customers questions up front to help us understand the severity of the problem. Based on their answers, the priority of the issues is set. Here are the definitions we use for priorities and the initial response times we strive for (taken from our support guidelines:

Level of Severity Description of Severity Initial Response Within:
Level 1 Production application down or major malfunction causing business revenue loss resulting in majority of users unable to perform their normal functions. 1 working hour
Level 2 Critical loss of application functionality or performance resulting in high number of users unable to perform their normal functions. 4 working hours
Level 3 Moderate loss of application functionality or performance resulting in multiple users impacted in their normal functions. 8 working hours
Level 4 Minor loss of application functionality or product feature question. 24 working hours

In general, we take higher priority issues before lower priority issues. We also have a rule that says that Level 1 issues (the most urgent) should be displayed to the whole team whenever they require action, even if they’re already assigned.

To make this decision in code, we need a few more pieces of data:

  • The priority of the issue: We’ll use the built-in priority field to answer this question.
  • How long the issue has been waiting: Time spent waiting is calculated as the current date minus the last time the issues was updated. We’ll calculate this in Java, but we need the “updated” field for this calculation.

The first two new fields can pretty easily incorporated with a couple of additional joins.

ji.pkey as Key,
prio.pname as Priority,
ist.pname as Status,
ji.summary as Summary,
ji.assignee as Assignee,
ji.updated as Updated
from jiraissue ji
join issuestatus ist on (
join project proj on (
join priority prio on (
where assignee is null
and proj.pkey in ('TEST')
and ist.pname in ('Open','Waiting for Support');

That gives us an SQL statement that brings in all the right issues and the data we need. Everything else is handled in code when we start importing the data.

Knitting Everything Together Using Java

We use Java code to:

  • Retrieve the list of issues using the SQL above.
  • During the import, “weight” the issues to classify them at an alert level based on their priority and the time spent waiting. The more urgent an issue is, the more quickly its alert level increases.
  • Display the list of issues, one issue per row, with hints to pick up the right styles for each field and alert level. Alert levels are represented using different background-colors. Our engineers work from the top of the queue down, so the alert levels are just a visual indicator of the health of the queue.

Sample Code

Attached is a source bundle and sample XML backup that will help you run the demo on your own. In addition to the source bundle, in order to make use of the sample materials, you will need:

  1. Java 1.5 or higher
  2. A test database server (the code is written to use a PostgreSQL database)
  3. A test instance of Jira 4.1.2 that connects to the test database server.
  4. Maven 2.0 or higher

Try the Sample Code

Our approach was originally designed to be run from within a beanshell macro in Confluence, but can also be used to generate HTML output (including styles) that can be viewed with a web browser. To try the sample code:

  1. Download the source bundle.
  2. Import the sample backup into your Jira instance. The backup contains only one user, admin, whose password is admin.
  3. Create a few sample issues in the included sample project.
  4. Edit and configure the database connection settings to point to your Jira instance. If you’re using a database other than PostgreSQL, you’ll need to update both the database driver and the pom.xml file.
  5. From the root of the source directory, create an executable jar using a command like:
    mvn package
  6. Run the script using a command like:
    java -jar target/sharedqueue-0.0.1-SNAPSHOT-jar-with-dependencies.jar > sample.html
  7. Open the output in a browser. The output will depend on the priority of the issues you’ve created, but it should look something like:
    Shared Queue Sample Output

These materials are provided as an example, hopefully they’ll help give you ideas about writing code to visualize your own shared queues.

What’s Next

We’re continuing to use our shared queues daily, and the fact that we use a single view lets us implement new ideas and process changes across our distributed team much more quickly. We’ve already transitioned to a Jira plugin that makes direct database queries and displays static output. We’re in the process of writing the next version of the plugin, which (among other things) will:

  • pull from a Jira search filter instead of direct database queries
  • provide a rule engine to allow users to define how issues are weighted and displayed real-time
  • provide a gadget that can refresh itself real-time as the state of the queue changes
  • work well with the full-screen Wallboard plugin

Stay tuned for more news.

Software Support Best Practices: How Atlassian Sup...