ScriptRunner JQL functions are two things:

Included Functions

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

Some functions require reindexing

You will need to reindex before you can use the following functions:

  • hasComments

  • hasAttachments

  • fileAttached

  • workLogged (if prior to JIRA 6.4)

  • linkedIssuesOfRemote

A background reindex will be fine.

Function name clashes with other Plugins

Some other plugins may provide functions with the same name as those provided by ScriptRunner, eg hasAttachments. For functions like these, no other name would make a great deal of sense, so this is not surprising.

The rules for when that happens are explained in JRA-24219, i.e, the plugin key that is first alphabetically will "win". As the other plugin is generally "JQL Tricks", whose key is before ScriptRunner’s, functions from that plugin are normally registered over ScriptRunner’s.

If you want to use both plugins, go to "Manage Plugins", then expand the modules for either plugin, and selectively enable or disable the JQL function modules for those functions. For example, in ScriptRunner, the module providing hasAttachment is displayed as:

jql disable module

Performance

All the functions have been tested on real-world instances with greater than five hundred thousand issues, and most execute in well under one second. Where that is not the case, the performance characteristics are documented.

Some functions take a subquery as a first parameter to narrow down the number of issues that will be checked. You should enter terms here to restrict the number of issues the function will operate on. The performance of a query will be proportional to the number of issues that are returned by the subquery. Some functions filter these further to issues that have the relevant field(s) non-empty.

Taking an example, if you are only interested in issues that have ABC<four digit number> in the description, that are assigned to me, the first of these two will be faster:

issueFunction in issueFieldMatch("assignee = currentUser()", "description", "ABC\\d{4}")
assignee = currentUser() and issueFunction in issueFieldMatch("", "description", "ABC\\d{4}")

Using a project clause outside the query parameter will behave the same as if you had used it within the parameter (new in 4.3.5). So the following two queries will have the same performance:

issueFunction in parentsOf("project = FOO")
project = FOO and issueFunction in parentsOf("")

Slow Log

Any ScriptRunner provided function that takes greater than one second will log to atlassian-jira-slow-queries.log. Note that we log when a particular clause is slow, not for the entire query, which we do not have access to.

In this case you will see something like the following in the slow query log:

2016-07-14 15:26:23,208 http-bio-8080-exec-3 INFO admin 926x1041x2 yi0om0 /rest/issueNav/1/issueTable [c.a.j.i.search.providers.LuceneSearchProvider_SLOW] ScriptRunner JQL function 'slowFunction' with clause '{issueFunction in slowFunction()}' took '2390' ms to run.

Query Timeouts

When any query that takes longer than one minute is run in the issue navigator by pressing the Run button, no results are displayed, and there is no indication that it timed out. However, if you do a full page refresh, it will wait as long as it takes (notwithstanding your reverse proxy may time out). This is a JIRA behaviour, not ScriptRunner.

By default we will time out any ScriptRunner function that takes more than one minute. This is to avoid the case where long-running queries continue to run in the background, even after the browser has timed out. If this happens it will be logged in the slow query log, with a warning that the function was killed.

To retain the old behaviour, set system property sr.jql.timeout=0. (See how).

To customise the timeout, set sr.jql.timeout to the number of milliseconds after which it should timeout, eg for two minutes set -Dsr.jql.timeout=120000.

Note that it may not time out immediately after the configured value, but it should happpen shortly thereafter.

Query Profiler

For administrators only, there is a facility to profile JQL to find poorly-performing queries. This simply surfaces information that is otherwise buried in log files, and somewhat simplifies it.

profile button

Clicking the Profile button may produce something like this.

jql profile

We can see that the largest component in the time taken was the linkedIssuesOf clause. Time taken for this function is approximately proportional to the number of issues selected by the first argument. Did we really need to find linked issues of every issue that has subtasks? Perhaps we could narrow down that subquery further…​ let’s say we are only interested in the linked issues of issues in the SALES project with subtasks.

Modifying the query shows that we speeded things up from 3.2 seconds to less than 0.1 second:

jql profile 2
Operations that took close to zero time are filtered out, to make it more readable. This, and other optimisations, explains why you may not get the same results if you re-run the profiler for the same query.

The other tabs provide technical information that you may or may not find useful.

Comments

hasComments

hasComments([number of comments])

In the simplest form, finds issues with comments:

issueFunction in hasComments()
What is issueFunction?
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, although for consistency with the documentation it’s probably best to leave it as is.

Alternatively you can find issues with an exact number, or greater or fewer comments than specified. Eg exactly 3 comments:

issueFunction in hasComments(3)

More than 5 comments:

issueFunction in hasComments('+5')

Less than 3 comments:

issueFunction in hasComments('-3')

commented

commented(comment query)

commented is for searching for issues by attributes of their comments. Example, to find issues that have been commented on recently:

issueFunction in commented("after -7d")
issueFunction in commented("after 2012/12/31")

Find issues commented by jbloggs within the last 4 weeks:

issueFunction in commented("after -4w by jbloggs")

Find issues that have a comment visible only by role Developers:

issueFunction in commented("role Developers")

Issues commented in the current month by the current user:

issueFunction in commented('after startOfMonth() by currentUser()')

Issues commented in the previous calendar month by the current user:

issueFunction in commented('after startOfMonth(-1) before endOfMonth(-1) by currentUser()')

The following predicates are available, you can use as many as you like:

Table 1. Predicates
Name Argument Type

by - comment by this user

username or user function, eg currentUser()

after - commented after

date or date expression, or date function, eg startOfDay(), lastLogin()

before - commented before

date or date expression, or date function

on - commented on this day

date or date expression, or date function

inRole - comment was made by a member of this role name, for the issue on which the comment was made

role name

inGroup - comment was made by a member of this group

group name

roleLevel - comment is restricted to this role level

role name

groupLevel - comment is restricted to thisgroup level

group name

For backwards compatilbilty role and group are synonyms for roleLevel and groupLevel, and application only to the commented function.

Using standard JQL functions as an argument type is supported.

lastComment

lastComment(comment query)

lastComment is similar to commented but is restricted to searching only the last comment for every issue.

This can be very useful for "support" workflows, where you want to ensure that customer comments are dealt with in a timely manner. For example, to find issues in a project where the person last commenting is not in the Developers role:

project = FOO and issueFunction not in lastComment("inRole Developers")

To find issues where the last comment was made by someone with the User role (and not the Developer role), and it was greater than 4 hours ago:

issueFunction in lastComment("inrole Users before -4h")  && issueFunction not in lastComment("inRole Developers")
Do not confuse roleLevel and inRole. Role level is the security level of the comment, "in role" refers to the role(s) of the person making the comment in that project.
Performance Characteristics

There are different factors that make up the overall time taken for this query:

  • The total number of comments in the system…​ in testing on a low spec machine, it requires around 400ms for an instance with 350k comments. If you have one millions comments, the cost will be about 1.2 seconds, on a low spec machine.

  • The number of issues returned by the query, eg "by jbloggs". If your query matches hundreds of thousands of comments it will take an additional second or two.

Any additional clauses that you AND or OR together with this one are irrelevant.

Attachments

hasAttachments

hasAttachments ([file extension])

Find issues with attachments:

issueFunction in hasAttachments ()

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

issueFunction in hasAttachments ("pdf")

fileAttached

fileAttached(attachment query)

Takes the same arguments as commented (see Predicates) 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:

issueFunction in fileAttached("after -4w by jbloggs")

Worklogs

workLogged

workLogged(worklog query)

This functions works differently depending on whether you are running JIRA 6.4 or above, or not. From JIRA 6.4 the worklog is indexed and is available for searching.

If you are on 6.4+ this takes the same arguments as commented, you can also use inRole, inGroup, and roleLevel predicates, to eg search for work logged by a user with a particular role. As above, roleLevel is for filtering on the security role level of the worklog.

If you are on prior to 6.4, you cannot use role, group and updatedby, which aren’t supported for searching worklogs.

Also, prior to 6.4 the following query would return incorrect results:

issueFunction in workLogged(on "2015/07/28" by admin)

It would return issues where work had been logged by admin, and work had been logged on that date, but not necessarily by that user.

To find all work logs by members of the Developers role in the preceeding calendar month:

issueFunction in worklogged("after startOfMonth(-1) before endOfMonth(-1) inRole Developers")

As of 6.4 you can do much of this in plain JQL, but you don’t have the role or group functions available.

Dates

dateCompare

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:

issueFunction in dateCompare("", "resolutionDate > dueDate")

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:

issueFunction in dateCompare("", "resolutionDate < dueDate +1w")

You can also use created and updated. To find issues that were resolved within two weeks of creation, use:

issueFunction in dateCompare("", "created +2w > resolutionDate ")

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:

issueFunction in dateCompare("", "created +1w < firstCommented ")

You can also use date and datetime custom fields. Example:

issueFunction in dateCompare("", "resolutionDate > Delivery Date")

where Delivery Date is the name of a date custom field.

You can also use the equality operator = to find issues issues that have been resolved on their delivery date:

issueFunction in dateCompare("", "resolutionDate = Delivery Date")
Performance

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:

issueFunction in dateCompare("project in myProjects()", "resolutionDate > dueDate")

lastUpdated

lastUpdated (by / inRole / inGroup)

Finds issues by the user who last updated them. Example, find all issues that were last updated by members of the Developers role:

issueFunction in lastUpdated('inRole Administrators')

Last updated comprises the following actions:

  • edits

  • state changes

  • adding/removing links, labels etc

  • commenting

Subtasks

hasSubtasks

Finds issues with subtasks, eg

issueFunction in hasSubtasks()

subtasksOf

subtasksOf(Subquery)

Returns the subtasks of issues specified by the subquery, eg

issueFunction in subtasksOf("project = JRA")

To find unresolved subtasks of resolved issues you might do:

issueFunction in subtasksOf("resolution is not empty") and resolution is empty

To find subtasks that are Open, but their parent issue has a resolution of Fixed, you could use:

issueFunction in subtasksOf("resolution = Fixed") and status = Open

subtasksOf is analagous to saying "subtasks which have a parent selected by the subquery".

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

parentsOf

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:

status = Closed and issueFunction in parentsOf("project = JRA and status = open")

To find all parent records where I am the assignee of an open subtask, I could do:

issueFunction in parentsOf("resolution is empty and assignee = currentUser()")

To show parent issues that have at least one open subtask, in the JRA project, you might do:

issueFunction in parentsOf("project = JRA and status = Open")
hasLinks([link name])

With no arguments searches for all issues that have any link.

With one argument, 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.

issueFunction in hasLinks("blocks") and resolution is empty

hasLinkType

hasLinkType(link type)

Searches for issues that have the specified link type in either direction. You need to provide the link type, for instance Blockers, Duplicate, Clones. If you misspell it the validation error will give you a list of suitable link types.

issueFunction in hasLinkType("Blockers") and resolution is empty

These two are equivalent:

issueFunction in hasLinkType("Blockers")
issueFunction in hasLinks("blocks") OR issueFunction in hasLinks("is blocked by")

linkedIssuesOf

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:

issueFunction in linkedIssuesOf("status = Open", "blocks") and resolution is empty

With no link name argument, will search for the linked issues whatever the link type:

issueFunction in linkedIssuesOf("resolution = unresolved")

epicsOf

JIRA Agile users can query on epic links, eg find all Epics that have unresolved stories:

issueFunction in epicsOf("resolution = unresolved")

issuesInEpics

You can go the other way, e.g. find all stories for open epics in a project

issueFunction in issuesInEpics("project = SSPB and status = 'To Do'")

Find unresolved stories in resolved epics:

issueFunction in issuesInEpics("resolution is not empty") and resolution is empty

More Complex Examples

Find all the stories in an epic, and all their subtasks. With these complex queries it helps to break them down into pieces, so the first step is to find the stories of the epics in a particular project, let’s say the JRA project.

issuesInEpics("project = JRA")

Now save that as a filter, called for example, Stories in Epic.

Now to query for their subtasks we can create a new filter, and do:

issuefunction in subtasksOf("filter = 'Stories in Epic'")
Don’t edit the Stories In Epic filter as you will cause a cyclical reference. Instead create a new filter.

Note this this now returns only the subtasks, whereas you want the subtasks and the stories. So we can put it all together to get:

filter = 'Stories in Epic' or issuefunction in subtasksOf("filter = 'Stories in Epic'")

If you also want issues linked to the stories, you can use:

filter = 'Stories in Epic' or
    issuefunction in subtasksOf("filter = 'Stories in Epic'") or
    issueFunction in linkedIssuesOf("filter = 'Stories in Epic'")

The linkedIssuesOf function takes an optional second parameter which is the link name, eg "blocks" or "is blocked by", so you can constrain the issues returned by that clause.

When working on complex queries bear in mind the following:

  • Clauses in a multiple clause query should be tested on their own to check they are returning what you want

  • If a subfilter gets complicated, save it as a filter and use filter = 'Filter Name'

  • You can use double and single quotes if necessary, so long as they are balanced. You cannot escape quotes, if you need to you will need to save as a filter.

linkedIssuesOfRecursive

linkedIssuesOfRecursive(Subquery, [Link name])

This is similar to linkedIssuesOf, in that it will return the linked issues, however this function traverses issue links recursively to return all issues that are linked (directly and indirectly) to the results of the initial subquery.

To find all direct and indirectly linked issues of an particular issue you can use:

issueFunction in linkedIssuesOfRecursive("issue = DEMO-1")

So if we have the following setup:

jql recursive

Then our query would return: DEMO-1, DEMO-2, DEMO-3, DEMO-4 and DEMO-5.

DEMO-1 is returned by the query above as it has "is blocked by" links from DEMO-2 and DEMO-3

You can limit the type (and direction) of links that are traversed using the second parameter.

issueFunction in linkedIssuesOfRecursive("issue = DEMO-1", "blocks")

In this instance, if we use the example already mentioned, only DEMO-2, DEMO-3 and DEMO-4 will be returned.

The Link type parameter behaves exactly the same as the Link description parameter for linkedIssuesOf.

If you have 1000s of indirectly linked issues, traversal of all of the links will take a few seconds.

linkedIssuesOfRecursiveLimited

linkedIssuesOfRecursiveLimited(Subquery, Traversal depth, [Link name])

This function is exactly the same as linkedIssuesOfRecursive but it allows us to limit the depth of traversals along issue links that the function will do.

The following query will follow all links, recursively, from all issues in the DEMO project until it has traversed a maximum of 2 links deep along any link path.

issueFunction in linkedIssuesOfRecursiveLimited("project = DEMO", 2)

Using the following setup:

jql recursive limited

Our query with the depth parameter would return: DEMO-1, DEMO-2, TEST-1, TEST-2, TEST-4 and TEST-5.

We could specify the link name as well, to get different results:

issueFunction in linkedIssuesOfRecursiveLimited("issue = TEST-2", 3, "is blocked by")

which would return: TEST-3, TEST-6 and TEST-5

linkedIssuesOfAll, linkedIssuesOfAllRecursive, and linkedIssuesOfAllRecursiveLimited

These functions provide the old behaviour of the linkedIssuesOf…​ style functions, in that they include subtask and epic links when no link type is specified. Other than that, they function identically to their counterparts.

linkedIssuesOfRemote([Remote Link property], search term)

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). Two options are available: either specify against which remote link property to search or search against title and url.

Search term supports two wildcard characters:

  • * - matches zero or more characters. For example, he* will match any word starting with he, such as he, her, help, hello, helicopter, hello world, and so on

  • ? - matches exactly one character. For example, he? will only match three-letter words starting with he, such as hem, hen, and so on.

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.

Examples

Remote Link properties supported: title, url, application name, application type, relationship, host, path, query.

  • Find all issues linked to a linked Confluence instance:

    issueFunction in linkedIssuesOfRemote("application name", "Confluence")
  • Find all issues with a remote link to some Atlassian app (Confluence, JIRA, Bamboo, Bitbucket, etc.):

    issueFunction in linkedIssuesOfRemote("application type", "com.atlassian.*")
  • Find issues linked to a specific page id:

    issueFunction in linkedIssuesOfRemote("query", "pageId=11469162")
  • Find issues linked to a specific web host:

    issueFunction in linkedIssuesOfRemote("host", "www.stackoverflow.com")
  • Find issues linked to a path starting with a particular string:

    issueFunction in linkedIssuesOfRemote("path", '/jira*')
    issueFunction in linkedIssuesOfRemote("path", '/projects*')

By querying on the 'relationship' property, you can emulate the hasLinks function for links to a remote JIRA instance.

issueFunction in linkedIssuesOfRemote("relationship", "mentioned in")
issueFunction in linkedIssuesOfRemote("relationship", "caused by")
Searching Against Title and URL
  • Find issues linked to a particular URL:

    issueFunction in linkedIssuesOfRemote("http://www.acme.com/confluence/pages/viewpage.action?pageId=11469162")
  • Find issues linked to a particular title (with some wildcards):

    issueFunction in linkedIssuesOfRemote("title wildcard* m?tch")
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 until it’s indexed. Make an edit or do any action on the issue to get it reindexed.

In order to prevent extremely slow WildcardQueries, a search term may not start with one of the wildcards * or ?

Find any issue that has a remote link of any kind.

issueFunction in hasRemoteLinks()

This query could be slow in instances with a large number of projects and issues, since it is an alias for linkedIssuesOfRemote('*').

User Functions

inactiveUsers

Returns users that have been marked Inactive. For example, to find issues that have been assigned to users that have left the organisation:

assignee in inactiveUsers()

(This assumes you have a working "leavers" process).

You can use this function with all User fields, eg reporter, creator, assignee, user custom fields.

If you want find issues assigned to active users, you can just invert the query above:

assignee not in inactiveUsers()

jiraUserPropertyEquals

jiraUserPropertyEquals(property name, property value)

This function return users with a matching property value. For example, to find all issues assigned to users in the AMER region:

assignee in jiraUserPropertyEquals("region", "AMER")

This assumes you have set properties on users. This can be done either programatically, or via the UI at Admin → Users, then click on the user, then Actions → Edit Properties.

So the example from the query above would return this user (and others):

edit user props
If you set properties programatically, note that only string properties are supported.

Also, unfortunately, only administrators can view user properties - vote for: JRA-11990.

The intention was originally that one could query users by LDAP attributes, for example office, location or manager etc. However JIRA cannot sychronise arbitrary attributes from LDAP, vote for JRA-24332.

Calculations

expression

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:

issueFunction in expression("", "timespent > originalestimate")

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:

issueFunction in expression("", "timespent + remainingestimate > originalestimate")

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

issueFunction in expression("", "timespent > originalestimate + 5*wd")
Do use 5*d or 5*wd and not 5d as in dateCompare - the syntax is (unfortunately) different.
Notes on time tracking

Unfortunately, a "day" can mean different things in different contexts. When comparing dates, we normally think in terms of the difference between two dates being 24 hour days.

However if we are comparing estimate fields, a day is normally thought of as an 8-hour working day, or whatever the JIRA time tracking is configured to be. Similarly, a week consists of 5 working days usually.

Previous versions of this function tried to do the conversion for you automatically. However this was confusing, so if you want to compare estimates adjusted for time-tracking, you now need to explictly specify either working day units:

wd

Number of milliseconds in a working day (according to your specification in Admin → Time tracking

ww

Numer of days in a working week multiplied by the value above.

Or, if you are comparing timetracking fields with non-time tracking fields, for example remaining effort and due date, you need to use the special fromTimeTracking function. For example, 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:

issueFunction in expression("resolution is empty", "now() + fromTimeTracking(remainingestimate) > duedate")

When you specify an estimate of 3 days, JIRA stores that internally as 24 hours, and renders it as 3 days for estimate fields. The fromTimeTracking function converts that to 72 hours so it can be used to manipulate and compare with other dates.

Find issues where the product of two number custom fields is greater than X:

issueFunction in expression("", "StoryPoints * BusinessValue > 100")
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.
You can only use custom fields that have a configured searcher, as, for performance reasons, we only retrieve values from the Lucene index. The standard searchers are supported, searchers supplied by plugins will not work.

Find issues where the creator is not equal to the reporter

issueFunction in expression("", "creator != reporter")

Find issues that were due on the same day they were created:

issueFunction in expression("", "created.clearTime() == dueDate")

Find issues that have a high ratio of votes to complexity (assuming Complexity is a numeric field):

issueFunction in expression("", "votes / Complexity > 100")
Conversions

Fields are passed to your expression with certain types, so you can do arithmetic and comparison:

Durations, such as time tracking or where you have used the Duration searcher

Provided as a Long value of the number of milliseconds

Dates

A java.sql.Timestamp object. Where there is no time component such as with due date, the time portion will be set to midnight on that date. You can use the .clearTime() method to clear the time portion for == comparisons with other dates.

Users

A String containing the user key. This is sufficient as they are just used for equality comparisons

In addition you can add a date field to a duration and get a new date.

Using functions

You can use all of the Date and User functions, for instance now(), startOfDay() etc, anywhere you would use a date.

Some arguments will need to be quoted. For example, if you want to say one week before the start of the month you would write startOfMonth('-1w').

aggregateExpression

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:

project = LOAD and issueFunction in aggregateExpression("Total Estimate for all Issues", "originalEstimate.sum()")
aggexp

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

project = LOAD and issueFunction in
    aggregateExpression("Total Estimate for all Issues", "originalEstimate.sum()", "Remaining work", "remainingEstimate.sum()")

results in:

multi agg

Some other examples for summary data:

Note Argument

Total timespent on these issues

timespent.sum()

Average original estimate of these issues

originalestimate.average()

Average work ratio

workratio.average()

Note: Will display as a decimal (e.g. 0.12 rather than 12%)

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}

Regular Expressions

issueFieldMatch

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:

issueFunction in issueFieldMatch("project = JRA", "description", "ABC\\d{4}")

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 $, e.g. ^ABC\\d{4}$

issueFieldExactMatch

issueFieldExactMatch (subquery, fieldname, regexp)

Find issues by matching the text of a field exactly. The intention behind this function was to work around issues where the Lucene word stemming makes exact matches difficult.

Previously it was incorrectly documented that it was for an exact regex match, and may have even behaved like that. This was a bug. If you were using it like this you can achieve the same behaviour by using issueFieldMatch by specifying the start and end of line regex tokens. Eg previously you might have had:

issueFunction in issueFieldExactMatch('Some Custom Field', 'b.d')

which would have matched only when the custom field was bad, bid, etc etc. To get the same behaviour you should change it to:

issueFunction in issueFieldMatch('Some Custom Field', '^b.d$')

projectMatch / componentMatch / versionMatch

projectMatch(reg exp)
componentMatch(reg exp)
versionMatch(reg exp)

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:

component in componentMatch("^Web.*")

All issues in the JRA project that have a fix version beginning with RC:

fixVersion in versionMatch("^RC.*")

Versions

releaseDate

releaseDate(release date query)

Finds issues by the release date of the associated version (fix versions, affects versions, version custom fields).

For example to find issues that have a fix version of any release that is due in the next 10 days:

fixVersion in releaseDate("after now() before 10d")

To find issues where the fix version will be released on a given day:

fixVersion in releaseDate("on 2016/09/07")

You can use any of the following predicates in these version functions:

Table 2. Predicates

Name

Argument Type

after - commented after

date or date expression, or date function, eg startOfDay(), lastLogin()

before - commented before

date or date expression, or date function

on - commented on this day

date or date expression, or date function

startDate

startDate(start date query)

Finds issues by the start date of the associated version (fix versions, affects versions, version custom fields).

For example to find any issues that have a fix version that is not going to start until two weeks from now:

fixVersion in startDate("after 14d")

You can use any of the predicates in Predicates.

overdue

overdue([release date query])

Finds issues by whether the associated version is overdue, optionally querying further on the release date.

An overdue version is one that is a) unreleased, and b) has it’s release date in the past.

To find all issues that are supposed to be fix in an overdue version:

fixVersion in overdue()

Find all issues with a fix version, that is at least two weeks overdue:

fixVersion in overdue("before -14d")

earliestUnreleasedVersionByReleaseDate

earliestUnreleasedVersionByReleaseDate(project key)

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

Projects

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:

project in myProjects()

recentProjects

Projects you have viewed recently.

project in recentProjects()

Agile

addedAfterSprintStart

addedAfterSprintStart(board name, [sprint name])

Example:

issueFunction in addedAfterSprintStart("Sample Scrum Board", "Sample Sprint 3")

Shows issues that were added to the named sprint (or all active sprints if second argument is not provided), after the sprint started. This is useful for seeing how the scope of a sprint has changed when the sprint is in progress.

You can also just pass a board name to list all issues added after the start of each sprint - this is primarily used in quick filters, e.g.:

added after start quick filter

This will let you drill down on those issues that were added after the scope was agreed, both in the planning and work boards.

This query is also available from the planning board by clicking the flag icon:

added after start icon

removedAfterSprintStart

removedAfterSprintStart(board name, [sprint name])

Shows issues that have been removed from the named sprint (or all active sprints if second argument is not provided), after the sprint has started.

incompleteInSprint

incompleteInSprint(board name, [sprint name])

Shows issues that not been completed in the named sprint (or all active sprints if second argument is not provided).

You can use plain JQL to show incomplete issues in any give sprint, eg:

sprint = 24 and resolution is empty

however, the incompleteInSprint function allows you to see incomplete issues in all currently active sprints.

completeInSprint

completeInSprint(board name, [sprint name])

Shows issues that are complete in the named sprint (or all active sprints if second argument is not provided).

nextSprint

nextSprint(board name)

Shows issues that are members of the next, not-yet-started sprint on the specified board.

This can be useful to drive dashboard charts and gadgets, so you can see the build-up of the next sprint.

previousSprint

previousSprint(board name)

Shows issues that are members of the last completed sprint.

inSprint(board name, sprint name)

inSprint is redundant since the migration from sprint markers to proper custom fields in JIRA Agile 6.3.

However it’s functionality lives on - you can access the query for active and planned sprints and the backlog by clicking on the symbol as shown above. Note that these icons and functionality are provided by ScriptRunner and not JIRA Agile, so if you have any problems or suggestions create an issue, don’t speak to Atlassian support.

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

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.

Note that the function does not take into account any quick filters applied, or the epic links etc.

If you don’t want this functionality (i.e. the links added to the planning board), disable the module named Resource to add View in Navigator to sprints on the rapid board, in the ScriptRunner plugin.

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.

Portfolio

These functions are only available if Portfolio for JIRA is installed.

After first install the version of ScriptRunner containing these functions, a full reindex is required, otherwise they will only show results for issues updated from that point.

portfolioChildrenOf

portfolioChildrenOf(Subquery)

Find issues that are children of the specified subquery.

For example, given the following Portfolio hierarchy:

  1. Theme

  2. Initiative

  3. Epic

  4. Story

To find all Initiatives that belong to Themes in a certain status:

issueFunction in portfolioChildrenOf("status = 'To Do'") and issuetype = Initiative

To find all children of Themes:

issueFunction in portfolioChildrenOf("status = 'To Do'")

This function does not traverse the link from Epic to Story. To also find stories you could combine multiple functions. First of all, create and save a query that finds all children of the Themes or Initiatives, e.g. save the above function as Children of initiatives.

Then the following function will also the stories of the epics:

filter = "Children of initiatives" or issueFunction in issuesInEpics('filter = "Children of initiatives"')

It can be useful to find high-level items that have not been properly placed in a structure. To search for Initiatives with no Themes:

issueFunction not in portfolioChildrenOf("issuetype = Theme") and issuetype = Initiative

portfolioParentsOf

portfolioParentsOf(Subquery)

The converse of portfolioChildrenOf. Finds parent issues of the provided subquery.

To find all themes of unresolved Epics you might use:

issueFunction in portfolioParentsOf("issuetype = Epic and resolution is empty") and issuetype = Theme

Continuing the example from above where we are looking for issues that did not fit the hierarchy, we can search for any Themes that have no children. Notice the not in.

issueFunction not in portfolioParentsOf("issuetype = Initiative") and issuetype = Theme

In simple terms, this looks for all Themes that are not the parent of an Initiative.

If you want to start from Stories, you need to create a combination of queries. Let’s search for open Initiatives of closed Stories:

issueFunction in portfolioParentsOf('issueFunction in epicsOf("issuetype = Story and status = Done")')
    and issuetype = Initiative and resolution is empty

For how-to questions please ask on Atlassian Answers where there is a very active community. Adaptavist staff are also likely to respond there.

Ask a question about ScriptRunner for JIRA, for for Bitbucket Server, or for Confluence.