MySQL query to list all project roles based on a project category

Jonas Andersson

I am trying to create a confluence page that lists all project roles for all projects a specific project category, both users and groups using a MySQL query against the Jira database.

Searching similar threads and copy pasting some stuff together got me this far:

SELECT pkey, p.pname, pr.NAME, 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 where pr.NAME in ('Administrators','Developers') order by pkey,NAME;

However this only shows the users, and seems to exclude if a (ad)group is added to a role. Can someone with a bit stronger MySQL-fu help me to include both users, (members of) groups and the project category to this query?

 

Andy Heinzer

Hi Jonas,

I see you are looking to try to find project role membership via some SQL queries.  I think I can help here.   You original query is pretty close, but there is a problem with it.  Namely, your last two joins of

INNER JOIN app_user au ON au.lower_user_name = pra.ROLETYPEPARAMETER
INNER JOIN cwd_user u ON u.user_name = au.user_key

This could produce some results, but it's flawed because of the way that Jira handles user account renames.  If an account has never been renamed, your query could work.  But because the username in the cwd_user table can change and the user_key in the app_user table cannot, for any account that has ever had a rename, your query won't work.  Instead of using that, try this:

SELECT p.pkey, p.pname, pr.NAME, au.lower_user_name AS 'Current Username' 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 

The username that appears in the projectroleactor is not necessarily the users current name, but rather the first name that account had (aka app_user.userkey).  So that was one problem, but there is also another problem because LDAP users could have capitalization in their usernames, which is why we can use the lower_user_name field to match these up correctly every time, regardless of capitalization differences.   There is a notable limitation of this query though: it does not show you users that have a role because they are a member of a group that has been assigned that role.

During my search on this topic I came across an old KB of https://confluence.atlassian.com/jirakb/how-to-get-a-list-of-users-assigned-to-project-roles-for-your-project-705954232.html

I found the SQL queries there were very similar to yours, so perhaps this is our fault for not keeping this KB updated.  Sorry about that.  I have updated this KB just now, because I found the same error in regards to trying to incorrectly join the app_user and cwd_user tables.  That KB might take a few moments to update the cache, but I have updated it with more accurate queries here.

Additionally, that page has a query to find the groups and their members for such project roles.  I have updated that query as well because it had the same account linking problem.  Try this too:

SELECT p.pname as ProjN, pr.NAME as roleN, pra.roletype, pra.roletypeparameter, cmem.child_name, 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 cwd_membership cmem ON lower_parent_name=pra.roletypeparameter
INNER JOIN app_user au ON au.lower_user_name = cmem.child_name
INNER JOIN cwd_user u ON u.lower_user_name = au.lower_user_name
WHERE pra.roletype = 'atlassian-group-role-actor' order by p.pname;

This will return all the projects that have a group assigned a role, and the members of that group.

I hope this helps.

Andy