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
Community showcase
Posted in United States

Welcome new members!

Wow, I got swamped and now that I've finally gotten my head back above water, I find we have several new members! Welcome @Darren Keith lowman , @Amanda Babb , @Luis Zapata , @Joli Holmes , and @bh...

31 views 0 2
View post

Community Events

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

Find an event

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

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you