SIL™ JQL Search

Owned by Radu Dumitriu (Deactivated)
Last updated: Sept 10, 2018 by Elena Kolesnik
6 min read

Availability

This feature is available for the Jira server deployment option only. We plan to add it to the Cloud version in the near future.

This page contains information on searches for:

With the introduction of version 4.0, we unlocked a very powerful mechanism to search your issues, in ways you never believed possible. Here is our guide on how to build your searches.

JQL Support

Before you start to create your own JQL functions check out the JQL Support (Server).

Search Performance

For performance reasons, put there a filter that keeps the potential number of matching issues as low as possible. Remember that running a script over an issue is more complex than simply comparing values.


Issues with comments

To return issues with comments  for a given project we can use this simple JQL:

JQL
key in silJQLExpression("size(getAllCommentIds(key)) > 0", "project = TEST")

If we want to find all the issues that contains an exact number of comments the JQL should look like below:

JQL
key in silJQLExpression("size(getAllCommentIds(key)) == 5", "project = TEST")


Issues by atributes of their comments

In order to find issues that have been commented after a specified date, we need to create a SIL™ script with the following code:

commented.sil
string [] keys = selectIssues("project = " + argv[0]);
string [] ret;
for(string k in keys) {
    number [] commentIds = getAllCommentIds(k);
    if(size(commentIds) != 0) { 
        for(number id in commentIds) {
            JComment comment = getCommentById(id);
            if(comment.created > argv[1]) {
                ret += k;   
            }
        }
    } 
}
return ret;

After we saved we can use our newly created script in search like below:

JQL
key in silJQLList("commented.sil", TEST, "2017-02-16")

In order to find issues commented by a certain user within the last 2 weeks:

commented.sil
string [] keys = selectIssues("project = " + argv[0]);
string [] ret;
for(string k in keys) {
    number [] commentIds = getAllCommentIds(k);
    if(size(commentIds) != 0) { 
        for(number id in commentIds) {
            JComment comment = getCommentById(id);
            if(comment.created > currentDate() - argv[1] && comment.author == argv[2]) {
                ret += k;   
            }
        }
    } 
}
return ret;
JQL
key in silJQLList("commented.sil", TEST, 2w, admin)

Issues by the user who last updated the comments

lastUpdated.sil
string [] keys = selectIssues("project = " + argv[0]);
string [] ret;
for(string k in keys) {
    number [] commentIds = getAllCommentIds(k);
    if(size(commentIds) != 0) { 
        for(number id in commentIds) {
            JComment comment = getCommentById(id);
            if(userInGroup(argv[1], comment.updatedBy)) {
                ret += k;   
            }
        }
    } 
}
return ret;

This script would return issues that were last updated by users that belongs to the given group.

JQL
key in silJQLList("lastUpdated.sil", TEST, jira-administrators)

Issues by worklogs

If we want to find the issues where work had been logged by a specified user. We can do this by using silJQLExpression:

JQL
key in silJQLExpression('size(getWorklogIdsForUser("user", key)) != 0', 'project = TEST')

Also, we can use this JQL to find the issues where work had been logged on a given date:

JQL
key in silJQLExpression('size(getWorklogIds("2017-03-16", key)) != 0', 'project = TEST')

Issues with attachments

To return issues with attachments for a given project we can use this simple JQL:

JQL
key in silJQLExpression('size(attachments) != 0', 'project = TEST')

In order to find the issues that are linked we can use the following JQL:

JQL
key in silJQLExpression('size(linkedIssues(key)) != 0', 'project = TEST')

Also, we can search for issues with a specified link type:

JQL
key in silJQLExpression('size(linkedIssues(key, "Relates")) != 0', 'project = TEST')

To find all the issues that are blocked by issues with status "To Do" we could use:


JQL
key in silJQLExpression('size(linkedIssues(key, "Blocks", 1)) != 0', 'project = TEST')  and status = 'To Do'

If we want to search for the linked issues whatever the link type with resolution "Done" :

JQL
key in silJQLExpression('size(linkedIssues(key)) != 0', 'project = TEST') and resolution = Done

Issues with subtasks

To find all issues with subtasks, we need to write this simple JQL:

JQL
key in silJQLExpression('size(subtasks(key)) != 0', 'project = TEST')

To perform a more advanced search like searching for closed issues with open subtasks , we need to create a SIL script:

parentsOf.sil
string [] keys = selectIssues("project= " + argv[0] + " and resolution= "+ argv[1]);
string [] ret;
for(string k in keys) {
    string [] subtasksKeys = subtasks(k);
    if(size(subtasksKeys) != 0) { 
        for(string sk in subtasksKeys) {
            if(%sk%.status == argv[2]) {
                ret += k; 
            }
        }
    }
}

return ret;

After we created the file, we can use in search like below:

JQL
key in silJQLList("parentsOf.sil", TEST, Done, 'To Do')


Subtasks of issues

To search for the subtasks of issues we need to create a SIL™ script:

subtasksOf.sil
string [] keys = selectIssues("project= " + argv[0]);
string [] ret;
for(string k in keys) {
    string [] subtasksKeys = subtasks(k);
    if(size(subtasksKeys) != 0) { 
        for(string sk in subtasksKeys) {
              ret += sk; 
        }
    }
}

return ret;

After we saved the file, we can perform the search using a silJQLList like this:

JQL
key in silJQLList("subtasksOf.sil", TEST)

If we want to perform a more advanced search like searching for the subtasks with status "TO DO" of issues with resolution "Done", we could use this script:

subtasksOf.sil
string [] keys = selectIssues("project= " + argv[0] + " and resolution= "+ argv[1]);
string [] ret;
for(string k in keys) {
    string [] subtasksKeys = subtasks(k);
    if(size(subtasksKeys) != 0) { 
        for(string sk in subtasksKeys) {
            if(%sk%.status == argv[2]) {
                ret += sk; 
            }
        }
    }
}

return ret;
JQL
key in silJQLList("subtasksOf.sil", TEST, Done, 'To Do')

Issues by comparing dates

To search for issues that were resolved after the due date:

JQL
key in silJQLExpression('resolution > dueDate', 'project = TEST')

To find issues that were updated within one week after being created:

JQL
key in silJQLExpression('updated < created + "1w" ' , 'project = TEST')

Issues that had changed priority

For example, if we want to search for issues that had the priority "Medium" and now has the priority set to "High". We can perform the following search:

JQL
key in silJQLExpression('arrayElementExists( fieldHistory(key, "priority"),  3) ', 'project = TEST') and priority = High //where 3 is the priority id for "Medium"

Issues which have not been updated by the users from a specific group

In order to find issues that have not been updated by the users form a specific group in the last 24 hours,  we need to create a SIL™ script:

notUpdatedByUsersInGroup.sil
string [] keys = selectIssues("project = " + argv[0]);
string [] ret;
for(string k in keys) {
    boolean updatedByUsersInGroup = false;
    JFieldChange [] changes = lastIssueChanges(k);
    for(number i = 0; i < size(changes); i++) {
        if(userInGroup(argv[1], changes[i].user) && (currentDate() - changes[i].#{date} < "24h")) {
            updatedByUsersInGroup = true;
        }
    }
    if(!updatedByUsersInGroup) {
        ret += k;
    }
}
return ret;

After we created the file, we can use the script in search like below:

JQL
key in silJQLList("notUpdatedByUsersInGroup.sil", TEST, jira-developers)

See also