How to get data on usage of Confluence Questions

Still need help?

The Atlassian Community is here for you.

Ask the community

Purpose

This article provides some examples of how to get information related to Confluence Questions directly from the external database.

Solution

Please use the following SQL examples and adapt them as necessary to fulfill your needs.

1. To get all existing questions:

SELECT *
FROM CONTENT
WHERE PLUGINKEY = 'com.atlassian.confluence.plugins.confluence-questions:question';


2. To get all answers from one specific question:

SELECT *
FROM CONTENT
WHERE PLUGINKEY = 'com.atlassian.confluence.plugins.confluence-questions:answer'
AND PARENTCCID = NNNNNN;

Replace NNNNNN with the number you got from the 'contentid' column in the previous query.


3. To get the creator and the label of the questions:

SELECT c.TITLE AS QUESTION_TITLE,
       um.USERNAME AS QUESTION_CREATOR,
	   l.NAME AS LABEL_NAME
FROM CONTENT c
JOIN CONTENT_LABEL cl ON c.CONTENTID = cl.CONTENTID
JOIN LABEL l ON cl.LABELID = l.LABELID
JOIN USER_MAPPING um ON c.CREATOR = um.USER_KEY
WHERE c.PLUGINKEY = 'com.atlassian.confluence.plugins.confluence-questions:question';


4. To get the answer body and the creator of the answer from one specific question:

SELECT um.USERNAME,
       bc.BODY 
FROM CONTENT c
JOIN BODYCONTENT bc ON c.CONTENTID = bc.CONTENTID
JOIN USER_MAPPING um ON c.CREATOR = um.USER_KEY
WHERE c.PLUGINKEY = 'com.atlassian.confluence.plugins.confluence-questions:answer'
AND c.PARENTCCID = NNNNNN;

Again, replace NNNNNN with the 'contentid' of the question.


5. All question titles, question bodies, answer bodies for each of those questions, modification date, creation date, and url to each of the questions for a particular Topic.

WITH ANSWERBODIES AS (SELECT bc.BODY AS answer_body,
					         c.PARENTCCID AS parent_question_id
                      FROM CONTENT c
                      JOIN BODYCONTENT bc ON c.CONTENTID = bc.CONTENTID
                      WHERE c.PLUGINKEY = 'com.atlassian.confluence.plugins.confluence-questions:answer')
SELECT l.NAME AS topic,
       c.TITLE AS question_title,
	   bc.BODY AS question_body,
	   ab.ANSWER_BODY,
	   c.LASTMODDATE AS modification_date,
	   c.CREATIONDATE AS creation_date,
	   CONCAT ('your_base_url','/questions/', c.CONTENTID) AS "URL"
FROM CONTENT c
JOIN CONTENT_LABEL cl ON c.CONTENTID = cl.CONTENTID
JOIN LABEL l ON cl.LABELID = l.LABELID
JOIN BODYCONTENT bc ON c.CONTENTID = bc.CONTENTID
JOIN ANSWERBODIES ab ON c.CONTENTID = ab.PARENT_QUESTION_ID
WHERE c.PLUGINKEY = 'com.atlassian.confluence.plugins.confluence-questions:question'
AND c.PREVVER IS NULL
AND l.NAME = 'your_label_here';

 You will need to replace the following two items in the above query:

  1. your_base_url in the CONCAT line above with your actual base URL (no trailing slash '/'), for example:
    • CONCAT ('https://wiki.example.com','/questions/', c.CONTENTID) AS "URL"
  2. your_label_here in the WHERE clause with the desired topic/label you wish to filter for, for example:
    • AND l.NAME = 'development';


6. All the Topics (labels) in which a question is included

SELECT DISTINCT(l.NAME) as label_name 
FROM CONTENT AS c
JOIN CONTENT_LABEL cl ON c.CONTENTID = cl.CONTENTID
JOIN LABEL l ON cl.LABELID = l.LABELID
WHERE c.pluginkey = 'com.atlassian.confluence.plugins.confluence-questions:question'
AND c.PREVVER IS NULL;


7. Total number of questions

SELECT COUNT(CONTENTID)
FROM CONTENT
WHERE PLUGINKEY = 'com.atlassian.confluence.plugins.confluence-questions:question'
AND PREVVER IS NULL;


8. Total questions with answers

SELECT COUNT(CONTENTID)
FROM CONTENT
WHERE PLUGINKEY = 'com.atlassian.confluence.plugins.confluence-questions:question'
AND CONTENTID IN (SELECT PARENTCCID
                  FROM CONTENT
                  WHERE PLUGINKEY = 'com.atlassian.confluence.plugins.confluence-questions:answer')
AND PREVVER IS NULL;


9. Total questions with no answers

SELECT COUNT(CONTENTID)
FROM CONTENT
WHERE PLUGINKEY = 'com.atlassian.confluence.plugins.confluence-questions:question'
AND CONTENTID NOT IN (SELECT PARENTCCID
                      FROM CONTENT
                      WHERE PLUGINKEY = 'com.atlassian.confluence.plugins.confluence-questions:answer'
                      AND PARENTCCID IS NOT NULL)
AND PREVVER IS NULL;

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 Aug 5, 2020

Was this helpful?

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