データベースから Confluence アナリティクスの情報を直接取得する

関連コンテンツ

お困りですか?

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

コミュニティに質問

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

このナレッジベース記事は製品の Data Center バージョン用に作成されています。Data Center 固有ではない機能の Data Center ナレッジベースは、製品のサーバー バージョンでも動作する可能性はありますが、テストは行われていません。サーバー*製品のサポートは 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: 

    Postgres のクエリ
    SELECT * FROM "AO_7B47A5_EVENT";
     
    MySQL クエリ
    SELECT * FROM AO_7B47A5_EVENT;
     
    SQL Server のクエリ
    SELECT * FROM "AO_7B47A5_EVENT";
     
    Oracle のクエリ
    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 のクエリ
    select * from "AO_7B47A5_EVENT" where "NAME" = 'page_viewed';

    MySQL クエリ
    select * from AO_7B47A5_EVENT where NAME = 'page_viewed';

    SQL Server のクエリ
    select * from "AO_7B47A5_EVENT" where "NAME" = 'page_viewed';

    Oracle のクエリ
    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 のクエリ
    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 クエリ
    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 のクエリ
    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 のクエリ
    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 のクエリ
    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 クエリ
    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 のクエリ
    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 のクエリ
    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 のクエリ
    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 クエリ
    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 のクエリ
    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 のクエリ
    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 のクエリ
    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 クエリ
    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 のクエリ
    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 のクエリ
    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 のクエリ
    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 クエリ
    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 のクエリ
    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 のクエリ
    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';


Last modified on Mar 7, 2025

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

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

関連コンテンツ

Powered by Confluence and Scroll Viewport.