Summary

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.

Solution

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.

Related:

• How to get a list of the most popular macros used in Confluence