export username,emails,Phone number

Question

I am the adming for confluence, i would like to export all usernames,email id and phone numbers.

note i to have access to postgress db.

i see all user id are under cwd_user and the phone numbers are under OS_PROPERTYENTRY table but somehow not able to link.
Please help

Kasi_7rjgwa · Answer

Hi Andre,

Thanks for help but it did  not helped.I am looking for 2 columns email and phone number.

Above query returned as below which will not help.

string_val                        |             username ----------------------------------------------------------+----------------------------------  XXX-XXX-XXXX                                          | c6aa8eee44dc96a00144dc985a4500ac  XXX-XXX-XXXX                                                       | c6aa8eee44dc96a00144dc985b3601da  XXX-XXX-XXXX                                                     | c6aa8eee44dc96a00144dc985a6e00e3  XXX-XXX-XXXX                                                      | c6aa8eee44dc96a00144dc9859f50057  XXX-XXX-XXXX                                                       | c6aa8eee44dc96a00144dc985b8a0255

Andy Heinzer · Answer

It looks like this same kind of question was answered over on Solved: Which Database table stores User Profiles? - Atlassian Communities

From that post @jing_hwa_cheokadded this SQL query to locate that information from the SQL database:

SELECT  OS_PROPERTYENTRY.string_val, CONTENT.USERNAME
FROM OS_PROPERTYENTRY
INNER JOIN CONTENT
ON OS_PROPERTYENTRY.entity_id=CONTENT.CONTENTID AND OS_PROPERTYENTRY.entity_key='confluence.user.profile.phone';

This SQL query appear to be able to join the phone number field from the OS_PROPERTY table  with the ID number in the content table that corresponds to the username.   Perhaps this will help.