How to display the user profile information in Confluence via SQL query

Still need help?

The Atlassian Community is here for you.

Ask the community

Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.

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

Problem Statement

Sometimes, it's useful to print out the information (email, phone number, office location etc) for all your users in Confluence for record purposes.


The following SQL query will provide you with the information sorted alphabetically using the username.

  • Username
  • First name

  • Last name

  • Office location

  • Website

  • Department

  • Phone number
  • Instant Messenger (IM)
  • Position
  • Email address
  • Last modified date
Postgres
select 
u.username, cw.first_name, cw.last_name, o.entity_key,
o.string_val , cw.email_address, c.LASTMODDATE 
from os_propertyentry o join content c on o.entity_id=c.CONTENTID 
join user_mapping u on u.user_key=c.USERNAME 
join cwd_user cw on cw.user_name=u.username 
where o.entity_key like '%confluence.user.profile%' order by u.username asc;
MySQL
select 
u.username, cw.first_name, cw.last_name, o.entity_key,
o.string_val , cw.email_address, c.LASTMODDATE 
from OS_PROPERTYENTRY o JOIN CONTENT c on o.entity_id = c.CONTENTID 
join user_mapping u on u.user_key=c.USERNAME 
join cwd_user cw on cw.user_name=u.username 
where o.entity_key like '%confluence.user.profile%' order by u.username asc;

The actual output will show each defined parameter for each Confluence user:

 username | first_name | last_name |             entity_key             |    string_val     |     email_address     |       lastmoddate       
----------+------------+-----------+------------------------------------+-------------------+-----------------------+-------------------------
 framsey  | Frank      | Ramsey    | confluence.user.profile.website    | www.atlassian.com | framsey@atlassian.com | 2020-03-04 04:47:02.108
 framsey  | Frank      | Ramsey    | confluence.user.profile.phone      | +61-2-92621443    | framsey@atlassian.com | 2020-03-04 04:47:02.108
 framsey  | Frank      | Ramsey    | confluence.user.profile.location   | Sydney            | framsey@atlassian.com | 2020-03-04 04:47:02.108
 framsey  | Frank      | Ramsey    | confluence.user.profile.position   | Submarine Captain | framsey@atlassian.com | 2020-03-04 04:47:02.108
 framsey  | Frank      | Ramsey    | confluence.user.profile.department | Atlassian Navy    | framsey@atlassian.com | 2020-03-04 04:47:02.108
(5 rows)

Last modified on Jun 22, 2023

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.