How to find users that use Gravatar in Jira
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. 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
Summary
Learn how to find all users that make use of Gravatar for their profile in Jira.
Solution
As such Jira doesn't save the URL to the gravatar or saves this anywhere in the database. Instead Jira tries to collect the gravatar (if enabled) for every user that has the default profile picture.
For users, that had a different avatar first and then switched to gravatar, you can find them using the following database query:
1
2
3
4
5
6
SELECT u.lower_user_name,a.* FROM app_user AS u
LEFT JOIN propertyentry AS pe ON pe.entity_id=u.id
LEFT JOIN propertynumber AS pn ON pe.id=pn.id
LEFT JOIN avatar AS a ON a.id=pn.propertyvalue
WHERE
entity_name = 'ApplicationUser' AND property_key='user.avatar.id' AND a.filename = 'Avatar-default.svg';NOTE: This query has been tested with Postgres and might need to be adjusted for other databases.
Also this will not work for users, that never changed their avatar, as this would be set as default.
Was this helpful?