Purpose

A Confluence administrator may find it useful to audit macro usage from time to time in order to see which macros are still actively being used by users. This may be done as part of a regular clean-up of Confluence plugins or as part of an upgrade to evaluate which plugins (which powers the macros) are still being used.

Solution for Confluence Cloud and Confluence 5.10 (or later)

If you're using Confluence Cloud or Confluence 5.10 or later, go to  > General Configuration > Macro Usage to find out how often macros provided by particular add-ons (including bundled add-ons) are used in your site.  We also list any add-ons that have macros that are not used on any pages in current spaces. This is useful if you're considering removing an add-on and want to check that you won't be causing 'unknown-macro' errors on pages in your site. 

Solution for Confluence 5.9 and earlier

1. Run the query in the Confluence Database:

   This SQL query will output the storage format of all current content (i.e. will not return historical pages) in Confluence that contains at least one macro. Given a large data set, this query can potentially take a long time. For this reason we strongly recommend running this query against a database cloned from production rather than the production database itself.

   Depending on how large your production DB is, the resulting file may easily have over 5GB. Make sure you have enough space on the file system to store it.

   SELECT bc.body
   FROM BODYCONTENT bc
   JOIN CONTENT c
   ON bc.contentid = c.contentid
   WHERE c.prevver IS NULL
   AND bc.body LIKE '%ac:structured-macro%';

2. Export the results as a CSV file (comma separated values). This can be done using your database tools.

3. Next, in a Linux terminal, or Cygwin, run the following against the CSV file generated earlier:

   awk 'BEGIN{FS=OFS=" ";}{for(i=1;i<=NF;i++){if($i~/<ac:structured-macro/){if($(i+1)~/ac:name/) {print $(i+1)} else {print $(i+2)} }}}' <filename> | cut -d"\"" -f3 | sort | uniq -c | sort -g

   Replace <filename> with the CSV file.

4. This will give you results similar to the following:

    22 recently-updated
    19 column
     9 panel
     4 gallery
     3 section
     3 pagetree
     2 gadget
     1 viewfile
     1 pagetreesearch
     1 calendar

Related:

• How to find all pages and spaces that use a specific macro via SQL