How to get Duplicate users from 2 different directories from Jira Database.

Tanu Ranjan Bharadwaj_hyzac3

Hello Team,

I have a requirement where I have to get the full list of Duplicate Users from cwd_user table.

Duplicate means, the user is created in 2 different directories not Jira Internal Directories.

Can you give me the sql for the same. I am using Oracle databse here.

 

Thanks in advance.

 

Best Regards,

Tanu Ranjan Bharadwaj

Suvarna Gaikwad_3o435r

@Tanu Ranjan Bharadwaj You can do this in below manner in two steps.

Step1: Get directory IDs of respective directories using below sql

select ID, DIRECTORY_NAME from CWD_DIRECTORY

Please take note of IDs of two directories you want to compare users from. Lets say firstdirectoryid and seconddirectoryid

These will be needed in following sql.

Step 2: Execute below JQL to get duplicate users.

select ID, USER_NAME from CWD_USER where DIRECTORY_ID = <firstdirectoryid> AND USER_NAME in (select USER_NAME from CWD_USER where DIRECTORY_ID = <seconddirectoryid>)

P.S. replace firstdirectoryid and seconddirectoryid with IDs from 1st sql

Hope this helps.