Retrieve the list of users with a specific prefix in their email address using a database query
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
This article contains information on how to retrieve user information like the last login timestamp, email address and other details from the database, which are associated with some specific users' email addresses in suffixes.
Environment
Jira Data Center
Solution
This was written and tested using a PostgreSQL DB, so you may need to tweak it depending on the database you are using.
Users can be associated with different email addresses to get a list of users with a specific email suffix. The below query can be helpful in retrieving those details from the database; it includes the directory details, email address, last login timestamp, and other details.
The queries below are pre-configured, but you have to adjust the email_address column value with the value in the following section for each database query version to fetch the directory details, email address, their last login details
1
2
3
4
5
6
7
8
9
10
11
select u.lower_user_name as "Lower Username", u.email_address as "Email Address", gd.id as "Group Directory Name", gd.directory_name as "Group Directory Name", gd.active as "Group Directory Status" , to_timestamp(CAST(ca.attribute_value AS BIGINT)/1000) AS "Last Login"
from cwd_user u
left join app_user a on a.lower_user_name = u.lower_user_name
join cwd_directory ud on ud.id = u.directory_id
left join cwd_membership m on m.child_id = u.id
left join cwd_group g on g.id = m.parent_id
left join cwd_directory gd on gd.id = g.directory_id
left join cwd_user_attributes ca on ca.user_id= u.id and ca.attribute_name = 'login.lastLoginMillis'
where u.email_address like '%xyz.com'
group by u.lower_user_name ,u.email_address,gd.id,gd.directory_name ,gd.active,to_timestamp(CAST(ca.attribute_value AS BIGINT)/1000)
order by u.lower_user_name ASC;To get the list of all users belonging to the Jira Internal Directory with their last login timestamp and email address.
1
2
3
4
5
6
7
8
9
10
11
select u.lower_user_name as "Lower Username", u.email_address as "Email Address" , gd.id as "Group Directory Name", gd.directory_name as "Group Directory Name", gd.active as "Group Directory Status" , to_timestamp(CAST(ca.attribute_value AS BIGINT)/1000) AS "Last Login"
from cwd_user u
left join app_user a on a.lower_user_name = u.lower_user_name
join cwd_directory ud on ud.id = u.directory_id
left join cwd_membership m on m.child_id = u.id
left join cwd_group g on g.id = m.parent_id
left join cwd_directory gd on gd.id = g.directory_id
left join cwd_user_attributes ca on ca.user_id= u.id and ca.attribute_name = 'login.lastLoginMillis'
where gd.id = 1
group by u.lower_user_name ,u.email_address,gd.id,gd.directory_name ,gd.active,to_timestamp(CAST(ca.attribute_value AS BIGINT)/1000)
order by u.lower_user_name ASC;To determine the count of users associated with the Directory using an email address containing a particular suffix:
1
select gd.directory_name as "Group Directory Name" , count(1) from cwd_user u, cwd_directory gd where u.directory_id=gd.id and u.email_address like '%xyz.com' group by gd.directory_name;To determine the number of users with domain-based email addresses across different directories.
1
2
3
4
5
6
select gd.directory_name as "Group Directory Name", REGEXP_MATCHES(u.email_address,'.+@(.*)$') as "Email Domain" , count(1) from cwd_user u, cwd_directory gd where u.directory_id=gd.id group by gd.directory_name , REGEXP_MATCHES(u.email_address,'.+@(.*)$');
Group Directory Name | Email Domain | count
-------------------------+--------------+-------
Jira Internal Directory | {xyz} | 3
Jira Internal Directory | {xyz.com} | 1Was this helpful?