List all Customers who have access to a JSM Project

[Fabian Duft]

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_ · Accepted solution]

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.

[Fabian Duft]

Hi Matthias,

Thank you very much.
I did not find this REST call in the documentation, so I guess you reverse engineered this one? :D 
Could please also suggest how to get more than 50 results from the REST API?

Thank you
Fabian

[Matthias Gaiser _K15t_]

Yes, you're right 😁.

You could take the meta data of the result to page through the results. Here's an example result:

{
  "results": [],
  "query": "",
  "total": 7,
  "pageNumber": 1,
  "lastDeterminedPaged": 1,
  "totalHasBeenDetermined": true,
  "resultsPerPage": 50,
  "pageRange": 5
}

I'd assume you can return the next page by increasing the page parameter in the url to page=2.

If you look at the pageNumber and lastDeterminedPaged, I guess these can help you to determine if you're already on the last page or if there are more to be expected.

Cheers,
Matthias.

[Fabian Duft]

Hi Matthias,

Thank you I already figured this out myself, but glad to hear that you thought about the same solution.

Cheers,
Fabian