You're on your way to the next level! Join the Kudos program to earn points and save your progress.
Level 1: Seed
25 / 150 points
1 badge earned
Challenges come and go, but your rewards stay with you. Do more to earn more!
What goes around comes around! Share the love by gifting kudos to your peers.
Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!
Join now to unlock these features and more
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.
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
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)