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

Jonas Andersson
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
July 23, 2019

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?

 

1 answer

1 accepted

0 votes
Answer accepted
Andy Heinzer
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
July 24, 2019

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

Jonas Andersson
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
July 25, 2019

Thanks for your work. This brought me pretty close, but it should only include certain project categories, where in this query could i include this, seems a tiny mod on your query does this:

 

{noformat}

SELECT p.pname as ProjN, pr.NAME as roleN, pra.roletypeparameter, cmem.child_name as username, u.display_name as fullname

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' and pr.NAME in ('different','project','roles','i','want','to','limit'','for')

and pID in (select SOURCE_NODE_ID from nodeassociation where ASSOCIATION_TYPE = 'ProjectCategory' and SINK_NODE_ID in ('11801','11802','11803')) # <-- project category IDs

order by pID, p.pname;

{noformat}

Am i doing that right? Looks like it's working..

Jonas Andersson
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
July 25, 2019

After a closer look, these queries can pull internal jira groups, but they do not seem to work for Active Directory groups? Is this just querying the internal groups, can i somehow query all?

Andy Heinzer
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
July 25, 2019

Hi Jonas,

Thanks for noting this group difference.  Upon closer inspection, it looks like the group names that have capital letters in them won't match that query, so I tweaked it again.  Try this one:

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

The previous query was trying to join the projectroleactor.roletypeparameter to cwd_membership.lower_group_name, which can work, but will exclude all groups with any capital letters.  I changed it to join on the cwd_membership.group_name column instead, and I believe that will help match these LDAP groups more accurately.

I believe the rest of your query to limit by the role names appears to be correct as well.

Andy

Like Jonas Andersson likes this
Jonas Andersson
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
July 25, 2019

MY BAD!!! Working as expected it seems..

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events