confluence

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

More than 1 year has passed since last update.


はじめに

Confluenceユーザのみなさまこんにちは。

私は自社のConfluenceの管理者を担当しています。主にユーザの管理、コンテンツの監視、普及推進などを行っています。

普及状況の報告として毎月アクセス解析結果を提出しているのですが、今回はConfluenceの主なテーブルとアクセス解析の取得内容を記載したいと思います。


confluenceの環境

当ページの情報は以下の環境となっています。

これ以外の環境については異なる場合がありますのでご注意下さい。


  • Confluenceサーバー版

  • バージョン 5.9.11

  • DBはPostgreSQL


主なテーブル

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


テーブル一覧

テーブル名
説明

content
ページとかコメントとかブログなどの情報

likes
いいね情報

cwd_user
ユーザ情報

user_mapping
ユーザキーとユーザ名のマッピング(大事)


ER図

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

confluencedb_er図.jpg


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を利用しているため取り上げません


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

期間を指定して、その間にページ、ブログが作成、更新された回数を日ごとに取得します。

ただし、1つのコンテンツに対し同じユーザが複数回の更新を行った場合は、1回とみなすようにユーザ名と更新日(年月日)で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を実行すると、それぞれこんな感じで取得できます。

confluencedb_集計SQL結果.jpg


データをグラフ化

取得したデータをグラフにした例です。

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

confluencedb_グラフ.png


さいごに

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

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

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