Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
Celebration

Earn badges and make progress

You're on your way to the next level! Join the Kudos program to earn points and save your progress.

Deleted user Avatar
Deleted user

Level 1: Seed

25 / 150 points

Next: Root

Avatar

1 badge earned

Collect

Participate in fun challenges

Challenges come and go, but your rewards stay with you. Do more to earn more!

Challenges
Coins

Gift kudos to your peers

What goes around comes around! Share the love by gifting kudos to your peers.

Recognition
Ribbon

Rise up in the ranks

Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!

Leaderboard

List all Customers who have access to a JSM Project

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

1 answer

1 accepted

5 votes
Answer accepted
Matthias Gaiser _K15t_
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
Sep 05, 2022

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.

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_
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
Sep 05, 2022

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.


Hi Matthias,

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

Cheers,
Fabian

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events