本文翻译自官方文档：https://confluence.atlassian.com/pages/viewpage.action?pageId=1115683563

Purpose

It can be useful to retrieve content analytics data directly from the database in case you want to inspect or manipulate the information externally. Some examples for this include identifying how many times a page has been viewed recently, the most recent date a page was viewed, or which actions have been taken on a page.

Solution

Analytics data and settings are stored in the following tables:

 public | AO_7B47A5_EVENT                           | table    | admin public | AO_7B47A5_EVENT_ID_seq                    | sequence | admin public | AO_7B47A5_SETTINGS                        | table    | admin public | AO_7B47A5_SETTINGS_ID_seq                 | sequence | admin

CODE

The AO_7B47A5_EVENT table is used to store each of the events, so the following query will retrieve all of them:

select * from "AO_7B47A5_EVENT";

CODE

If looking for a specific type of event, the NAME column can be used. Here is an example for page views:

select * from "AO_7B47A5_EVENT" where "NAME" = 'page_viewed';

CODE

Click here for other examples that might be useful

→ Filtering page views per user on a space, replace space key and event name accordingly:

select count(ev."ID") as TOTAL_EVENTS_PER_SPACE_PER_USER,um.username,ev."NAME" as EVENT_NAME,ev."SPACE_KEY"from "AO_7B47A5_EVENT" evjoin user_mapping um ON ev."USER_KEY" = um.user_key where ev."NAME" = 'page_viewed'and ev."SPACE_KEY" = 'TEST'group by um.username, ev."NAME", ev."SPACE_KEY";

SQL

Note: Unique event names are the following ones, replace it in the query above accordingly:

select distinct("NAME") from "AO_7B47A5_EVENT" ;        NAME        -------------------- page_updated attachment_viewed page_viewed page_trashed blog_viewed page_created blog_created attachment_created page_removed

SQL

→ Getting total page views per space:

select count("ID") as TOTAL_VIEWS_PER_SPACE,"SPACE_KEY","NAME"from "AO_7B47A5_EVENT" where "NAME" = 'page_viewed'and "SPACE_KEY" = 'TEST'group by "NAME", "SPACE_KEY";

SQL

→ Getting total views and last viewed date per page:

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" aoejoin content c on aoe."CONTENT_ID" = c.contentidjoin spaces s on c.spaceid = s.spaceidjoin 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.spacekeyorder by s.spacename;

SQL

→ Fetching total page views per space in a given period of time (replace date range accordingly):

select count("ID") as TOTAL_VIEWS_PER_SPACE_PER_PERIOD,"SPACE_KEY","NAME"from "AO_7B47A5_EVENT" where "NAME" = 'page_viewed'and "SPACE_KEY" = 'TEST'and to_timestamp("EVENT_AT" / 1000)::date > '2022-07-01'group by "NAME", "SPACE_KEY";

SQL

Note: timestamp conversion tested on Postgres, might need adjustment for other DBs. EVENT_AT is an epoch in milliseconds time stamp.

→ To fetch details about the latest view date, and total view count, for each content in a space. You can remove spacekey filter if you need details for all spaces.

select count(ev."ID") as Total_page_views,max(to_timestamp(ev."EVENT_AT" / 1000)::timestamp) AS latest_viewedtime, um.username, ev."NAME" as EVENT_NAME, ev."SPACE_KEY", ev."CONTENT_ID" from "AO_7B47A5_EVENT" ev join user_mapping um ON ev."USER_KEY" = um.user_key join content ON content.contentid=ev."CONTENT_ID" where ev."NAME" = 'page_viewed' and content.prevver is null and ev."SPACE_KEY" = '<insert_space_key>' group by um.username, ev."NAME", ev."SPACE_KEY",ev."CONTENT_ID";

SQL

Data Retention job history

The analytics functionality has two data retention strategies to contain its footprint on the database, more details here. To check the execution history of those jobs, we can use the following queries:

Data retention period (deletion based on date):

select * from scheduler_run_details where job_id = 'com.addonengine.analytics.DataRetention';

CODE

Event retention (deletion based on event count):

select * from scheduler_run_details where job_id like 'AnalyticsForConfluence.EventLimiter';

CODE