How to get all users of a project with Scriptrunner

Gauthier Gaukel September 26, 2023

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

1 answer

1 accepted

2 votes
Answer accepted
Vikrant Yadav
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
September 26, 2023

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 September 27, 2023

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

Like Vikrant Yadav likes this
Likhitha Kosanam May 23, 2024

@Vikrant Yadav Thank you ..very helpful

Like Vikrant Yadav likes this

Suggest an answer

Log in or Sign up to answer