To get the list of Jira Service Management portal-only customers from the database
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
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 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:
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:
"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:
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:
"user_id" "directory_id" "lower_user_name"
10100 1 "franklin.ayres@test.tst.com"
10102 1 "frayres@test.tst.com"