Retrieving Confluence Analytics information directly from the Database

関連コンテンツ

  • 関連コンテンツがありません

お困りですか?

アトラシアン コミュニティをご利用ください。

コミュニティに質問

プラットフォームについて: Data Center のみ - この記事は、Data Center プラットフォームのアトラシアン製品にのみ適用されます。

この KB は Data Center バージョンの製品用に作成されています。Data Center 固有ではない機能の Data Center KB は、製品のサーバー バージョンでも動作する可能性はありますが、テストは行われていません。サーバー*製品のサポートは 2024 年 2 月 15 日に終了しました。サーバー製品を利用している場合は、アトラシアンのサーバー製品のサポート終了のお知らせページにて移行オプションをご確認ください。

*Fisheye および Crucible は除く

目的

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. 

例

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: 

    SELECT * FROM "AO_7B47A5_EVENT";
    SELECT * FROM AO_7B47A5_EVENT;
    SELECT * FROM "AO_7B47A5_EVENT";
    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
    select * from "AO_7B47A5_EVENT" where "NAME" = 'page_viewed';
    select * from AO_7B47A5_EVENT where NAME = 'page_viewed';
    select * from "AO_7B47A5_EVENT" where "NAME" = 'page_viewed';
    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:

    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";
    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;
    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";
    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:

    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";
    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;
    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";
    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:

    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;
    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;
    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;
    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):

    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";
    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;
    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;
    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:

    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";
    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`;
    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;
    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. データ保持期間 (日付に基づく削除)

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

  2. イベントの保持 (イベント数に基づく削除)

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


最終更新日: 2024 年 10 月 17 日

この内容はお役に立ちましたか?

はい
いいえ
この記事についてのフィードバックを送信する

関連コンテンツ

  • 関連コンテンツがありません
Powered by Confluence and Scroll Viewport.