To get the list of Jira Service Management portal-only customers from the database

Still need help?

The Atlassian Community is here for you.

Ask the community

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 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 not 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"

Last modified on Aug 9, 2022

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.