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!
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!
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 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?”
Thanks for these tips! Is there a way to use JQL to return a count of linked issues?
Directly, no. JQL will always return a list of issues. However, you can indirectly get these with either a dashboard gadget or looking at the following areas within the query returns: https://thejiraguycom.files.wordpress.com/2020/06/annotation-2020-06-20-225507.png
And that our Quality team members usernames on JIRA are joe, john, and susan,
And that the available statuses in our JIRA system are To Do, In Progress, For Testing, In Testing and Done,
And that we don’t have a status called Reopened,
please what JQL query that will retrieve all issues of type “bug” that has been re-opened by one of our quality team members in the past two months
issuetype = Bug and status changed FROM Closed BY membersof(“quality-group”)
Where Closed is your final state, and quality group is a JIRA Group containing all your quality team members. If your team is not in a group, you can use the below JQL instead. Just keep adding another OR group until you have your entire team covered.
issuetype = Bug AND (status CHANGED FROM Closed BY memberA OR status CHANGED FROM Closed BY memberB)
Nice write, Rodney! I learned something new today 🙂
My personal favorite is to use something like the following to expand on Jira’s default My Assigned Issues filter:
resolution is EMPTY AND (reporter = currentUser() OR assignee = currentUser() OR watcher = currentUser())
Since Jira sucks at telling you what “your work” is, this is one of the closest thing I can think of to attempt to find all the open tickets you’re involved in OOTB. Of course, this could get messy is your name is everywhere.
LikeLiked by 1 person
How to sort the list of stories based on dependency (from parent to child) in JQL.
Do you know a way to find the tickets that have the same reporter? we want to find duplicates but the condition is all the ticket that have the same reporter repeated .
Reporter=duplicate or something like that.
How would I exclude stories that may have multiple comments when one of those comments has specific wording and is from a specific user?
Here is the issue I am trying to solve for. We have automation that writes a comment to a story from a specific account. I know the account and I know what sorts of comment wording I want to exclude. For example: the [email protected] account can write a few automated comments and it uses keywords REQMOV or REQSTUFF or REQACK. Additionally, we will post comments to the story.
So, I need to be able to filter out any stories that contain a comment where commentedby is [email protected] AND the comment contains (actually always starts with) “REQMOV”. What appears to be happening is that if the story has more than one comment and any of those comments are not from [email protected] (or if any of the comments contain something other than “REQMOV”) it will keep the story even if the story DOES actually have a comment that is both from [email protected] AND contains “REQMOV” – and therefore I don’t want that story.
I’ll get the bad news over with first. Unless you have the Scriptrunner App, there is no way to search for issues that have comments by a particular user. If you do run Scriptrunner, though, you can use the following clause to search for issues with comments by a particular user.
issuefunction in commented (” by [email protected]“)
As for the first part your request, yes, Text searches in Jira comments are possible. What you need to do is use a wildcard (*) before and after your search phrase, as shown in the JQL clause below.
comment ~ “*BADGER*”
Hope that helps!