Finding not used project roles

[m93]

As within a time JIRA project roles get bigger than Admin expectations, I'm looking for a way to detect those project roles which are not used.

I see here two ways:

1. SQL - magic for me

2. rest api - /rest/api/2/project/{projKey}/role and then went trough each role to collect empty one

Did someone have had similar optimization task?

[m93 · Accepted solution]

I find SQL for that purpose. It can be easily modified to find groups also.

SELECT p.pname, pr.NAME, pra.roletype, u.display_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.lower_user_name = pra.ROLETYPEPARAMETER
INNER JOIN cwd_user u ON u.user_name = au.user_key
order by p.pname, pr.name, u.display_name

source

[Daniel Vargas Llopis]

Hi Marcin,

If the list is not very long, this can be done manually via the Project Role Browser (/secure/project/ViewProjectRoles.jspa) and then clicking on 'View Usage' for each Project Role.

Thanks

[m93]

Daniel, you are right. That's another option. My case is focused on large instance so i will stay with rest.