What's the easiest way to get a report of last updated date for all pages in a space?

[Brian Harris]

Periodically, I'd like to know the last time each page was updated, so I can see the oldest pages and check they're still accurate.

[Eva Dezsi]

But if you want to see the name of the last updater:

SELECT s.SPACENAME, c.TITLE, c.LASTMODDATE, c.LASTMODIFIER, u.[username]
from [confluence].[dbo].[CONTENT] as c, [confluence].[dbo].[SPACES] s, [confluence].[dbo].[user_mapping] u
where c.CONTENTTYPE = 'PAGE'
and c.TITLE is not null
and s.SPACENAME= 'HR'
and s.SPACEID = c.SPACEID
and u.[user_key] = c.LASTMODIFIER
order by c.LASTMODDATE DESC;

[Eva Dezsi]

Hi, modified the query a little bit, so that last modified date is displayed in a descending order: 

SELECT s.SPACENAME, c.TITLE, c.LASTMODDATE
from [confluence].[dbo].[CONTENT] as c
join [confluence].[dbo].[SPACES] s on s.SPACEID = c.SPACEID
where c.CONTENTTYPE = 'PAGE'
and s.SPACENAME= 'HR'
order by c.LASTMODDATE DESC;

If one desires only published pages:

SELECT s.SPACENAME, c.TITLE, c.LASTMODDATE
from [confluence].[dbo].[CONTENT] as c
join [confluence].[dbo].[SPACES] s on s.SPACEID = c.SPACEID
where c.CONTENTTYPE = 'PAGE'
and c.TITLE is not null
and s.SPACENAME= 'HR'
order by c.LASTMODDATE DESC;

[Rodrigo Girardi Adami]

Hi Brian,

There are 2 ways to check for a page update. 

1) Through the Interface.

2) Through a database SQL query. I've created this one for you that shows all versions and modified dates of each page version:

select s.spacename, c.title, c.lastmoddate
from content c
join spaces s on s.spaceid = c.spaceid
where contenttype = 'PAGE'
and prevver is null
and spacename = '<SPACENAME>';

Note the <SPACENAME> must be replaced with the exact same name as the space including capital letters.

This query will show you all pages from a specific space with their last modification dates and names.

Cheers,

Rodrigo

[Brian Harris]

Thanks. Method 2 is better as I actually want a report of all pages showing their last updated date.