Which Database table stores User Profiles?

[Yagnesh Bhat]

I need to do a bulk update of many user profiles' phone numbers, so need to know precisely what table actually stores this data in confluence. Please advise.

[jing_hwa_cheok · Accepted solution]

Hi Yagnesh,

User profile information is stored in OS_PROPERTYENTRY table. For user phone number, look for the row with the entity_key confluence.user.profile.phone.

select string_val,entity_id from OS_PROPERTYENTRY where entity_key='confluence.user.profile.phone';

The column entity_id in the table refer to the CONTENTID column in the CONTENT table with the CONTENTTYPE USERINFO. You can join this two table to find the username that the phone number belongs to.

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';

Hope this helps!

Regards,
Jing Hwa

[Yagnesh Bhat]

Thanks Jing. That helped!

[jing_hwa_cheok]

Glad I could help.

[Sandro Silva]

Hi,

I've updated a user property on OS_PROPERTYENTRY table but I don't see the new value on my profile.

Is there anything that I need to do after the update?

Thanks.

Regards,

Sandro

[Mustafa Abusalah]

You need to clear the cache

[Kasi]

I am looking for 2 columns email and phone number. Please help

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