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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.