Excerpt
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.

...

use Confluence as a query / reporting engine, querying SQL data sources like the Jira database. For our example we query JIRA's database to build a Monthly Worklogs Report, showing hours worked per day for every user in a given month

...

. We use the free Play SQL Base plugin.

Of course, Tempo Timesheets is the de-facto plugin for this sort of thing, and already has a report like what we're building:

...

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.

Implementation

Here is an overview of what we're going to od:

Install a Confluence plugin capable of rendering SQL query results in a page
Configure a read-only Postgres account to query database with
Configure our plugin
Display sample table results in Confluence
Create our real timesheet query, and render that.

Choosing a Confluence SQL plugin

For this tutorial we are using the free Play SQL Base plugin. You could alternatively use PocketQuery or SQL for Confluence, which are in fact better plugins overall - in For this tutorial we are using 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.

...

Click 'Manage Connections and Permissions' and set up the database connection. In my instance my space's database connection delegates use a Global . Here we just use the global datasource:

Clicking 'General Admin' shows the global config:

...

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.

For the sake of security, we want to connect as a user with read-only permissions, and with visibility restricted to just data necessary for our report.

The read-only requirement can be achieved with Postgres permissions. The restricted visibility requirement can be achieved by only allowing queries of predefined views, in a custom queries schema. The main Jira tables in the public schema will be inaccessible.

First, First, create a 'queries' schema, with a sample view containing a small amount of data:

Code Block

language	sql

root@jturner-desktop:~# su - postgres
postgres@jturner-desktop:~$ psql redradish_jira
Null display is "␀".
Line style is unicode.
Border style is 2.
psql (12.2 (Ubuntu 12.2-4))
Type "help" for help.

redradish_jira=# createCREATE schemaSCHEMA ifIF notNOT existsEXISTS queries;
CREATE SCHEMA
redradish_jira=# createCREATE orOR replaceREPLACE viewVIEW queries.sample asAS select project.pkey || '-' || jiraissue.issuenum AS key, summary from public.project JOIN public.jiraissue ON project.id=jiraissue.project LIMIT 5;
CREATE VIEW
redradish_jira=# select * from queries.sample;
┌──────────┬─────────────────────────────────────────┐
│   key    │                 summary                 │
├──────────┼─────────────────────────────────────────┤
│ SOC-3    │ A second Response for good measure      │
│ ML-53    │ Ongoing Atlassian Product Support, 2014 │
│ IC-34    │ Invoice 93236 - 1/Jul/15 to 30/Sep/15   │
│ JTODO-19 │ Tax Payment Q2 Due                      │
│ CLIC-2   │ Move projects to OnDemand               │
└──────────┴─────────────────────────────────────────┘
(5 rows)

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:. Run them when connected to the Jira database, not the default 'postgres' database.

Code Block

redradish_jira=# CREATE ROLE jira_queries_readonly;
CREATE ROLE
redradish_jira=# GRANT CONNECTGRANT CONNECT ON DATABASE redradish_jira TO jira_queries_readonly;
GRANT
redradish_jira=# GRANT USAGE ON SCHEMA queries TO jira_queries_readonly;
GRANT
redradish_jira=# GRANT SELECT ON ALL TABLES IN SCHEMA queries TO jira_queries_readonly;
GRANT
redradish_jira=# CREATE USER confluence_reports WITH PASSWORD 'confluence_reports';
CREATE ROLE
redradish_jira=# GRANT jira_queries_readonly TO confluence_reports;
GRANT ROLE

Verify that, when connecting as confluence_reports we can see our sample query but not generic Jira tables:

Code Block

root@jturner-desktop:~## PGUSER=confluence_reports PGPASSWORD=confluence_reports PGHOST=localhost PGDATABASE=redradish_jira psql -tAc "select count(*) from queries.sample;"
5
root@jturner-desktop:~## PGUSER=confluence_reports PGPASSWORD=confluence_reports PGHOST=localhost PGDATABASE=redradish_jira psql -tAc "select count(*) from public.jiraissue;"
ERROR:  permission denied for table jiraissue

...

A direct connection - the plugin will contact the database directly, given a hostname, port, username and passworpassword
A JNDI/Datasource connection - the plugin will ask Confluence's middleware (the Tomcat application server) for a preconfigured database connection

...

Expand

title	Why use a Datasource instead of a direct connection?

It's more secure - database credentials aren't stored as plaintext in the database or in inumerable innumerable backups.
it lets you configure the 'QueriesDS' differently in production vs. sandbox. The database 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 conf/server.xml .
the app server can provide stats about database connection use via JMX or JavaMelody.
It's just conceptually nicer (the inversion of control principle).

...

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 default 'global connection', which Play SQL uses by default:

Create a test Play SQL Table

...

Warning

Did we mention Play SQL Base is free? It is free, but also buggy, and at this point the bugs are very evident:

The list of queryable tables on the right may or may not be correct. In the screenshot above it reflects an unrelated 'playsql' schema, not 'queries'.
SQL queries can't end with a semi-colon, or you'll get an error
Clicking 'Save' on a newly defined query, as you will now want to do, results in an error:

But don't worry, your query did save.

If you power onpersevere, it does work in the end. Don't complain - the Play SQL author makes his money from Play SQL Spreadsheets, not Play SQL Base - we're fortunate to have a free, roughly functional plugin at all.

Create the

...

timesheets database view

So far we've successfully queried queries.sample . We now create a queries.worklog_monthly view containing our real timesheet data.

We're not going to dwell too much on the specifics of our query. Here it is:

...

Code Block
/opt/atlassian/jira/current # mkdir SQL_QUERIES /opt/atlassian/jira/current # cd SQL_QUERIES/ /opt/atlassian/jira/current/SQL_QUERIES #

Then you can fetch the SQL directly using curl : and run it to create the view in your database:

Code Block

/opt/atlassian/jira/current/SQL_QUERIES # curl -sLOJ 'https://github.com/redradishtech/jira-interesting-sql-queries/raw/master/worklog_monthly.sql'
/opt/atlassian/jira/current/SQL_QUERIES # head worklog_monthly.sql 
-- A giant table of worklog hours per day, for each day of the month, selectable by user, year and month
-- https://www.redradishtech.com/display/~jturner/2019/12/19/A+monthly+worklog+report+within+Confluence?moved=true
create schema if not exists queries;
create or replace view queries.worklog_monthly AS
select * from (
        select user_name, email_address, year, month
        , round(sum(sum),2) AS month_total
        ,case sum("1") when 0 then 0 else round(sum("1"),2) end AS "1"
        ,case sum("2") when 0 then 0 else round(sum("2"),2) end AS "2"
        ,case sum("3") when 0 then 0 else round(sum("3"),2) end AS "3"
/opt/atlassian/jira/current/SQL_QUERIES #

and run it in your database:

Code Block

root@jturner-desktop:~# PGUSER=jira_ro PGPASSWORD=jira_ro PGHOST=localhost PGDATABASE=redradish_jira psql 
psql (12.2 (Ubuntu 12.2-4))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

redradish_jira=> \d
redradish_jira=> select^C
redradish_jira=> \conninfo
You are connected to database "redradish_jira" as user "jira_ro" on host "localhost" (address "127.0.0.1") at port "5432".
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
redradish_jira=> select * from jiraissue ;
ERROR:  permission denied for table jiraissue
redradish_jira=> \q
root@jturner-desktop:~# PGUSER=jira_ro PGPASSWORD=jira_ro PGHOST=localhost PGDATABASE=redradish_jira psql 
psql (12.2 (Ubuntu 12.2-4))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

redradish_jira=> \conninfo
You are connected to database "redradish_jira" as user "jira_ro" on host "localhost" (address "127.0.0.1") at port "5432".
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
redradish_jira=> select * from jiraissue;
ERROR:  permission denied for table jiraissue
redradish_jira=> \q
root@jturner-desktop:~#

At this point, you could just grant jira_ro read-only access to the Jira tables:

Code Block

root@jturner-desktop:~# su - postgres
postgres@jturner-desktop:~$ psql redradish_jira
Line style is unicode.
Border style is 2.
psql (12.2 (Ubuntu 12.2-4))
Type "help" for help.

postgres=# grant usage on schema public to jira_ro;
GRANT
postgres=# grant select on all tables in schema public to jira_ro;
GRANT
postgres=#

That does the trick:

Code Block
postgres@jturner-desktop:~$ logout root@jturner-desktop:~# PGUSER=jira_ro PGPASSWORD=jira_ro PGHOST=localhost PGDATABASE=redradish_jira psql -tAc "select count(*) from public.jiraissue;" 68

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:

...

sudo -u postgres psql redradish_jira -tAXq  < worklog_monthly.sql

Verify that our confluence_reports user can read our new queries.worklog_monthly table:

Code Block
# PGUSER=confluence_reports PGPASSWORD=confluence_reports PGHOST=localhost PGDATABASE=redradish_jira psql -tAc "select count(*) from queries.worklog_monthly;" 121

Create a worklog_monthly Play SQL Table

As we did earlier for queries.sample , now configure a Table in Play SQL for our queries.worklog_monthly view.

You should first enter the query:

Code Block

language	sql

select * from worklog_monthly

Preview it to make sure that works. If so, parametrize it:

Code Block
select * from queries.worklog_monthly where year='$year'::integer and month='$month'::integer and email_address ~ '$email'

Click 'Options >>' and configure the parameters:Image Added

You may want to tick the 'Cache' checkbox if you have a lot of data to query.

Create a page containing the table

Our final step is to create a page in the Confluence space, containing a Play SQL Query macro:

Image Added

Configure the macro to use the worklog_monthly query:

Image Added

and there you have it: our final worklog report:

Image AddedA 'DataSource' just means a database connection defined in

Page tree

Versions Compared

Old Version 2

New Version 3

Key

Implementation

Choosing a Confluence SQL plugin

Choosing a Confluence SQL plugin

Create a test Play SQL Table

Create the

timesheets database view

Create a worklog_monthly Play SQL Table

Create a page containing the table

Page tree

Page History

Versions Compared

Old Version 2

New Version 3

Key

Implementation

Choosing a Confluence SQL plugin

Choosing a Confluence SQL plugin

Create a test Play SQL Table

Create the

timesheets database view

Create a worklog_monthly Play SQL Table

Create a page containing the table