How to perform a Confluence site search for keywords and links through the database

Still need help?

The Atlassian Community is here for you.

Ask the community

Purpose

There are limitations for the default Lucene search algorithm in Confluence.

These are a few examples of the limitation :

  • URL links are not searchable :  CONF-27503 - Getting issue details... STATUS
  • Parts of a string are not searchable :  CONF-33996 - Getting issue details... STATUS  , CONF-36770 - Getting issue details... STATUS

There are times when you really need to search for the pages that contain the keywords.

For an example, an external URL was inserted to numerous pages but as time goes by, those URL might point to a dead link as there might be some changes in the subdomain/URL path.

Solution

  • To search for these contents, run the SQL query below on your Confluence database. Replace the <INSERT_KEYWORD_HERE> with your keyword. The % symbol represents a wildcard search.
    The SQL results will return the content type, title along with the space details including the spacestatus (either CURRENT or ARCHIVED). If a space is Archived, it won't be searchable in Confluence's User Interface.

    select c.CONTENTTYPE,c.TITLE, s.SPACENAME, s.SPACEKEY, s.SPACETYPE, s.SPACESTATUS 
    from content c join spaces s on c.SPACEID=s.SPACEID 
    where CONTENTID in 
    (select CONTENTID from bodycontent where BODY like '%<INSERT_KEYWORD_HERE>%')

Last modified on Nov 5, 2017

Was this helpful?

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