One of the great things about Jira is that it is easily extensible. It’s extensiblity layer makes it easy to add new features or extend existing ones. JQL is no exception in that regard. There are a number of plugins for Jira on the Atlassian Marketplace that enhance JQL. You can also develop custom functions yourself or work with an Atlassian Expert to build a custom tailored experience.
Ready to install: Atlassian Marketplace
The JQL Tricks plugin (paid) has a lot of functions that extend JQL. It includes a set of functions that query around many of Jira’s core constructs: users, versions, issues, comments, etc. A full function list can be found at J-Tricks. I like to stay on top of new issues that get created in Jira for projects I own. I’ve created a dashboard that includes the Filter Results gadget and uses a filter that binds to the JQL query:
[cc lang=’sql’ line_numbers=’false’]project in projectsLeadByUser() and created > lastLogin() ORDER BY created[/cc]
ProjectsLeadByUser() returns all the projects where I am the project lead. That function will also take a username if you want to see someone else’s projects. The created > lastLogin() returns all the issues created since I last logged in. Now everytime I log into Jira I see a concise list of new issues.
One of the great things about Jira is it’s ability to search issues in its database. The Full-text Search Attachments for Jira Plugin (paid) enables JQL statements to search attachments for name, type, and content. The plugin supports a variety of file types as well. For organizations that store documents inside of Jira this plugin now enables them to search those documents for content. If I wanted to search for an attachment that had the words case study in it, I can elegantly type:
[cc lang=’sql’ line_numbers=’false’]issue in havingAttachments(“Case Study”)[/cc]
Reach into filters, subtasks, and linked issues
One of the limitations of JQL in core Jira is the ability to perform a sub select. The Craftforge JQL Functions Plugin (free) allows users to search within a filter, linked issues, and subtaks with JQL. Why is this useful? At a prior engagement I needed a way to see all of the subtasks that were part of a set of issues in one query. This plugin allowed me to get all of that data in one query. For example:
[cc lang=’sql’ line_numbers=’false’](type = story and fixVersion = earliestUnreleasedVersion()) or issue in subtaskIssuesFromQuery(“type = story and fixVersion = earliestUnreleasedVersion()”)[/cc]
In sprints my team works on a variety of issues: stories, tasks, bugs, etc. When we commitied a story to a sprint, the team would create subtasks to break that story down across disciplines (server, ui, and test). The above query would return the main stories as well as the subtasks we committed to the sprint. It separated the new feature work from other existing issues. Issues not in this query usually was technical debt we were working to resolve.
Issues as a custom field
If you’ve ever wanted to combine the power of links but restrict the issues users can link to, take a look at the Query Issues Custom Fields plugin (free). This plugin enables a custom field that shows a set of issues defined by a JQL query. Some organizations uses this plugin to link incoming issues to a project ticket. As the project tickets close, they dynamically drop off of the custom field in the create dialog.
Flexible Email Notifications
We covered subscriptions in the last lesson. Subscriptions allow users to sign up for notifications on a set interval if issues are returned when a JQL query is run. If you are looking for even more flexibility the Notification Assistant for Jira (paid) brings subscription to the next level. Subscriptions are great for self notification and to groups defined within Jira. The Notification Assistant, however, allows more flexible reporting to distribution lists, users outside of Jira and has more options for setting thresholds for user notifications.
Jira speaks Hipchat
For users who prefer to get notification via instant messaging, Jira now seamlessly integrates with Hipchat in our free connector. Hipchat is Atlassian’s instant messaging platform for software teams. Hipchat has all the goodness you’d expect from an instant messaging tool including a self building buddy list, video integration, group chat. Now it integrates with Jira! If you want to alert specific engineers when a blocker issue gets fixed, this is your plugin. Atlassian University put out a great tutorial on how to set up the integration.
Building Your Own
Atlassian Experts help deliver an incredible experience with our products. Atlassian Experts are familiar with all aspects of our products and can assist in installation, configuration, and customization of our products. It’s easy to engage with one of our Experts to extend our products to meet your key requirements. To learn more, visit http://www.atlassian.com/resources/experts.
Building it yourself
Atlassian hosts all of the developer documentation on http://developer.atlassian.com. We’ve got two main articles on JQL addons. I’d recommend starting with the tutorial that outlines how to add a recent projects function to Jira. More complete documentation can be found in the JQL section of our plugin module documentation.
More JQL tips
This post is a part of a series on how to use JQL. Check out some of the other posts in the series below.
- Part 1: JQL Basics
- Part 2: Functions, History, Sorting, Scoping
- Part 3: Using filters and subscriptions