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

[Kishore]

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.

[Kishore · Accepted solution]

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

[Nic Brough _Adaptavist_]

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]

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.