特定のマクロを利用するすべてのページおよびスペースを SQL 経由で取得する方法
プラットフォームについて: 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 は除く
要約
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)
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>"%';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
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>"%';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.