List of pages where a confluence macro occurs

[richardofrugeley]

The macro RMsis Planned Requirements List is used to display requirements matching a filter parameter on multiple pages throughout a space. I would like to get a list of those pages so I can edit the filter parameters. At the moment I'm unsure which pages include this macro and it would be more efficient if I could target the affected pages.

I don't have Admin privileges.

I have tried using the Search Results macro on a new page within the space of interest, but Confluence is not understanding what I'm asking, e.g.

• macroName:rmsis-planned-requirement-list*
• macroName:"RMsis Planned Requirement List"*

[Nicolai Sibler · Accepted solution]

Hi @richardofrugeley ,

you're on the right track using the macroName:example-macro search query in Confluence's search.

You'll just have to find out the expression to replace example-macro. While this information is available for Confluence's own macros, it's a little diffcult for third party apps like your macro.

So you may ask your admin to pass the list like @Bill Bailey suggested or you may ask the app vendor.

Regards,

Nicolai

[Bill Bailey · Accepted solution]

If you are an admin, you can find that info in the admin console. Under Administration -> Macro Usage.

[MrBadExample]

Hello @richardofrugeley ,

You can use one of following SQL queries for that:

1. List all pages with specified macro:

SELECT DISTINCT s.spaceid, s.spacekey, s.spacename, c.contentid, u.user_name, u.email_address, u.display_name
FROM CONTENT c
JOIN BODYCONTENT bc
    ON c.contentid = bc.contentid
JOIN SPACES s
    ON c.spaceid = s.spaceid
JOIN user_mapping um
    ON um.user_key = c.lastmodifier
join cwd_user u
    ON u.user_name = um.username
WHERE c.prevver IS NULL
    AND c.contenttype IN ('PAGE', 'BLOGPOST')
    AND bc.body LIKE '%ac:name="jira%';  

2. List pages that contains desired amount of macros on page:

SELECT DISTINCT s.spaceid, s.spacekey, s.spacename, c.contentid, u.user_name, u.email_address, u.display_name,
(CHAR_LENGTH(bc.body) - CHAR_LENGTH(REPLACE(bc.body, 'ac:name="jira', ''))) / CHAR_LENGTH('ac:name="jira') as occurances
FROM CONTENT c
JOIN BODYCONTENT bc
ON c.contentid = bc.contentid
JOIN SPACES s
ON c.spaceid = s.spaceid
JOIN user_mapping um
ON um.user_key = c.lastmodifier
join cwd_user u
ON u.user_name = um.username
WHERE c.prevver IS NULL
AND c.contenttype IN ('PAGE', 'BLOGPOST')
AND (CHAR_LENGTH(bc.body) - CHAR_LENGTH(REPLACE(bc.body, 'ac:name="jira', ''))) / CHAR_LENGTH('ac:name="jira') > 40
AND bc.body LIKE '%ac:name="jira%';  

You can find macro name by inspecting page on your browser:

https://www.browserstack.com/guide/inspect-element-in-chrome#:~:text=One%20of%20the%20easiest%20ways,%2C%20Sources%2C%20and%20other%20tools.

Regards,

Szymon