How to use SQL Queries to Track Page Modifications by Date in Confluence
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
As an admin, you may want to get a list of all articles within a space that have or haven't been modified recently. This export will return the title, page id, content type, and last modification date for all content in a specified space during a specified time period.
Environment
These queries were written using PostgreSQL and MySQL. Depending on your database type, you may need to modify these queries in order to use them.
There currently isn't a way to export this information directly from the Confluence via the UI.
Solution
This info can be found by querying the database. Update the date and username in the queries below with the target date and run these against the database.
Find content that has been modified since a certain date in a specific space.
SELECT content.title, content.pageid, content.contenttype, content.lastmoddate, spaces.spacename
FROM content join spaces on content.spaceid=spaces.spaceid
WHERE(content.lastmoddate) > '2019-05-10';SELECT content.title, content.pageid, content.contenttype, content.lastmoddate, spaces.spacename
FROM content join spaces on content.spaceid=spaces.spaceid
WHERE content.lastmoddate > TO_DATE('2019-05-10','YYYY-MM-DD')This query will only return content that has a modification date after May 10th, 2019.
Find content that has not been modified since a certain date in a specific space.
SELECT content.title, content.pageid, content.contenttype, content.lastmoddate, spaces.spacename
FROM content join spaces on content.spaceid=spaces.spaceid
WHERE(content.lastmoddate) <= '2019-05-10';SELECT content.title, content.pageid, content.contenttype, content.lastmoddate, spaces.spacename
FROM content join spaces on content.spaceid=spaces.spaceid
WHERE(content.lastmoddate) <= TO_DATE('2019-05-10','YYYY-MM-DD');This query will only return content that has not been modified since May 10th, 2019.
Find a List of pages created by a specific user after a certain date in a specific space.
SELECT c.title as "PAGE TITLE" , u.username, c.creationdate, s.spacename from content c
inner join user_mapping u on c.creator=u.user_key
inner join spaces s on c.spaceid=s.spaceid
where c.creationdate>'2020-08-01' and c.contenttype='PAGE' and c.content_status='current'
and u.username='admin'SELECT c.title as "PAGE TITLE" , u.username, c.creationdate, s.spacename from content c
inner join user_mapping u on c.creator=u.user_key
inner join spaces s on c.spaceid=s.spaceid
where c.creationdate> TO_DATE('2020-08-01','YYYY-MM-DD') and c.contenttype='PAGE' and c.content_status='current'
and u.username='admin'Find a List of pages modified by a specific user after a certain date in a specific space.
SELECT c.title as "PAGE TITLE" , u.username, c.lastmoddate, s.spacename from content c
inner join user_mapping u on c.creator=u.user_key
inner join spaces s on c.spaceid=s.spaceid
where c.lastmoddate>'2020-08-01' and c.contenttype='PAGE' and c.content_status='current'
and u.username='admin'SELECT c.title as "PAGE TITLE" , u.username, c.lastmoddate, s.spacename from content c
inner join user_mapping u on c.creator=u.user_key
inner join spaces s on c.spaceid=s.spaceid
where c.lastmoddate>TO_DATE('2020-08-01','YYYY-MM-DD') and c.contenttype='PAGE' and c.content_status='current'
and u.username='admin'Find a List of Spaces created by a specific user after a certain date
SELECT c.spacename , u.username, c.creationdate from spaces c inner join user_mapping u
on c.creator=u.user_key
where c.creationdate>'2020-08-01' and c.spacestatus='CURRENT'
and u.username='admin'SELECT c.spacename , u.username, c.creationdate from spaces c inner join user_mapping u
on c.creator=u.user_key
where c.creationdate>TO_DATE('2020-08-01','YYYY-MM-DD') and c.spacestatus='CURRENT'
and u.username='admin'Find a List of Spaces modified by a specific user after a certain date
SELECT c.spacename , u.username, c.lastmoddate from spaces c inner join user_mapping u
on c.creator=u.user_key
where c.lastmoddate>'2020-08-01' and c.spacestatus='CURRENT'
and u.username='admin'SELECT c.spacename , u.username, c.lastmoddate from spaces c inner join user_mapping u
on c.creator=u.user_key
where c.lastmoddate>TO_DATE('2020-08-01','YYYY-MM-DD') and c.spacestatus='CURRENT'
and u.username='admin'This info can be found by querying the database. Update the date and username in the queries below with the target date and run these against the database.
Find content that has been modified since a certain date in a specific space.
SELECT content.title, content.pageid, content.contenttype, content.lastmoddate, spaces.spacename
FROM content join spaces on content.spaceid=spaces.spaceid
WHERE(content.lastmoddate) > '2019-05-10';SELECT content.title, content.pageid, content.contenttype, content.lastmoddate, spaces.spacename
FROM content join spaces on content.spaceid=spaces.spaceid
WHERE content.lastmoddate > TO_DATE('2019-05-10','YYYY-MM-DD')This query will only return content that has a modification date after May 10th, 2019.
Find content that has not been modified since a certain date in a specific space.
SELECT content.title, content.pageid, content.contenttype, content.lastmoddate, spaces.spacename
FROM content join spaces on content.spaceid=spaces.spaceid
WHERE(content.lastmoddate) <= '2019-05-10';SELECT content.title, content.pageid, content.contenttype, content.lastmoddate, spaces.spacename
FROM content join spaces on content.spaceid=spaces.spaceid
WHERE(content.lastmoddate) <= TO_DATE('2019-05-10','YYYY-MM-DD');This query will only return content that has not been modified since May 10th, 2019.
Find a List of pages created by a specific user after a certain date in a specific space.
SELECT c.title as "PAGE TITLE" , u.username, c.creationdate, s.spacename from content c
inner join user_mapping u on c.creator=u.user_key
inner join spaces s on c.spaceid=s.spaceid
where c.creationdate>'2020-08-01' and c.contenttype='PAGE' and c.content_status='current'
and u.username='admin'SELECT c.title as "PAGE TITLE" , u.username, c.creationdate, s.spacename from content c
inner join user_mapping u on c.creator=u.user_key
inner join spaces s on c.spaceid=s.spaceid
where c.creationdate> TO_DATE('2020-08-01','YYYY-MM-DD') and c.contenttype='PAGE' and c.content_status='current'
and u.username='admin'Find a List of pages modified by a specific user after a certain date in a specific space.
SELECT c.title as "PAGE TITLE" , u.username, c.lastmoddate, s.spacename from content c
inner join user_mapping u on c.creator=u.user_key
inner join spaces s on c.spaceid=s.spaceid
where c.lastmoddate>'2020-08-01' and c.contenttype='PAGE' and c.content_status='current'
and u.username='admin'SELECT c.title as "PAGE TITLE" , u.username, c.lastmoddate, s.spacename from content c
inner join user_mapping u on c.creator=u.user_key
inner join spaces s on c.spaceid=s.spaceid
where c.lastmoddate>TO_DATE('2020-08-01','YYYY-MM-DD') and c.contenttype='PAGE' and c.content_status='current'
and u.username='admin'Find a List of Spaces created by a specific user after a certain date
SELECT c.spacename , u.username, c.creationdate from spaces c inner join user_mapping u
on c.creator=u.user_key
where c.creationdate>'2020-08-01' and c.spacestatus='CURRENT'
and u.username='admin'SELECT c.spacename , u.username, c.creationdate from spaces c inner join user_mapping u
on c.creator=u.user_key
where c.creationdate>TO_DATE('2020-08-01','YYYY-MM-DD') and c.spacestatus='CURRENT'
and u.username='admin'Find a List of Spaces with their latest modified date
SELECT spaces.spacename, MAX(content.lastmoddate)
FROM content, spaces
WHERE content.spaceid = spaces.spaceid
GROUP BY spaces.spacename; Depending on what type of database you are running, you may need to format this query differently.
Last modified on Mar 12, 2024
Powered by Confluence and Scroll Viewport.