Importing JIRA Issues as Time Cockpit Tasks

Tuesday, April 30, 2013 by Simon Opelt


JIRA is an issue tracking system which can be extended for agile teams by using GreenHopper. It can be used for Scrum or Kanban and is adaptable to the details of your development process.

To be able to create time sheet entries assigned to JIRA tasks they first have to be imported into time cockpit. We are planning to have this feature out of the box as well as a matching signal tracker, but for now it can be added via an IronPython script or a time cockpit action. This article shows how to implement an action that imports JIRA tasks into time cockpit.

A similar article is available which shows you how to import work items if you are using TFS.

The full sample can be found at our github repository.

Changes to the Data Model

To create a link between the projects in JIRA and time cockpit and to store the additional information contained in a JIRA issue several changes to the data model are necessary.

The entity APP_Project has to be extended by an additional text property called JiraProject which will optionally (nullable) contain the Key of the corresponding JIRA project.

Because JIRA issues might contain lots of additional (customizable) fields we picked a reasonable set for this example. We extended APP_Task by the following properties (all using the default setting except where stated otherwise):

  • JiraUpdated (date time property) will contain the timestamp of the last change of the issue in JIRA which was already imported to the corresponding time cockpit task.
  • JiraType (text property) contains the human-readable issue type (e.g. Bug, Story).
  • JiraStatus (text property) contains the current human-readable status of the issue (e.g. Fixed).
  • JiraLink (text property, maximum length 500) contains a hyperlink to the issue in JIRA's web frontend.

To make additional use of the new properties (besides filtering or in interactive queries) the lists and forms would have to be changed accordingly. This would for example allow to add an UrlCell to the form/list which shows a hyperlink to quickly jump into JIRA when working in time cockpit.

Simple JIRA Queries in IronPython

The REST API provided by JIRA can be accessed using .NET's System.Web, System.Net and Json.NET which are all provided within time cockpit's scripting environment. The following sample contains two simple classes representing a JIRA issue (with all the fields relevant for this use-case) as well as the API access itself. It only uses basic authentication which requires a username and password which should be changed to OAuth for more critical production scenarios.

class Issue(object):
    def __init__(self, key=None, type=None, summary=None, link=None, status=None, updated=None, timeOriginalEstimate=None, subTaskKeys=None):
        self.Key = key
        self.Type = type
        self.Summary = summary
        self.Link = link
        self.Status = status
        self.Updated = updated
        self.TimeOriginalEstimate = timeOriginalEstimate
        self.SubTaskKeys = subTaskKeys

class Jira(object):
    def __init__(self, repository, username, password):
        from System import Uri
        self.repository = Uri(repository)
        self.username = username
        self.password = password
        self.requestedFields = [ "summary", "issuetype", "status", "updated", "timeoriginalestimate", "subtasks" ]

    def search(self, jql):
        from System.Web import HttpUtility
        from System.Net import HttpWebRequest
        from System.IO import StreamReader
        from Newtonsoft.Json import JsonTextReader
        from Newtonsoft.Json.Linq import JObject
        from System import Decimal
        import Newtonsoft.Json
        usernamepw = Convert.ToBase64String(Encoding.UTF8.GetBytes(String.Format("{0}:{1}", self.username, self.password)))

        fieldsparam = String.Join(",", self.requestedFields)
        requestUri = String.Format("{0}rest/api/2/search?jql={1}&fields={2}", self.repository.AbsoluteUri, HttpUtility.UrlEncode(jql), fieldsparam)
        Logger.Write(LogLevel.Verbose, "Jira.Search: {0}", requestUri)

        request = HttpWebRequest.Create(requestUri)
        request.ContentType = "application/json"

        request.Headers.Add("Authorization", "Basic " + usernamepw)

        request.Method = "GET"
        with request.GetResponse() as response:
            with StreamReader(response.GetResponseStream()) as sr:
                with JsonTextReader(sr) as jr:
                    result = JObject.Load(jr)
                    issues = result["issues"]

                    items = list()
                    for issue in issues:
                        item = Issue()
                        item.Key = Newtonsoft.Json.Linq.Extensions.Value[String](issue["key"])
                        fields = issue["fields"]
                        item.Updated = Newtonsoft.Json.Linq.Extensions.Value[DateTime](fields["updated"])

                        # transform seconds to hours
                        estimate = Newtonsoft.Json.Linq.Extensions.Value[System.Object](fields["timeoriginalestimate"])

                        if estimate is not None:
                            estimate = Newtonsoft.Json.Linq.Extensions.Value[Decimal](fields["timeoriginalestimate"])
                            estimate = estimate / (60.0 * 60.0)

                        item.TimeOriginalEstimate = estimate
                        status = fields["status"]
                        item.Status = Newtonsoft.Json.Linq.Extensions.Value[String](status["name"])
                        item.Summary = Newtonsoft.Json.Linq.Extensions.Value[String](fields["summary"])
                        type = fields["issuetype"]
                        item.Type = Newtonsoft.Json.Linq.Extensions.Value[String](type["name"])
                        item.Link = self.repository.ToString() + "browse/" + item.Key

                        subTasks = fields["subtasks"]
                        item.SubTaskKeys = System.Linq.Enumerable.Cast[JObject](subTasks).Select(lambda t: Newtonsoft.Json.Linq.Extensions.Value[String](t["key"])).ToArray[String]()

                    return items;

Note that the Jira class currently only supports a single method search accepting a JQL query and returning a list of matching Issue instances. It is implemented by first building a get request containing the query, setting the authentication, getting the result and parsing it through Json.NET.

One-Way Syncing the Data

The basic workflow of the core import functionality consists of the following steps:

  • Get all time cockpit projects which have a JiraProject name set.
  • For each found project:
    • Get the maximum last updated JIRA timestamp of the tasks related to the current project.
    • Query all JIRA issues for the current project that have changed since the last import.
    • Insert or update a task in time cockpit for each JIRA issue.

Of course there are some more details in the sample like transaction handling, exception handling, logging, batch-based selection of existing tasks and checks which avoid updating tasks if the corresponding issue only has changes in fields not relevant for us.

commit = True
timeDelta = 0.01

jira = Jira("", "...", "...")
jiraProjects = dc.Select("From P In Project Where :IsNullOrEmpty(P.JiraProject) = False Select P")

for jiraProject in jiraProjects:
        jiraName = jiraProject.JiraProject
        Logger.Write(LogLevel.Information, "JiraImport: Handling project '{0}'", jiraName)
        projectUuid = jiraProject.ProjectUuid

        lastUpdated = dc.SelectSingleWithParams({ "Query": "From T In Task Where T.Project = @ProjectUuid Select New With { .LastUpdated = Max(T.JiraUpdated) }", "@ProjectUuid": projectUuid }).LastUpdated
        if lastUpdated is None:
            lastUpdated = DateTime(1970, 1, 1)
        jqlAdditionalCondition = String.Format(" and updated >= '{0}' order by updated asc", lastUpdated.ToString("yyyy-MM-dd HH:mm", CultureInfo.InvariantCulture))
        jql = String.Format("project='{0}'{1}", jiraName, jqlAdditionalCondition)
        issues = i: i.Key)

        if issues.Any():
            query = String.Format("From T In Task.Include(*) Where T.Project = @ProjectUuid And T.Code In ({0}) Select T", String.Join(", ", issues.Select(lambda i: String.Format('"{0}"', i.Key)).ToArray()))
            tasks = dc.SelectWithParams({ "Query": query, "@ProjectUuid": projectUuid }).GroupBy(lambda t: t.Code).ToDictionary(lambda g: g.Key, lambda g: g.Single())

            newIssues = issues.Keys.Except(tasks.Keys).ToArray()
            updatedIssues = issues.Keys.Except(newIssues).ToArray()
            Logger.Write(LogLevel.Information, "JiraImport: {0} new issues, {1} updated issues for query {2}", newIssues.Length, updatedIssues.Length, jql)
            for key in newIssues:
                issue = issues[key]
                task = dc.CreateTask()
                task.APP_BudgetInHours = issue.TimeOriginalEstimate
                task.APP_Code = issue.Key
                task.APP_Project = jiraProject
                task.USR_JiraLink = issue.Link
                task.USR_JiraStatus = issue.Status
                task.USR_JiraType = issue.Type
                task.USR_JiraUpdated = issue.Updated
                task.APP_Description = issue.Summary
                Logger.Write(LogLevel.Information, "JiraImport: Adding task {0}", key)

            for key in updatedIssues:
                changed = False
                task = tasks[key]
                issue = issues[key]

                if task.APP_BudgetInHours <> issue.TimeOriginalEstimate:
                    if (task.APP_BudgetInHours is None and issue.TimeOriginalEstimate is not None) or (task.APP_BudgetInHours is not None and issue.TimeOriginalEstimate is None) or (abs(task.APP_BudgetInHours - issue.TimeOriginalEstimate) > timeDelta):
                        Logger.Write(LogLevel.Verbose, "JiraImport: Changed property for task {0}: {1}", key, "TimeOriginalEstimate")
                        task.APP_BudgetInHours = issue.TimeOriginalEstimate
                        changed = True
                if task.USR_JiraLink <> issue.Link:
                    Logger.Write(LogLevel.Verbose, "JiraImport: Changed property for task {0}: {1}", key, "Link")
                    task.USR_JiraLink = issue.Link
                    changed = True
                if task.USR_JiraStatus <> issue.Status:
                    Logger.Write(LogLevel.Verbose, "JiraImport: Changed property for task {0}: {1}", key, "Status")
                    task.USR_JiraStatus = issue.Status
                    changed = True
                if task.USR_JiraType <> issue.Type:
                    Logger.Write(LogLevel.Verbose, "JiraImport: Changed property for task {0}: {1}", key, "Type")
                    task.USR_JiraType = issue.Type
                    changed = True
                if task.USR_JiraUpdated <> issue.Updated:
                    Logger.Write(LogLevel.Verbose, "JiraImport: Changed property for task {0}: {1}", key, "Updated")
                    task.USR_JiraUpdated = issue.Updated
                    changed = True
                if task.APP_Description <> issue.Summary:
                    Logger.Write(LogLevel.Verbose, "JiraImport: Changed property for task {0}: {1}", key, "Summary")
                    task.APP_Description = issue.Summary
                    changed = True

                if changed:
                    Logger.Write(LogLevel.Information, "JiraImport: Updating task {0}", key)
                    Logger.Write(LogLevel.Information, "JiraImport: Skipping unchanged task {0}", key)

        if commit:
    except System.Exception, e:
        Logger.Write(LogLevel.Warning, "JiraImport: Exception while handling {0}: {1}\r\n{2}", jiraProject.JiraProject, e.Message, e.StackTrace)


The shown example can be put together to form a script which can be scheduled or a time cockpit action which can be interactively executed. This ensures that your JIRA issues are usually just there when you need to book times on them or you can trigger an import for the few corner cases where you need an issue to be available earlier than the next import.

There are lots of things that can be improved or adapted for production use (e.g. most installations currently only return 50 items per query which means that the query would have to be run in a loop until no new items are discovered) but this article should give an overview of what is possible.

comments powered by Disqus