SQL to translate a JIRAUSERxxxxx to a User Name or ID on the CWD_USER table

Tom Gross August 4, 2021

There are a couple of columns the JIRA SQL database that list 'JIRAUSERxxxxx' instead of the standard User_Name or LAN ID.  I understand there was a change awhile back in JIRA.

I'm trying to figure out the SQL needed to translate the JIRAUSERxxxxx ID to a row on the CWD_USER table.  Originally I thought, "Hey ... the xxxxx corresponds to the ID column in the CWD_USER table!"  unfortunately that's not the case.

JIRA seems to be able to do this in the GUI.  Can anyone point me in the right direction which tables I need to join to resolve this via SQL?

Thanks!

1 answer

1 accepted

12 votes
Answer accepted
Alex
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
August 4, 2021

Hi @Tom Gross 

I believe the JIRAUSERxxxxx is the user_key column at the app_user table. Give a try on this query (tested on PostgreSQL):

select app_user.user_key, cwd_user.lower_user_name from app_user , cwd_user where app_user.lower_user_name = cwd_user.lower_user_name

I did test it myself for a few user migration/consolidations I did for clients.

I hope it helps. If this answer helps solve the problem, please come back and mark this answer as solved to help other community members with the same challenge. If not, you are welcome to share your solution as well.

Cheers,

Alex

Tom Gross August 4, 2021

Exactly what I was looking for.  Thanks!

Like Alex likes this
CAMS_Cloud administrator
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
April 20, 2023

Thanks!

Roman Liubymenko
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
January 24, 2024

Thank you)

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
SERVER
TAGS
AUG Leaders

Atlassian Community Events