Boosting JIRA Reports using PowerShell

PowerShell scripts are widely used by small or large enterprises. This scripting language has been provided by Microsoft and by using it IT professionals are empowered to securely manage various Windows functions and operations through the easy-to-use command-line shell. If we can enumerate all the features that are supported to be accessed and managed by PowerShell that would be a really long list. But, if we need to point out some, these would be the most interesting: access to full COM and WMI locally or remotely, full access to .NET classes, Access to APIs, access to a central repository for thousands of PowerShell modules, management for all Microsoft tools since 2012 like SQL server, Exchange, SharePoint etc.

⋮IWConnect is a partner of both to Microsoft and Atlassian. We are widely using Atlassian Jira, and part of our success is shown by reports generated by Jira. A Jira feature that we often use is “Log Work” option. That gives our team the opportunity to log their actual work effort directly in the tracking system, on one location, on an actual story they were working on, without using different web solutions. Although there are some – more or less- pricey Jira plugins that can give us all in one report for the time spent on a story by the developers, we like to have the freedom to generate our own reports and show them in a suitable structure. That’s where the power of Microsoft PowerShell comes along. Using the JiraPS, a Windows PowerShell module that eases the interaction with Atlassian JIRA via a REST API, we are able to build our own queries to select, group, filter or sum the selected data, and then to export it in the desired format. And that’s not all. We can automate the process with the creation of stories, changing their status, and all the interactions that are available through the web interface.

This is one of the scripts that we created. The challenge we had was creating a custom report that can sum the logged work from multiple sub-tasks into their parent story, and to export it grouped by date and work log author in a specified time range.

param (
    [string]$Names,
    [string]$project,
    [string]$server,
    [DateTime]$Date,
    [int]$days = 7
) 
 
if (Get-Module -ListAvailable -Name "JiraPS") {
    Write-Host "JiraPS Module exists"
} 
else {
    Install-Module JiraPS -Scope CurrentUser
}

try {
    Set-JiraConfigServer $server;

    $cred = Get-Credential 

    [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
    New-JiraSession -Credential $cred 

    $worklogAuthor = $Names -split ','
    $startDate = $Date
    $endDate = $startDate.AddDays($days)

    $list = for ($i = $startDate; $i -lt $endDate; $i = $i.AddDays(1)) {
        $datestr = get-date $i -UFormat "%Y\u002f%m\u002f%d";
        $datestrFormatted = get-date $i -UFormat "%d.%m.%Y";
					
        foreach ($wa in $worklogAuthor) {
            Get-JiraIssue -Fields "key,summary,timespent,parent,worklog"  -Query "project = $project AND worklogAuthor = $wa AND (worklogDate =  $datestr )" -First 1000 | Select-Object -Property key, summary,
            @{
                Name       = 'Author'
                Expression = { $wa } 
            }, @{
                Name       = 'Minutes'
                Expression = { ($_.worklog.worklogs | Select-Object -Property timeSpentSeconds, started -ExpandProperty updateauthor |  Where-Object {$_.name -eq $wa -AND (get-date $_.started).Date -eq (get-date $i).Date   } | measure-object -Property  timeSpentSeconds  -sum).Sum / 60  } 
            }, @{
                Name       = 'parent'
                Expression = { if (  [string]::IsNullOrEmpty($_.parent.key) ) { $_.key } else { $_.parent.key } }
            } | 
                Group-Object -Property parent | 
                Select-Object -Property @{
                Name       = 'Key'
                Expression = {$_.Name}
            }, 
            @{
                Name       = 'Name'
                Expression = { Get-JiraIssue $_.Name | Select-Object -ExpandProperty summary }
            },
            @{
                Name       = 'Date'
                Expression = {$datestrFormatted}
            },
            @{
                Name       = 'Author'
                Expression = { $_.Group.Author | select-object -first 1 }
            },
            @{
                Name       = 'TimeSpent'
                Expression = { ($_.Group | measure-object -Property  Minutes -sum).Sum  }
            },
            @{
                Name       = 'TimeSpentHours'
                Expression = { ($_.Group | measure-object -Property  Minutes -sum).Sum / 60 }
            }
        } 
    } 
    $list | Export-Csv -NoTypeInformation -Path "data.csv"
}
Catch {
    Write-Error "Processing failed  $_.Exception.Message"
    Break
}

Executing the script is pretty easy. We can provide the names of the team members we want to include in the report, the name of the server, the name of the project, start date and the number of days after the start date.

.\jira.ps1 -Names "john.smith,joe.doe,jane.doe" -server "https://jira/" -project "PROJ" -Date "2019/01/10" -days 7

And voilà! As simple as that with a little bit excel styling, we can get a perfect report for our needs.