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

Question

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 · Answer

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 · Answer

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;

Brian Harris1 · Answer

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