nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Kishore_l0q82n

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.

Accepted answers

Kishore_l0q82n

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

All comments

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_l0q82n

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.

Kishore_l0q82n

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

awesome thanks

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

server

sql

jira-software

jira-core

jira-core-server