Jira Work Analysis Project

Welcome to my project to download Jira issues and epics, store them in a Sqlite database.

I've also included some of the queries and scripts I use against this data to analyse work done and to produce billing reports for our clients.

This project started as a quick script to let me download and query data about Jira tickets from Atlassian Cloud and escalated from there.

Current Status

The project is functional and I use it to support running our business.

Usage

An API token is needed to get data out of Jira. You need to get your own and then put it in a jira_conf.ini file in the project directory. Your jira_conf.ini file should look like this:

[settings]
email = {the email account you use to access Atlassian cloud}
api_token = {your API token}
server = {the URL of your Atlassian cloud Jira instance}

For example mine looks like:

[settings]
email = andy.todd@appsure.com.au
api_token = {my API token}
server = https://appsure.atlassian.net

To run the Python code you will need to create a virtual environment and install the libraries used in the code:

$ python -m venv jiraanalysis
$ source jiraanalysis/bin/activate
$(jiraanalysis) python -m pip install -f requirements.txt

For more information on Python virtual environments take a look at https://docs.python.org/3/tutorial/venv.html

Once you have a working virtual environment and ini file you can run some code. To download the issues, epics and worklogs for a specific Jira project you can run:

$ python jira_issues_download.py -p {Project Key} -b {database file name}

For example, each month I produce the database snapshot of Jira for the 'CS' project using:

$ python jira_issues_download.py -p CS -b jira_issues_cs.sqlite

For further information on the options when running jira_issues_download.py just run:

$ python jira_issues_download.py --help

To produce billing data for the monthly report I run the get_sprint_details query in jira_issues.sql using jira_issues_query.py against the freshly downloaded data in my Sqlite database. To get the details for the March sprint I run

$ python jira_issues_query.py -b jira_issues_cs.sqlite get_sprint_details %March%

I then copy and paste the results from the output CSV file into the Excel billing report.

For further information on the options when running jira_issues_query.py just run:

$ python jira_issues_query.py --help

Roadmap

There isn't one at the moment

License and Acknowledgements

Inspiration and ideas for the initial code came from Willem Hoek - https://whoek.com/b/use-jira-api-to-create-excel-reports

This work is licensed under the MIT License

Copyright 2022 Andrew J. Todd esq.

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.