It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Which Database table stores User Profiles?

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.

3 answers

1 accepted

4 votes
Answer accepted

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

Thanks Jing. That helped!

Glad I could help.

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

You need to clear the cache

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

Suggest an answer

Log in or Sign up to answer
This widget could not be displayed.
This widget could not be displayed.
Community showcase
Posted 3 hours ago in United States

Atlassian Government Symposium

Good day members, Not sure if you are aware that Atlassian will be hosting a Government Symposium on May 1st, 2019 in Washington DC. For more details please click here If you have any questions...

12 views 0 0
View post

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you