Scan Confluence instance for macro usage...

[Dave_Harrington]

Is there a way to look through all the content on our Confluence instance (4.2.4) for the presence of a certain set of macros?

We have a couple plug-ins licensed that we don't think are being used, and don't want to renew. Naturally, we don't want anything breaking when we reach the end of their maintenance period.

Thanks.

[JohnA · Accepted solution]

Hi Dave,

There used to be a plugin called the Macro Usage Stats plugin, but it isn't compatible with anything above Confluence v3.5.x and therefore the only option at the moment is to run SQL queries directly on the database. However, the following query should work for you:

Select CONTENT.CONTENTID, Title, SpaceID, CREATOR, LASTMODDATE
from CONTENT, BODYCONTENT
WHERE
PREVVER IS NULL
AND (CONTENTTYPE = 'PAGE' OR CONTENTTYPE = 'BLOGPOST')
AND CONTENT.CONTENTID = BODYCONTENT.CONTENTID
AND CONTENT_STATUS = 'current'
AND (BODY like '%{macro%')

All the best,
John

[R Donato]

Hi,

Does this run a report on all macros or individual macros? I'd like to run a report on all of them since searching for each macro individually would take a very long time and some might be missed.

Thanks!

[Davin Studer]

Here is a user macro that I have created for this. Just put it on a page and it should give you a report of all of the macros and user macros that are being used in your instance. This requires at least Confluence 5.5 as the MacroBrowserManager.getMacroSummaries() method was added in 5.5.

## Developed by: Davin Studer
## Date created: 12/17/2014
## @param Space:title=Space|type=spacekey|desc=This will restrict the macro usage report to a specific space.

#set($containerManagerClass = $content.class.forName('com.atlassian.spring.container.ContainerManager'))
#set($getInstanceMethod = $containerManagerClass.getDeclaredMethod('getInstance',null))
#set($containerManager = $getInstanceMethod.invoke(null,null))
#set($containerContext = $containerManager.containerContext)
#set($macroBrowserManager = $containerContext.getComponent('macroBrowserManager'))
#set($allMacros = $macroBrowserManager.getMacroSummaries())

#########################################################################################
## This is used for getting around velocity issues when writing jQuery.                ##
#########################################################################################
#set( $d = '$' )

#########################################################################################
## Populate the macro information into a string that we will use as a JS object array. ##
#########################################################################################
#set ($i = 0)
#set($macroObjects = "")
#foreach($macro in $allMacros)
  #if($i != 0)
    #set($macroObjects = $macroObjects + ",")
  #end
  #set($macroObjects = $macroObjects + "{title:'" + $macro.getTitle().getKey().replace("'","\'") + "', name:'" + $macro.getMacroName().replace("'","\'") + "'}")
#set ($i = $i + 1)
#end

#########################################################################################
## Decide which space to search.                                                       ##
#########################################################################################
#if ($!paramSpace && $!paramSpace != "")
    #set ($searchSpace = $!paramSpace)
#else
    #set ($searchSpace = "conf_all")
#end

<script type="text/javascript">
var queue = {
    macros: [$macroObjects],
    current: 0,
    start: function () {
        this.macros.sort(compare);
        this.current = 0;
        this.next();
    },
    next: function () {
    	if (this.current >= this.macros.length) {
            AJS.$('#queryStatus span').removeClass('aui-lozenge-current');
            AJS.$('#queryStatus span').addClass('aui-lozenge-success');
            AJS.$('#queryStatus span').text('Report Complete');
            AJS.tablessortable.setTableSortable(AJS.$('#macroUsageReport table'));
            return null;
		}
		else {
			this.current += 1;
			lookupMacro(this.macros[this.current - 1]);
		}
    }
};

function lookupMacro(macro) {
    var html = '';
    var searchURL = '';
    
    AJS.$('#queryStatus span').text('Getting counts for macro: ' + macro.title);
    
    searchURL = AJS.params.baseUrl + '/rest/searchv3/1.0/search?where=${searchSpace}&spaceSearch=true&queryString=macroName:' + encodeURIComponent(macro.name);
    AJS.${d}.ajax(
        {
            type: 'GET',
            url: searchURL,
            dataType: "json",
            timeout:60000,
            success: function(data) {
                if(data.total > 0) {
                    var spaces = '';
                    for(var i = 0; i < data.results.length; i++)
                    {
                        if(spaces.indexOf(data.results[i].searchResultContainer.name) == -1)
                        {
                            spaces = spaces == '' ? '' : spaces + ', ';
                            spaces = spaces + data.results[i].searchResultContainer.name;
                        }
                    }
                    html = '<tr>';
                    html += '   <td class="confluenceTd">' + macro.title + '</td>';
                    html += '   <td class="confluenceTd">' + macro.name + '</td>';
                    html += '   <td class="confluenceTd">' + data.total + '</td>';
                    html += '   <td class="confluenceTd">' + spaces + '</td>';
                    html += '   <td class="confluenceTd"><a href="' + AJS.params.baseUrl + '/dosearchsite.action?where=conf_all&spaceSearch=true&queryString=macroName:' + encodeURIComponent(macro.name) + '" target="_blank">macro usage</a></td>';
                    html += '</tr>';
                }
                AJS.$('#macroUsageReport table tbody').append(html);
                
                queue.next();
            },
            error: function (x, y, z) {
                AJS.$('#queryStatus span').removeClass('aui-lozenge-current');
                AJS.$('#queryStatus span').addClass('aui-lozenge-error');
                AJS.$('#queryStatus span').text('Error getting counts for macro:' + macro.title);
            }
        }
    );
}

function compare(a, b) {
    if (a.title < b.title) {
        return -1;
    }
    if (a.title > b.title) {
        return 1;
    }
    return 0;
}

AJS.toInit(function(){
    queue.start();    
});
</script>
<div id="queryStatus">
    <span class="status-macro aui-lozenge aui-lozenge-current"></span>
</div>
<div id="macroUsageReport">
    <table class="confluenceTable">
        <thead>
            <tr>
                <th>Macro Title</th>
                <th>Macro Name</th>
                <th>Times Used</th>
                <th>Spaces Used In</th>
                <th>Pages Used On</th>
            </tr>
        </thead>
        <tbody>
        </tbody>
    </table>
</div>

[SteenerO]

When I try the user macro you have created for this, I get this error: "ERROR GETTING COUNTS FOR MACRO:ACTIVITY STREAM". Do you have any idea why? I have Confluence version 5.6

[Davin Studer]

What's your base url for confluence? Is it like this htttp://mywikiserver/ or like this htttp://mywikiserver/confluence. Or maybe something else?

[Davin Studer]

One thing that could be an issue. I just updated line 60 to add "AJS.params.baseUrl + " to the searchURL. If your base url is not at the root of the server, then the script wouldn't work correctly.

[SteenerO]

That helped :-) Now it displays the correct table with a list of the macro use. But when I click on one of the "macro usage"-links I land on an error page with this message "The requested URL /dosearchsite.action was not found on this server." Why is that? And thank you very much for your help!

[SteenerO]

the base url for confluence looks a lot like this http://mywikiserver/confluence :-)

[SteenerO]

Arh I added "confluence" before /dosearchsite.action? and that fixed it (big grin)(big grin)

[Davin Studer]

I've updated the code above to fix the link issue. I also added a column called "Spaces Used In".

[pratikraj11]

Devin, I m using your created macro but I am getting null results. I had replaced "baseUrl" with mine "http://localhost:8094/";. could you please help me out in this?

[pratikraj11]

Is there any other Changes we need to do ...?

[pratikraj11]

I using confluence version 5.7.1

[Davin Studer]

@Pratik Raj You should not need to do anything with the baseURL now. I edited the code above to fix the baseURL issue. The above code should work without any changes. You should be able to just put it into a user macro and then put the user macro on a page and have it render out.

[pratikraj11]

David, I didn't find a change in the code above.Could you please check it.

[pratikraj11]

also I tried without any change with base Url , I get this error: "ERROR GETTING COUNTS FOR MACRO:ACTIVITY STREAM"

[Davin Studer]

The change was made a while ago. It should just be plug and play. I have not tried it on 5.7.1 yet. I'll give it a go and see what happens.

[Davin Studer]

I think the issue might have to do with the AJAX call timing out. I am seeing it happen in our instance too. I have now edited the macro above to extend the AJAX timeout.

[Davin Studer]

I have also added the ability to limit the search to a particular space.

[pratikraj11]

Thanks a lot @Davin Studer , its completely working now....I really appreciate your so concern, attitude and approach , its means a lot for me .

[JY 김]

How can I use this script? 

Should do I put it on confluence page and uncomment the commented line with #?

It is shown as text content. 

[Davin Studer]

You need to create a user macro of it. You can create user macros in Confluence Admin. After creating the user macro you will have that macro in the macro browsers when you are in edit mode. Once that is done just put the macro on a page and save.

[Deleted user]

Awesome macro! Thanks for sharing this 

[ColinM]

Excellent macro, thanks a lot ! Works on Confluence v5.9.12

EDIT: Also, by using getMacroMetadata() instead of getMacroSummaries() in the initialization, we can get more information (for instance, I wanted to know from which plugin each macro are coming)

[Darin Hafer]

Hello @Davin Studer, I tried your code on Confluence 6.8.2, and while it runs and I can see the list of macros, the page I have this on will not render the HTML output. This is what the page renders:

Which of the 'Macro Body Processing' options does your macro require?

Also, how can I debug and log to a file from within a user macro? console.log either isn't working or isn't getting to my code, but I do have a log stmt as the first line in my javascript tag.

Thanks 

[Davin Studer]

When this answer was ported from the old Atlassian Answers site the code code HTML encoded. Thus things like < > and & ended up getting changed to &lt; &gt; and &amp;. So, you would just need to do a find a replace to fix it. However, since you are on 6.8.2 you should have access to a new-ish report that they put in Confluence Administration that provides basically the some information. It's toward the bottom and is called Macro Usage.

http(s)://{your server}/admin/pluginusage.action

[Davin Studer]

I've fixed the code in the above post so it should be correct now.

[Darin Hafer]

Thanks Davin, I noticed the code was escaped but I thought it was deliberate, like when encoding a url. That made it render, so thank you! 

I've used Macro Usage also but thought this might get some extra data. In fact, it looks like it finds more than Macro Usage does.

Macro usage doesn't seem to tell me about plugins/add-ons, just as the name suggests, it's only about Macros. Is that Correct?

[Darin Hafer]

Looks like Times Used is incorrect, but I'll look to solve it.

[Davin Studer]

Hmm, I might have messed something up by just trying to clean up the HTML encoding. I re-inserted the code above from the source in my Confluence instance. Give that a try.

[Darin Hafer]

That did the trick, Davin, many thanks!

[Brian Pipa]

Thie info this macro shows is wrong for the "Spaces Used" column. The calls this macro makes only returns 10 actual results (plus the correct total) so the "Spaces Used" number is wrong for any macro in use in more than 10 pages. To do this correctly, it would have to page through every result and it doesn't do this.

[Narasimha Tunuguntla]

@Davin Studer we have upgraded our confluence to 7.3.5 and the macro seams to stop working (not getting any results). If you have a updated version I would love to use it :) 

[Davin Studer]

Atlassian disabled some features that I was using in the user macro and there is no other way to do it. However, one thing to note is that there is now a macro usage report in Confluence Administration. It gives you much of the same data as my user macro did.

Also, the feature they removed can be re-enabled by editing this file ...

Confluence_Install/confluence/WEB-INF/classes/velocity.properties

Change this line

introspector.restrict.packages = java.lang.reflect,\

to

#introspector.restrict.packages = java.lang.reflect,\
introspector.restrict.packages = \

And these two lines

introspector.restrict.classes = java.lang.Class,\
java.lang.ClassLoader,\

to

#introspector.restrict.classes = java.lang.Class,\
#java.lang.ClassLoader,\
introspector.restrict.classes = \

These allowed you to dynamically add classes into the Velocity context, which is needed for my user macro to work.

[Narasimha Tunuguntla]

@Davin StuderThank you for getting back. Good to know about the in app 'Macro Usage' feature. I was using this user macro to know the 'Spaces' information, which is missing in the in app feature. I will see if I can find a workaround. Thanks again for taking a look. 

[SarahA]

Hallo Dave

You can use the "macroName:" keyword in the standard Confluence search box.

For example, let’s say that you want to search for all “include” macros. Type this into the Confluence search box:

macroName: include*

There's more detail in this post:

http://ffeathers.wordpress.com/2011/11/04/how-to-search-confluence-for-usage-of-a-macro/

I hope this helps!

Cheers, Sarah

[Dave_Harrington]

Tha't exactly the type of thing I was looking for! Thanks, Sarah!