How to Audit Personal User Email Settings Using the Database
Platform Notice: Data Center - This article applies to Atlassian products on the Data Center platform.
Note that this knowledge base article was created for the Data Center version of the product. Data Center knowledge base articles 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
Purpose
It may be useful for an Administrator to check the users' email configurations in their personal profiles (Profile > Settings > Email). For example, an administrator may be interested in knowing how many people are subscribing to the Daily Digest, Recommend Updates, or Auto-watching their own content. This can be helpful in estimating the volume of email notifications which are sent out by Confluence for load planning purposes.
Solution
The panel below illustrates how to use SQL queries to retrieve a count of each enabled setting in Profile > Settings > Email. These sample queries demonstrate where these settings are stored database-side, and you can modify them as needed to suit your use case.
Autowatch - Pages and blog posts that you create, edit or comment on will automatically be watched for future changes.
SELECT COUNT(*) FROM OS_PROPERTYENTRY WHERE entity_key = 'confluence.prefs.watch.my.own.content' AND boolean_val = 't';
Subscribe to daily updates - You will receive a daily email report summarising all changes that you have permission to view.
SELECT COUNT(*) FROM NOTIFICATIONS WHERE digest = 't';
Subscribe to all blog posts - You will receive an email when any blog post is added, even if it is in a space you aren’t watching. You won’t receive emails for comments on those blog posts, or changes to them.
SELECT COUNT(*) FROM NOTIFICATIONS WHERE contenttype = 'blogpost';
Subscribe to network - You will receive an email when anyone you are following adds or changes content.
SELECT COUNT(*) FROM NOTIFICATIONS WHERE network = 't';
Subscribe to new follower notifications - You will receive an email when anyone chooses to follow you.
SELECT COUNT(*) FROM OS_PROPERTYENTRY WHERE entity_key = 'confluence.prefs.notify.on.new.followers' AND boolean_val = 't';
Notify on my actions - You will receive notifications for changes you make, in addition to other people's changes.
SELECT COUNT(*) FROM OS_PROPERTYENTRY WHERE entity_key = 'confluence.prefs.notify.for.my.own.actions' AND boolean_val = 't';
Show changed content - Check this option to see changes made in Edit notification emails.
SELECT COUNT(*) FROM OS_PROPERTYENTRY WHERE entity_key = 'confluence.prefs.email.show.diff' AND boolean_val = 't';
Subscribe to recommended updates - You will receive an email with recommended items based on comments and likes
SELECT COUNT(*) FROM OS_PROPERTYENTRY WHERE entity_key = 'confluence.prefs.daily.summary.receive.updates' AND boolean_val = 't';