Peter Zehrer Absent Member.
Absent Member.
709 views

Stake Holders Groups list

Jump to solution

Hi,

 

Can any one pelase let me know the SQL query to pull the list of Security groups assigned to project as stake holders.  I am able to get the list of Resources but not groups.  Attached screenshot.


Thanks,

Vamsee.

Tags (1)
0 Likes
1 Solution

Accepted Solutions
Established Member.. Utkarsh_Mishra
Established Member..

Re: Stake Holders Groups list

Jump to solution

Here you go... the SQL shows the users and the users in the security group that are assigned as the stakeholder.

 

The second part of the query list the security group users. You can modify this to simply get the security group name or the ID.

 

 

SELECT DISTINCT gpu.user_id, 'User' TYPE, 'N/A' security_group
           FROM itg_trustee t, itg_group_participant_users gpu, pm_projects p
          WHERE p.project_id = <project_id>
            AND t.project_type_id = p.project_type_id
            AND t.trustee_type_code = 1
            AND gpu.trustee_id = t.trustee_id
UNION /* Query for Security group*/
SELECT DISTINCT us.user_id, 'User from Security Group' TYPE,
                ks.security_group_name security_group
           FROM knta_user_security us,
                itg_trustee t,
                itg_security_group_trustees sgt,
                pm_projects p,
                knta_security_groups_nls ks
          WHERE p.project_id = <project_id>
            AND t.project_type_id = p.project_type_id
            AND t.trustee_type_code = 1
            AND sgt.trustee_id = t.trustee_id
            AND us.security_group_id = sgt.security_group_id
            AND ks.security_group_id = us.security_group_id;

 

Cheers..
Utkarsh Mishra

-- Remember to give Kudos to answers! (click the KUDOS star)
1 Reply
Established Member.. Utkarsh_Mishra
Established Member..

Re: Stake Holders Groups list

Jump to solution

Here you go... the SQL shows the users and the users in the security group that are assigned as the stakeholder.

 

The second part of the query list the security group users. You can modify this to simply get the security group name or the ID.

 

 

SELECT DISTINCT gpu.user_id, 'User' TYPE, 'N/A' security_group
           FROM itg_trustee t, itg_group_participant_users gpu, pm_projects p
          WHERE p.project_id = <project_id>
            AND t.project_type_id = p.project_type_id
            AND t.trustee_type_code = 1
            AND gpu.trustee_id = t.trustee_id
UNION /* Query for Security group*/
SELECT DISTINCT us.user_id, 'User from Security Group' TYPE,
                ks.security_group_name security_group
           FROM knta_user_security us,
                itg_trustee t,
                itg_security_group_trustees sgt,
                pm_projects p,
                knta_security_groups_nls ks
          WHERE p.project_id = <project_id>
            AND t.project_type_id = p.project_type_id
            AND t.trustee_type_code = 1
            AND sgt.trustee_id = t.trustee_id
            AND us.security_group_id = sgt.security_group_id
            AND ks.security_group_id = us.security_group_id;

 

Cheers..
Utkarsh Mishra

-- Remember to give Kudos to answers! (click the KUDOS star)
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.