G’day JQL Ninjas in training! You are now on your way to creating more effective queries in JIRA to have a clearer view on your projects, teams, and work ahead. If you’ve not seen lesson 1, I’d suggest reviewing that post as we will build on a number of concepts talked about in that post. You should be comfortable with simple JQL queries as well as using the AND and OR keywords at this point.

Functions: Why they are cool?

In JIRA, fields store data related to an issue. Issue fields include: priority, issue key, summary, etc. Functions however pack a lot of power inside of them. Functions can contain a lot of complex logic that is easy for a user to access in a simple manner. They can optionally take input and then always return a result. For example, JIRA supports a function called membersof(). It returns true if the user is a part of the group and false if they are not.

project = pwc AND assignee in membersof('test-engineering')

This query returns all of the issues that are assigned to test engineers. Groups can be defined inside of JIRA or come from existing groups in your company’s preexisting directory servers. Functions are powerful because they can react dynamically as the environment changes. If users are added to the test-engineering group, this query will dynamically update. It is more flexible solution than having assignee in (user1, user2, user3). Let’s see how we might see what issues got fixed in the last release.

project = pwc AND status in (resolved, closed) and fixversion = "Sprint A"

This will return all of the issues that were fixed in that particular release. What if we want a list that always shows the issues fixed in the last release whenever it was run? That’s very easy to do with a function.

status in (resolved, closed) and fixVersion = latestReleasedVersion(PWC)

What’s the difference here? Eventually the team will release sprints B, C, and D. The first query only returns the list of issues resolved in Sprint A. The second one queries JIRA to find the last released version for project PWC. Thus as sprints B, C, and D are released this query will pull the right data for those releases. Pretty cool, aye?

History: What happened back then?

JIRA 5 was a major step forward for the language of JQL. In an issue’s lifecyle there are two types of data that are useful to drive projects. In all the queries we have discussed to this point, the data is reflective of the current time the query was run. In the diagram below we can see several prior issue states that hold interesting data. JIRA stores each step an issue goes through in its lifecycle.  It is also known as an issue’s change history.

History

If you wanted to see all of the issues assigned to John Smith in project PWC, a simple query could find that data:

project = PWC AND assignee = jsmith

With JQL in JIRA 5 we can now query an issue’s historical data. Why is this important? We can now use JQL to see how our projects change over time. For instance, what if we wanted to see the bugs that John Smith resolved in the PWC project? In JQL we can use the following query:

project = PWC AND status CHANGED FROM "In Progress" TO "Resolved" BY jsmith

Sometimes issues that fail verification or get reopened are of special interest to see why a change didn’t work as intended. To find those bugs it’s easy to run this query:

status CHANGED FROM "In Progress" TO "Open"

To see what issues were in flight during the current week we can use the following JQL:

status was ("In Progress") DURING (startofweek(), endofweek());

At the end of the year it can be nice to see how many issues you resolved during the year. To do so, all it takes is a little JQL:

resolution changed to "Fixed" by currentUser()

during (startOfYear(), endOfYear())

Scoping and Sorting: You’ve got to do it mate!

Your time is valuable. The real power of JQL is narrowing down the results of your query so that you have the right set of issues to make a decision about your project. Part of a good query is knowing how to get data out of JIRA. The other part is knowing how to engage you and your customers with the result. Let’s start with two concepts that are critical to effective data presentation:

  • Scoping – focusing your query so it pulls the right amount of data so the user sees only the information relevant to the current item at hand
  • Sorting – ordering your data such that the most critical set of data is listed first

Scoping

Let’s take a look at a visual representation of a set of issues that get returned from JIRA.

scoping2

When starting searching JIRA it’s easy to perform searches that return too many issues. In the above example both diagrams have the same number of issues we care about (issues with A on them) but the first query isn’t specific enough. How does this play out in the real world? Development managers often have to find areas of risk in their programs. Good queries often take a bit of refinement as they get developed. Let’s see if we can find some risky areas in our project PWC.

Starting with open issues:

project = pwc AND status = open

We find that query returns too many issues as it includes everything from our backlog as well as issues we are currently working on. We can tighten it up a bit by eliminating the backlog issues.

project = pwc AND status = open AND fixVersion = "Current Sprint"

This result is better, but I’m really curious to know what didn’t make last sprint and got moved into this sprint

project = pwc AND status = open AND fixVersion = "Current Sprint"
AND fixVersion WAS "Last Sprint"

Now that I have the issues that I really care about in front of me, I’m able to look at each of these issues with a high degree of focus and see why they didn’t make the last sprint. Was it an esimation problem, requirements, or something else. The issues were there in the first query, but they were too hard for me to find manually. Good query creation is an interative process: query, review, see if you can focusing the results. The more you do, the easier it becomes.

Sorting

Let’s take another look at issues returned from JIRA. This time we have our query properly scoped.

sorting2

Both diagrams have the same number of issues in them. The right set is easier to process as we’ve given the reader a natural guide to follow. The natural guide is extremely important to add in. The easier the data is for you and your readers to consume, the more effectively the data can change your next action. Let’s say we want to see all the bugs open for the team in a particular sprint:

project = pwc AND fixVersion = "Current Sprint" AND status = open

What are we trying to glean from this data? If we are trying to understand risk we might want to see the list ordered by priority and then by engineer so that we can easily see if one engineer has two high priority bugs. JQL has a keyword ORDER BY that tells JIRA to sort the results. The above query could be exteded to include sorting:

project = pwc AND fixVersion = "Current Sprint" AND status = open
ORDER BY priority, assignee

JIRA will first order the list by priority and then sort by assignee for all of the issues with the same priority. Let’s look at another example examining the incoming bugs to our project. We want to see any new critical or blocking bugs that have come in recently to see if recent checkins have decreased stability.

project = pwc AND priority in (blocker, critical) AND created > -2w
ORDER BY created DESC

The query controls for priority and limiting the created time properly scope the query. We’re only interested in critical issues that came in recently. What the sorting does is show us the most recent issues first. We use the DESC keyword to sort in reverse (newest to oldest). That way we focus our audeice on the most important issues first. Not everyone will look at each issue in your reports. Always sort your queries so that the most important issues show up first.

One Caveat: If another system will process your resuts before a user sees them, don’t sort. Sorting twice will degrade the performance of your application.

In closing

At this point you should be feeling pretty good about querying JIRA to pull issues with JQL. We’ve covered basic structure, functions, current data, historical data, sorting and scoping. If you’d like to explore more JQL, take a look at the JQL Reference. In the next sessions we will be covering

Stay tuned in the next few weeks to learn more tips and tricks about JIRA. W Want to learn even more? Sign up for the JIRA Insiders Email.

Subscribe