How to find pages using a specific label in Confluence via SQL
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
Confluence administrators may want to audit Label usage on their Confluence instance. This can be done by using the labelText: search field.
The alternative approach is by locating a labeled page or post and choosing any label to access the Labeled Content page.
Issue
If viewing and editing restrictions are applied to a page, the page will not be included on the search performed when viewing the Labeled Content pages. This also occurs when accessing with a user who belongs to confluence-administrator group.
In these case, it is possible to get this information from the database using the SQL statements below.
Solution
Make sure you have a backup of your database before performing any SQL query.
| Language | Query pages with no labels | Query pages with specific label |
|---|---|---|
| PostgreSQL | 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; | 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.content_status = 'current' AND c.prevver IS NULL; |
| MySQL | SELECT c.title, s.spacename FROM confluence.CONTENT c JOIN confluence.SPACES s ON s.spaceid = c.spaceid LEFT JOIN confluence.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; | SELECT c.title, s.spacename, l.name AS label FROM confluence.CONTENT c JOIN confluence.SPACES s ON s.spaceid = c.spaceid JOIN confluence.CONTENT_LABEL cl ON cl.contentid = c.contentid JOIN confluence.LABEL l ON l.labelid = cl.labelid WHERE l.name = 'label1' AND c.contenttype = 'PAGE' AND c.content_status = 'current' AND c.prevver IS NULL; |
| SQL Server | SELECT c.TITLE, s.SPACENAME FROM confluence.dbo.CONTENT c JOIN confluence.dbo.SPACES s ON s.SPACEID = c.SPACEID LEFT JOIN confluence.dbo.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; | SELECT c.TITLE, s.SPACENAME, l.NAME AS label FROM confluence.dbo.CONTENT c JOIN confluence.dbo.SPACES s ON s.SPACEID = c.SPACEID JOIN confluence.dbo.CONTENT_LABEL cl ON cl.CONTENTID = c.CONTENTID JOIN confluence.dbo.LABEL l ON l.LABELID = cl.LABELID WHERE l.NAME = 'label' AND c.CONTENTTYPE = 'PAGE' AND c.CONTENT_STATUS = 'current' AND c.PREVVER IS NULL; |