Versions Compared

Old Version 2

changes.mady.by.user Jeff Turner

Saved on

compared with

New Version 3

changes.mady.by.user Jeff Turner

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Excerpt Include
JIRA REST-based Reporting Scripts
JIRA REST-based Reporting Scripts

Report Synopsis

Excerpt

 Given a JIRA project name, a start date and and end date, find total counts of issues completed before, on or after the due date, per priority:

 TotalUnfinishedFinished On DueFinished Before DueFinished After Due
Major, with due date     
Major, without due date  ---
Critical, with due date     
Critical, without due date  ---

Implementation

In Ruby, using the jira-ruby gem.

First we set up a $client object, using HTTP Basic authentication;

Code Block
languageruby
require 'jira'
require 'parallel'

HOST='https://REDACTED.atlassian.net'
$options = {
  :site => HOST,
  :context_path => '',
  :username => 'myusername',
  :password => %q{REDACTED},
  :auth_type => :basic
}

$client = JIRA::Client.new($options)

Next, we fetch the issues we're interested in:

...

issues = $client.Issue.jql("project=UX and updated>='2015-10-01' AND updated<='2015-10-27'", max_results:1000) { |i| i.fetch; i }

Now for the interesting part. Issues with a due date will have a resolutiondate field, which we can parse wtih strptime:

...

rdate = issues.find { |i| i.resolutiondate  }.resolutiondate
=> "2015-10-26T09:23:07.000-0700"
rdate = DateTime.strptime(rdate, '%Y-%m-%dT%H:%M:%S.%L%z')
=> #<DateTime: 2015-10-26T09:23:07-07:00 ((2457322j,58987s,0n),-25200s,2299161j)>
rdate = rdate.to_date                # Discard time portion
=> #<Date: 2015-10-26 ((2457322j,0s,0n),+0s,2299161j)>

We will also have a duedate, which we can parse similarly:

Code Block
languageruby
ddate = issues.find { |i| i.duedate  }.duedate
=> "2015-11-05"
Date.strptime(ddate, "%Y-%m-%d")
=> #<Date: 2015-11-05 ((2457332j,0s,0n),+0s,2299161j)>

and a priority, which is actually an object, so we'll just use the name part of it:

...

[14] pry(main)> ddate = issues.find { |i| i.priority  }.priority.name
=> "Critical"

The script achieving this is found in Bitbucket at https://bitbucket.org/redradish/jira-ruby-reports/src/master/overdue_by_priority/. Sample use:

Code Block
jturner@jturner-desktop ~/src/bitbucket.org/redradish/jira-ruby-reports/overdue_by_priority $ bundle exec ./jira_overdue_by_priority_report.rb "project=UX and created>='2015-10-01' AND created<='2015-10-27'"
--------------------------------------------------
[[nil,
  "Total",
  "Unfinished",
  "Finished On Due",
  "Finished Before Due",
  "Finished After Due",
  "Finished, no due date"],
 ["Minor, without due date", 44, 36, 0, 0, 0, 8],
 ["Blocker, without due date", 5, 0, 0, 0, 0, 5],
 ["Critical, without due date", 4, 1, 0, 0, 0, 3],
 ["Major, without due date", 131, 46, 0, 0, 0, 85],
 ["Minor, with due date", 1, 1, 0, 0, 0, 0],
 ["Major, with due date", 2, 2, 0, 0, 0, 0],
 ["Blocker, with due date", 1, 1, 0, 0, 0, 0]]
--------------------------------------------------
|                            | Total | Unfinished | Finished On Due | Finished Before Due | Finished After Due | Finished, no due date |
| Minor, without due date    | 44    | 36         | 0               | 0                   | 0                  | 8                     |
| Blocker, without due date  | 5     | 0          | 0               | 0                   | 0                  | 5                     |
| Critical, without due date | 4     | 1          | 0               | 0                   | 0                  | 3                     |
| Major, without due date    | 131   | 46         | 0               | 0                   | 0                  | 85                    |
| Minor, with due date       | 1     | 1          | 0               | 0                   | 0                  | 0                     |
| Major, with due date       | 2     | 2          | 0               | 0                   | 0                  | 0                     |
| Blocker, with due date     | 1     | 1          | 0               | 0                   | 0                  | 0                     |
--------------------------------------------------
<table border=1>
<tr><th>    </th><th>Total</th><th>Unfinished</th><th>Finished On Due</th><th>Finished Before Due</th><th>Finished After Due</th><th>Finished, no due date</th></tr>
<tr><th>Minor, without due date</th><td>44</td><td>36</td><td>0</td><td>0</td><td>0</td><td>8</td></tr>
<tr><th>Blocker, without due date</th><td>5</td><td>0</td><td>0</td><td>0</td><td>0</td><td>5</td></tr>
<tr><th>Critical, without due date</th><td>4</td><td>1</td><td>0</td><td>0</td><td>0</td><td>3</td></tr>
<tr><th>Major, without due date</th><td>131</td><td>46</td><td>0</td><td>0</td><td>0</td><td>85</td></tr>
<tr><th>Minor, with due date</th><td>1</td><td>1</td><td>0</td><td>0</td><td>0</td><td>0</td></tr>
<tr><th>Major, with due date</th><td>2</td><td>2</td><td>0</td><td>0</td><td>0</td><td>0</td></tr>
<tr><th>Blocker, with due date</th><td>1</td><td>1</td><td>0</td><td>0</td><td>0</td><td>0</td></tr></table>

Implementation Walkthrough

In Ruby, using the jira-ruby gem.

First we set up a $client object, using HTTP Basic authentication;

Code Block
languageruby
require 'jira'
require 'parallel'

HOST='https://REDACTED.atlassian.net'
$options = {
  :site => HOST,
  :context_path => '',
  :username => 'myusername',
  :password => %q{REDACTED},
  :auth_type => :basic
}

$client = JIRA::Client.new($options)

Next, we fetch the issues we're interested in:

Code Block
ruby
ruby
issues = $client.Issue.jql("project=UX and updated>='2015-10-01' AND updated<='2015-10-27'", max_results:1000) { |i| i.fetch; i }

Now for the interesting part. Issues with a due date will have a resolutiondate field, which we can parse wtih strptime:

Code Block
ruby
ruby
rdate = issues.find { |i| i.resolutiondate  }.resolutiondate
=> "2015-10-26T09:23:07.000-0700"
rdate = DateTime.strptime(rdate, '%Y-%m-%dT%H:%M:%S.%L%z')
=> #<DateTime: 2015-10-26T09:23:07-07:00 ((2457322j,58987s,0n),-25200s,2299161j)>
rdate = rdate.to_date                # Discard time portion
=> #<Date: 2015-10-26 ((2457322j,0s,0n),+0s,2299161j)>

We will also have a duedate, which we can parse similarly:

Code Block
languageruby
ddate = issues.find { |i| i.duedate  }.duedate
=> "2015-11-05"
Date.strptime(ddate, "%Y-%m-%d")
=> #<Date: 2015-11-05 ((2457332j,0s,0n),+0s,2299161j)>

and a priority, which is actually an object, so we'll just use the name part of it:

Code Block
ruby
ruby
[14] pry(main)> ddate = issues.find { |i| i.priority  }.priority.name
=> "Critical"

Now we need to:

  • group issues by priority
    • for each priority's group, group again by classification:
      • if there is no resolution date, classify as "Unfinished"
      • if there is a resolution date, but no due date, classify "Finished, no due date"
      • If the resolution date and due date match, classify as "On Due"
      • If the resolution date is earlier than due date, classify as "Before Due"
      • If the resolution date is after the due date, classify as "After Due"

The Ruby Enumerable module's group_by method does the group-into-buckets job nicely, giving us a hash-of-hashes data structure.

Code Block
ruby
ruby
data = issues.group_by { |i|
                i.priority.name + ", " + (i.duedate ? "with" : "without") + " due date" }
        .inject({}) { |h, (priority, issues)|
                h[priority] = issues.group_by { |i|
                        resdate = i.resolutiondate && DateTime.strptime(i.resolutiondate, '%Y-%m-%dT%H:%M:%S.%L%z').to_date
                        duedate = i.duedate && Date.strptime(i.duedate, "%Y-%m-%d")
                        if !resdate then "Unfinished"
                                elsif !duedate then "Finished, no due date"
                                elsif resdate == duedate then "Finished On Due"
                                elsif resdate < duedate then "Finished Before Due"
                                else "Finished After Due"
                        end
                }
                h[priority]["Total"] = issues
                h
         }

data.keys   # Show our top-level groupings (this will be rows)
=> ["Critical, without due date", "Major, without due date", "Minor, without due date", "Major, with due date", "Blocker, without due date"]
cols = data.collect { |(k,v)| v.keys  }.flatten.uniq  # Identify unique columns.
=> ["Unfinished", "Finished, no due date"]

Reporting

We now have our data in a nested-hash data structure, and want to output it in tabular format.

First, we iterate over rows and columns and count the issues, giving us a simple 2d structure:

Code Block
ruby
ruby
cols = ["Total", "Unfinished", "Finished On Due", "Finished Before Due", "Finished After Due", "Finished, no due date"]
result = [[nil] + cols] # First row is a list of columns, starting with a nil
# Add rows, consisting of an array beginning with 'rowname', followed by the number of issues, or zero
result += data.collect { |(priority, issues_by_finishedstatus)|
        [priority] + cols.collect { |col|
                 issues_by_finishedstatus[col] ? issues_by_finishedstatus[col].size : 0 }
        }
=> pp result
[[nil,
  "Total",
  "Unfinished",
  "Finished On Due",
  "Finished Before Due",
  "Finished After Due",
  "Finished, no due date"],
 ["Minor, without due date", 44, 36, 0, 0, 0, 8],
 ["Blocker, without due date", 5, 0, 0, 0, 0, 5],
 ["Critical, without due date", 4, 1, 0, 0, 0, 3],
 ["Major, without due date", 131, 46, 0, 0, 0, 85],
 ["Minor, with due date", 1, 1, 0, 0, 0, 0],
 ["Major, with due date", 2, 2, 0, 0, 0, 0],
 ["Blocker, with due date", 1, 1, 0, 0, 0, 0]]

Displaying our array-of-arrays properly indented can be done with:

Code Block
ruby
ruby
puts "| " + result.collect { |r| r.collect.with_index { |c,i|
                colwidth = (i==0 ? 26 : result[0][i].size)
                "%-#{colwidth}s" % c }.join(" | ")
        }.join(" |\n| ") + " |"
=>
|                            | Total | Unfinished | Finished On Due | Finished Before Due | Finished After Due | Finished, no due date |
| Minor, without due date    | 44    | 36         | 0               | 0

 

Now we need to:

  • group issues by priority
    • for each priority's group, group again by classification:
      • if there is no resolution date, classify as "Unfinished"
      • if there is a resolution date, but no due date, classify "Finished, no due date"
      • If the resolution date and due date match, classify as "On Due"
      • If the resolution date is earlier than due date, classify as "Before Due"
      • If the resolution date is after the due date, classify as "After Due"

The Ruby Enumerable module's group_by method does the group-into-buckets job nicely, giving us a hash-of-hashes data structure.

Code Block
rubyruby
data = issues.group_by { |i|
                i.priority.name + ", " + (i.duedate ? "with" : "without") + " due date" }
        .inject({}) { |h, (k, v)|
                h[k] = v.group_by { |i|
 0                  | 8        resdate = i.resolutiondate && DateTime.strptime(i.resolutiondate, '%Y-%m-%dT%H:%M:%S.%L%z').to_date
        |
| Blocker, without due date  | 5     | 0        duedate = i.duedate && Date.strptime(i.duedate, "%Y-%m-%d")
| 0               | 0          if !resdate then "Unfinished"
      | 0                  | 5      elsif !duedate then "Finished, no due date"
         |
| Critical, without due date | 4     | 1          | elsif0   resdate == duedate then "On Due"
       | 0                   | 0    elsif resdate < duedate then "Before Due"
        | 3                     |
| Major, elsewithout "After Due"
due date    | 131   | 46         | 0     end
          | 0     }
         h  }

data.keys   #| Show0 our top-level groupings (this will be rows)
=> ["Critical, without due date", "Major, without due date", "Minor, without due| date", "Major, with due date", "Blocker, without due date"]
cols = data.collect { |(k,v)| v.keys  }.flatten.uniq  # Identify unique columns.
=> ["Unfinished", "Finished, no due date"]

Then for reporting. I have yet to find a nice easy way of formatting a 2d array of numbers with arbitrary column numbers. The pretty print pp module might be good enough:

Code Block
rubyruby
result = [[nil] + cols] # First row is a list of columns, starting with a nil
# Add rows, consisting of an array beginning with 'rowname', followed by the number of issues, or zero
result += data.collect { |(rowname,v)|
85                    |
| Minor, with due date       | 1     | 1          | 0               | 0               [rowname] + cols.collect { |col| v[col]0 ? v[col].size : 0 }
        }
require 'pp'
pp result
[[nil, "Unfinished", "Finished, no due date"],
 ["Critical, without due date", 1, 3],
 ["Major, without due date", 133, 143],
 ["Minor, without due date", 27, 10],
 ["     | 0                     |
| Major, with due date",   3, 0],
 ["Blocker, without due| date",2 0, 4]]

or some primitive HTML output:

Code Block
rubyruby
puts "<table border=1>\n" + 
 | 2        result.map.with_index { |r, i| "<tr>" + 
 0               | 0        r.map.with_index { |c, j|
        | 0               el = (i==0 || j==0 ? "th" : "td")
                 |
| Blocker, with due date   "<#{el}>" + (c| ?1 c.to_s : "\t") + "</#{el}>" }.join + 
 | 1          | 0                "</tr>"
| 0                   | 0       }.join("\n") +
      "</table>"
=>
<table border=1>
<tr><th>    </th><th>Unfinished</th><th>Finished, no due date</th></tr>
<tr><th>Critical, without due date</th><td>1</td><td>3</td></tr>
<tr><th>Major, without due date</th><td>133</td><td>143</td></tr>
<tr><th>Minor, without due date</th><td>27</td><td>10</td></tr>
<tr><th>Major, with due date</th><td>3</td><td>0</td></tr>
<tr><th>Blocker, without due date</th><td>0</td><td>4</td></tr></table>| 0                     |

The script in Bitbucket also emits HTML, which renders as:

 TotalUnfinishedFinished On DueFinished , no due dateBefore DueFinished After DueFinished, no due date
Minor, without due date44360008
Blocker, without due date500005
Critical, without due date410003
Major, without due date1331314600085143
Minor, without with due date2711000010
Major, with due date2200030
Blocker, without with due date1100004