How to obtain a list of all pages, their authors, and related information from the Confluence database
プラットフォームについて: サーバーと Data Center のみ。この記事は、サーバーおよび Data Center プラットフォームのアトラシアン製品にのみ適用されます。
要約
This article details how to create a simple report (SQL Output) showing the following information for the entire Confluence instance:
- Page Title
- 作成者
- 作成日
- Last Changed by
- Last Changed Date
This is meant to serve as a reference template for creating your own reports.
環境
Confluence Server and Confluence Data Center.
ソリューション
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.
例
Here's a query to obtain the Page title, Author, Date Created, Last Changed, and Last changed by whom:
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;
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):
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;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:
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';
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)
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'"