要約

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.

環境

Jira Service Management 4.20.5 and 5.1.0.

ソリューション

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:

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"