Find the number and types of macros used in Confluence pages
Platform Notice: Cloud and Data Center - This article applies equally to both cloud and data center platforms.
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.
*Except Fisheye and Crucible
Summary
Learn how to find how many macros exist in your Confluence instance pages and which macros are used.
Overview
Confluence administrators may wish to find how many macros are added to pages and the names of the macros for troubleshooting or auditing.
This document takes on this task by querying the Confluence database.
Solution
The SQL queries below use wildcard searching to locate page content that matches the 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.
Replace on line 16 the "<contentid>" with the content ID of the page if you want to filter the search by page.
Please select the SQL query below that matches your Database language.
Oracle
Oracle
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT
c.TITLE,
c.CONTENTID,
s.SPACEKEY,
REGEXP_COUNT(bc.BODY,'<ac:(structured-)?macro') AS MATCHES,
LISTAGG(c2.PROPERTYNAME, ',') WITHIN GROUP (ORDER BY c2.PROPERTYNAME) AS MACRONAMES
FROM
BODYCONTENT bc
JOIN CONTENT c ON bc.CONTENTID = c.CONTENTID
JOIN SPACES s ON s.SPACEID = c.SPACEID
INNER JOIN CONTENTPROPERTIES c2 ON c.CONTENTID = c2.CONTENTID
WHERE
c.PREVVER IS NULL
AND bc.BODY LIKE '%ac:structured-macro%'
AND c2.PROPERTYNAME LIKE '%macro-%'
AND c.CONTENTID = <contentid>
GROUP BY
c.TITLE,
c.CONTENTID,
s.SPACEKEY,
REGEXP_COUNT(bc.BODY,'<ac:(structured-)?macro')
ORDER BY
MATCHES DESC;PostgreSQL and MSSQL
Postgres and MSSQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SELECT
c.TITLE,
c.CONTENTID,
s.SPACEKEY,
SUM(CASE WHEN bc.BODY LIKE '%<ac:structured-macro%' OR bc.BODY LIKE '%<ac:macro%' THEN 1 ELSE 0 END) AS MATCHES,
STRING_AGG(c2.PROPERTYNAME, ',') AS MACRONAMES
FROM
BODYCONTENT bc
JOIN CONTENT c ON bc.CONTENTID = c.CONTENTID
JOIN SPACES s ON s.SPACEID = c.SPACEID
INNER JOIN CONTENTPROPERTIES c2 ON c.CONTENTID = c2.CONTENTID
WHERE
c.PREVVER IS NULL
AND (bc.BODY LIKE '%<ac:structured-macro%' OR bc.BODY LIKE '%<ac:macro%')
AND c2.PROPERTYNAME LIKE '%macro-%'
AND c.CONTENTID = <contentid>
GROUP BY
c.TITLE,
c.CONTENTID,
s.SPACEKEY
ORDER BY
MATCHES DESC;MySQL
MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SELECT
c.TITLE,
c.CONTENTID,
s.SPACEKEY,
SUM(CASE WHEN bc.BODY LIKE '%<ac:structured-macro%' OR bc.BODY LIKE '%<ac:macro%' THEN 1 ELSE 0 END) AS MATCHES,
GROUP_CONCAT(c2.PROPERTYNAME SEPARATOR ',') AS MACRONAMES
FROM
BODYCONTENT bc
JOIN CONTENT c ON bc.CONTENTID = c.CONTENTID
JOIN SPACES s ON s.SPACEID = c.SPACEID
INNER JOIN CONTENTPROPERTIES c2 ON c.CONTENTID = c2.CONTENTID
WHERE
c.PREVVER IS NULL
AND (bc.BODY LIKE '%<ac:structured-macro%' OR bc.BODY LIKE '%<ac:macro%')
AND c2.PROPERTYNAME LIKE '%macro-%'
AND c.CONTENTID = <contentid>
GROUP BY
c.TITLE,
c.CONTENTID,
s.SPACEKEY
ORDER BY
MATCHES DESC;Results
The above query will give you results similar to the following:
title | contentid | spacekey | matches | macronames |
|---|---|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
The meaning of each column:
title: The title of the page.
contentid: The ID of the page.
spacekey: The space key where the page belongs.
matches: The total number of macros on the page.
macronames: The list of macro names, separated by a comma.
Related articles
Was this helpful?