Build your own reporting tool for Jira — with Tableau, Python, PostgreSQL & Airflow
Multiple teams working in the same Jira Service Management Environment. Different teams, issuetypes and configuration are increasing the complexity for a quick general overview. The performance dashboard should be accessible for all, especially those who don’t work in Jira. Therefore we build a small workaround to create awesome dashboards to track the work processed on several teams. The dashboard will be updated automatically at any given time. In this way, the best from all the mentioned software products above are combined. In our simple use case the tableau dashboard will show how different teams are receiving tickets over time.
I was not satisfied with the reporting views Jira offered me. The evaluation of various contents such as categorising according to individual keywords in text fields or the description is not possible by default. You reach the system limits relatively quickly. Since I’ve been a big Tableau fan for a long time, I wanted to fix the problem this way, a direct link is unfortunately not possible. That’s why I need a few other tools in between to have the data available in such a way that I can start. The solution in the end was relatively simple, even if it seems like a long journey. I am happy to give you an insight into how I dealt with the problem and how I came to the solution.
Your teams are working within Jira or Jira Service Management?
Jira is a software to plan, track, and manage your agile and software development projects. More and more Jira is used in classic engineering and development projects to improve the transparency, overview of topics and to shift the work within the team members. The aim was to create an overview that not only works across projects, but also combines the contents of various fields and thus allows a general evaluation. The standard dashboard views were quickly outdated. I wasn’t satisfied with the plugins either.
Set up the PostgreSQL to store the data
PostgreSQL is a extrem powerful, open source object-relational database. The database is used in this case to store the relevant data out the Jira system in a way to make the data accessible for tableau. For our use case one table with three columns is sufficient (Issue-Key, created at date and the project). The Issue-Key will be the “id column” to check if the issue is already existing in the database. This is useful because each key is only issued once in Jira. The key column (ticketkey) and the two information columns (createdat, project) are all of the data type “character varying” because both, the issue key and the project can consist of integers and strings.
Python to transfer the data from Jira to the database
Python scripts can be used to transfer the data via the rest API of Jira to the database. For a code example how to query Jira informations and write the collected data in the database check my code out: Github. Following imports are necessary to schedule the code, hosted on airflow:
import airflow
from airflow.models import DAG
from airflow.operators.python import PythonOperator
from airflow.providers.postgres.hooks.postgres import PostgresHook
from airflow.providers.jira.hooks.jira import JiraHookThose connections for Postgres and Jira have to been set and later will be used within the script. Airflow needs to know how to connect to the environment. Information such as hostname, port, login and passwords to the Jira API and the postgres database is handled in the Admin->Connections section of the UI. The python code will then reference the 'conn_id' of the Connection objects.
jira_conn = JiraHook(“jira”).get_conn()
postgres_conn = postgres_hook.get_conn()Now the two connections are implemented and the tickets in Jira can be queried and the postgres connection to write those informations in there is set up. With the JQL (Jira Query Language) you can define which issues you want to query. For information read this cool blog about JQL: the most flexible way to search Jira.
jql = “project in <project_name>\
AND createdDate >= \”2021/01/01\”” issues = jira_conn.search_issues(jql,startAt=0, maxResults=0)
# Default of the search is 50 tickets, set it to 0 to receieve all results logging.info(‘Found Tickets for JQL: ‘ + str(issues.total))
To generate the information issue by issue, a for loop is set up to receive the field values of every issue. The executed SQL now is trying to insert the values and if there is a conflict (the key is already present) it updates the fields additional to the key column (e.g. the “createdat” and “project” column.)
for issue in issues:Ticket_Key = issue.key
Ticket_Creation = issue.fields.created
Ticket_Project = issue.fields.project.key# SQL to insert new tickets and update the existing onescursor = conn.cursor()
cursor.execute
("""
INSERT INTO jira.reporting
(ticketkey, createdat, project)
VALUES
(%s, %s, %s) ON CONFLICT(ticketkey) DO UPDATE
SET
createdat=excluded.createdat,
project=excluded.project;
""",
(Ticket_Key, Ticket_Creation, Ticket_Project)
)
Keep sure to commit your changes to the PostgreSQL and close your postgres connection with the following commands:
conn.commit() cursor.close() conn.close()Data Preparation with Tableau
Tableau is a visual analytics platform to make the most of their data. All the data has now been transferred to the database and saved. Data is only useful if it can be understood. In our use case it should be shown how the different teams/Jira- projects receive tickets over time (x-axis). First the connection to the created and now filled databse has to be setup. Therefore be sure the tableau postgres connector is installed. Start Tableau and under Connect, select PostgreSQL. Then fill in the relevant informations like Server, Port, Database (name) and your authentication type.
For the use case just set up a small line diagram with one column: DATETRUNC(‘day’, [Createdat]) and one row: RUNNING_SUM(COUNT([Ticketkey])) Filters can be added if necessary.
Mark the different projects with colours and highlight the project name in the diagram and already basically finished in the diagram. With those settings we have now created the following diagram. This can of course be expanded as required, but reflects everything that is necessary for our use case.
Additional the workbook can be pushed to the server to make it accessible for the colleagues. Therefore take a look to the tableau documentation, how to publish your workbook with simple steps.
Refresh the database with Airflow
In order to complete the whole project, I would like to briefly introduce Airflow. To update the database in a given schedule airflow is used to trigger the python script to renew the database. We use Airflow to author workflows as Directed Acyclic Graphs (DAGs) of the task. The Airflow scheduler executes the python script on an array of workers while following the specified dependencies. For more informations about airflow take al look in the official Apache Airflow documentation.
After uploading the code be sure to set up the Connections in the Admin Area like I mentioned it above. With the weekly data update, we have created a dashboard that is alive and constantly up-to-date.
Conclusion
Although this implementation has a few tools in the creation chain, this implementation offers a great deal of freedom in presentation and evaluation. For me it was also the first test and the first successful implementation, but once I’ve put it on I can rebuild it over and over again and cover every application. I hope I can give you the incentive and the first necessary basic information to implement this use case. You can also check out my code at GitHub.
