To get the list of Jira Service Management portal-only customers from the database
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
When a customer is added to Jira Service Management it can be created as a portal-only account. In some cases, it is required to get a list of the customers portal-only, for example, you want to migrate and don't want to migrate the portal-only accounts.
Environment
Jira Service Management 4.20.5 and 5.1.0.
Solution
There is a possibility to invite customers to access your Jira Service Management portal project. The customer can be a created as portal-only account and you can find the customer list under the Customer
option in your project definition. As described in Adding customers.
The customers are added as shown in the image below:
The user account is created and can be listed in Jira User Management:
To get the customer portal-only users to run the following SQL statement were tested in MySQL and PostgreSQL:
1
2
3
4
5
6
7
8
9
select id, directory_id, lower_user_name from cwd_user where id in (select DISTINCT u.id from cwd_user u
JOIN cwd_membership m ON u.id = m.child_id AND u.directory_id = m.directory_id
JOIN licenserolesgroup lrg
ON Lower(m.parent_name) = Lower(lrg.group_id)
JOIN cwd_directory d
ON m.directory_id = d.id
WHERE d.active = '1'
AND u.active = '1'
AND license_role_name IN ('jira-servicedesk','jira-core','jira-software'))The result will be in the example case:
1
2
3
4
"user_id" "directory_id" "lower_user_name"
10101 1 "test"
10100 1 "franklin.ayres@test.tst.com"
10102 1 "frayres@test.tst.com"To list the users created from the email or sign up:
1
select id, directory_id, lower_user_name from cwd_user where id in (select user_id from cwd_user_attributes where attribute_name = 'synch.servicedesk.requestor')The result of the query above will be:
1
2
3
"user_id" "directory_id" "lower_user_name"
10100 1 "franklin.ayres@test.tst.com"
10102 1 "frayres@test.tst.com"Was this helpful?