Willem Hoek

Why I created Scrumdog - a program to download Jira Issues to a local database

Jul 14, 2022


The Tower of Babel by Pieter Bruegel the Elder, 1563


Scrumdog is a software program I created to help me automate my Jira Issue reporting. Scrumdog downloads Jira Issues to a local SQLite database for analysis and reporting. It is a really helpful tool if you are managing a complex, multi party project with both Jira and non-Jira users and you report regularly to a Steering Committee.

You can download Scrumdog from here.

Table of Contents:

Getting info from Jira

Jira has many real-time dashboards and reports (“filters”) that can be used to manage Jira Issues. Jira also provides you with the ability to export issues in CSV, XML or Excel/Google Sheets format. However – if you find yourself (like me) downloading thousands of Jira issues regularly (e.g. daily) for reporting and analysis – it might be easier to just automate your steps.

The approach I took to automate Jira reporting consists of two steps:
Step 1 - Get the Jira Issues and save them in a local SQL database
Step 2 - Create Excel report(s) from local SQL database

In a previous post I showed how this can be done with Python. However to simplify the process further, Scrumdog can be used to automate Step 1 without you having to write any code.

When to use Scrumdog?

When not to use Scrumdog

Design considerations:

The following was important to me as a user of Scrumdog:

MUST HAVE

IF POSSIBLE

Development of Scrumdog

Almost any general purpose programming language could be used to develop Scrumdog. The evaluation criteria I used to pick a suitable programming language:

I came up with the following shortlist of possible programming languages to use: Python, C#, Java, D, OCaml and Go. This list is based on my experience and requirements – yours will most definitely be different.

Python

Positives:

Negatives:

C#

Positives:

Negatives:

Java

Positives:

Negatives:

Go

Positives:

Negatives:

D

Positives:

Negatives:

OCaml

Positives:

Negatives:


I excluded Python, C# and Java based on the negatives listed above and decided to create simple prototypes using D, OCaml and Go. The prototypes were 20-30 lines of code each to test the following:

After evaluating the prototypes, Go was excluded based on the following:

With OCaml and D left, I decided to proceed further with both. After a few hundred lines of code – I had the basics working for both the OCaml and D programs.

And the winner is …

At this point it was almost a coin-flip on which one to use, but I decided to continue with OCaml based on the following:

After a few more days of developement and testing I had a working version of Scrumdog and was able to use it in my own automated reporting workflow.

Working with SQLite

You would need a tool to test SQL queries and manage the SQLite database files. I find the SQLite command-line shell program a bit too minamilistic to use. My prefered way to create and test SQL queries is with a (free) program called DB Browser for SQLite. With DB Browser for SQLite it is also possible to display the database table contents and/or edit data, which is very handy during testing.

Creating Excel reports from SQLite

Although not the focus of this post – you may want to create Excel reports from SQLite. Python has some great libraries to get you going very quickly. Here is an example of how to create a simple Excel report using data from SQLite.

import pandas as pd
import sqlite3

# Get data from SQLite db
con = sqlite3.connect("jira.db")
sql = "select * from zz_issues; "
df = pd.read_sql_query(sql, con)
con.close() 

# write to Excel
df.to_excel ("report.xlsx", index=False, header=True)

Future Improvements to Scrumdog

Some enhancements in the pipeline:

Request for feedback

Feel free to test out Scrumdog – which you can download for free from here.

References and further reading

[0] Scrumdog website
https://scrumdog.app/

[1] Scrumdog source code
https://github.com/whoek/scrumdog

[2] DB Browser for SQLite
https://sqlitebrowser.org/

[3] SQLite website
https://www.sqlite.org/

[4] JIRA Cloud REST API documention
https://developer.atlassian.com/cloud/jira/platform/rest/

[5] Post: using Python to automate your Jira reports
https://whoek.com/b/use-jira-api-to-create-excel-reports

[6] TIOBE Programming Community index
https://www.tiobe.com/tiobe-index/

Related Posts

Jane Street puzzle Feb 2021 SOLVED! OCaml to the rescue

Solving the Jane Street puzzle of Dec 2020 - Backtracking with OCaml

Automate your Jira reporting with Python and Excel

Solving the Jane Street Puzzle of June 2020; Circle Time

Solving the Jane Street Puzzle of May 2020; Expelled with OCaml