How to perform a Confluence site search for keywords and links through the database
Platform Notice: Data Center and Cloud By Request - This article was written for the Atlassian data center platform but may also be useful for Atlassian Cloud customers. If completing instructions in this article would help you, please contact Atlassian Support and mention it.
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.
*Fisheye および Crucible は除く
目的
There are limitations for the default Lucene search algorithm in Confluence.
These are a few examples of the limitation :
- URL links are not searchable: CONFSERVER-27503 - Getting issue details... STATUS
- Parts of a string are not searchable: CONFSERVER-33996 - Getting issue details... STATUS , CONFSERVER-36770 - Getting issue details... STATUS
There are times when you really need to search for the pages that contain the keywords.
For example, a user inserted an external URL into numerous pages, but as time goes by, those URLs might point to a dead link as there might be some changes in the subdomain/URL path.
ソリューション
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 ID referred to in the Confluence UI as the page ID, content type, page/blog title, and the Space Key.
The query below uses wildcard searching in order to locate page content that matches a specific pattern.
Due to the nature of wildcard searching and the target tables' possible size, this query may take a long time to execute.
If possible, it is recommended to carry these out in a cloned production database rather than on the production environment itself to avoid any impact on the production environment.
SELECT c.CONTENTID, c.CONTENTTYPE, c.TITLE, s.SPACEKEY
FROM CONTENT c
JOIN BODYCONTENT bc
ON c.CONTENTID = bc.CONTENTID
JOIN SPACES s
ON c.SPACEID = s.SPACEID
WHERE c.PREVVER IS NULL
AND c.CONTENTTYPE IN ('PAGE', 'BLOGPOST')
AND bc.BODY LIKE '%<INSERT_KEYWORD_HERE>%';SELECT c.CONTENTID, c.CONTENTTYPE, c.TITLE, s.SPACEKEY
FROM dbo.CONTENT c
JOIN dbo.BODYCONTENT bc
ON c.CONTENTID = bc.CONTENTID
JOIN dbo.SPACES s
ON c.SPACEID = s.SPACEID
WHERE c.PREVVER IS NULL
AND c.CONTENTTYPE IN ('PAGE', 'BLOGPOST')
AND bc.BODY LIKE '%<INSERT_KEYWORD_HERE>%';SELECT CONCAT('SOURCEURL.COM/confluence/pages/viewpage.action?pageId=', c.contentid) AS "Page Link",
c.CONTENTID,
c.CONTENTTYPE,
c.TITLE,
s.SPACEKEY
FROM CONTENT c
JOIN BODYCONTENT bc ON c.CONTENTID = bc.CONTENTID
JOIN SPACES s ON c.SPACEID = s.SPACEID
WHERE c.PREVVER IS NULL
AND c.CONTENTTYPE IN ('PAGE', 'BLOGPOST')
AND bc.BODY LIKE '%ENTER THE EXTERNAL URL/WEB LINK PATTERNS HERE!%'; -- INSERT THE URL PATTERN 参考情報
特定のマクロを利用するすべてのページおよびスペースを SQL 経由で取得する方法