How to get data on why user gets mail notification from Confluence Questions without watching the page
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
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
This article provides some examples of how to get information related to Confluence Questions directly from the external database if a user is getting the notifications without watching the Questions.
Solution
Please use the following SQL examples and adapt them as necessary to fulfill your needs.
1. List of users and what topics they are watching
select um.username as Username, l.name as Topic from content c
join content_label cl on cl.contentid=c.contentid
join label l on cl.labelid=l.labelid
join notifications n on n.labelid=l.labelid
join user_mapping um on um.user_key=n.username
where c.pluginkey='com.atlassian.confluence.plugins.confluence-questions:topic-metadata' order by username ASC;
You will need to replace the um.lower_username with the lower username of the user who is receiving the notification instead of admin.
2. Lists out the topics a user is watching
select l.name as Topic from content c
join content_label cl on cl.contentid=c.contentid
join label l on cl.labelid=l.labelid
join notifications n on n.labelid=l.labelid
join user_mapping um on um.user_key=n.username
where c.pluginkey='com.atlassian.confluence.plugins.confluence-questions:topic-metadata' and um.lower_username='admin' order by l.name ASC;
3. Lists out the questions that a user is watching
select c.title as Question, l.name as Topic from content c
join notifications n on n.contentid=c.contentid
join user_mapping um on um.user_key=n.username
join content_label cl on cl.contentid=c.contentid
join label l on l.labelid=cl.labelid
where c.pluginkey='com.atlassian.confluence.plugins.confluence-questions:question' and um.lower_username='admin' order by l.name ASC;
4. Lists out the questions that a user is watching because they watch the topic
select c.title as Question, l.name as Topic from content c
join content_label cl on cl.contentid=c.contentid
join label l on cl.labelid=l.labelid
join notifications n on n.labelid=l.labelid
join user_mapping um on um.user_key=n.username
where c.pluginkey='com.atlassian.confluence.plugins.confluence-questions:question' and um.lower_username='admin' order by l.name ASC;
5. Lists out the questions that a user is watching because they watch the space where the question was asked in
select c.title as Question, s.spacename as Space from content c
join spaces s on s.spaceid=c.spaceid
join notifications n on s.spaceid=n.spaceid
join user_mapping um on um.user_key=n.username
where c.pluginkey='com.atlassian.confluence.plugins.confluence-questions:question' and um.lower_username='admin' order by s.spacename ASC;
More Information
The Confluence Data Model includes the DB schema (under View our visualization ) which you can use to further refine these queries or craft new queries for your use case.