How to get data on usage of Confluence Questions
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:
- 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"
- 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.
