How to get data on why user gets mail notification from Confluence Questions without watching the page

Still need help?

The Atlassian Community is here for you.

Ask the community

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. 


Last modified on Feb 3, 2021

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.