How to query the database to find pages using a specific label in Confluence

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.

Summary

Confluence administrators may want to audit Label usage on the instance. This can be done by using the labelText: search field.

The alternative way is by locating a labeled page or post and choosing any label to access the Labeled Content page.

Issue

If Viewing and editing restrictions is applied to any page, that page will not be included on the search performed or when viewing the Labeled Content pages even when accessing with a confluence-administrator user.

In this case, it is possible to audit this information from the database using the queries below.

Solution

Make sure you have a backup of your database before performing any SQL query.

List of pages that don't have any label:

SELECT c.title,s.spacename
FROM content c
JOIN spaces s ON s.spaceid = c.spaceid
LEFT JOIN content_label cl ON cl.contentid = c.contentid
WHERE c.contenttype = 'PAGE'
  AND c.prevver IS NULL
  AND c.content_status = 'current'
  AND cl.contentid IS NULL
ORDER BY s.spacename,c.title;

List of pages that have a specific label:

SELECT c.title,s.spacename,l.name as label
FROM content c
JOIN spaces s ON s.spaceid = c.spaceid
JOIN content_label cl ON cl.contentid = c.contentid
JOIN label l ON l.labelid = cl.labelid
WHERE l.name = 'demo'
  AND c.contenttype = 'PAGE'
  AND c.prevver IS NULL;



Last modified on Jan 23, 2020

Was this helpful?

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