How to obtain a list of all pages, their authors, and related information from the Confluence database
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs 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.
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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
1
2
3
4
5
6
7
8
9
10
11
12
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
1
2
3
4
5
6
7
8
9
10
11
12
13
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:
1
2
3
4
5
6
7
8
9
10
11
12
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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:
1
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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
1
2
3
4
5
6
7
8
9
10
11
12
13
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
1
2
3
4
5
6
7
8
9
10
11
12
13
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:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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) More Examples
1
2
3
4
5
6
7
8
9
10
-- 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;1
2
3
4
5
6
7
-- 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;1
2
3
4
5
6
7
8
9
10
-- 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 = 'FTo 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'"
Updated on February 14, 2025
Was this helpful?
Still need help?
The Atlassian Community is here for you.