Confluence SQL Query of Page related data in the database

[Tim Hailey]

I am trying to construct a SQL query that will provide some inventory of a Confluence space from the database, so that I can clean it up based on the results.

Any suggestions on how to retrieve this specific data from a Confluence space in the Confluence database?

1. Space Name
2. Page Name
3. Page URL
4. Page Created Date
5. Page Created By (optional)
6. Last Edited
   
   

Thank you,

Tim Hailey
Atlassian Systems Administrator

[Srinatha T · Accepted solution]

@Tim Hailey ,

That should be achievable I will update you once i have it modified. 

Thanks,

Srinath T

[Tim Hailey]

Hello @Srinatha T

Did you ever get a chance to look at this?

I need to search for only 1 space name in the database. Do you know how I could get these same results but for just a specified space name?

Thank you,

Tim Hailey

[Srinatha T]

@Tim Hailey , 

If you are looking for individual space details they you need to enter the space id manually. 

Step 1: Get the sapce id from below query:

• SELECT spaceid, spacename, spacekey FROM spaces;

Step2: change the space id and run the query.

• SELECT title, contentid, lastmoddate, content_status FROM content WHERE spaceid = <spaceId> and contenttype = 'PAGE' and content_status in ('current', 'deleted')

I hope that helps. Have a good day!

Thanks,

Srinath T

[Tim Hailey]

@Srinatha T

Thank you. This gives me results for the specified space id but not for what I had originally requested in this thread. 

Can you please help with this query? I am trying to clean up the Space based on the results.

1. Space Name or Space Key or Space Id (I only need results for 1 specified space)
2. Page Name
3. Page URL
4. Page Created Date
5. Last Edited date

Thank you very much for your assistance.

Tim Hailey

[Srinatha T]

@Tim Hailey ,

The previous query gives all the details . You can simply modify it like below to get it for one space, just change the space name and you will get details for that space. 

SELECT c.title, s.spacename AS SpaceName, s.spacekey, u.username AS Creator, c.creationdate, c.lastmoddate, um.username AS LastModifier FROM content c join spaces s ON c.spaceid = s.spaceid JOIN user_mapping u ON c.creator = u.user_key JOIN user_mapping um ON c.lastmodifier = um.user_key WHERE c.prevver IS NULL AND c.contenttype = 'PAGE' AND c.content_status = 'current' and spacename = 'MySpace';

Thanks,

Srinath T

[Srinatha T]

@Tim Hailey ,

I could not work on it during weekend as its festival time here in India. 

Checking now. 

Thanks,

Srinath T 

[Srinatha T]

Hi @Tim Hailey ,

Welcome to Atlassian community. 

Below is the query. 

SELECT c.title, s.spacename AS SpaceName, s.spacekey, u.username AS Creator, c.creationdate, c.lastmoddate, um.username AS LastModifier FROM content c join spaces s ON c.spaceid = s.spaceid JOIN user_mapping u ON c.creator = u.user_key JOIN user_mapping um ON c.lastmodifier = um.user_key WHERE c.prevver IS NULL AND c.contenttype = 'PAGE' AND c.content_status = 'current';

I hope the above helps. Have a good day!

Thanks,

Srinath T 

[Tim Hailey]

@Srinatha T

This gets me most of the way there. However, I need to search for only 1 space name in the database. Do you know how I could get these same results but for just a specified space name?

Thank you so much,

Tim Hailey

[Tim Hailey]

@Srinatha T Thank you. I look forward to your next response.

[Moses Ebrahimi]

Hi @Srinatha T 

This was very helpful for my case too! thanks for the information. Just a quick question, can this query modified to return (ONLY) last page that been updated along with list of spaces?

I think this query returns all spaces as well as all pages and their last update etc, I'm looking for a way to capture all spaces but only last page updated record.

Thank you

Moses