Retrieve all pages with last view date with total view count
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
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
Summary
Data on how often a page is viewed and whether it has been viewed recently can be valuable in a variety of situations, such as pruning old content. This article contains instructions for retrieving this information from the database.
Solution
The below query will retrieve the following data:
- Total Views Per Page
- Page Title
- Space Name
- Space Key
- Latest Viewed Date/Time
- Content ID
Always back up your data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
SELECT count (*) as "TOTAL_VIEWS_PER_PAGE",
c.title AS "PAGE_TITLE",
s.spacename AS "SPACE_NAME",
s.spacekey AS "SPACE_KEY",
max(to_timestamp(aoe."EVENT_AT" / 1000)::timestamp) AS "LATEST_VIEWEDTIME",
c.contentid AS "CONTENT_ID"
FROM "AO_7B47A5_EVENT" aoe
JOIN content c on aoe."CONTENT_ID" = c.contentid
JOIN spaces s on c.spaceid = s.spaceid
JOIN user_mapping um ON aoe."USER_KEY" = um.user_key
WHERE aoe."NAME" = 'page_viewed'
AND c.prevver is null
AND c.CONTENT_STATUS = 'current'
GROUP BY aoe."CONTENT_ID", c.spaceid, c.title, s.spacename, c.contentid, s.spacekey
ORDER BY s.spacename;More Information
For a broader overview on retrieving content analytics and additional queries to pull similar data, please see the below article: