How to Retrieve Content Analytics Information 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
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 | adminThe 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";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';→ 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" ev
join 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";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→ 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";→ 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" 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;→ 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";
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";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';Event retention (deletion based on event count):
select * from scheduler_run_details where job_id like 'AnalyticsForConfluence.EventLimiter';