Help with SQL query with Confluence database

Tim Hailey_fnavxg

I am trying to figure out the syntax to search for a specific email address and provide a list of all the pages in Confluence that it lives on with links to the pages. I can use Search in the UI but there are so many references that this is not helpful. I am trying to query the Confluence SQL database to get this information.

Any ideas on how to construct this query?

Thank you,

Tim Hailey
Atlassian Systems Administrator

Srinatha Tondihal

@Tim Hailey ,

You can get the page id by appending the page url in the below query:

SELECT c.CONTENTID, c.CONTENTTYPE, s.SPACEKEY, 'https://SITENAME.atlassian.net/wiki/spaces/'||s.spacekey||'pages/'||c.contentid as "link"--,c.TITLE
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 '%test%';

just replace the sitename.atlassian.net/wiki to your space url with context path. 

I hope that answers your question. 

Thanks,

Srinath T