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

[Tom Gross]

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!

[Alex · Accepted solution]

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]

Exactly what I was looking for.  Thanks!

[marco_mancini_exprivia_com]

Thanks!