SQL query to get the user details based on the email address

Kishore June 16, 2020

Hi All,

Is there any SQL query to get the user details like username, email_address, Full name, display name, groups that are associated with the user, and last login. 

Regards,
Kishore.

2 answers

1 accepted

0 votes
Answer accepted
Kishore June 16, 2020

I figured it out. Here is the SQL query:

 

select DISTINCT u.USER_NAME, u.display_name, u.email_address, array_to_string(array_agg(g.group_name), ', ') as "Group Names"
from cwd_user u JOIN cwd_directory d ON u.DIRECTORY_ID = d.id
JOIN cwd_group g ON d.id = g.DIRECTORY_ID
where u.LOWER_EMAIL_ADDRESS = 'Email_address'
group by u.USER_NAME, u.display_name, u.email_address

 

Stan Dalenc April 20, 2022

awesome thanks

0 votes
Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
June 16, 2020

Read cwd_user for the list of accounts and most of that data.

Join it to cwd_membership  to see the groups (and cwd_group if you want to see the names of the groups rather than the ids)

Kishore June 16, 2020

Hi @Nic Brough -Adaptavist- ,

I verified cwd_user, cwd_membership, cwd_group, and cwd_directory tables. But I did not found any common column for the cwd_membership with remaining tables to write a join query.

Regards,
Kishore.

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events