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?
Thank you,
Tim Hailey
Atlassian Systems Administrator
That should be achievable I will update you once i have it modified.
Thanks,
Srinath T
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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:
Step2: change the space id and run the query.
I hope that helps. Have a good day!
Thanks,
Srinath T
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
@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.
Thank you very much for your assistance.
Tim Hailey
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I could not work on it during weekend as its festival time here in India.
Checking now.
Thanks,
Srinath T
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
@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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Suggested to our Service team to done as soon as possible.
And report to service center as soon as possible.
Thansl
Muhammad Yasin
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.