How to find a list of pages that contains Content by Label macro and Content by Label is displaying a specific page

Still need help?

The Atlassian Community is here for you.

Ask the community


Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.

Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.

*Except Fisheye and Crucible

 

Summary

Some labeled pages can be displayed on other pages using the Content By Label macro. For content maintenance or reporting purposes, Confluence administrators may want to find out on which pages the relevant page is displayed using the Content By Label macro. This knowledge base article takes on this task by querying the database using SQL queries

Solution

The queries below use wildcard searching in order to locate page content that matches a specific pattern for macros. Due to the nature of wildcard searching, these queries may take a long time to execute, and it is recommended to carry these out in a clone of the production database rather than on the production environment itself to avoid any impact to the production environment.


  1. Use the below query and replace <page_title> with the relevant page's title to get the labels of this page.

    select c.title as page_title, l.name as label_name
    from content_label cl
             join content c on cl.contentid = c.contentid
             join label l on cl.labelid = l.labelid
    where c.title = '<page_title>'
    order by title desc;
  2. Run the following query, replacing these labels one by one with the <label_title> to get pages containing the Content by Label macro with the given label used as a CQL filter.

    select s.spacekey, s.spacename, c.title, c.contentid
    from content c
             join bodycontent b on c.contentid = b.contentid
             join spaces s on c.spaceid = s.spaceid
    where c.contenttype = 'PAGE'
      and c.content_status = 'current'
      and c.prevver is NULL
      and b.body like '%ac:name="contentbylabel%'
      and b.body like '%label = _quot_<label_title>_quot_%';

Related:


Last modified on Mar 31, 2021

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.