特定のマクロを利用するすべてのページおよびスペースを SQL 経由で取得する方法
プラットフォームについて: Data Center のみ。 - This article only applies to Atlassian apps on the Data Center プラットフォーム。
この KB は Data Center バージョンの製品用に作成されています。Data Center 固有ではない機能の Data Center KB は、製品のサーバー バージョンでも動作する可能性はありますが、テストは行われていません。 Server* 製品のサポートは 2024 年 2 月 15 日に終了しました。Server 製品を実行している場合は、 アトラシアン Server サポート終了 のお知らせにアクセスして、移行オプションを確認してください。
*Fisheye および Crucible は除く
要約
For purposes of troubleshooting or auditing, Confluence administrators may wish to find pages or spaces that contain a specific macro. This knowledge base article takes on this task by querying the Confluence database using SQL queries.
ソリューション
The queries below use wildcard searching in order to locate page content that matches a specific pattern for macros. Due to the nature of wildcard searching, these queries may take a long time to execute, and it is recommended to carry these out in a clone of the production database rather than on the production environment itself to avoid any impact to the production environment. In all queries below, please replace <macro_name> with the actual name of your macro.
Find all current pages or blog posts that contain the macro (historical pages are not considered)
PostgreSQL - MySQL - Oracle
SELECT c.contentid, c.contenttype, c.title, s.spacekey
FROM CONTENT c
JOIN BODYCONTENT bc
ON c.contentid = bc.contentid
JOIN SPACES s
ON c.spaceid = s.spaceid
WHERE c.prevver IS NULL
AND c.contenttype IN ('PAGE', 'BLOGPOST')
AND bc.body LIKE '%ac:structured-macro ac:name="<macro_name>"%';Find all spaces that have current content that uses a macro
PostgreSQL - MySQL - Oracle
SELECT DISTINCT s.spaceid, s.spacekey, s.spacename
FROM CONTENT c
JOIN BODYCONTENT bc
ON c.contentid = bc.contentid
JOIN SPACES s
ON c.spaceid = s.spaceid
WHERE c.prevver IS NULL
AND c.contenttype IN ('PAGE', 'BLOGPOST')
AND bc.body LIKE '%ac:structured-macro ac:name="<macro_name>"%';On some occasions, after a page or space import, error messages like "unknown macro: Gliffy" are noted in the imported pages. You could spot the pages that use these macros by replacing %ac:name="<macro_name> with the text "unknown macro: Gliffy" from the above queries.
Export to .CSV
You can export the results of these queries to a file. This is useful for auditing, as you can import a .CSV file into Microsoft Excel (for example).
In order to export the output to a file, just add the following line at the end of the above SQL queries:
to '<Enter Location to Save File>' with CSV HEADER;Please replace <Enter Location to Save File> with the desired location for storing the .CSV file generated. (Note: The user running the queries must be allowed to create a file on the specified folder).
Retrieving the Storage Format of a page that contains the macro can help you check for the exact Macro Name to be used in the SQL queries above.
関連資料
この内容はお役に立ちましたか?