Confluence Page Views Extractor

/, How To, Linux, Scripts/Confluence Page Views Extractor

Confluence Page Views Extractor (confluence-views.pl) helps extracting page views statistics from Atlassian Confluence which utilizing a MySQL database. The script is written in Perl and uses MySQL queries.

Confluence database schema contains the quite useful table AO_92296B_AORECENTLY_VIEWED, records about recently viewed pages are stored there. Unfortunately, the data in the table is not a pure views history and the distinctions as below:

  • Records are stored only for the last 2 months
  • A page hit entry will be added if the content_id and user_key combination is unique. If there is already a row for content_id and user_key Confluence will simply update that row

There is a ticket raised with Atlassian for the addition of a new table in which a complete history will be stored, but it seems that Atlassian does not plan to add such functionality. Also, a few plugins are available on Atlassian Market but they rely on the mentioned Confluence table and/or are simply front-ends for Google Analytics. Confluence Page Views Extractor was written to get at least some statistics and it is better than nothing.

If you a Java developer and brave enough, you can take the MySQL queries from this script as a basis and write your own plugin for Confluence.

It is also possible to obtain statistics of user activity by two other approaches:

  • Implement a log analyzer system and periodically feed it with Tomcat/Reverse proxy access log files
  • Add a JavaScript code of Google Analytics or Piwik Analytics at the end of HEAD (Confluence > General Configuration > Custom HTML)

Install & Configure

Name: Confluence Page Views Extractor
Version: 0.1 (check for the latest version)
Language: perl
Platform: Linux Logo Linux
Requirements: Excel::Writer::XLSX (perl lib), mysql-client, mpack, ssh
Download

Download and install:
You should modify the destination directory in accordance with your preferences.

wget https://svelab.com/download/confluence-views.zip -O /tmp/confluence-views.zip
mkdir -p /root/scripts
unzip /tmp/confluence-views.zip -d /root/scripts/
rm -f /tmp/confluence-views.zip
chmod +x /root/scripts/confluence-views.pl
exit

Configurable user variables in the script:

Variable Default value Description
output_file wiki_pageviews.xlsx File to be generated
email_rcpt admin@svelab.com List of recipients who will get an email with an Excel file
mysql_auth /etc/mysql/debian.cnf File with MySQL credentials, Debian has this file by default, if you use another Linux you must create a similar file with credentials to connect to your MySQL instance
mysql_db confluence_prod Your Confluence database
mysql_ssh_host blank ” – connect directly (localhost) using the mysql command,
example.com‘ – connect to MySQL via ssh. If you have a dedicated server for your scripts, you can install this script on it and put the hostname of your MySQL server here, in this case, the script will get data using ssh, the ssh key of your dedicated server must be added to your MySQL server
sshTimeout 5 How long ssh will be waiting for a response. Default is 5 seconds
only_groups client% ” – all groups, ‘value’ – only selected groups (MySQL format)

Usage

Execute in the CLI:

root@svelab:/root/scripts# ./confluence-views.pl

Screenshots:

Page Views Statistics
confluence-views-pages
Space Views Statistics
confluence-views-spaces
Detailed Statistics
confluence-views-detailed

Source code

See also

JIRA and Confluence Database Schemas
Atlassian Products Checker
JIRA Boards Extractor

Contact us

Please, feel free to contact us if you have any questions or suggestions. Post a comment below if you want to report a bug.

By |2018-04-28T21:46:38+00:00November 19th, 2017|Confluence, How To, Linux, Scripts|0 Comments

Leave A Comment