How can I find which pages users have recently viewed?
Platform Notice: Data Center - This article applies to Atlassian products on the Data Center platform.
Note that this knowledge base article was created for the Data Center version of the product. Data Center knowledge base articles for non-Data Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Problem
We are hoping to gain insights into our site usage by reviewing which pages are being viewed recently and by which users. While we are aware that Third-Party Plugins 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 - Confluence should have a "last viewed date" property for pages.
CONFSERVER-43461 - Ability to track Confluence Page Views
Resolution
Recently viewed pages are stored in the AORECENTLY_VIEWED table in Confluence's database. The following query can be run to see the data ordered by descending the 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.
Additionally, you might be interested in Retrieve all pages with last view date with total view count for other SQL statements relate with AORECENTLY_VIEWED table