Confluence サポート

プラットフォームについて: Server および Data Center のみ。この記事は、Server および Data Center プラットフォームのアトラシアン製品にのみ適用されます。

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.

*Fisheye および Crucible は除く

要約

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;

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;

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):

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;

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;

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:

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';

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';

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'"

説明	SQL report for page authors, title, url, date created and modified.
製品	Confluence server, Confluence data center

Confluence サポート

使用を開始する

ナレッジベース

製品

Jira Software

Jira Service Management

Jira Work Management

Confluence

Bitbucket

リソース

関連ドキュメント

コミュニティ

システムステータス

提案とバグ

Marketplace

請求とライセンス情報

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

お困りですか?

要約

環境

ソリューション

Database Information

例

Page Links

ページ

Viewport

Confluence

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

関連コンテンツ

お困りですか?

要約

環境

ソリューション

Database Information

例

Page Links

関連コンテンツ