List all Customers who have access to a JSM Project

Fabian Duft_uovsxg

Hi,

Is there a way to list all customers that have access to a JSM project?
I could find the tables for developing a DB query that provides a list of of customers inside an organization who have access to a project.

SELECT p.pkey, au.lower_user_name AS "Customer", o."NAME" AS "Organization Name"
FROM project p
INNER JOIN "AO_54307E_ORGANIZATION_PROJECT" op ON p.id = op."PROJECT_ID"
INNER JOIN "AO_54307E_ORGANIZATION" o ON o."ID" = op."ORGANIZATION_ID"
INNER JOIN "AO_54307E_ORGANIZATION_MEMBER" om ON om."ORGANIZATION_ID" = o."ID"
INNER JOIN app_user au ON au.user_key = om."USER_KEY"
WHERE p.pkey = <pkey>
ORDER BY o."NAME"; 

Missing are still those customers who are not member of an organization but have direct access to the project.

I did only find a way to list the customers via project roles, but this is not working if the customers do not have any project role assigned.

SELECT 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 = 'Service Desk Customers' AND p.pkey = <pkey>;

The best solution would be to extend the first query, to also match customers which are no member of an organization.

Thank you

Matthias Gaiser _K15t_

Hi @Fabian Duft

I'm not too much involved with the DB query parts because I like to interact on a higher level, the REST API, usually.

However, after analyzing JSM's page to view the customers, I've extracted this REST call from there - maybe that works for you as well?

YOUR_JIRA_URL/rest/servicedesk/1/pages/people/customers/pagination/YOUR_PROJECT_KEY/search?query=&page=1

If you replace YOUR_JIRA_URL and YOUR_PROJECT_KEY with the corresponding values, it should return all your customers for the given project (service desk). If you have organizations in there, it will return the organizations which you can either expand via your db query or via the REST API to get the users in an organization.

Hope this helps,
Matthias.