How to get the number of users who subscribed for email daily update.
Platform Notice: Data Center and Cloud By Request - This article was written for the Atlassian data center platform but may also be useful for Atlassian Cloud customers. If completing instructions in this article would help you, please contact Atlassian Support and mention it.
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
Both global email and space email configurations are stored in Confluence BANDANA table:
SELECT * FROM BANDANA where BANDANAKEY='atlassian.confluence.space.mailaccounts';
SELECT * FROM BANDANA where BANDANAKEY='atlassian.confluence.smtp.mail.accounts';Email Setting can be found in OS_PROPERTYENTRY table (example for daily updates):
select * from os_propertyentry where entity_key like 'confluence.prefs.daily.summary.receive.updates';The current obtained from Confluence source:
PROPERTY_USER_SUBSCRIBE_TO_DIGEST = "confluence.prefs.email.notify"
PROPERTY_USER_NOTIFY_FOR_MY_OWN_ACTIONS = "confluence.prefs.notify.for.my.own.actions"
PROPERTY_USER_WATCH_MY_OWN_CONTENT = "confluence.prefs.watch.my.own.content"
PROPERTY_USER_EMAIL_SHOW_DIFF = "confluence.prefs.email.show.diff"
PROPERTY_USER_NOTIFY_ON_NEW_FOLLOWERS = "confluence.prefs.notify.on.new.followers"
PROPERTY_USER_SUBSCRIBE_TO_RECOMMENDED_UPDATES = "confluence.prefs.daily.summary.receive.updates"
PROPERTY_USER_SUBSCRIBE_TO_RECOMMENDED_UPDATES_SET = "confluence.prefs.daily.summary.receive.updates.set"
PROPERTY_USER_RECOMMENDED_UPDATES_SCHEDULE = "confluence.prefs.daily.summary.schedule"Solution
Run the following database query to get the list of users who subscribe/not subscribe for the daily email update:
select username from user_mapping where username in (SELECT username FROM user_mapping JOIN os_propertyentry ON os_propertyentry.entity_name LIKE CONCAT('%',user_mapping.user_key ,'%') and os_propertyentry.entity_key like 'confluence.prefs.daily.summary.receive.updates');