This is a brief tutorial on how to create interactive reports from Jira data, as a Confluence page, using the free Play SQL Base plugin.For our example, we are going to build a Monthly Worklogs Report, showing hours worked per day for every user in a given month:
Of course, Tempo Timesheets is the de-facto plugin for this sort of thing, and already has a report like what we're building:
Tempo's report is prettier and more powerful, allowing hours to grouped by any field (e.g. project, or tempo Account), even hierarchically. Tempo's one deficiency here, which motivated this reimplementation, is that it cannot show users which have not logged any work.
But for the purposes of this tutorial, worklog information is just a nice example of something in the Jira database which you'd like to query in an interactive manner.
Here we assume you are using Postgres.
Which Confluence SQL plugin?
tall the free Play SQL Base plugin. You could alternatively use PocketQuery or SQL for Confluence, which are in fact better plugins overall - in particular, they let you restrict who can run SQL queries, whereas Play SQL can't. This tutorial uses Play SQL Base because it's what I had available. We will restrict SQL queries at the Postgres layer, which is a good thing to do anyway.
Step 1: Install Play SQL Base
In Confluence, type 'gg', 'Find new apps' and install the free Play SQL Base plugin.
In Confluence spaces you will now see a new 'Tables' menu item. Here is the page from a live Confluence instance, with various queries already defined (there's one from the Automatically deactivating inactive Jira users report):
Configure your database connection
Click 'Manage Connections and Permissions' and set up the database connection. In my instance my space's connection delegates use a Global datasource:
Clicking 'General Admin' shows the global config:
Creating a Postgres read-only account
At this point we're about to tell Play SQL how to connect to our database. We should probably do it with a dedicated read-only Postgres user. Also, since Jira might contain sensitive information irrelevant to our reports, we don't want to grant access to all Jira tables.
Our solution is to create a database view per report, in a special
queries schema so that our custom views can be distinguished from regular Jira table.
First, create a 'queries' schema, with a sample view containing a small amount of data:
Next, create a
jira_queries_readonly role that can only view the
queries schema tables, and a
confluence_reports user granted that role. These commands are cribbed shamelessly from https://blog.redash.io/postgres-readonly/, so read that to understand them properly:
Verify that, when connecting as
confluence_reports we can see our sample query but not generic Jira tables:
Define a Datasource in Confluence
There are two ways to tell Play SQL (and other SQL plugins) how to connect to a database:
- A direct connection - the plugin will contact the database directly, given a hostname, port, username and passwor
- A JNDI/Datasource connection - the plugin will ask Confluence's middleware (the Tomcat application server) for a preconfigured database connection
Either way will work. I used a datasource, defined as the
jdbc/QueriesDS section in my /opt/atlassian/confluence/conf/server.xml file:
You will need to restart Confluence to pick up this change.
- It's more secure - database credentials aren't stored as plaintext in the database or in inumerable backups.
- it lets you configure the 'QueriesDS' differently in production vs. sandbox. The hostname for Jira might be different on the sandbox server. Rather than reconfigure PlaySQL every time you sync sandbox data, you configure 'QueriesDS' once correctly in the sandbox
- It's just conceptually nicer (the inversion of control principle).
Configure PlaySQL with the Datasource
To recap, we've just been on a detour to create a read-only Postgres account, and edited Confluence's
conf/server.xml file to define our
QueriesDS datasource. Now configure Play SQL to use the Datasource. Here I've configured QueriesDS as our 'global connection', which Play SQL uses by default:
Create a test Play SQL Table
Now return to the 'Tables' tab in a space:
Under 'Queries' click 'Create new...'.
Now query your
sample view and click 'Preview' to verify it works:
Did we mention Play SQL Base is free? It is free, but also buggy, and at this point the bugs are very evident. The best we can say is that if you power on, it does work in the end.
At this point, you could just grant
jira_ro read-only access to the Jira tables:
That does the trick:
For security, I suggest you not grant
jira_ro access to the raw JIRA tables. Rather, for each report you want, create a custom view or table, and grant
jira_ro access to that.
A clean way to achieve this is with a custom database schema:
A 'DataSource' just means a database connection defined in