reporting-jira-cloud

Script for extracting data from Jira Cloud API's for reporting purposes using a Jira Issues Filter

Dependencies

• Python 3.9
• See requirements.txt (use: pip install -r requirements.txt)

Update

See Releases in GitHub repository

Configuration Setup

The Jira authentication settings and label lookup data is stored in jira_conf.yaml

To stop any changes to this file getting back into Git use:

# use --no-skip-worktree to undo
git update-index --skip-worktree jira_conf.yaml

Jira authentication

Create an API Token against your Atlassian account and store it somewhere safe: https://support.atlassian.com/atlassian-account/docs/manage-api-tokens-for-your-atlassian-account/

Edit jira_conf.yaml and set the url, user and token. These are the values specific to your Jira instance, example:

jira:
    url: https://your-domain.atlassian.net
    user: [email protected]
    token: my-api-token

Lookups (labels)

The team and category data is based on specific labels against a Jira issue (ticket). The label used to represent a team should be added to jira_conf.yaml under the "team" section and the work category under the "category" section. The lookup uses lowercase, so please ensure you add the key in lowercase (e.g. "team1", not "TEAM1). If tickets are spread over multiple projects (e.g. one team per project), the lowercase Jira project key can also be added in the team section. The tickets labels will first be used in the lookup, but if there is no match it will then look for the project key taken from the first part of the Jira issue key. So if the key is "XXX-123", it would look for "xxx" in the "team" section.

Edit jira_conf.yaml and add the relevant entries to the "team" and "category" sections. The first value (key) should be the label (or project key for teams) in lowercase and the second value, the value you want it to be displayed as, example:

team:
    team1-label: My team
    team2-label: Another team
    team-project-key: Your team
category:
    _unknown_: Unknown,firebrick
    project-label: Project,peru

To control the colours used in the bar chart for the graphs generated by report.py, set the colour using a comma after the category display name. Available colour options: https://matplotlib.org/stable/gallery/color/named_colors.html

The "_unknown_" category is used to provide the display text used when no matching category is found. If this is not found in the config file, the default text is "Unknown" and the colour is red.

To control the colours used in the pie charts generated by epics.py, add the status name, followed by the colour under a section called "status". The default colour for any status not listed is red.

status:
    To Do: silver
    In Progress: tab:blue
    Done: tab:green
    Rejected: tab:olive

Filters

These can also be added to jira_conf.yaml as a lookup, using a more memborable name as ids can be difficult to remember.

Edit jira_conf.yaml and add your filters to the "filter" sections. The first value (key) should be easy to remember name with no spaces and the second value the id of the filter setup in Jira, example:

filter:
    work_done: 12345
    team_tickets: 11111

Example filter JQL for tickets resolved in the last 6 months

(project = team-project-key OR project = ABC AND labels in (team1-label, team2-label)) AND status = Done AND resolutiondate >= startOfMonth(-6) AND resolutiondate < startOfMonth() AND type not in (Epic, Sub-task, Subtask) ORDER BY resolutiondate

Scripts

extract.py

Extracts Jira issue data based on a Jira Filter ID that can be supplied as a parameter or retrieved from jira_conf.yaml

Each search generates a .CSV file in the data folder, using the filter name as a subfolder. To improve performance we currently only request the following fields in the search:

• summary
• status
• created
• resolutiondate
• labels
• issuetype
• parent
• customfield_10014 (Epic Key)
• customfield_10016 (Story Point Estimate)
• customfield_10023 (Time in Status)
• customfield_10024 (Story Points)

Team and Category resolution is based on labels and driven by the lookup data in jira_conf.yaml. All keys should be lowercase to enable matching.

The generated .CSV file contains the following columns; "Key", "Summary", "Category", "Team", "Status", "Created", "Resolved", "Epic", "Epic ID", "Issue Type", "Story Points", "Lead Time", "To Do", "In Progress", "Lead Days", "Cycle Days". For Jira Issues that have not been resolved (Done), "Resolved", "Lead Time", "To Do", "In Progress", "Lead Days" and "Cycle Days" will not be populated. "Lead Time", "To Do", "In Progress" are in milliseconds. All elapsed date and time values include weekends.

The total number of issues extracted, and the name of the file created are output on successful execution. Any unresolved teams will be reported, along with the Jira Issue ID and unresolved categories will just be reported as "Unknown" in the .CSV

report.py

Uses the underlying code in extract.py to generate a .CSV using a Jira Filter ID, then pivots the data to create team graphs (.PNG) covering a monthly view of the number of issues and story points completed and a weekly breakdown of lead and cycle times. An Excel spreadsheet (.XLSX) containing this pivot data is also created. A pre-generated .CSV file created by extract.py can also be passed in, so it's possible to skip the initial data extraction phase. Any issues that don't have a status of "Done" are not included in the graph or spreadsheet. The team and category labels defined in jira_conf.yaml are used to drive the data displayed, though it is possible to specify which team data is displayed in the graph. On successful completion, the names of the files generated are output.

epics.py

Uses the underlying code in extract.py to generate a .CSV using a Jira Filter ID and generates two .PNG files containing pie charts for the tickets in each epic grouped by status. One set of charts for completed epics, epics that only contain Done & Rejected tickets, and the other for active epics. The radius of each pie chart indicates the ratio of tickets in the epic compared to the others. Active epics are ordered by the number of "To Do" tickets and Completed epics are ordered by the total number if tickets "Done". The colours used for the issue states are configured in the yaml file in the "status" section (see "Lookups" above). If the filter used includes tickets that don't have a parent epic, then these are grouped together under "NO EPIC".

Example filter JQL for all epics with child tickets in a project

team-managed projects:

project = team-project-key AND type not in (Epic, Subtask) AND parent is not EMPTY ORDER BY parent ASC

company-managed projects:

project = ABC AND type not in (Epic, Sub-task) AND "Epic Link" is not EMPTY ORDER BY "Epic Link" ASC

Run

Generates a .PNG, .XLSX and .CSV file for each team defined in jira_conf.yaml, using the first filter configured

py report.py

Generates a .PNG, .XLSX and .CSV file for each team defined in jira_conf.yaml

py report.py 12345
py report.py work_done

Generates a .PNG, .XLSX and .CSV file for the team(s) specified (these must exist in jira_conf.yaml)

py report.py 12345 "My team"
py report.py work_done "My team,Another team"

Generates a .PNG and .XLSX file for each team defined in jira_conf.yaml using an existing .CSV file (created by extract.py)

py report.py -f ".//data//Some Filter Name//2021-06//24_tickets.csv"

Generates a .PNG, .XLSX and .CSV file for the first filter defined in jira_conf.yaml and the team(s) specified (these must exist in jira_conf.yaml)

py report.py -t "My team,Another team"

Generates a .PNG and .XLSX file for the team(s) specified using an existing .CSV file (created by extract.py)

py report.py -f ".//data//Another Filter Name//2021-06//24_tickets.csv" "My team"
py report.py -f ".//data//Another Filter Name//2021-06//24_tickets.csv" "My team,Another team"

Generates a .CSV file for the first filter defined in jira_conf.yaml

py extract.py

Generates a .CSV file for the filter id or filter defined in jira_conf.yaml

py extract.py 12345
py extract.py work_done

Generates a .CSV and .PNG file(s) with the number of tickets in each epic grouped by status for the filter id or filter defined in jira_conf.yaml

py epics.py 12345
py epics.py team_tickets