Query to get all projects using certain groups

[PaulaA]

Hi, 

I need to find the way to get all projects where a certain group is used . Any ideas? I don't see the link in the database for this

Thanks

[PaulaA · Accepted solution]

Here a useful link https://confluence.atlassian.com/jirakb/how-to-identify-group-usage-in-jira-441221524.html?_ga=2.253363467.360573586.1545030767-283587560.1542704789

I could solve it using this

[Ankush Kumar]

Hi @PaulaA 

I have a same requirement. Can you please help me with the script. If you have already resolved this.

[Marc Minten _EVS_]

Hi,

I guess you mean User groups used in Project roles ?

In that case, this db query might help:

SELECT distinct p.pname, p.ID projectid, a.PROJECTROLEID roleid, a.ROLETYPEPARAMETER
FROM dbo.projectroleactor a
    join dbo.project p on a.pid=p.id
WHERE roletype like '%group%'
ORDER BY p.pname, a.ROLETYPEPARAMETER

[PaulaA]

Hi

Thanks.

I have the following query:

SELECT p.pname, p.PKEY, p.LEAD, pr.NAME, u.user_name, u.display_name, u.email_address
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 = 'Administrators' and u.active=1
ORDER BY p.pname;

this gives me all administrators for the projects that I have but I still need to filter and get only the projects where certain user groups are used.

The idea is that the administrators that are using certain groups will receive an email

[David WS Ingty]

SELECT p.pname as project_name, p.pkey, pr.NAME as project_role, u.display_name as user_displayName, u.lower_email_address as email_address
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