Scrumdog
Download your Jira Issues to a local SQL database

Scrumdog is a simple utility that downloads your Jira Cloud Issues to a local SQLite database. With the Jira issues available in a local database, you can then use standard SQL tools to report on and track your Jira project issues.

Features

• Simple to install - just download a single (executable) file and run it
• Safe to use - as it only pulls data from Jira, no Jira updates or any other calls are made
• As a command line application (CLI), Scrumdog can easily be scheduled to run in background
• Minimal setup requires - so you will be up and running within minutes
• Automatic field mapping for all key Jira fields
• Field mapping is customizable for custom Jira fields. See [fields] tag below
• The source code is available if you want to compile the program yourself or see how it works

Download and Usage

Click on the image to download the executable file - else go to the downloads page.

Windows

• Download scrumdog.exe via link above
• Save file to somewhere suitable on your machine
• Run it via Command Line

Linux or macOS

• Download the scrumdog executable via link above
• Set as executable chmod +x scrumdog
• Run ./scrumdog

Build from source

Another option is to build the executable from source. Scrumdog is written in OCaml. After installing OCaml, proceed with the following steps.

Configuration

In order for Scrumdog to work, some basic configuration settings are required. These settings are done in a text file and used when you run Scrumdog. To create a sample configuration file - run scrumdog.exe -j. This will create a file sample.sql. All you need to change are: your cloud server adddress, your email and Jira API token and `JQL` required to select the Jira Issues.

Sample configuration file:

  [server]   https://dev987.atlassian.net/
  [email]    chris@dev987.com
  [api_token]     zdBWY3Jq5ppRCtDsNJjJ6A4E
  [db_filename]   jira.db
  [db_table_prefix]      zz
  [jql]                  project = zz

  [fields]

  assignee   assignee.displayName
  creator    creator.displayName
  issuetype  issuetype.name
  priority   priority.name

  

The configuration file can be named anything but I suggest you use a short name and .jql extension, example config.jql.

The following settings are required:

  [server]      Your Jira Cloud URL. Get from browser when using Jira.
  [email]       Your Jira account username
  [api_token]   API token from your Jira account settings
  [db_filename] The SQLite filename you want to use
  [db_table_prefix]    Prefix added to all tables in case of multiple JQLs
  [jql]         The Jira Query to select the issues  

Optional settings:

  [fields]      Field mappings: database fieldname and JSON object name  (case sensitive).

When you execute Scrumdog you specify which configuration file should be used. Example scrumdog config.jql.

How it Works

What goes on behind the scenes when you run Scrumdog?

The setup file contains your JIRA credentials, JQL to execute, the SQLite database filename to use and table name where the Jira issues will be loaded. A https API call is made from your PC to the JIRA Cloud API to download the Issues. You can read more about the Jira Cloud API's here.

Data is received from Jira in a JSON format. The Scrumdog application will extract the key Jira fields from the JSON and saved them in SQLite. Every row in the SQLite issues table is a Jira Issue. The full JSON that was received from Jira, is also stored in the local database. This is particularly handy where you have custom fields.

Every time Scrumdog is run - it will re-create the tables listed below. The tables will have a prefix as defined in your confg file.

  fields:       List of all the Jira and custom fields in your system.
  issues:       The Jira Issues as per JQL provided in config file.
  comments:     All the comments for the issues. The text only.
  links:        List of linked issues.
  subtasks:     List of subtask issues.
  labels:       All Jira labels assigned to Jira Issues.
  compenents:   All components assigned to jira Issues.

The Jira API response (header file, response code and JSON payload) of the last Jira API call is stored in a log folder. The log folder is in same directory where the SQLite database file is located.

Best practices

If you have multiple queries (JQL) you want to run you will have multiple config files. Typically all issues are saved in the same database file. However you can you the db_table_prefix tag to ensure the issues are stored in different tables.

Work in progress

This is a early beta version -- so it might not work for you. Any feedback would be greatly appreciated. See my email address lower down or log a GitHub Issue

The application should run OK on all Microsoft Windows 10 or 11, MacOS (Intel) and Linux (Debian) machines.

The command line executable is not (yet) code signed, so you may get a message about not being a trusted application. This will change is later versions.

Who created this and why?

The utility was created by Willem Hoek. I was downloading issues from Jira Cloud multiple times a day for reporting. I created and use Scrumdog to automate reporting of Jira Issues.

If you find this program of use or have any comments or issues -- please contact me via my email below

See also

• GitHub - source code
• GitHub - scrumdog-binaries
• More about the project