Script runner JQL functions are two things: 1) a library of useful JQL functions that you can modify or extend, and 2) a simple way for you to write your own JQL functions, without having to learn the Atlassian SDK, and harnessing the expressiveness and simplicity of groovy.
- Included Functions
- Issue Links
- Remote Issue Links
- Aggregate Expressions
- Writing your own functions
There are a large number of included functions, documented below.
hasComments([number of comments])
In the simplest form, finds issues with comments:
Alternatively you can find issues with an exact number, or greater or fewer comments than specified. Eg exactly 3 comments:
More than 5 comments:
Less than 3 comments:
commented is for searching for issues by attributes of their comments. Example, to find issues that have been commented on recently:
Find issues commented by jbloggs within the last 4 weeks:
Find issues that have a comment visible only by role Developers:
The following predicates are available, you can use as many as you like:
by - comment by this user
after - commented after
date or date expression
before - commented before
date or date expression
on - commented on this day
date or date expression
role - comment has this role level
group - comment has this group level
hasAttachments ([file extension])
Find issues with attachments:
You can use the optional first argument to specify the attachment file extension:
Takes the same arguments as commented with the exception of role, group and updatedby, which aren't supported for attachments.
Example: find issues which have files attached by jbloggs within the last 4 weeks:
Takes the same arguments as commented with the exception of role, group and updatedby, which aren't supported for searching worklogs.
dateCompare(Subquery, date comparison expression)
This function lets you compare two dates on the same issue, for instance to find all issues that were resolved later than their due date:
You can use time windows on either side of the expression. Eg to find issues resolved before or up to one week after their due date:
You can also use created and updated. To find issues that were resolved within two weeks of creation, use:
In addition, you can use the "fields" firstCommented, and lastCommented (reindex required after first install). To find issues that had not been commented within one week of creation:
You can also use date and datetime custom fields. Example:
where Delivery Date is the name of a date custom field.
The first parameter is a subquery to narrow down the number of issues that will be checked. In practice you can normally leave this empty. Performance will be proportional to the number of issues that have both fields in the date comparison set. If this is a very high number of issues (greater than 50k) you can filter some out: eg:
This is an absurdly powerful function that lets you compare attributes of fields. What you can compare are the system estimate and date fields, and any numeric, date, or datetime custom field. It's probably easiest to explain through some examples, starting from the simple.
Find issues where more work was logged than originally estimated:
Note that this could also be done by using plain JQL: workratio > 1. However with plain JQL, you could not find issues which are likely to exceed their estimate:
You would probably want to use resolution is empty as the subquery, to filter out issues that have been completed.
Search for issues where the work logged exceeded the original estimate by more than 5 days (normalised for timetracking, so > 40 hours work logged):
Search for issues which, if their remaining estimate is valid, are going to miss their due date. You could devote extra resources to these to ensure that doesn't happen:
Alternatively use a date custom field such as DeliveryDate, instead of duedate.
Find issues where the product of two number custom fields is greater than X:
You don't need to read this to use this function, but it's here for completeness.
To speed things along, this function filters out any issues that have an unset or default value, so for instance StoryPoints * BusinessValue == 0 won't find issues where either field was never set, rather than being explicitly set to zero. If this is an issue you could add a clause to your query: or StoryPoints is empty or BusinessValue is empty, outside the function.
Use brackets to specify operator precedence: StoryPoints * (BusinessValue + 10) > 100.
The only constants available are today - current datetime, d - day, and w - week.
This function will adjust estimate and timespent fields to account for timetracking, so that they can be compared to date fields. So one day logged is actually stored internally as 8 hours (assuming 8 hour day set up in timetracking). If you want to use say, four hours in your estimate expression, use 0.5 * d.
This function could replace dateCompare - ideally use this and not dateCompare.
Much work has been done to make this perform well. To that extent, no database calls are made. This means that any custom field used in your expression must have a searcher. If it does not you will get the error message: Field NonIndexedNumberField found but has no searcher. Edit it, set the searcher, and reindex.
If your custom field name is "Story Points" use StoryPoints. If it is named "How many story points?" use customfield_12345. You can get the ID by doing a normal jql search, it will show up as cf.
Finds issues with subtasks, eg
Returns the subtasks of issues specified by the subquery, eg
To find unresolved subtasks of resolved issues you might do:
To find subtasks that are Open, but their parent issue has a resolution of Fixed, you could use:
subtasksOf is analagous to saying "subtasks which have a parent selected by the subquery".
Returns the parents of issues specified by the subquery. For example, to find closed parents with open subtasks
in the project JRA, you could do:
To find all parent records where I am the assignee of an open subtask, I could do:
To show parent issues that have at least one open subtask, in the JRA project, you might do:
Searches for issues that have the specified link. You need to provide the link name, for instance
blocks, is blocked by, duplicates, is duplicated by. If you misspell it the validation error will give you a list of suitable link names.
hasLinkType(link type name)
Searches for issues that have the specified link type in either direction. You need to provide the link type name, for instance
Blockers, Duplicate, Clones, "Epic-Story Link" (for Greenhopper). If you misspell it the validation error will give you a list of suitable link names.
These two are equivalent:
linkedIssuesOf(Subquery, link name)
This is similar to parentsOf and subtasksOf, in that it will return the linked issues.
To find all the unresolved issues that are blocked by issues in the Open state you could use:
Greenhopper users can query on epic links, eg find all Epics that have unresolved stories:
Remote Issue Links
Find issues that link to remote content (for instance web pages, Confluence pages, or any other custom remote link type that you have set up).
A primary use case for this is to find issues linking to a particular Confluence page. This allows you to show on your wiki page all the JIRA issues that reference it (but see numerous caveats below). You can do this with either the JIRA issues macro, or the Filter Results gadget. Note that the JIRA issues macro has a cache, so if you are testing this you need to click the refresh icon on the JIRA issues macro. Both examples are shown below:
Warnings and Issues
For Confluence pages the title is always Wiki Page - so you can't search on the Confluence page title.
For Confluence pages the URL is always the one with viewpage.action?pageId in - you can get the pageId by clicking Edit and copying it out of Confluence. A Confluence macro would save having to do this. Alternatively just click through on the remote link from the JIRA issue.
Adding a remote link to an issue does not reindex the issue - the function won't find it til it's indexed. Make an edit or do any action on the issue to get it reindexed.
issueFieldMatch (subquery, fieldname, regexp)
Query on any field by regular expression. Performance will be roughly proportional to the number of issues selected by the subquery,
so use the query that selects the smallest set of issues you can, eg just your projects. On my dev machine this function handles around 20k issues per second.
To find all issues where the description contains a ABC0000 where 0000 is any number, you could use:
Note - you need to double the backslashes.
Note - the function searches for the reg exp anywhere within the field. To match the entirety of the field, use ^ and $, eg ^ABC
issueFieldExactMatch (subquery, fieldname, regexp)
Find issues by exact matches on a field. Example, to find issues with the single fix version REL1 use:
Selects only issues from projects in which you are a member. Being a member means being in any role, except where that is by virtue of being in a group with a global permission. That is, many projects will have the group jira-users in the Users role. These won't be included in myProjects, as generally you will not be interested in them. Usage:
Projects you have viewed recently.
projectMatch(reg exp) / componentMatch / versionMatch
These functions provide lists of projects, components, versions respectively
that match the provided regular expression.
Example: all issues that have a component beginning with Web:
All issues in the JRA project that have a fix version beginning with RC:
Returns the earliest unreleased version by release date, as distinct from the built-function earliestUnreleasedVersion, which goes by the version ordering.
inSprint(board name, sprint name)
Finds issues in the given sprint. This works for sprints that are not yet started, active, and completed sprints. Instead of the sprint name, you can also use the string "Backlog".
Note that it will return all issues including subtasks, whereas by default these are not shown in the planning mode on the board. To only return parent tasks you could add: and issuetype in standardIssueTypes().
If you don't want this functionality, from the plugin modules, disable the module named Resource to add View in Navigator to sprints on the rapid board.
There are a couple of caveats.
Because you pass board and sprint by name and not ID, it follows that the board name should be unique. If this is not the case the function will warn you to rename one or more boards.
To see issues in the exact same order don't forget to "order by Rank - otherwise the ordering is unlikely to be the same as shown in the board.
Likewise, the sprint name should be unique within the particular board. It doesn't make any sense to have multiple sprints with the same name, but Greenhopper (as of 6.1.1) does not enforce uniqueness.
Note: this is just for the new boards, not the "classic" boards.
Often you have a requirement to show some summary data based on the issues in the filter, for instance, you select all open issues in a version, and you want to see the total estimated time for all issues. Probably this should be called a summary function not an aggregate function, however this is a bit ambiguous in jira-land.
If you need more than simply a couple of values then you should probably consider writing a report. Most people will do these calculations in Excel anyway, but an aggregate function can draw attention to some figure, eg total remaining estimate from all issues shown in the current query.
The aggregate function is added to the JQL because in doing that, it will ensure other people who run the same query also see the summary value(s). Note that these won't appear in excel views, or anywhere other than the issue navigator. Adding an aggregate function does not change the results from the query in any way.
For example, to see the total estimate for all issues in the LOAD project run this jql:
If the function has just one argument, the data label will be Aggregate data value. The expression can have multiple values, in which case use: (label1, expr1, label2, expr2, ...).
Some other examples for summary data:
Total timespent on these issues
Average original estimate of these issues
Total remaining work
(originalEstimate.sum() - timeSpent.sum()) / remainingEstimate.sum()
Number of issues in this list created by user jbloggs
Simple breakdown of reporter
Writing your own functions
You write your own functions in the groovy language, as with some of the other script runner extension points.
- Go to Admin -> Script JQL Functions.
- Click Add New Function
A template is in the text area. Follow the steps in the template:
- Modify the class name to something of your choice
- Select data type as IssueFunction
- Change the if clause in the getQuery method
- Delete the getValues method
- Set the number of arguments if required
- Optionally, implement argument validation
When you are happy click Save and test in the issue navigator. If you are not ready to share your function you can disable it, whilst you keep testing.