nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Confluence SQL Query of Page related data in the database

Tim Hailey_fnavxg

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?

Space Name
Page Name
Page URL
Page Created Date
Page Created By (optional)
Last Edited

Thank you,

Tim Hailey
Atlassian Systems Administrator

Accepted answers

Srinatha Tondihal

@Tim Hailey ,

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

Thanks,

Srinath T

All comments

Srinatha Tondihal

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_fnavxg

@Srinatha Tondihal

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

Srinatha Tondihal

@Tim Hailey ,

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

Thanks,

Srinath T

Tim Hailey_fnavxg

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

Tim Hailey_fnavxg

Hello @Srinatha Tondihal

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 Tondihal

@Tim Hailey ,

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

Checking now.

Thanks,

Srinath T

Srinatha Tondihal

@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_fnavxg

@Srinatha Tondihal

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.

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

Thank you very much for your assistance.

Tim Hailey

Srinatha Tondihal

@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.

Thanks,

Srinath T

Moses Ebrahimi

Hi @Srinatha Tondihal

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

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

confluence

server

confluence-server