More JQL Tricks

Well, we’ve made it another week. And let me tell you what a week it was. You guys killed it on the previous post – it’s currently the second most viewed post on the blog – and on it’s way to becoming number one! Thank you all so much!

Today’s topic also mirrors another popular post. I am looking at several more JQL tricks you can use to optimize your queries. Some of these are suggestions that I got in response to my previous post, some are new tricks I’ve learned, and some are stuff I cannot believe I missed. Lets get rolling on this.

Starting with the Basic search

So this was one of the ones I got as feedback from my previous article on JQL, thanks to Ed Gaile. And the trick is so simple I am ashamed I forgot to include it. This trick is when you start building a new query – start in basic mode.  

Starting with the basic search will allow you to define parameters easily. Then, once you do so you can switch to Advanced, you can see and edit the JQL String.

One of the most significant benefits of using the basic mode first comes with adding a time-based parameter. When you do so, this handy pop up will appear to let you define your exact timeframe with no hassles!

membersOf()

We covered previously how powerful the currentuser() function is. It will let us easily customize a single query to whoever views it. Let’s consider this situation.

Your QA team consists of Bob, Lisa, Jerry, Beth, and Chris. You want a query that will return everything assigned to QA. And go!

Your first instinct is to write a query similar to this:

assignee in (Bob, Lisa, Jerry, Beth, Chris)

While functionally correct, this can cause problems. Let us say Jerry is tapped to become a Program Manager. You now have to go back and update your query. Tom joins the team now to replace the vacancy left by Jerry. Another update. What if there is another way?

If your team is within a JIRA group, you can reference that group directly in a query. Let’s assume the same situation, but we know the JIRA Admins use group jira-qa to manage permissions specific to QA. We can then replace the above query with the following:

assignee in membersOf("jira-qa")

The benefit here is that the Admins will update the group to change out Jerry’s job function and on-board Tom, so your query requires no changes to stay up to date.

If your instance is using Active Directory syncing, you might also be able to leverage AD Groups directly in JIRA. The benefit here is as your AD Groups are updated, the change is automatically reflected in JIRA, and therefore your query!

lastLogin()

So this one is very situational. Let us assume that as part of a triage operation, you want to make a query that shows everything that users created since the last time you logged into JIRA.  

You can write a query that looks for everything created in the past day, but that will only get half the data from the weekend. Plus, for most weekdays, that will include issues you’ve already triaged. Not ideal.

That is where the function lastlogin() comes in. This function will return a timestamp from your last login so that you can find new issues. In practice, it looks something like this:

issuetype=Bug and createdDate >= lastLogin()

This function also works with updatedDate and resolutionDate, so you can find issues that were updated or resolved since your last log in. For Triaging and reporting, definitely a good thing to have in your toolbox.

Filter using another filter

All right, here’s one I found as research for this post. And honestly, I didn’t even know it existed. You can reference another filter in your query, and add onto it!

filter = "Filter Name" AND ...

Yes, this is a real thing! You can use this trick to narrow down the results of a query further, or two build several variations on a query while abstracting out the common parts. It amazes me to this day that I can have studied this platform extensively for years, yet I can still find something I genuinely did not know!

Subscribing to a Saved Filter

So, let us go back to the Triage example from the lastLogin() section. You’re happy with the results, but you hate that you have to log in to JIRA each morning to get started. Maybe you’d like to review the results on the train while on your way to get a head start? You can use JIRA’s Mobile App, but that won’t remind you to do it.

There is a way to get JIRA to email you the result of your query on a set schedule. This trick is called a subscription, where JIRA will run the saved filter based on your permissions, and send you the results via email.  

To set up a subscription, first navigate to your saved Filter. Then click “Details -> New Subscription”

Once there, you can set up which groups (to which you are a member) will receive the query. You can also select “Personal Subscription” (which is the default) to send it to yourself. Afterward, select how often you would like to receive the filter results and click “Subscribe.” Congratulations, you will now start regularly receiving the Query results.

Searching through the Past

So, it is now time for “Admin Storytime” with Rodney. Once upon a time, a Project Manager came up to the brave JIRA Admin nervous. One of the villagers wasn’t too smart and had closed many issues that shouldn’t have been. The Project Manager was scared that this would mess up their project, so he needed our hero’s help in finding all the closed issues and reversing the dreadful curse. 

So what did the hero do? He did a historical searched using the Changed functionality. 

For those not familiar, you can use “changed” to take a look at what a field value was, who changed it, what they changed it to, and when they changed it. In this case, the query looked like this:

status changed to Closed by Villager after startOfDay()

I would note that not all fields support this kind of searching – in fact, the vast majority doesn’t. Status, however, is one I can usually search the history on, so it’s handy to keep in mind.  

sorting

Sorting your returns

So, most of the time, the order you get issues back in is not essential. But sometimes, it does help you understand the data if you can sort it by one metric or another. This situation is where the “Order By” function comes in.  

reporter = currentUser() order by created ASC, updated DESC

This function allows you to specify what order you’d like to receive the results. For example, you can sort it by date created in Descending (DESC) to get a list of results sorted from the newest to the oldest. Put the field you’d like to sort by, and then the direction you want to sort on, and you are off to the races.

You can also sort by more than one field. For example, you can first sort by the assignee to get an alphabetized list of issues by assignee, then sort by Updated to get the newest issues touched by each user on top of each user’s results.

I should note that not all gadgets respect the Order By function, so your mileage may vary on Dashboards, but it’s still something handy to keep in mind.

So, what are your favorite JQL Tricks?

As I have noted, some of these I got from your comments on the previous JQL post. So what other JQL tricks do you have? Post your favorite tricks, and I may share them on a future post!

If you are in the Atlanta, GA area next week, I’ll be speaking during the first half of the (Remote) Atlassian Community Event on how to set up Nagios to monitor Atlassian Applications. I hope you will join us!

Don’t forget to comment, like, and share this post on social media of your choice to help spread the word! As I said, you guys have been killing it this month on the numbers, and I want to see if we can get an all-time high! Also, be sure to follow me on twitter at @theJIRAguy.

And as one last note, check out the newest Atlassian Blog on the block, run by my good friend Kris. She will also be posting weekly! Her blog will be more focused on the Super-User aspect, so it should be an different yet much needed point of view. http://www.notesfromkris.com/

But until next time, my name is Rodney, asking, “Have you updated your JIRA issues today?”