Generate data to list wiki pages that are old or with few views in a Space

Nancie Gari

Hello Friends -  I would like to create a list of wiki pages in my space that shows me number of views and pages that are older then a couple years with no views.  We are doing a wiki clean-up effort and this data would be helpful. Otherwise I have to open each page to find the information.  Thank you in advance. Have a great day.

Nan

Dawid Joshua _TechTime_

An App worth looking into is Better Content Archiving for Confluence (Marketplace link). It can display all pages not viewed for x days OR/AND not updated for x days. It can also automatically archive these pages, which would save you a lot of time.

If you're looking for a free solution, you will need to write your own REST API (documentation link) or use SQL but that's already advanced territory. 

I got a SQL solution for it which I don't want to hide in case someone from the community needs it, but please be aware that this solution is for users with SQL experience only: 

With SQL, you can get a view count on pages that have been viewed in the past 2 months (that's the time limit for AO_92296B_AORECENTLY_VIEWED, as confluence code deletes records that are older than 2 months):

--TESTED WITH PostgreSQL ONLY
SELECT s.spacekey,
c.title, 
u.username AS Creator, 
c.creationdate,
um.username AS LastModifier,
c.lastmoddate,
CONCAT('https://baseURL','/pages/viewpage.action?pageId=', c.contentid) AS "URL", --EDIT baseURL
COUNT(rv."CONTENT_ID") AS pageviewes
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
RIGHT JOIN "AO_92296B_AORECENTLY_VIEWED" rv ON c.contentid = "CONTENT_ID" --right join excludes not viewed pages
WHERE c.prevver IS NULL 
AND c.contenttype = 'PAGE'
AND c.content_status = 'current'
AND s.spacekey IN ('SPACE1','SPACE2','AndSoOn...') --EDIT SPACE KEYS
GROUP BY c.contentid, s.spacekey, u.username, um.username
ORDER BY spacekey,title;

 To get a list of pages that have not been viewed in the past 2 months: 

--TESTED WITH PostgreSQL ONLY
SELECT s.spacekey,
c.title, 
u.username AS Creator, 
c.creationdate,
um.username AS LastModifier,
c.lastmoddate,
CONCAT('https://baseURL','/pages/viewpage.action?pageId=', c.contentid) AS "URL", --EDIT baseURL
COUNT(rv."CONTENT_ID") AS pageviewes
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
LEFT JOIN "AO_92296B_AORECENTLY_VIEWED" rv ON c.contentid = "CONTENT_ID" --left join includes not viewed pages
WHERE c.prevver IS NULL 
AND c.contenttype = 'PAGE'
AND c.content_status = 'current'
AND s.spacekey IN ('SPACE1','SPACE2','AndSoOn...') --EDIT SPACE KEYS
GROUP BY c.contentid, s.spacekey, u.username, um.username
HAVING COUNT(rv."CONTENT_ID") = 0
ORDER BY spacekey,title;

code modified from official Atlassian documentation (link). There might be a better SQL solution, don't hesitate to share.