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:



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.



















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)

# 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

[1] Scrumdog source code

[2] DB Browser for SQLite

[3] SQLite website

[4] JIRA Cloud REST API documention

[5] Post: using Python to automate your Jira reports

[6] TIOBE Programming Community index

Related Posts

Howto Import Excel file into SQLite and back to Excel again

Solving the Jane Street puzzle of December 2022

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