Retrieving Confluence Analytics information directly from the Database

Related content

Still need help?

The Atlassian Community is here for you.

Ask the community

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

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.

In this KB, we provide multiple examples of information you may want to extract from the database; such as 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, among others. 

Examples

Please refer to our KBs Confluence Analytics Data Model and Confluence Data Model for a full understanding of the DB tables used by Confluence


  • To retrieve all Analytics events: 

    Postgres query
    SELECT * FROM "AO_7B47A5_EVENT";
     
    MySQL query
    SELECT * FROM AO_7B47A5_EVENT;
     
    SQL Server query
    SELECT * FROM "AO_7B47A5_EVENT";
     
    Oracle query
    SELECT * FROM "AO_7B47A5_EVENT";
     

  • Filtering by a specific type event, the NAME  column can be used, as the example below: 

    Click here for the list of unique event names
    • page_viewed
    • page_created
    • page_updated
    • page_removed
    • page_trashed
    • page_restored
    • blog_viewed
    • blog_created
    • blog_updated
    • blog_removed
    • blog_trashed
    • blog_restored
    • comment_removed
    • comment_created
    • comment_updated
    • attachment_viewed
    • attachment_created
    • attachment_updated
    • attachment_removed


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

    MySQL query
    select * from AO_7B47A5_EVENT where NAME = 'page_viewed';

    SQL Server query
    select * from "AO_7B47A5_EVENT" where "NAME" = 'page_viewed';

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


  • Example of filtering page views per user on a space, replace space key and event name accordingly: 

    Please be aware that, if the Increased Privacy Mode enabled, queries with USER_MAPPING  table returns empty due to hashing of the user_key in order to minimise the amount of personally identifiable information (PII) 

    Postgres query
    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" = '<insert_space_key>' 
GROUP BY um.username, ev."NAME", ev."SPACE_KEY";

    MySQL query
    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 = '<insert_space_key>'
GROUP BY um.username, ev.NAME, ev.SPACE_KEY;

    SQL Server query
    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" = '<insert_space_key>'
GROUP BY um.username, ev."NAME", ev."SPACE_KEY";

    Oracle query
    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" = '<insert_space_key>'
GROUP BY um.username, ev."NAME", ev."SPACE_KEY";
     

  • Getting total page views per space: 

    Postgres query
    SELECT COUNT("ID") as TOTAL_VIEWS_PER_SPACE, "SPACE_KEY", "NAME"
FROM "AO_7B47A5_EVENT"
WHERE "NAME" = 'page_viewed' AND "SPACE_KEY" =  '<insert_space_key>'
GROUP BY "NAME", "SPACE_KEY";

    MySQL query
    SELECT count(ev.ID) AS TOTAL_VIEWS_PER_SPACE, ev.SPACE_KEY, ev.NAME
FROM AO_7B47A5_EVENT ev
WHERE ev.NAME = 'page_viewed' and ev.SPACE_KEY =  '<insert_space_key>'
GROUP BY ev.NAME, ev.SPACE_KEY;

    SQL Server query
    SELECT COUNT("ID") as TOTAL_VIEWS_PER_SPACE, "SPACE_KEY", "NAME"
FROM "AO_7B47A5_EVENT"
WHERE "NAME" = 'page_viewed' AND "SPACE_KEY" =  '<insert_space_key>'
GROUP BY "NAME", "SPACE_KEY";

    Oracle query
    SELECT COUNT("ID") as TOTAL_VIEWS_PER_SPACE, "SPACE_KEY", "NAME"
FROM "AO_7B47A5_EVENT"
WHERE "NAME" = 'page_viewed' AND "SPACE_KEY" =  '<insert_space_key>'
GROUP BY "NAME", "SPACE_KEY";
     

  • Getting total views and last viewed date per page: 

    Postgres query
    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;

    MySQL query
    SELECT COUNT(*) AS `TOTAL_VIEWS_PER_PAGE`, 
       c.title AS `PAGE_TITLE`, 
       s.spacename AS `SPACE_NAME`, 
       s.spacekey AS `SPACE_KEY`,
       MAX(FROM_UNIXTIME(aoe.`EVENT_AT` / 1000)) 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;

    SQL Server query
    SELECT 
    COUNT(*) AS TOTAL_VIEWS_PER_PAGE, 
    c.TITLE AS PAGE_TITLE, 
    s.SPACENAME AS SPACE_NAME, 
    s.SPACEKEY AS SPACE_KEY,
    MAX(DATEADD(SECOND, aoe.EVENT_AT / 1000, '1970-01-01')) 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;

    Oracle query
    SELECT COUNT(*) AS "TOTAL_VIEWS_PER_PAGE", 
       c.title AS "PAGE_TITLE", 
       s.spacename AS "SPACE_NAME", 
       s.spacekey AS "SPACE_KEY",
       TO_TIMESTAMP('1970-01-01 00:00:00.0','YYYY-MM-DD HH24:MI:SS.FF')
       + NUMTODSINTERVAL(MAX(aoe."EVENT_AT")/1000, 'SECOND') 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): 

    Postgres query
    SELECT count("ID") AS TOTAL_VIEWS_PER_SPACE_PER_PERIOD, "SPACE_KEY", "NAME"
FROM "AO_7B47A5_EVENT" 
WHERE "NAME" = 'page_viewed' AND "SPACE_KEY" = '<insert_space_key>' AND to_timestamp("EVENT_AT" / 1000)::date > '2022-07-01'
GROUP BY "NAME", "SPACE_KEY";

    MySQL query
    SELECT COUNT(ID) AS TOTAL_VIEWS_PER_SPACE_PER_PERIOD, SPACE_KEY, NAME
FROM AO_7B47A5_EVENT
WHERE NAME = 'page_viewed' AND SPACE_KEY = '<insert_space_key>' AND FROM_UNIXTIME(EVENT_AT / 1000) > '2022-07-01'
GROUP BY NAME, SPACE_KEY;

    SQL Server query
    SELECT 
    COUNT(ID) AS TOTAL_VIEWS_PER_SPACE_PER_PERIOD, 
    SPACE_KEY, 
    NAME 
FROM 
    AO_7B47A5_EVENT
WHERE 
    NAME = 'page_viewed' 
    AND SPACE_KEY =  '<insert_space_key>'  
    AND DATEADD(SECOND, EVENT_AT / 1000, '1970-01-01') > '2022-07-01'
GROUP BY 
    NAME, SPACE_KEY;

    Oracle query
    SELECT 
    COUNT(ID) AS TOTAL_VIEWS_PER_SPACE_PER_PERIOD, 
    SPACE_KEY, 
    NAME 
FROM 
    AO_7B47A5_EVENT
WHERE NAME = 'page_viewed'
AND TO_TIMESTAMP('1970-01-01 00:00:00.0','YYYY-MM-DD HH24:MI:SS.FF') + NUMTODSINTERVAL("EVENT_AT"/1000, 'SECOND')
 > TO_TIMESTAMP('2024-07-01', 'YYYY-MM-DD')
    AND SPACE_KEY =  '<insert_space_key>' GROUP BY NAME, SPACE_KEY;
     

  • To fetch details about the latest view date, and total view count, for each content in a space: 

    (info) You can remove SPACE_KEY  filter if you need details for all spaces.

    Postgres query
    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";

    MySQL query
    SELECT 
    COUNT(ev.`ID`) AS Total_page_views,
    MAX(FROM_UNIXTIME(ev.`EVENT_AT` / 1000)) 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 Server query
    SELECT 
    COUNT(ev.ID) AS Total_page_views,
    MAX(DATEADD(SECOND, ev.EVENT_AT / 1000, '1970-01-01')) 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;

    Oracle query
    SELECT 
    COUNT(ev.ID) AS Total_page_views,
    TO_TIMESTAMP('1970-01-01 00:00:00.0','YYYY-MM-DD HH24:MI:SS.FF')
       + NUMTODSINTERVAL(MAX(ev."EVENT_AT")/1000, 'SECOND') 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 = 'TEST'
GROUP BY 
    um.username, ev.NAME, ev.SPACE_KEY, ev.CONTENT_ID;
     

Data Retention job History

Confluence Analytics 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:

  1. Data retention period (deletion based on date):

    SELECT * FROM scheduler_run_details WHERE job_id = 'com.addonengine.analytics.DataRetention';

  2. Event retention (deletion based on event count):

    SELECT * FROM scheduler_run_details where job_id like 'AnalyticsForConfluence.EventLimiter';


Last modified on Mar 7, 2025

Was this helpful?

Yes
No
Provide feedback about this article

Related content

Powered by Confluence and Scroll Viewport.