Tapping Into Jira’s Hidden Treasure
Jira collects a wealth of information that can help provide insights into your teams/organizations and substantially accelerate your process improvement and performance. Unfortunately much of this information is hidden or difficult to retrieve in a usable form. The Jira Search tool/JQL provides you some ability to get at the data, but it is still quite limited and cumbersome to use. Exporting queries is clunky and in most cases you are restricted to exporting no more than 1000 rows.
In addition to all the aforementioned limitations neither the reporting nor the query language let you get at the history detail which in my opinion is the most valuable data in the system. The only way to get that information in a useful form is via the Jira APIs which obviously require some form of programming knowledge and skill.
Hope springs eternal, but I don’t have an eternity to wait so I decided to create a tool to do what I needed. The premise is very simple, allow any Jira user to easily pull the information they need out of Jira, with no programming required, into a spreadsheet where they can manipulate it anyway they like. The name of the tool is EJQT, Enhanced Jira Query Tool, because it essentially picks up where the standard Jira Search(query) tool leaves off.
The Enhanced Jira Query Tool provides an easy to use mechanism for retrieving data from Jira into Google Sheets. It utilizes existing JQL queries to retrieve the data without the 1000 row export limit. It allows you to configure what Jira fields are to be included, how to format the retrieved fields, what names to use for the column headers, and how the underlying workflows will be mapped to cycle and lead time.
Now, before you ask…I used Google Sheets rather than Excel because GS is free, requires no licensing/installation of any kind, and runs in just about any browser which means anyone can use it. I will be the first one to say that GS is a far cry from Excel, but the focus here is on getting the data out and making the tool accessible to everyone. You always have the option of importing the data into Excel or saving the GS as an Excel workbook if you need more analytic horsepower. It will save the the structure, formulas, formatting, and data, but the underlying logic written in Google Apps Script will not come along for the ride.
In addition to the fields that are normally available through the native Jira Search tool EJQT provides access to data elements that are available only via the Jira APIs. Examples include things like:
- Comment count & authors
- Additional data properties such as user display name and email address for any user based fields such as Creator, Reporter, or Assignee
- Issue Sprint properties like start date, end date, and count
- Linked issues and counts
The tool can access standard Jira fields as well as any custom fields you may have created in your Jira instance. As an example the Jira instance I have been working with has over 570 fields that are accessible through the tool.
Finally EJQT provides a set of special derived fields that are not available directly in Jira or the APIs. They include fields like:
- Cycle/Lead: time, start time, wait time and work time
- Unplanned work
- Issue blocked time
- Sprint assignment date
- Status times(time in status)
- Status transition dates
The tool can be configured to work with up to 4 different workflows per query and map those workflows to an unlimited number of issue types. You can store up to 100 different queries and their respective configurations. Once a query is configured you can execute it at will through the custom JQT Options menu. Below are some sample screen shots:
I think this is a tool just about every Jira user can benefit from so I am making EJQT freely available to anyone who would like to use it along with a user’s guide that will provide all the information necessary to setup, configure, and use the tool. The EJQT and its user’s guide are available on the Agile Creatives website Tools page. Here is a demo video of the tool that makes it very easy to see how it works. I have also developed a training course for the tool on Udemy.
In addition I have developed two different reporting tools: Lean Analytics Dashboard and Lean Scrum Analytics Dashboard that utilizes the same query engine. The dashboard tools transforms the raw data into concise chart based metrics that are rendered in configurable dashboards allowing you to focus on the areas that are most important to you and your organization. There are over 80 Lean and Agile metrics out of the box with the ability to configure an unlimited number of additional metrics. These tools are also available on the Agile Creatives website Tools page. Below are a few screen shots to provide a glimpse:
I hope you find the tool useful. I know it has made aspects of my work much easier. I would love to hear any feedback you have so please feel free to post away!
