How to identify which confluence page is not viewed or updated since 2020

[Sriram Pallapothu]

Hi All,

We are in process of cleaning our existing confluence site, In that scenario we need data relates to unused and not updated confluence spaces and pages.

please share any database query to identify the below result

How to identify which confluence page and space is not viewed or updated since 2020

Regards,

Sriram. 

[Sriram Pallapothu]

Got the query to get the list of spaces which are not modified from 2020. 

SELECT spaces.spacename, spaces.spacekey
FROM content, spaces
WHERE content.spaceid = spaces.spaceid
GROUP BY spaces.spacename, spaces.spacekey
HAVING MAX(content.lastmoddate) < '2020-01-01'; 

Need help to get the query which pages and spaces are not viewed from 2020

[Mirek]

Well not sue what DB are you using so there might be different code but in order to see last modification date you can give it a try with something like this:

SELECT content.title, content.pageid, content.contenttype, content.lastmoddate, content.spaceid

FROM content

WHERE(content.lastmoddate) > TO_DATE('01/JAN/2021','dd/mon/yyyy')

AND content.spaceid = 124977154

ORDER BY content.lastmoddate DESC;

In case of views I am not sure if this information is stored in the database..

https://jira.atlassian.com/browse/CONFSERVER-43461

[Sriram Pallapothu]

Hi @Mirek , 

Query did not worked and giving error "To_DATE" does not exists, we are using MYSQL DB. And I need a query to get data which pages are not viewed from 2020