Query for all Jira Projects that have specific User-Group

[Judah]

I have a Jira User Group called "Jira Business Users"

I need a SQL query to find all Jira projects with that specific User Group assigned to it. 

If you can tell me what db or table to query that would be a big help too :)

[Ravi Sagar _Sparxsys_]

Hi @Judah 

First of all I would say you don't really need to query your database for doing these things.

Groups are not directly associated with projects. Here are some relations.

• Group can be used in project permission scheme
• User (assignee, reporter, watcher, voter, user custom field) can be part of a group
• Group can be used in a workflow
• Group can be used in a Group picker field used in a project
• Group can be used in your project roles

Take a look at the database schema but using Java API will be much more convenient. Use ScriptRunner for Jira if you already have it or evaluate it. You will find plenty of scripts in the library and also here on the community.

I hope it helps.

Ravi

[Judah]

Well that is great news! For a little more clarification:

In the Users and Roles section of a given Project I can see a list of Users and their roles. 

Some of these users are actual a User Group. Is their anyway I can see all of the Projects in which a specific group appears? 

[Judah]

@Ravi Sagar _Sparxsys_ Here is my script.

It is working to return all Projects in which <user> has Administrator Role. 

Can we change this so it returns all Projects in which <mygroup> has Administrator role? 

import com.atlassian.jira.bc.projectroles.ProjectRoleService
import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.security.roles.ProjectRoleActor
import com.atlassian.jira.security.roles.ProjectRoleManager
import com.atlassian.jira.util.SimpleErrorCollection

def groupManager = ComponentAccessor.getGroupManager()
def projectManager = ComponentAccessor.getProjectManager()
def projectRoleService = ComponentAccessor.getComponent(ProjectRoleService)
def projectRoleManager = ComponentAccessor.getComponent(ProjectRoleManager)
def errorCollection = new SimpleErrorCollection()


// Fetch project role object by name
def projectRole = projectRoleManager.getProjectRole("Administrators")

// Fetch group (case insensitive)
def mygroup = groupManager.getGroup("Jira Business PMO Management")

// Myself as user
def user = ComponentAccessor.jiraAuthenticationContext.getLoggedInUser()

def allProjects = projectManager.getProjects()
def adminProjects = []

allProjects.each{
// If I try to pass <mygroup> in place of <user> on the line below I get an error
def projectRoles = projectRoleManager.getProjectRoles(user, it)
if(projectRoles.find(){it.getName() == "Administrators"}){
adminProjects.push(it.key)
}
}

return (adminProjects)