JQL to find all issues where the user's group is in a group customfield?

[Scott Harman]

Hi guys

We're using group names for a permission scheme, and we also want our external users to find their issues where (deep breath)...

The user is the assignee, reporter or owner (custom field)

OR

Their primary group is the value of the CC Group custom field (alternatively, a member of their primary group (which copied to the CC Group field for permission reasons) is the assignee or reporter)

i.e. User Alice belongs to the groups: Customers, jira-users, CustomerA

CustomerA is stored in the CC Group field

User Bob also belongs to the same groups, and is a manager - so never logs issues, but expects to see all issues raised by Alice and all issues raised by our helldesk on their behalf.

I'd really just like to have a generic couple of shared dashboards available for all our customers and agents which use the same underlying JQL

The current permission scheme only allows users to view their own reported issues, or when their user-group is in the CC user field - but I'd rather not have an open search on everything just in case there's finger trouble on our end - I do want to explicitly restrict it to assignee, reporter, owner are current user, and assignee or reporter are in the group which is set in CC Groups

It's just such an obvious thing that I can't get my head around it ;)

What I'm afraid of is that someone sets the cc group field to 'jira-users' or 'customers' but that's not an issue if it's half a dozen issues - it's more of a concern if we fundamentally break the permission scheme and that's really what I want to guard against.

Any thoughts?

Cheers

[Leo Dopson · Accepted solution]

Paste this in Scripted JQL Functions, provide three arguments. 
1=Project
2=Custom Field with Crowd Group
3=assignee, reporter, or literal user

This can be modified to work with Role, there are examples of how to search the Roles out there.

/*
    Sample template

    # See docs at https://studio.plugins.atlassian.com/wiki/display/GRV/Scripted+JQL+Functions
 */

package examples
import com.atlassian.jira.ComponentManager;
import com.atlassian.jira.component.ComponentAccessor;
import com.atlassian.crowd.embedded.api.User
import com.atlassian.jira.issue.Issue
import com.atlassian.jira.jql.operand.QueryLiteral
import com.atlassian.jira.jql.query.QueryCreationContext
import com.atlassian.jira.util.MessageSet
import com.atlassian.query.clause.TerminalClause
import com.atlassian.query.operand.FunctionOperand
import com.onresolve.jira.groovy.jql.AbstractScriptedJqlFunction
import org.apache.lucene.index.Term
import org.apache.lucene.search.BooleanClause
import org.apache.lucene.search.BooleanQuery
import org.apache.lucene.search.Query
import org.apache.lucene.search.TermQuery

import com.atlassian.jira.issue.CustomFieldManager;
import com.atlassian.jira.issue.fields.CustomField;
import com.atlassian.jira.issue.fields.config.FieldConfig;
import com.atlassian.jira.issue.customfields.manager.OptionsManager;
class ABC/*YourClassName*/ extends AbstractScriptedJqlFunction {

    def String subquery
    @Override
    MessageSet validate(User user, FunctionOperand operand, TerminalClause terminalClause) {
        def messageSet = super.validate(user, operand, terminalClause)
        subquery = "project = '" + operand.args[0] + "'"
        // if your function is slow, you might want to limit the results here
        messageSet.addMessageSet (validateSubquery(user, subquery))
        messageSet
    }

    /**
     * Implement this method for using datatype = anything but IssueFunction
     * Delete it otherwise
     * @param queryCreationContext
     * @param operand
     * @param terminalClause
     * @return list of query literals
     */
    List<QueryLiteral> getValues(QueryCreationContext queryCreationContext, FunctionOperand operand, TerminalClause terminalClause) {
        def List<Issue> issues = getIssues(subquery )
        def literals = []
        log.error("Operand " + operand.getName());
        
        CustomFieldManager cfm       = ComponentManager.getInstance().getCustomFieldManager();
        CustomField cf_queueGroups   = cfm.getCustomFieldObjectByName(operand.args[1]);
        if(! cf_queueGroups) {
           log.error("Custom Field does not Exist!");
           messages.addErrorMessage("Project key is invalid");
           return Collections.emptyList();
        } else {
           log.error("CUSTOM FIELD: "  + cf_queueGroups);
        }
        
        OptionsManager  optionsManager  = ComponentManager.getComponentInstanceOfType(OptionsManager.class);
        def groupManager = ComponentAccessor.getGroupManager()
        def userfound = false;
        def person = operand.args[2];
        
        issues.each { issue ->
                //log.error("Issue Key: "  + issue.getKey()); 
                def crowdgroup = issue.getCustomFieldValue(cf_queueGroups);
                if(crowdgroup) { 
                  //log.error("FIELD VALUE: "  + crowdgroup )

                  Collection<User> crowdusers = groupManager.getUsersInGroup(crowdgroup)
                  if(operand.args[2] =~ "assignee") { 
                    person = issue.getAssigneeId();
                  } else if(operand.args[2] =~ "reporter") {
                    person = issue.getAssigneeId();                     
                  }
		  if(person) {  
                     Object optionsset = cf_queueGroups.getValue(issue);
                     def QueueName = optionsset.get(0).getName();
  		     //log.error(" Queue Value" +  QueueName);

                     for(User user : crowdusers) { // replaced with crowd users
                        if(user =~ person) {
                           log.error("FOUND: " + user + " person: " + person + " key " + issue.getKey() + " group " + QueueName);
                          userfound = true;
                        }
                     }
                     if (userfound) {
                         literals << new QueryLiteral(operand, issue.id as Long)
                         userfound = false;
                    }
                  } else {
                     //log.error("PERSON is NULL " + person + " issue " + issue.getKey() );
                  }
                } else {
                   // log.error("CROWD GROUP NULL " + crowdgroup);
                }
            }
        log.error("Literals " + literals);
        literals
       }
}

[Scott Harman]

Hi Leo

Thanks for that - sorry I've been working on a different project for the last few weeks so haven't had a chance to look yet

Will hopefully have some time today.

[Caner Arda]

Groups of User JQL Function let's you query group customfields by current user or a specific username.

You can do searches like the one below:

"group customfield" in groupsOfCurrentUser()

"group customfield" in groupsOfUser("username")

Hope it helps someone.

[dleng]

I don't think you could get a list of groups a user belongs to as a criteria when searching with JQL... been experimenting on this but no luck so far.

(assignee = currentUser() OR reporter = currentUser() OR "User Picker Field" = currentUser()) AND "Group Picker Field" IN (Group_A, Group_B, Group_C)

This checks the assignee/reporter/userpicker field for the current user, and then a multi group picker field if those groups are found inside it. And then I found this plugin which allows you to search by group membership of a particular user:

https://marketplace.atlassian.com/plugins/com.j-tricks.jql-plugin

Quoting the documentation:

Group Functions

These functions return JIRA GROUP objects and hence should be used in JQL in the following format.

groupPickerCustomField in functionName([parameters])

Following are the various user functions supported by JQL Tricks plugin.

v 1.0

• groupsOfUser() - To search based on groups of a specific user

Added in v 2.0

• groupMatches() - To search based on groups matching to a regular expression

[Leo Dopson]

I've written a JQL function that can successfully return a collection based on a customfield that contains a group. I'm now researching how to get the issue context so that I can grab the customfield for every issue. I'm using Jamie Echlin's Scripted JQL to build my function.

Once I'm done I will share the JQL function. A word of caution, this query would have to run on every issue, there could be negative resource impacts.

[Philip Prentis]

Please let us know when you have the JQL function ready to share.

[Leo Dopson]

Look back at my last post in this thread, the script is there.

[Jim Downing]

I'm also trying to achieve this, having set up a group picker custom field to define review groups for issues following advice here (https://confluence.atlassian.com/display/JIRA/How+do+I+assign+issues+to+multiple+users). A user can be in several review groups, so to show all the review work they could do, I'd like this to be valid JQL:

currentUser() in membersOf(reviewGroup)

It's somewhere between disappointing and appaling that this problem of group issue ownership seems to have existed for _a decade_ without solution, except by a plugin that isn't available through OnDemand.

[Leo Dopson]

I tried membersOf but it takes the custom field as a literal. If there is a way to get the value of the field before the lookup actually occurs, that would do the trick. Like: membersOf(valueOf(groupA))

[Scott Harman]

Hi Leo - I used the membersOf, but I will have to look and see exactly how it was filtered

[Leo Dopson]

Scott,

Did you get an answer to this?

I would like to construct q JQL where the user (asignee) belongs to a group whose value is in a custom field.

userA is in groupA

customfield contains groupA

Leo