はじめに

Confluenceユーザのみなさまこんにちは。
私は自社のConfluenceの管理者を担当しています。主にユーザの管理、コンテンツの監視、普及推進などを行っています。
普及状況の報告として毎月アクセス解析結果を提出しているのですが、今回はConfluenceの主なテーブルとアクセス解析の取得内容を記載したいと思います。

confluenceの環境

当ページの情報は以下の環境となっています。
これ以外の環境については異なる場合がありますのでご注意下さい。

Confluenceサーバー版
バージョン 5.9.11
DBはPostgreSQL

主なテーブル

先ず、ページ情報やユーザ情報など、アクセス解析にあたって利用するテーブルについて紹介します。

テーブル一覧

テーブル名	説明
content	ページとかコメントとかブログなどの情報
likes	いいね情報
cwd_user	ユーザ情報
user_mapping	ユーザキーとユーザ名のマッピング（大事）

ER図

簡単にER図を書いてみました。

contentテーブル

コンテンツ情報のテーブルです。
ページの情報だけでなく、ページ[PAGE]、コメント[COMMENT]、ブログ[BLOGPOST]、添付ファイル[ATTACHMENT]など、各種コンテンツの情報が格納されます。これらはcontenttypeで指定することが可能です。
titleはコンテンツのタイトルが入りますが、ページやブログ以外のタイトルが無いコンテンツ情報の場合は、このカラムはNULLです。
creatorはコンテンツの作成者ですが、ユーザ名ではなくユーザキー（※後述）が登録されています。

likesテーブル

いいね情報が入っています。
usernameにいいねを押したユーザキー（※後述）が、contentidにコンテンツのIDが登録されています。
コンテンツと紐づける場合はcontentidを使います。

cwd_userテーブル

ユーザ情報のテーブルです。
プライマリキーはidで、user_nameにはログインする時のユーザIDが登録されています。また、ログイン後の名前はdisplay_nameが表示されています。（たぶん）
activeが 'T' のユーザが有効で、 'F' のユーザが無効の状態となっています。

なお、confluenceではユーザ認証設定（ディレクトリ設定）が複数設定することが出来るため、どの認証設定のユーザ情報かはdirectory_idで判断することが出来ます。

user_mappingテーブル

ユーザキーとユーザ名をマッピングしているテーブルです。
user_keyにユーザキーが、usernameにログイン時のユーザIDが登録されています。
contentテーブルやlikesテーブルの情報について、「誰が登録したのか？」を確認する際に利用します。

アクセス解析

今回は以下の情報取得について紹介します。

指定期間のページ、ブログ更新数
指定期間のコメント数
指定期間のいいね数

※PVはGoogleAnalyticsを利用しているため取り上げません

指定期間のページ、ブログ更新数

期間を指定して、その間にページ、ブログが作成、更新された回数を日ごとに取得します。
ただし、１つのコンテンツに対し同じユーザが複数回の更新を行った場合は、１回とみなすようにユーザ名と更新日（年月日）でgroup byします。

特定期間の日ごとのページ更新数取得SQL

select 
    updates
    , count(title) 
from (
    select 
        c.title
        , to_char(c.lastmoddate,'yyyy/mm/dd') updates
        , u.username 
    from content c 
        inner join user_mapping u 
            on c.creator = u.user_key 
    where contenttype in ('PAGE','BLOGPOST') 
    and c.lastmoddate between '2016/10/01 00:00:00' and '2016/10/31 23:59:59' 
    group by 
        title
        , updates
        , u.username
    ) contents 
group by updates 
order by updates;

指定期間のコメント数

期間を指定して、その間にいいねされた回数を日ごとに取得します。
コメント数はページやブログと違い何度も編集されることは少ないと思われるので、シンプルな集計にしています。

特定期間の日ごとのコメント数取得SQL

select 
    to_char(creationdate, 'yyyy/mm/dd') dates
    , count(contentid) 
from content 
where contenttype = 'COMMENT' 
and creationdate between '2016/08/01 00:00:00' and '2016/08/31 23:59:59' 
group by dates 
order by dates;

指定期間のいいね数

期間を指定して、その間にいいねされた回数を日ごとに取得します。
コメント数と同様にシンプルな集計です。

特定期間の日ごとのいいね数取得SQL

select 
    to_char(creationdate, 'yyyy/mm/dd') dates
    , count(id) 
from likes 
where creationdate between '2016/08/01 00:00:00' and '2016/08/31 23:59:59' 
group by dates 
order by dates;

集計SQLの実行結果

集計SQLを実行すると、それぞれこんな感じで取得できます。

データをグラフ化

取得したデータをグラフにした例です。
Confluenceのグラフマクロでも可能ですが、設定の融通が効かない（知らないだけだったらすいません）ので、エクセルでグラフを生成して画像として保存し、Confluenceに貼り付けています。

さいごに

当エントリーの内容は、テーブル定義やデータと睨めっこして掘り出した情報のため、実はちょっと意味が違うとか、もっと楽な方法があるとかあるかもしれません。Confluenceのテーブル定義などはググれば色々と情報は出てきますが、ER図は見たことがないので、それだけでも書いた価値があればと思います。

なお、ここで紹介した以外のテーブルでも覚えておくと良さそうなテーブルがありますが、それはまた別の機会に書きたいと思います。

最後まで読んで頂きありがとうございました。

Confluenceのテーブル情報とアクセス解析