Problem

We are hoping to learn insights on our site usage by reviewing which pages are being viewed recently and by which users. While we are aware there are Third Party Plugins that may extend this functionality for Confluence, we would like to run a query on the Confluence database to gather this information manually. Is this possible?

Cause

Currently, Confluence does not  have a native function for tracking historical page views. We have a few open feature requests tracking this behavior:

CONFSERVER-33386 - Getting issue details... STATUS

CONFSERVER-43461 - Getting issue details... STATUS

Resolution

Recently viewed pages are stored in the AORECENTLY_VIEWED table in Confluence's database. The following query can be ran to see the data ordered by descending last view date and mapped to the user who viewed the page. 

SELECT recent."CONTENT_ID", pages."title", max(recent."LAST_VIEW_DATE") as LAST_VIEW_DATE, recent."SPACE_KEY", um.username
FROM "AO_92296B_AORECENTLY_VIEWED" recent
INNER JOIN CONTENT pages ON recent."CONTENT_ID" = pages.contentid
JOIN user_mapping um ON um.user_key = recent."USER_KEY"
GROUP BY pages."title", recent."CONTENT_ID", recent."SPACE_KEY",um.username; 

SELECT abav.CONTENT_ID , pages.title, max(abav.LAST_VIEW_DATE) as LAST_VIEW_DATE, abav.SPACE_KEY, um.username
FROM AO_92296B_AORECENTLY_VIEWED abav
INNER JOIN CONTENT pages ON abav.CONTENT_ID = pages.contentid
JOIN user_mapping um ON um.user_key = abav.USER_KEY
GROUP BY pages.title, abav.CONTENT_ID, abav.SPACE_KEY,um.username; 

SELECT abav.CONTENT_ID , pages.TITLE, max(abav.LAST_VIEW_DATE) as LAST_VIEW_DATE, abav.SPACE_KEY, um.username
FROM AO_92296B_AORECENTLY_VIEWED abav
INNER JOIN CONTENT pages ON abav.CONTENT_ID = pages.CONTENTID
JOIN user_mapping um ON um.user_key = abav.USER_KEY
GROUP BY pages.TITLE, abav.CONTENT_ID, abav.SPACE_KEY,um.username; 

If you are not interested in the specific user who viewed the page/blogspot, but only the last time it was viewed, you can run this query instead: 

SELECT recent."CONTENT_ID", pages."title", max(recent."LAST_VIEW_DATE") as LAST_VIEW_DATE, recent."SPACE_KEY"
FROM "AO_92296B_AORECENTLY_VIEWED" recent
INNER JOIN CONTENT pages ON recent."CONTENT_ID" = pages.contentid
GROUP BY pages."title", recent."CONTENT_ID", recent."SPACE_KEY"; 

SELECT abav.CONTENT_ID, pages.title, max(abav.LAST_VIEW_DATE) as LAST_VIEW_DATE, abav.SPACE_KEY
FROM AO_92296B_AORECENTLY_VIEWED abav
INNER JOIN CONTENT pages ON abav.CONTENT_ID = pages.contentid
GROUP BY pages.title, abav.CONTENT_ID, abav.SPACE_KEY; 

SELECT abav.CONTENT_ID, pages.TITLE, max(abav.LAST_VIEW_DATE) as LAST_VIEW_DATE, abav.SPACE_KEY
FROM AO_92296B_AORECENTLY_VIEWED abav
INNER JOIN CONTENT pages ON abav.CONTENT_ID = pages.CONTENTID
GROUP BY pages.TITLE, abav.CONTENT_ID, abav.SPACE_KEY;

 Note that this table by nature tracks recency and does not contain all historical data for page views. If you are looking to track overall page views, you will need to consult the Atlassian Marketplace for plugins that may extend this functionality in Confluence.