Confluence SQL Query of Page related data in the database

Tim Hailey
Contributor
October 21, 2022

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

4 answers

1 accepted

0 votes
Answer accepted
Srinatha T
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
October 24, 2022

@Tim Hailey ,

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

Thanks,

Srinath T

Tim Hailey
Contributor
October 31, 2022

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

0 votes
Srinatha T
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
October 31, 2022

@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
Contributor
November 1, 2022

@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
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
November 1, 2022

@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

0 votes
Srinatha T
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
October 31, 2022

@Tim Hailey ,

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

Checking now. 

Thanks,

Srinath T 

0 votes
Srinatha T
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
October 22, 2022

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
Contributor
October 24, 2022

@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

Like Srinatha T likes this
Tim Hailey
Contributor
October 27, 2022

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

Like Srinatha T likes this
Moses Ebrahimi
Contributor
March 15, 2023

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

Muhammad Yasin
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
September 14, 2023

Suggested to our Service team to done as soon as possible.

And report to service center as soon as possible.

 

Thansl

Muhammad Yasin  

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
SERVER
VERSION
7.15.2
TAGS
AUG Leaders

Atlassian Community Events