Query list of user who have "Subscribe to all blog posts" checked

[Mike]

I am working to find a list of users who have "Subscribe to all blog posts" checked in user email settings. 

The closest I could find is here:

https://confluence.atlassian.com/confkb/manually-uncheck-all-email-notifications-from-users-using-sql-queries-941591426.html

However, this is for unchecking all email notifications.

Would anyone know the correct sql query for only "subscribe to all blog posts?"

Confluence version 7.13.0

Thanks 

[Diego · Accepted solution]

Hello there Mike!

Getting this data should be easy if you have direct access to your database. Looking through the tables, we can see that this information is stored into the notifications table.

One thing I would like to tell you is to always be careful when touching your database.

Now that we have that taken care of, let us proceed to select how many users have this option checked:

1. Go to you database management tool
2. Run the following query:

SELECT * FROM notifications
WHERE  contentid IS NULL AND spaceid IS NULL AND contenttype like 'blogpost';

Since the query above is a select statement, there should be no need to shutdown Confluence before doing it.

Here is the thing. When a user selects to Subscribe to all blog posts, and entry will be created at the notifications table. This entry will have both contentid and spaceid as null while the contenttype will be set as blogpost.

However, if you need to remove those entries you will need to run the following procedure.

We have a few important measures to be taken before the procedure:

- schedule a downtime for Confluence

- backup your database

- backup <confluence-home>

- backup <confluence-install>

Here is the procedure

1. Start the scheduled downtime
2. Shutdown Confluence
3. Run the following query:

DELETE FROM notifications
WHERE  contentid IS NULL AND spaceid IS NULL AND contenttype like 'blogpost';

This query should remove all users from the option to Subscribe to all blog posts.

Again, always be careful when dealing with your database and backup everything before proceeding. If anything happens, you will have data from a working point that will allow rollback.

Let us know your thought on this!

[Mike]

Thanks Diego - This is very helpful. Do you know of how I can translate who the users are that have them active? Under username it populates a string of number/letters 

[Diego]

Hi Mike! Adding a JOIN clause to the SELECT takes care of it! The able we want to look into is user_mapping. This table has the column called user_key, which is equivalent to the username found under the notifications table.

The following query should take care of your request:

SELECT * FROM notifications
JOIN user_mapping ON user_mapping.user_key = notifications.username
WHERE  contentid IS NULL AND spaceid IS NULL AND contenttype like 'blogpost';

 Let us know!