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 is an overview of what we're going to od:
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.
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):
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:
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:
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=# create schema if not exists queries; CREATE SCHEMA redradish_jira=# create or replace view queries.sample as 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:
redradish_jira=# CREATE ROLE jira_queries_readonly; CREATE ROLE redradish_jira=# GRANT 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:
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 |
There are two ways to tell Play SQL (and other SQL plugins) how to connect to a database:
Either way will work. I used a datasource, defined as the jdbc/QueriesDS
section in my /opt/atlassian/confluence/conf/server.xml file:
<Engine name="Standalone" defaultHost="localhost" debug="0"> <Host name="localhost" debug="0" appBase="webapps" unpackWARs="true" autoDeploy="false" startStopThreads="4"> <Context path="" docBase="../confluence" debug="0" reloadable="false" useHttpOnly="true"> <Resource name="jdbc/ConfluenceDS" auth="Container" type="javax.sql.DataSource" username="confluence" password="<REDACTED>" driverClassName="org.postgresql.Driver" url="jdbc:postgresql://localhost:5432/confluence" maxTotal="20" validationQuery="select 1"/> <Resource name="jdbc/QueriesDS" auth="Container" type="javax.sql.DataSource" username="confluence_reports" password="confluence_reports" driverClassName="org.postgresql.Driver" url="jdbc:postgresql://localhost:5432/jira?currentSchema=queries" maxTotal="20" validationQuery="select 1"/> <!-- Logging configuration for Confluence is specified in confluence/WEB-INF/classes/log4j.properties --> <!-- Uncomment this to DISABLE session serialization. <Manager pathname=""/> --> <Valve className="org.apache.catalina.valves.StuckThreadDetectionValve" threshold="60"/> </Context> <Context path="${confluence.context.path}/synchrony-proxy" docBase="../synchrony-proxy" debug="0" reloadable="false" useHttpOnly="true"> <Valve className="org.apache.catalina.valves.StuckThreadDetectionValve" threshold="60"/> </Context> </Host> </Engine> |
You will need to restart Confluence to pick up this change.
|
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:
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:
If you power on, 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. |
We're not going to dwell too much on the specifics of our query. Here it is:
I suggest creating a directory in your Confluence app dir for SQL queries like this:
/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
:
/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:
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:
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:
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:
A 'DataSource' just means a database connection defined in