How to display the user profile information in Confluence via SQL 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
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 query
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 query
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)Was this helpful?