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