JIRA Boards Extractor

///JIRA Boards Extractor

JIRA Boards Extractor (jira-boards.pl) is the perl script which extracts data from the MySQL database of your Atlassian JIRA instance to help you identify redundant and unused JIRA boards (kanban and scrum), quick filters on each board, and associated JQL filters.

The script generates a formatted Microsoft Excel file with four tabs: all_boards, scrum_boards (boards with sprints), quick_filters (per each board), and prj_filters. You can find all associated MySQL queries with tabs in the script code and adjust them in accordance with your needs.

Install & Configure

Name: JIRA Boards Extractor
Version: 0.1 (check for the latest version)
Language: perl
Platform: Linux Logo Linux (tested on Debian but should work on most Linux platforms, only you need is to create a file with MySQL credentials)
Requirements: perl, libexcel-writer-xlsx-perl (Excel::Writer::XLSX), mysql-client, mpack
Download

Download and install:

sudo bash
apt-get -y install libexcel-writer-xlsx-perl mpack mysql-client
mkdir -p /root/scripts
wget https://svelab.com/download/jira-boards.zip -O /tmp/jira-boards.zip
unzip /tmp/jira-boards.zip -d /root/scripts/
chmod +x /root/scripts/jira-boards.pl
rm -f /tmp/jira-boards.zip
exit

You can install and use the script in two ways: on the MySQL server of your JIRA or on a dedicated server for your scripts. It is configurable in the script, read more in the next section.

Configurable user variables in the script:

Variable Default value Description
output_file jira_boards.xlsx Extracted data will be put into this file
email_rcpt jira_admin@svelab.com Generated file will be emailed to these recipients (use a comma as delimiter if you want to email a file to many people)
mysql_auth /etc/mysql/debian.cnf File with MySQL credentials, Debian has this file by default, if you use another Linux you must create a similar file with credentials to connect to your MySQL instance
mysql_db jira_prod Your JIRA database
mysql_ssh_host blank – connect directly (localhost) using the mysql command,
‘example.com’ – connect to MySQL via ssh. If you have a dedicated server for your scripts, you can install this script on it and put the hostname of your MySQL server here, in this case, the script will get data using ssh, the ssh key of your dedicated server must be added to your MySQL server
hide_deleted 1 1 – removed boards and filters will not be added to a Microsoft Excel file,
0 – removed boards and filters will be added and marked with a grey colour (you cannot see these boards and filters in the JIRA GUI but they still remain in a database)

Usage

root@svelab:~/scripts# ./jira-boards.pl

The script will extract needed data from a MySQL server, generate a Microsoft Excel file, and email it to all recipients mentioned in the email_rcpt variable. It is assumed that on the server where this script is run, the mail service (relay) is configured, anyway, a Microsoft Excel file will be generated.

Source code

See also

JIRA and Confluence Database Schemas

Contact us

Please, feel free to contact us if you have any questions or suggestions. Post a comment below if you want to report a bug.

By |2018-04-28T21:49:28+00:00September 17th, 2017|Linux, Scripts|0 Comments

Leave A Comment