はじめに
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します。
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;
指定期間のコメント数
期間を指定して、その間にいいねされた回数を日ごとに取得します。
コメント数はページやブログと違い何度も編集されることは少ないと思われるので、シンプルな集計にしています。
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;
指定期間のいいね数
期間を指定して、その間にいいねされた回数を日ごとに取得します。
コメント数と同様にシンプルな集計です。
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図は見たことがないので、それだけでも書いた価値があればと思います。
なお、ここで紹介した以外のテーブルでも覚えておくと良さそうなテーブルがありますが、それはまた別の機会に書きたいと思います。
最後まで読んで頂きありがとうございました。