Calculate Hours Spent for Project Version

This post will show how to calculate hours spent for a project version using PDI JIRA plugin. Of course there are plugins which give such reports in Jira, but PDI Jira plugin for Kettle can give more flexibility and provide this information into your own data store for analysis. You can download the latest PDI JIRA version here.

Transformations

First create IssueHours.ktr transformation whith following steps in it. This transformation is intended to extract hours spent on a single issue.

Step worklog should be configured to extract the issue information for an issue passed as a parameter to the transformation.

issue.key will be provided from the job running this transformation, so it must be configured as a transformation parameter. See how to set transformation parameters here. Configure the output fields to be extracted from JIRA service responses. This should be easy to do as there are preconfigured JSON Path expressions provided by the plugin and these expressions are context dependent, i.e. you'll get fields available in the response for the issue request. Of course you are not limited with the patterns and you can specify any other fields.

Following steps should sort records by author field and perform sum on timeSpent for each author. The summary will be time spent by authors for the issue. This See how to use sort step here and group step here .

There is text file set to collect output results. The reason temporary file is used for the output is it can accumulate the records in several transformation executions. This is not possible if you try to collect the resulting records in a transformation output because you'll lose the information on next issue calculation.

Transformation which searches  all issues for a project version need to be set up. It will provide information about the issues found to the hours calculating transformation.

 

Key for the issues found will be stored in the transformation output (step send - see how to use here), so the job can use them to execute next calculation step. Version tasks step is a PDI JIRA plugin step which extracts the issues from specified version.

Again providing project key and version label is left to be done from outside using parameters. When executed transformation should provide keys for the tasks found in the version to the next transformation which appears to be issue hours calculation step.

Finally a transformation which to summarize all results should be set up. This transformation will read all data collected for issues from the temporary text file. Actually this file will contain time spent for each task in the project version. Then summarize the hours for all tasks and calculate the time in hours, as the JIRA gives them in seconds. You're free to output the result to any store - table, file, etc. In this case result is simply traced in the job output.

The Job

The job will execute calculation steps in sequence. First the helper file for collecting time spent should be cleared and make sure no records left from previous executions. This file is used by issues transformation and because it accumulates the results it need to be clear.

Next step should perform the search all issues for the project version and execute issue calculation step for each issue found, so this step should be set to execute for each input row (see how). Summary step will take the contents of the temporary file where times spent for all issues found will be collected.

 

Run PDI 5.0 Under Ubuntu 14.04 LTS

The problem faced when starting PDI 5.0 either from binary distribution either from source build in Ubuntu is that it breaks down the JVM with dump like:

#
# A fatal error has been detected by the Java Runtime Environment:
#
#  SIGSEGV (0xb) at pc=0x00007fe30873d2a1, pid=4547, tid=140615535015680
#
# JRE version: Java(TM) SE Runtime Environment (8.0_31-b13) (build 1.8.0_31-b13)
# Java VM: Java HotSpot(TM) 64-Bit Server VM (25.31-b07 mixed mode linux-amd64 compressed oops)
# Problematic frame:
# C  [libsoup-2.4.so.1+0x6c2a1]  soup_session_feature_detach+0x11
#
# Failed to write core dump. Core dumps have been disabled. To enable core dumping, try "ulimit -c unlimited" before starting Java again
#
# If you would like to submit a bug report, please visit:
#   http://bugreport.java.com/bugreport/crash.jsp
# The crash happened outside the Java Virtual Machine in native code.
# See problematic frame for where to report the bug.
#

The problem is with the packed SWT library. This can be easily fixed if the swt.jar from working PDI 5.1 or 5.2 distributions is used.

Export JIRA Data Using Kettle REST Client

Kettle version 4.4 provides step which is able to connect remote REST services. As JIRA provides such interface it is fully possible to get data remotely using this step. Anyway some tricks should be applied in order to do that.

Mandatory Input Row

To start REST step communication with remote host an input row is needed. This was hard for me to find out as it is normal for such step to provide data input directly from its communication.

So if you need to input data from remote JIRA you'll have to provide at least one (in this case generated) input row.

Configure JIRA Connection

To connect remote JIRA server you'll need its root URL.

Add the root URL and don't forget to set the REST call statement. In this case calling search statement for project with key 'DOX'.

Set HTTP method to GET and Application type to JSON. Resulting JSON text will be passed to following steps under 'result' field name.

It is also necessary to provide authentication information if your JIRA instance interface is not publicly accessible.

Set your JIRA user name into 'Http Login' field and your password into 'Http Password' field.

Extract Data

As the REST service step provides JSON text input you can use 'Json Input' step provided in Kettle the same way as mentioned in Export JIRA Data Using Pentaho DI

Just provide JSON Path expressions for each field you need to extract. On this picture extracting task key, task summary and task status is shown.

PDI Jira

The idea behind PDI Jira is to be more specialized in using JIRA REST interface for integrating JIRA data using Pentaho DI although it is in too early phase and still needs JSON Input step for parsing result.