How to get all users of a project with Scriptrunner

[Gauthier Gaukel]

Hello Guys,

at the moment, I'm writing a script where I do an export of all users in a project (including groups). The Main problem is, that I don't know how to get the user of a project. With the project Manager, I'm not able to access them. Has anyone an idea? 

Kind regards,

Gauthier

[Vikrant Yadav · Accepted solution]

Hi @Gauthier Gaukel  Run below script in Script Console to get users in Project role, groups in project role and users which are having via user group :- 

Basic the script is running a sql query and a result giving a table, kindly update project key as per your requirement.

SQL query source :- https://confluence.atlassian.com/jirakb/retrieve-a-list-of-users-assigned-to-project-roles-in-jira-server-705954232.html

import com.atlassian.jira.component.ComponentAccessor

import groovy.sql.Sql

import org.ofbiz.core.entity.ConnectionFactory

import org.ofbiz.core.entity.DelegatorInterface

import java.sql.Connection

def delegator = (DelegatorInterface) ComponentAccessor.getComponent(DelegatorInterface)

String helperName = delegator.getGroupHelperName("default")

def sqlStmt = """

SELECT p.pkey as "Project key", pr.name as "Role name", u.lower_user_name as "Username", u.display_name as "Display name", u.lower_email_address as "e-Mail", null as "Group name"

FROM projectroleactor pra

INNER JOIN projectrole pr ON pr.ID = pra.PROJECTROLEID

INNER JOIN project p ON p.ID = pra.PID

INNER JOIN app_user au ON au.user_key = pra.ROLETYPEPARAMETER

INNER JOIN cwd_user u ON u.lower_user_name = au.lower_user_name

WHERE pra.roletype = 'atlassian-user-role-actor'

  AND p.pkey = 'VYT'

UNION

SELECT p.pkey as "Project key", pr.name as "Role name", cmem.lower_child_name as "Username", u.display_name as "Display name", u.lower_email_address as "e-Mail", cmem.lower_parent_name as "Group name"

FROM projectroleactor pra

INNER JOIN projectrole pr ON pr.ID = pra.PROJECTROLEID

INNER JOIN project p ON p.ID = pra.PID

INNER JOIN cwd_membership cmem ON cmem.parent_name = pra.roletypeparameter

INNER JOIN app_user au ON au.lower_user_name = cmem.lower_child_name

INNER JOIN cwd_user u ON u.lower_user_name = au.lower_user_name

WHERE pra.roletype = 'atlassian-group-role-actor'

  AND p.pkey = 'VYT'

ORDER BY 1, 2, 3;

"""

Connection conn = ConnectionFactory.getConnection(helperName)

Sql sql = new Sql(conn)

String result = """

<table>

  <tr>

    <th>Project key</th>

    <th>Role name</th>

    <th>Username</th>

    <th>Display name</th>

    <th>e-Mail</th>

    <th>Group name</th>

  </tr>

"""

sql.eachRow(sqlStmt){ it ->

    result += """

        <tr>

          <td>${it.getAt("Project key")}</td>

          <td>${it.getAt("Role name")}</td>

          <td>${it.getAt("Username")}</td> 

          <td>${it.getAt("Display name")}</td> 

          <td>${it.getAt("e-Mail")}</td> 

          <td>${it.getAt("Group name")}</td> 

        </tr>

"""

    }

return result+"</table>"

sql.close()

[Gauthier Gaukel]

@Vikrant Yadav Thank you so much. You're a life saver.