How can I see in which project a group is used?

[Jean Coudert]

Hi everyone,

I'm not a specialist to write/execute JQL queries and I try to execute the Code below to get which projects are using a defined Group.

SELECT
pra.roletypeparameter AS "Group",
pr.name AS "Project Role",
p.pname AS "Project"
FROM
projectroleactor pra
LEFT JOIN projectrole pr ON pra.projectroleid = pr.id
LEFT JOIN project p ON pra.pid = p.id
WHERE
pra.roletype = 'atlassian-group-role-actor'
AND pra.roletypeparameter in ('helpdesk', 'administrators');

And I get an error: Error in the JQL Query: 'SELECT' is a reserved JQL word. You must surround it in quotation marks to use it in a query. (line 1, character 1)

Why do I've to use quotation marks on all SQL commands ?

Thanks

[Earl McCutcheon · Accepted solution]

Hello @Jean Coudert ,

Thanks for reaching out and looking at the example you are using the syntax looks formatted as a SQL style database query, from this KB:

• How to identify group usage in JIRA

But, this only applies to a Jira server instance and is run from the database directly on the systems backend,  If you are on cloud or doing a search on the front end in server the JQL search will use a different approach and syntax.

As a referance point I recomend checking out the advanced JQL documentation for a full breakdown on the avaliable syntax and options avaliable:

• What is advanced searching in Jira Cloud?

  • Advanced search reference - JQL functions
  • Advanced search reference - JQL fields
  • Advanced search reference - JQL keywords
  • Advanced search reference - JQL operators
  • Advanced search reference - JQL developer status

However JQL is going to be used to return a list of issues that meet a specific criteria and will not give you data on the layout of a project directly.

An option you could look at in Jira cloud is the API with a few different calls you can collect the details you are looking for.

• https://developer.atlassian.com/cloud/jira/platform/rest/v3/

You noted wanting the groups associated to a project, noting that you will need to itterate the call for each project and scheme. and ther is some additional discussion on this viewable in the following thread:

•  https://community.atlassian.com/t5/Jira-questions/How-to-list-all-projects-a-user-has-access-to-using-Jira-s-REST/qaq-p/423593

First you can get a list of projects and their permission schemes via a Get all permission schemes:

• GET /rest/api/3/permissionscheme

OR Looking specifically at the individual permission scheme via Get permission scheme grants:

• GET /rest/api/3/permissionscheme/{schemeId}/permission

using this endpoint it will give you the full breakdown of what users groups or roles are associated to the permissions of a project.

and if using roles you would also want to take the role id generated from the above query and pass it to Get project role for project API endpoint to see the users  or groups associated to that specific role via:

• GET /rest/api/3/project/{projectIdOrKey}/role/{id}

Regards,
Earl

[Jean Coudert]

Hi Earl

Thanks for your full answer, I will spend sometime to understand how all his works but it seems to be quiet complex.

Regards

Jean