Tips for Finding Old Macros in Confluence

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

1. Of course PocketQuery for Confluence should be installed.
2. 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:

<ac:structured-macro ac:name="viewpdf">
    <ac:parameter ac:name="name">
      <ri:attachment ri:filename="My_document.pdf"/>
    </ac:parameter>
  </ac:structured-macro>

Only the following part is interesting:

ac:name="viewpdf"

Setting up PocketQuery for Confluence

We need:

1. a new query (Query)
2. a converter
3. 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)': '<a href="'+baseURL+'/pages/viewpage.action?pageId='+current.contentid+'" target="_blank">'+current.title + ' ('+ current.contentid+')</a>',
                'Space': '<a href="'+baseURL+'/display/'+current.spacekey+'/" target="_blank">'+current.spacekey+'</a>',
                '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.

Save and Insert Macro

In the query you have to select the converter and template.

The macro can then be inserted on a Confluence page. From the preparation step the search term for the macro has to be inserted.

The search term should best be packed in wildcards, because before and after there are other contents. For example %ac:name=”viewpdf”%

It is helpful to activate dynamic loading.

Result

A table with all the linked pages that use the macro at some point, so they can easily be edited.