Tips for Finding Old Macros in Confluence

May 29, 2019

2 min

read

It can happen now and then that macros are removed in Confluence and replaced by a new feature. For example the View PDF Macro that was marked as obsolete with Confluence 5.9. But what to do to find the affected pages? Here PocketQuery for Confluence can help. A very simple one, admittedly, but I think it’s a practical example to use templates as well as converters and to have a quick access.

Preconditions

Of course PocketQuery for Confluence should be installed.
A connection to the respective database of Confluence should be established.

Preparation

To find the macro on any page, you must first display the format in which it was saved. The macro is stored here in the same format as it can be found in the database. For example the View PDF Macro:

Only the following part is interesting:

ac:name="viewpdf"

Setting up PocketQuery for Confluence

We need:

a new query (Query)
a converter
a template (optional)

SQL Queries

SELECT title, "content".contentid, spaces.spacekey, MAX("version"), (CHAR_LENGTH(bodycontent.body) - CHAR_LENGTH(REPLACE(bodycontent.body, :searchterm, ''))) / CHAR_LENGTH('ac:name="viewpdf') AS "occurrences" FROM "content" JOIN spaces ON spaces.spaceid = "content".spaceid JOIN bodycontent ON bodycontent.contentid = "content".contentid WHERE contenttype = 'PAGE' AND bodycontent.body LIKE concat('%', :searchterm, '%') GROUP BY content.contentid, title, spaces.spacekey, bodycontent.body ORDER BY spacekey, title

The parameter is created as a ‘String’ type and stored as a search term.

Converter

In general you don’t need the converter, but it makes the process a bit more user-friendly to create URLs and make them clickable.

The converter is based on the standard converter, but a few columns are put together.

function convert(json) { // json is the result as json string var baseURL = 'https://confluence.mycompany.com' var result = []; // the array-of-objects we will return var parsedJsonObject = JSON.parse(json); // parse json string var current, index; // loop variables for (index in parsedJsonObject) { // iterate through the result // only continue if this property is not inherited if (parsedJsonObject.hasOwnProperty(index)) { current = parsedJsonObject[index]; // current object result.push({ // add a converted object 'Page Title (Page ID)': ''+current.title + ' ('+ current.contentid+')', 'Space': ''+current.spacekey+'', 'Occurrences': current.occurrences }); } } return result; // return the converted result }

Important! In line 2 the Base URL of Confluence must be entered.

Template

In our example we added the default template with the button “Add default template” - this is optional and only necessary if you want to change the appearance.