Help with SQL query with Confluence database

[Tim Hailey]

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 T · Accepted solution]

@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

[Tim Hailey]

@Srinatha T Thank you so much for your help. when I run this query, I don't get an error but it doesn't return anything either. Do you see any issues with my query?

SELECT c.CONTENTID, c.CONTENTTYPE, s.SPACEKEY, 'https://confluence.sentryds.com/'||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 '%CPManagement@sentryds.com%';

Thank you,

Tim Hailey

[Srinatha T]

@Tim Hailey ,

Thats a bit of work and I need to append the confluence url . Should be achievable. I have got your other request as well. I will work on this during weekend and share my thoughts. 

Thanks,

Srinath T 

[Srinatha T]

@Tim Hailey ,

The above query is going to search in all the spaces and since i am using % wild card it might take time to complete the query so first test the query in dev environment and then try on prod. Better to try in non business hours. 

Thanks,

Srinath T 

[Srinatha T]

Hi @Tim Hailey ,

Welcome to Atlassian community. 

The data is stored in the Body column of the bodycontent table. 

You can use the below query to find the data.

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>%';

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.

I hope the above query helps. Have good weekend.

Thanks,

Srinath T  

[Tim Hailey]

Hello @Srinatha T

Thank you so much for this. This is helpful.

Is there a way to also display a link to the page that the email address resides on? Right now, I have to take the results of the query and search Confluence for each page referenced.

Thank you,

Tim Hailey

[Tim Hailey]

Hello @Srinatha T

Is there a way to also display a link to the page that the email address resides on? Right now, I have to take the results of the query and search Confluence for each page referenced.

Thank you,

Tim Hailey