How to Retrieve the Number of Pages Edited or Viewed by Users from the Database
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs 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
Summary
You would like to retrieve the number of pages edited or viewed by users from the database, some examples are given below.
Details regarding page views and edits are held in our AO_7B47A5_EVENT analytics table and the amount of data held will depend on analytics being enabled (it is only available on a Datacenter licence) and on its data retention settings.
If both of these things are true, we also have a How to Retrieve Confluence Analytics Information from the Database KB document which discusses it a little further.
Solution
ℹ️The queries below were written for PostgreSQL and may need syntax changes depending on your database.
Total number of page edits per user, per day
1
2
3
4
5
6
7
8
9
SELECT USER_MAPPING.lower_username AS username,
to_timestamp("EVENT_AT"/1000)::date AS date,
count("CONTENT_ID") AS page_edits
FROM "AO_7B47A5_EVENT"
LEFT JOIN user_mapping
ON "USER_KEY" = USER_MAPPING.user_key
WHERE "NAME"='page_updated'
GROUP BY USER_MAPPING.lower_username, "date"
ORDER BY "date" DESC;Total number of page views per user, per day
1
2
3
4
5
6
7
8
9
SELECT USER_MAPPING.lower_username AS username,
to_timestamp("EVENT_AT"/1000)::date AS date,
count("CONTENT_ID") AS page_views
FROM "AO_7B47A5_EVENT"
LEFT JOIN user_mapping
ON "USER_KEY" = USER_MAPPING.user_key
WHERE "NAME"='page_viewed'
GROUP BY USER_MAPPING.lower_username, "date"
ORDER BY "date" DESC;Total number of distinct page edits per user, per day
1
2
3
4
5
6
7
8
9
SELECT USER_MAPPING.lower_username AS username,
to_timestamp("EVENT_AT"/1000)::date AS date,
count(DISTINCT "CONTENT_ID") AS pages_edited
FROM "AO_7B47A5_EVENT"
LEFT JOIN user_mapping
ON "USER_KEY" = USER_MAPPING.user_key
WHERE "NAME"='page_updated'
GROUP BY USER_MAPPING.lower_username, "date"
ORDER BY "date" DESC;Total number of distinct page views per user, per day
1
2
3
4
5
6
7
8
9
SELECT USER_MAPPING.lower_username AS username,
to_timestamp("EVENT_AT"/1000)::date AS date,
count(DISTINCT "CONTENT_ID") AS pages_viewed
FROM "AO_7B47A5_EVENT"
LEFT JOIN user_mapping
ON "USER_KEY" = USER_MAPPING.user_key
WHERE "NAME"='page_viewed'
GROUP BY USER_MAPPING.lower_username, "date"
ORDER BY "date" DESC;Was this helpful?