SIL JQL Search

On this page:

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.

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.

Find 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")

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

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

Find 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')