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?”



JQL – How have we not talked about this already?

Hello everyone! How has your week been going? This week we are going to talk about JQL – and honestly, I’m not entirely sure how this hasn’t come up already. I know I’ve provided a few queries here and there to help you in other topics, but today I’ll be talking about some of my favorite tips and tricks to get the most out of your queries. So without any more delays, let’s get into it.

Always about the Docs

So it’s been a while since I’ve included this section – but I feel this subject could be helped with some further reading. So for this, I am referencing two documents:

I should note that the function reference is for JIRA Cloud – so it does have some new functions that have not made it to server. However, the options I’m covering here will work for JIRA Cloud, JIRA Server, and JIRA Data Center.

Currentuser()

So, this first trick is one of my favorites, as it allows you to customize a query to be specific for whoever is viewing it. In your query, you can set a user field equal to currentuser(), and the function will return whoever is logged in and viewing the query. For example, let’s take the query:

assignee = currentuser()

And let us take a look at what that looks like when running from the Admin account, and my account:

Each one returns whatever is assigned to the specific user. What’s more, this would carry over to any dashboard that uses this query – allowing you to customize a single dashboard to whoever views it.

This also works with any user field: reporter, assignee, watcher – and even custom fields. This is a handy trick to have if you don’t like to spend your day making queries and dashboards…I mean, who would enjoy that, am I right…

Working with time

So, when I’m giving some training for JQL, the most common problem people have is working with time. “How do I search for stuff in the past?” “How do I work with time?”…it seems to be a challenge.

Here’s a trick that’s always helped me. Most databases store time in the Unix Epoch time format – which is simply counting the seconds between midnight of 1 Jan 1970 and today. This means that any time in the past will be less than the current time, and any time greater than now will be in the future. Same for JQL. The past is “less than” the timestamp you are looking at, and the future is “greater than”.

However, JIRA also gives us some handy functions so we are not having to spell out particular date. First off is now(), which simply returns the current timestamp whenever it’s run. Handy when just trying to find out what’s past due, such as in this example (courtesy Atlassian):

duedate < now() and status not in (closed, resolved)

In the above query, duedate < now() returns everything that has a duedate in the past, and status not in (closed, resolved) returns everything that is currently open – giving you an idea what is past-due.

DayWeekMonthYear
startingstartOfDay()startofWeek()startaOfMonth()startOfYear()
endingendOfDay()endOfWeek()endOfMonth()endOfYear()

These are great for establishing a time frame. The really powerful thing about the functions on this table is they take parameters, meaning you can adjust them forward and backward – to say find all the issues resolved last month:

resolutiondate > startOfMonth(-1)  AND resolutiondate < endOfMonth(-1) 

In this query, we shift both startOfMonth() and endOfMonth() by -1. The minus (-) tells us it’s a previous time, and the one (1) tells us to shift one whole month, meaning both of these times return values for the previous calendar month. This gives us a handy time-box for our queries – which is great for a report.

Sprints and Versions

So, this section is for my JIRA Software teams out there.

When I’m a part of Agile teams, I like to have a dashboard we can all view that tells us what’s going on in the current sprint. For this, we can use the opensprints() function. I like to pair this with a project key query so that I only get the open sprint for my project, such as:

Project = "Project Name" and Sprint in openSprints()
Pardon the blur…I actually don’t have any open Sprints in my test instance, so I had to use my work instance.

A note on openSprints() is that it only supports the operators IN and NOT IN. Using an equal sign will cause the query to return an error.

There is also the operators futureSprints() and closedSprints(). However, these return ALL future and closed sprints, respectively, so further filtering will be needed to focus on any particular sprint (such as Sprint = “Name”)

So, let us say you are in a situation where you care more about what has and hasn’t been released than what is in any particular sprint. Then you’ll be interested in the functions latestReleasedVersions() and earliestUnreleasedVersions(). These operate on the fields AffectedVersion, FixVersion, as well as any custom fields with type Version. Let’s say you want to know what bug-fixes have gone out the door:

issuetype = Bug and fixVersion = latestReleasedVersion()

The neat trick about this query is that it will automatically update if your team releases a new version, meaning if you use it in a dashboard, it will always show the bugs fixed in the last released version.

Filtering out sub-tasks

So this one comes up often. A manager wants a dashboard, but they don’t want to get bogged down in the minutia of the sub-tasks. This a handy trick I use to filter out all sub-tasks from a query. I simply and the existing query with this clause:

issuetype not in subtaskIssueTypes()

It’s a simple thing to add but is an easy way to filter out all sub-tasks. Because of how it works, if you add any new sub-task types to the project in the future, the filter will still work without needing to be updated.

Cleaning up watched issues

So, JIRA in its current form sends out A LOT of emails.

Just a *few* emails…

Atlassian has heard our cries, and they are working on it, but in the meantime – this is where we are. And as an Admin, you will be touching a lot of issues – which will put you on their watchers list automatically. So I find it helpful to run this JQL and clean things up every few months.

watcher = currentuser() and project not in ("Home Project A", "Home Project B", ...)

This gives me a list of all issues I’m watching on an instance that is outside of what I consider to be my “Home Projects” I use to organize my work. From there you can run a bulk change on the list, and on Step 2, choose the “Stop Watching Issues” operation. I don’t know if this honestly counts as a JQL trick, but it has helped keep me sane many a time.

And that’s all for this time!

Did you learn anything new? What are some of your favorite JQL tricks that I didn’t cover? If I get enough I’ll do a part 2 to this!

So it’s been a busy time for me. I decided to take advantage of the current time I have to study for my next ACP exam. It’s scheduled for May 8th, which doesn’t give me too much study time left. But hopefully, I’ll be sending out good news the following week!

My Wife and I also have been working on 3D printing face-shields for first responders – you can read about our efforts (okay, mostly her efforts) on her blog here: https://cadstories.com/2020/04/17/engineering-and-covid-19/

I also have a twitter account for the blog now! If that’s your preferred source of Social Media, give me a follow! I’ll be posting blog posts there, as well as anything else Atlassian related I find interesting! https://twitter.com/theJIRAguy You can also sign up here to get an email every time I publish a new blog post! Simply use the sign-up form at the bottom of this post!

Until next time though, this is Rodney, asking “Have you updated your JIRA Issues today?”