Confluence Support

Platform Notice: Data Center - This article applies to Atlassian products on the Data Center platform.

Note that this knowledge base article was created for the Data Center version of the product. Data Center knowledge base articles for non-Data Center-specific features may also work for Server versions of the product, however they have not been tested. 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

Summary

This article details how to create a simple report (SQL Output) showing the following information for the entire Confluence instance:

Page Title
Author
Date Created
Last Changed by
Last Changed Date

This is meant to serve as a reference template for creating your own reports.

Environment

Confluence Server and Confluence Data Center.

Solution

The examples below are for a PostgreSQL database, but can be translated by your DBA to another DBMS type if needed.

Database Information

The Confluence DB Schema: Confluence Data Model

The CONTENT table contains the page information, and the USER_MAPPING table ties the user_key to the username referenced on the content table.

Example

Here's a query to obtain the Page title, Author, Date Created, Last Changed, and Last changed by whom:

PostgreSQL

SELECT   c.title,
         u.username AS Creator,
         c.creationdate,
         c.lastmoddate,
         um.username AS LastModifier
FROM     content c
JOIN     user_mapping u
ON       c.creator = u.user_key
JOIN     user_mapping um
ON       c.lastmodifier = um.user_key
WHERE    c.prevver IS NULL
AND      c.contenttype = 'PAGE'
AND      c.content_status = 'current'
ORDER BY title;

SQL Server

SELECT c.TITLE, 
       u.username as Creator, 
       c.CREATIONDATE, 
       c.LASTMODDATE, 
       um.username AS LastModifier
FROM confluence.dbo.CONTENT c 
JOIN confluence.dbo.user_mapping u ON c.CREATOR = u.user_key 
JOIN confluence.dbo.user_mapping um ON c.LASTMODIFIER = um.user_key 
WHERE  c.PREVVER IS NULL 
  AND c.CONTENTTYPE = 'PAGE' 
  AND c.CONTENT_STATUS = 'current'
ORDER BY TITLE;

MySQL

SELECT c.title,
       u.username AS Creator,
       c.creationdate,
       c.lastmoddate,
       um.username AS LastModifier
FROM confluence.CONTENT c
JOIN confluence.user_mapping u ON c.creator = u.user_key
JOIN confluence.user_mapping um ON c.lastmodifier = um.user_key
WHERE c.prevver IS NULL
  AND c.contenttype = 'PAGE'
  AND c.content_status = 'current'
ORDER BY title;

Which provides output like this::

       title       | creator |      creationdate       |       lastmoddate       | lastmodifier
-------------------+---------+-------------------------+-------------------------+--------------
 first             | admin   | 2018-03-29 17:03:30.021 | 2018-03-29 17:03:38.814 | admin
 test 4            | admin   | 2019-03-06 16:46:18.145 | 2019-03-06 16:59:32.55  | admin
 Jira test         | admin   | 2018-12-20 15:45:57.242 | 2018-12-20 15:47:17.02  | admin
 Team Cal Test     | admin   | 2019-03-05 18:01:43.848 | 2019-03-06 13:11:13.48  | admin
 Copy of test 4    | admin   | 2019-03-06 17:10:39.382 | 2019-03-06 17:26:06.953 | admin
 New Space Home    | admin   | 2019-04-02 16:50:57.3   | 2019-04-02 16:50:57.3   | admin
 Jalapeño          | jsmith  | 2019-04-02 17:02:35.91  | 2019-04-02 17:03:02.111 | jsmith
 Keep Austin Weird | admin   | 2019-04-02 16:51:29.838 | 2019-04-02 17:17:25.735 | jsmith
 Atlassian         | admin   | 2019-04-02 16:52:56.343 | 2019-04-02 17:17:31.999 | jsmith
(9 rows)

The query can be modified to filter the results by spaces. Replace the spacekey values in the query below (Space keys starting with ~ are indicating personal spaces):

PostgreSQL

SELECT s.spacekey,
       c.title, 
       u.username AS Creator, 
       c.creationdate,
       um.username AS LastModifier,
       c.lastmoddate
FROM content c
JOIN spaces s ON c.spaceid = s.spaceid
JOIN user_mapping u  ON c.creator = u.user_key 
JOIN user_mapping um ON c.lastmodifier = um.user_key
WHERE c.prevver IS NULL 
  AND c.contenttype = 'PAGE' 
  AND c.content_status = 'current'
  AND s.spacekey IN ('SPACE1','SPACE2','AndSoOn...')
ORDER BY spacekey,title;

SQL Server

SELECT s.SPACEKEY,
       c.TITLE, 
       u.username AS Creator, 
       c.CREATIONDATE,
       um.username AS LastModifier,
       c.LASTMODDATE
FROM confluence.dbo.CONTENT  c
JOIN confluence.dbo.SPACES  s ON c.SPACEID = s.SPACEID
JOIN confluence.dbo.user_mapping u ON c.CREATOR = u.user_key 
JOIN confluence.dbo.user_mapping um ON c.LASTMODIFIER = um.user_key
WHERE c.PREVVER IS NULL 
  AND c.CONTENTTYPE = 'PAGE' 
  AND c.CONTENT_STATUS = 'current'
  AND s.SPACEKEY IN ('Space1','Space2','AndSoon')
ORDER BY s.SPACEKEY, c.TITLE;

MySQL

SELECT s.spacekey,
       c.title, 
       u.username AS Creator, 
       c.creationdate,
       um.username AS LastModifier,
       c.lastmoddate
FROM confluence.CONTENT  c
JOIN confluence.SPACES s ON c.spaceid = s.spaceid
JOIN confluence.user_mapping  u  ON c.creator = u.user_key 
JOIN confluence.user_mapping um ON c.lastmodifier = um.user_key
WHERE c.prevver IS NULL 
  AND c.contenttype = 'PAGE' 
  AND c.content_status = 'current'
  AND s.spacekey IN ('Space1','Space2','AndSoon')
ORDER BY s.spacekey, c.title;

Page Links

If you'd like to include the normal link to the page (not the short link), you can add this column to the select query:

       CONCAT  ('your_base_url','/pages/viewpage.action?pageId=', c.contentid) AS "URL"

You will need to replace your_base_url with your actual base URL (no trailing slash '/'), for example:

PostgreSQL

SELECT c.title, 
       u.username  AS Creator, 
       c.creationdate, 
       c.lastmoddate, 
       um.username AS LastModifier, 
       CONCAT  ('https://wiki.example.com','/pages/viewpage.action?pageId=', c.contentid) AS "URL"
FROM   content c 
       JOIN user_mapping u 
         ON c.creator = u.user_key 
       JOIN user_mapping um 
         ON c.lastmodifier = um.user_key 
WHERE  c.prevver IS NULL 
       AND c.contenttype = 'PAGE' 
       AND c.content_status = 'current';

SQL Server

SELECT c.TITLE,
       u.username AS Creator,
       c.CREATIONDATE,
       um.username AS LastModifier,
       c.LASTMODDATE,
       'https://wiki.example.com/pages/viewpage.action?pageId=' + CONVERT(NVARCHAR(10), c.CONTENTID) AS URL
FROM confluence.dbo.CONTENT c
JOIN confluence.dbo.SPACES  s ON c.SPACEID = s.SPACEID
JOIN confluence.dbo.user_mapping u ON c.CREATOR = u.user_key
JOIN confluence.dbo.user_mapping um ON c.LASTMODIFIER = um.user_key
WHERE c.PREVVER IS NULL
  AND c.CONTENTTYPE = 'PAGE'
  AND c.CONTENT_STATUS = 'current';

MySQL

SELECT c.title,
       u.username AS Creator,
       c.creationdate,
       um.username AS LastModifier,
       c.lastmoddate,
       CONCAT('https://wiki.example.com','/pages/viewpage.action?pageId=', c.CONTENTID) AS URL
FROM confluence.CONTENT c
JOIN confluence.SPACES s ON c.spaceid = s.spaceid
JOIN confluence.user_mapping u ON c.creator = u.user_key
JOIN confluence.user_mapping um ON c.lastmodifier = um.user_key
WHERE c.prevver IS NULL
  AND c.contenttype = 'PAGE'
  AND c.content_status = 'current'

The result set will look something like this:

       title       | creator |      creationdate       |       lastmoddate       | lastmodifier |                              URL
-------------------+---------+-------------------------+-------------------------+--------------+---------------------------------------------------------------
 first             | admin   | 2018-03-29 17:03:30.021 | 2018-03-29 17:03:38.814 | admin        | https://wiki.example.com/pages/viewpage.action?pageId=65586
 test 4            | admin   | 2019-03-06 16:46:18.145 | 2019-03-06 16:59:32.55  | admin        | https://wiki.example.com/pages/viewpage.action?pageId=2850819
 Jira test         | admin   | 2018-12-20 15:45:57.242 | 2018-12-20 15:47:17.02  | admin        | https://wiki.example.com/pages/viewpage.action?pageId=1966081
 Team Cal Test     | admin   | 2019-03-05 18:01:43.848 | 2019-03-06 13:11:13.48  | admin        | https://wiki.example.com/pages/viewpage.action?pageId=2621444
 Copy of test 4    | admin   | 2019-03-06 17:10:39.382 | 2019-03-06 17:26:06.953 | admin        | https://wiki.example.com/pages/viewpage.action?pageId=2850822
 New Space Home    | admin   | 2019-04-02 16:50:57.3   | 2019-04-02 16:50:57.3   | admin        | https://wiki.example.com/pages/viewpage.action?pageId=4030466
 Jalapeño          | jsmith  | 2019-04-02 17:02:35.91  | 2019-04-02 17:03:02.111 | jsmith       | https://wiki.example.com/pages/viewpage.action?pageId=4030474
 Keep Austin Weird | admin   | 2019-04-02 16:51:29.838 | 2019-04-02 17:17:25.735 | jsmith       | https://wiki.example.com/pages/viewpage.action?pageId=4030468
 Atlassian         | admin   | 2019-04-02 16:52:56.343 | 2019-04-02 17:17:31.999 | jsmith       | https://wiki.example.com/pages/viewpage.action?pageId=4030471
 Second child page | jsmith  | 2019-04-02 17:35:40.592 | 2019-04-02 17:35:50.199 | jsmith       | https://wiki.example.com/pages/viewpage.action?pageId=4030478
 Third Child Page  | jsmith  | 2019-04-02 17:35:53.198 | 2019-04-02 17:35:58.477 | jsmith       | https://wiki.example.com/pages/viewpage.action?pageId=4030480
(11 rows)

-- Total Number of pages space wise

select count(CONTENTID) as "number of pages", SPACES.SPACENAME from CONTENT
join SPACES on CONTENT.SPACEID = SPACES.SPACEID
where CONTENT.SPACEID is not null
and CONTENT.PREVVER is null
and CONTENT.CONTENTTYPE = 'PAGE'
and CONTENT.CONTENT_STATUS='current'
group by SPACES.SPACENAME
order by "number of pages" desc;

-- Total Number of versions of the pages in a space

select count(version) as "number of versions", content.title,SPACES.SPACENAME from content 
left join SPACES on CONTENT.SPACEID = SPACES.SPACEID
where CONTENT.CONTENTTYPE = 'PAGE' and spacename='<insert spacename here>'
group by SPACES.SPACENAME,content.title
order by content.title asc;

-- Identify pages owned by inactive users. You should manually validate any of these results before deleting anything.

SELECT cwdu.user_name, con.contentid, con.title
FROM cwd_user AS cwdu
JOIN user_mapping AS um
ON cwdu.user_name = um.username
JOIN content AS con
ON um.user_key = con.creator
WHERE con.contenttype = 'PAGE'
AND cwdu.active = 'F

To get a list of Blogs, alter "c.contenttype = 'PAGE'" to "c.contenttype = 'BLOGPOST'"

To get a list of Attachments alter "c.contenttype = 'PAGE'" to "c.contenttype = 'ATTACHMENT'"

Confluence Support

Get started

Knowledge base

Products

Jira Software

Jira Service Management

Jira Work Management

Confluence

Bitbucket

Resources

Documentation

Community

System Status

Suggestions and bugs

Marketplace

Billing and licensing

How to obtain a list of all pages, their authors, and related information from the Confluence database

Still need help?

Summary

Environment

Solution

Database Information

Example

PostgreSQL

SQL Server

MySQL

PostgreSQL

SQL Server

MySQL

Page Links

PostgreSQL

SQL Server

MySQL

Page

Viewport

Confluence

How to obtain a list of all pages, their authors, and related information from the Confluence database

Related content

Still need help?

Summary

Environment

Solution

Database Information

Example

PostgreSQL

SQL Server

MySQL

PostgreSQL

SQL Server

MySQL

Page Links

PostgreSQL

SQL Server

MySQL

Related content