Child pages
  • Scripted JQL Functions
Skip to end of metadata
Go to start of metadata

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

There are a large number of included functions, documented below.

Icon

You will need to reindex before you can use the hasComments and hasAttachments functions. A background reindex
will be fine, if that is available in your jira version (5.2)

Icon

All the functions have been tested on instances with greater than 400 thousand issues, and all execute in well under one second.

Comments

hasComments([number of comments])

In the simplest form, finds issues with comments:

What is issueFunction?

Icon

Do use issueFunction in and not issue in. It is done this way for performance reasons.

issueFunction is just a custom field that will be added by the plugin. If the name doesn't make sense in your language it's safe to rename it.

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(comment query)

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:

Name

Argument Type

by - comment by this user

username

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

role name

group - comment has this group level

group name

Attachments

hasAttachments ([file extension])

Find issues with attachments:

You can use the optional first argument to specify the attachment file extension:

fileAttached(attachment query)

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:

Icon

JIRA needs to be reindexed after plugin installation to use this function.

Worklogs

workLogged(worklog query)

Takes the same arguments as commented with the exception of role, group and updatedby, which aren't supported for searching worklogs.

Icon

JIRA needs to be reindexed after plugin installation to use this function.

Dates

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.

Performance

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:

expression(Subquery, expression)

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):

Icon

Do use 5*d and not 5d as in dateCompare - the syntax is (unfortunately) different.

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:

Icon

Custom field names are likely to have spaces, which can't be parsed. If so, remove the spaces. It's not case-sensitive but use camel-case for maximum readability. If your field names have any other punctuation you must use the format customfield_12345.

Advanced Notes

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[12345].

Subtasks

hasSubtasks()

Finds issues with subtasks, eg

subtasksOf(Subquery)

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".

Icon

You can leave the subquery as an empty string in all these examples if you want.

parentsOf(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:

hasLinks(link name)

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:

Icon

Experimental - from 2.1.12 only.

Feedback appreciated - for instance is the fuzzy matching working properly? Do you want to query on just the Confluence page ID rather than the URL? Do you need it to work as soon as the link is added, rather than waiting til some action happens on the issue?

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:

Usage is:

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.

Icon

JIRA needs to be reindexed after plugin installation to use this function.

Others

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
d{4}$

issueFieldExactMatch (subquery, fieldname, regexp)

Find issues by exact matches on a field. Example, to find issues with the single fix version REL1 use:

myProjects()

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:

recentProjects()

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:

earliestUnreleasedVersionByReleaseDate(project key)

Returns the earliest unreleased version by release date, as distinct from the built-function earliestUnreleasedVersion, which goes by the version ordering.

Agile

inSprint(board name, sprint name)

Example:

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().

There is also some javascript to add a link to the issue navigator to get the query for the sprint, which makes it a bit easier to find the board and sprint names:

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.

The javascript to show the issue navigator link is still a bit rough round the edges, the link might disappear when clicking around epics etc. If so refresh the page. Note that the function does not take into account any quick filters applied, or the epic links etc.

Note: this is just for the new boards, not the "classic" boards.

Aggregate Expressions

Experimental

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, ...).

results in:

Some other examples for summary data:

Note

Argument

Total timespent on these issues

timespent.sum()

Average original estimate of these issues

originalestimate.average()

Total remaining work

remainingEstimate.sum()

Tracking error

(originalEstimate.sum() - timeSpent.sum()) / remainingEstimate.sum()

Number of issues in this list created by user jbloggs

reporter.count('jbloggs')

Simple breakdown of reporter
but you're probably better off using a pie chart as this is not displayed nicely at the moment

reporter.countBy{it}

Writing your own functions

You write your own functions in the groovy language, as with some of the other script runner extension points.

Quick Start

  • Go to Admin -> Script JQL Functions.
  • Click Add New Function

A template is in the text area. Follow the steps in the template:

  1. Modify the class name to something of your choice
  2. Select data type as IssueFunction
  3. Change the if clause in the getQuery method
  4. Delete the getValues method
  5. Set the number of arguments if required
  6. Optionally, implement argument validation
  7. Test

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.

Icon

Although the template is available in the text area, it's better to point to a file. If you set up your IDE you can get code completion etc.

If you want to do this just copy the template to a file, and enter the absolute or relative path (relative to the jira working directory).

 

 

Comments Disabled

Icon

Comments are disabled for performance reasons. For questions ask on https://answers.atlassian.com, for bugs create an issue here.

Pages with several hundred comments were taking a long time to load, compounded by general AOD performance woes.

 

 

  • No labels