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.

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)

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?

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:

Issues commented in the current month by the current user:

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

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

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 maderole name
inGroup - comment was made by a member of this groupgroup 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 from 3.1.3 onwards.

 

lastComment (comment query)

Available from 3.1.3

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:

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:

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:

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:

  1. 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.
  2. 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.

lastUpdated (by / inRole / inGroup)

Available from 3.1.3 and only for JIRA 6.3.10 and above, due to a JIRA bug

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

Last updated comprises the following actions:

  • edits
  • state changes
  • adding/removing links, labels etc
  • commenting

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:

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.

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

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:

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

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

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.

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:

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

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.

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:

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

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

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(Subquery, [Link type])

 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:

So if we have the following setup:

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. 

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(Subquery, Traversal depth, [Link type])

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. 

 Using the following setup:

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 type as well, to get different results:

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

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.

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 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 bug specifying the stand and end of line regex tokens. Eg previously you might have had:

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

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

addedAfterSprintStart(board name, [sprint name])

(From 3.0). Example:

This lists issues that were added to the sprint 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, eg:

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:

removedAfterSprintStart(board name, [sprint name])

(From 3.1.3). 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(board name, [sprint name])

(From 3.1.3). 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:

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

completeInSprint(board name, [sprint name])

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

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 Script Runner 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.

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.

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 Script Runner 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.

 


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.

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

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