DevOps

0.2.0

statistics.ps1

<#
.SYNOPSIS
Get statistics on Bookings, UserStories, StoryPoints and Bugs.
 
.DESCRIPTION
Timeframe of statistic can be either the whole project, a given timerange (use from and to parameters) or an iteration (use iteration parameter).
 
Stories and Bugs are treated as planned when they are assigned to the iteration including 2 workdays after the start of the iteration
    having a status of 'New' or 'Active'. XXX: consider already resolved/closed between start and this date
For planned items, the Storypoints are counted - meaning for planned Bugs storypoints are counted, for Bugs which are added during the
iteration (unplanned) the StoryPoints are not considered.
 
Stories and Bugs are treated as done (status 'Resolved' or 'Closed') when they are assigend to the iteration at the enddate of the iteration.
 
.PARAMETER tcProjects
    The name of the Timecockpit projects to get the data for.
 
.PARAMETER devOpsOrganization
    The DevOps organization the project is in. If omited (which is the default), then only booking statistics are created.
 
.PARAMETER devOpsProject
    The name of the devops project to get the data for.
 
.PARAMETER iteration
    Name of iteration to get data for. If Iteration is passed, the from and to date is taken from the iteration.
    The following macro names can be used for referencing iterations relative: @previous, @current, @next
 
.PARAMETER from
    Optional Parameter to consider costs only from the given date on. If iteration is set, the date is taken from the iteration.
    If neither from nor iteration are set, no date-from filter is considered and all past records are taken.
 
.PARAMETER to
    Optional Parameter to consider costs only up to the given date. If iteration is set, the date is taken from the iteration.
    If neither from nor iteration are set, no date-to filter is considered and all past records are taken.
 
.PARAMETER tolerance
    Tolerance of how many workdays into the sprint added items are still considered as planed. Is 0 by default.
    The holiday calendar is used from TimeCockpit to determine what is a workday or not.
     
.PARAMETER tcDBCredential
    Credential for Timecockpit DB. If not given they will be requested and stored, if used, in CredentialManager.
 
.PARAMETER devOpsCredential
    Credential for DevOps. If not given they will be requested and stored, if used, in CredentialManager.
     
.PARAMETER useCredentialsManager
    If credentialmanager should be used. Default is $True.
     
.EXAMPLE
    .\statistics.ps1 -devOps Rolsped -devOpsProject TruckerApp -iteration 2019.48-49
     
.EXAMPLE
    Uses new credentials for DevOps and stores those in the credential manager
     
    .\statistics.ps1 -devOps Rolsped -devOpsProject TruckerApp -iteration 2019.48-49 -devOpsCredential Get-Credential -useCredentialsManager $True
#>
[CmdletBinding(SupportsShouldProcess=$true)]
param(
   [string[]] $tcProjects,
   [string] $devOpsOrganization,
   [string] $devOpsProject,
   [string] $iteration,
   [DateTime] $from,
   [DateTime] $to,
   [int] $tolerance = 0,
   [PSCredential] $tcDBCredential,
   [PSCredential] $devOpsCredential,
   [Boolean] $useCredentialsManager = $True
)

function Get-PAT
{
    Param(
       [Parameter(Mandatory=$true)]
       [string] $devOpsOrganization,
       [PSCredential]$Credential,
       [Boolean]$UseCredentialsManager = $False
    ) #end param

    # check module prerequisites
    if($UseCredentialsManager)
    {
        $module = Get-Module -ListAvailable -Name "CredentialsManager";
        if (!$module) { throw "Module 'CredentialsManager' needed. Please install executing 'Install-Module -Name CredentialsManager' as Administrator."; }
    }
    
    if($UseCredentialsManager -and $Credential -eq $Null)
    {
        $Credential = Read-Credential -ListAvailable | Where { $_.Environment -eq "DevOps.${devOpsOrganization}" }
    }
    if(!$Credential) { $Credential = Get-Credential -Message "Please enter Credentials for ${devOpsOrganization} DevOps."; }
    if($UseCredentialsManager) 
    { 
        Write-Credential "DevOps.${devOpsOrganization}" -Credential $Credential; 
    }

    return $Credential;
}

function Get-ItemsByIds([int[]] $ids, [datetime] $asOf)
{
    if($ids.Count -eq 0) { return @() }

    $allItems= @()
    for($i = 0; $i -lt $ids.Count;$i += 200)
    {
        $idsList = $ids[$i..($i+199)] -join ","

        $devOpsGetCall = "${devOpsUrl}/_apis/wit/workitems?ids=${idsList}&fields=System.Id,System.State,Microsoft.VSTS.Scheduling.StoryPoints&asOf=$($asOf.ToString('yyyy-MM-dd HH:mm'))&api-version=5.1";
        $items = Invoke-RestMethod -Uri $devOpsGetCall -Method Get -ContentType "application/json" -Headers @{Authorization=("Basic {0}" -f $base64AuthInfo)}
        $allItems += $items
    }

    return $allItems
}

function Get-Items([string] $wiql, [datetime] $asOf)
{
    $devOpsCall = "${devOpsUrl}/_apis/wit/wiql?api-version=5.1"
    $content = ConvertTo-JSON( @{ query=$wiql; });

    $returned = Invoke-RestMethod -Uri $devOpsCall -Method Post -Body $content -ContentType "application/json" -Headers @{Authorization=("Basic {0}" -f $base64AuthInfo)}

    $ids = $returned.workitems | Select-Object -ExpandProperty Id
    if($ids -eq $Null) { return @(); }
    $items = Get-ItemsByIds $ids $asOf

    return $items
}

function Get-StoryPoints([System.Object] $items)
{
    $storyPoints = 0
    foreach($item in $items.value)
    {
        $storyPoints += $item.fields."Microsoft.VSTS.Scheduling.StoryPoints";
    }
    return $storyPoints
}

function Substract-List([int[]] $listToSubstractFrom, [int[]] $toSubstract)
{
    $list = [System.Collections.ArrayList]$listToSubstractFrom.Clone()
    foreach($id in $toSubstract)
    {
        $list.Remove($id)
    }
    return $list
}

# check prerequisites
$tcModule = Get-Module -ListAvailable -Name "TimeCockpit";
if (!$tcModule) { throw "Module 'TimeCockpit' needed. Please install executing 'Install-Module -Name TimeCockpit' as Administrator."; }
Write-Information "Using TimeCockpit Module v$($tcModule.Version)."

# validate parameters
if($devOpsOrganization -and !$devOpsProject)
{
    throw "When a devOpsOrganization is configured, then also a devOpsProject needs to be set."
}
if(!$tcProjects -or $tcProjects.Count -eq 0)
{
    throw "tcProjects requires to be set to at least one project."
}

$tcProjectsResolved = $tcProjects | foreach { "'" + $_ + "'" }

if($devOpsOrganization)
{
    $devOpsUrl = "https://dev.azure.com/${devOpsOrganization}"

    # Authentication Token for DevOps
    $devOpsCredential = Get-PAT $devOpsOrganization $devOpsCredential $useCredentialsManager
    $base64AuthInfo = [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes(("{0}:{1}" -f $devOpsCredential.UserName, $devOpsCredential.GetNetworkCredential().password)))

    $iterationId = $Null;
    $iterationObj = $Null;

    # get iterations the fetch start and enddate
    if($iteration)
    {
        $callUrl = "${devOpsUrl}/${devOpsProject}/_apis/work/teamsettings/iterations?api-version=5.1"
        $response = Invoke-RestMethod -Uri $callUrl -Method Get -ContentType "application/json" -Headers @{Authorization=("Basic {0}" -f $base64AuthInfo)}

        $iterations = $response.value;

        $from = [DateTime]::MinValue;

        for($i = 0;$i -lt $iterations.Count; $i++)
        {
            $existingIteration = $iterations[$i];
            
            if($iteration[0] -eq "@" -and $existingIteration.attributes.timeframe -eq "current")
            {
                if($iteration -eq "@current")
                {
                    $iterationObj = $existingIteration;
                    break;
                }
                if($iteration -eq "@previous" -and $i -gt 0)
                {
                    $iterationObj = $iterations[$i - 1];
                    break;
                }
                if($iteration -eq "@next" -and $i -lt $iterations.Count)
                {
                    $iterationObj = $iterations[$i + 1];
                    break;
                }
            }
            if($existingIteration.name -eq $iteration) 
            {
                $iterationObj = $existingIteration;
                break;
            }
        }

        if(!$iterationObj) 
        {
            Write-Output "Iteration ${iteration} not found in project ${devOpsProject}.";
            Return; 
        }
        $from = ([datetime]$iterationObj.attributes.startDate);
        $to = ([datetime]$iterationObj.attributes.finishDate).AddDays(1).AddSeconds(-1);
        $iterationId  = $iterationObj.id
        $iteration = $iterationObj.name
    }
}
if(!$iteration)
{
    if(-not $from) 
    { 
        $from = [DateTime]::MinValue 
    }
    else
    {
        $from = $from.Date
    }
    
    if(-not $to) 
    {
        $to = [DateTime]::MaxValue
    }
    else 
    {
        # set time of to to end of day
        $to = $to.Date.AddDays(1).AddSeconds(-1)
    }
}

###################################################
#### Effort Statistics ############################
###################################################

$plannedEffort = @{}
if($iteration)
{
    # determine workdays in sprint iteration
    $workdaysInDuration = [math]::Round(($to - $from).TotalDays);
    for ($d=$from;$d -le $to;$d=$d.AddDays(1))
    {
        if ($d.DayOfWeek -match "Sunday|Saturday") { $workdaysInDuration--; }
    }

    # fetch capacity data of the given sprint
    $callUrl = "${devOpsUrl}/${devOpsProject}/_apis/work/teamsettings/iterations/${iterationId}/capacities?api-version=5.1"
    $response = Invoke-RestMethod -Uri $callUrl -Method Get -ContentType "application/json" -Headers @{Authorization=("Basic {0}" -f $base64AuthInfo)}
    foreach($line in $response.value)
    {
        $daysOff = 0
        $line.daysOff | Foreach-Object { $daysOff += (([datetime]$_.end).AddDays(1) - [datetime]$_.start).Days }
        $capacityPerDay = ($line.activities | Measure-Object -Property capacityPerDay -Sum).Sum;

        $plannedEffort[$line.teamMember.uniqueName] = [pscustomobject][ordered]@{ DaysOff=$daysOff; CapacityPerDay=$capacityPerDay; }
    }
}

# Query by Colleague

$query = @"
select
    Customer.APP_Code as 'Customer',
    Project.APP_Code as 'Project',
    UserDetail.APP_Firstname as 'Firstname',
    UserDetail.APP_Username as 'Username',
    sum(cast(DATEDIFF(minute, Timesheet.APP_BeginTime, Timesheet.APP_EndTime) as float)) / 60 as 'Effort'
from edhrqnfxua.APP_Timesheet as Timesheet
    join edhrqnfxua.APP_Project as Project on Project.APP_ProjectUuid = Timesheet.Relation_APP_Project
    join edhrqnfxua.APP_Customer as Customer on Customer.APP_CustomerUuid = Project.Relation_APP_Customer
    join edhrqnfxua.APP_UserDetail as UserDetail on UserDetail.APP_UserDetailUuid = Timesheet.Relation_APP_UserDetail
where
    Project.APP_Code IN ($($tcProjectsResolved -join ','))
    and Timesheet.APP_BeginTime > '$($from.ToString('yyyy-MM-dd'))' AND Timesheet.APP_EndTime <= '$($to.ToString('yyyy-MM-dd HH:mm'))'
group by Customer.APP_Code, Project.APP_Code, UserDetail.APP_Firstname, UserDetail.APP_Username, UserDetail.APP_UserDetailUuid
order by UserDetail.APP_Firstname
"@

$items = (Invoke-TCDBSqlCmd -Query $query)
$totalHours = $items | Measure-Object -property Effort -Sum;
$sumSprintCapacity = 0

Write-Output "## By Colleague ##" 
foreach($item in $items) 
{
    $capacity = $plannedEffort[$item.Username];
    $sprintCapacity = ($workdaysInDuration - $capacity.DaysOff) * $capacity.CapacityPerDay
    $sumSprintCapacity += $sprintCapacity
    if($sprintCapacity -gt 0)
    {
        Write-Output "$($item.Firstname) $($item.Effort.ToString('0.##'))h vs. $($sprintCapacity.ToString('0.##'))h ($([Math]::Round($item.Effort / $sprintCapacity * 100, 1))%)";
    }
    else
    {
        Write-Output "$($item.Firstname) $($item.Effort.ToString('0.##'))h ($([Math]::Round($item.Effort / $totalHours.Sum * 100, 1))%)";
    }
}
if($sprintCapacity -gt 0)
{
    Write-Output "Total $($totalHours.Sum.ToString('0.##'))h vs. $($sumSprintCapacity)h ($([Math]::Round($totalHours.Sum / $sumSprintCapacity * 100, 1))%)";
}
else
{
    Write-Output "Total $($totalHours.Sum.ToString('0.##'))h";
}
Write-Output "" 

# Query by type of work

$query = @"
    select
        Customer.APP_Code as 'CustomerCode',
        Project.APP_Code as 'ProjectCode',
        CASE
            WHEN (CHARINDEX('-', Task.APP_Code) > 0)
                THEN 'Functionality'
            WHEN ISNUMERIC(Task.APP_Code) = 1
                THEN 'Functionality'
                ELSE Task.APP_Code
            END AS 'TaskCode',
        ISNULL(sum(cast(DATEDIFF(minute, Timesheet.APP_BeginTime, Timesheet.APP_EndTime) as float)) / 60, 0) as 'Effort'
    from edhrqnfxua.APP_Timesheet as Timesheet
        join edhrqnfxua.APP_Task as Task on Task.APP_TaskUuid = Timesheet.Relation_APP_Task
        join edhrqnfxua.APP_Project as Project on Project.APP_ProjectUuid = Timesheet.Relation_APP_Project
        join edhrqnfxua.APP_Customer as Customer on Customer.APP_CustomerUuid = Project.Relation_APP_Customer
    where
        Project.APP_Code IN ($($tcProjectsResolved -join ','))
        and Timesheet.APP_BeginTime > '$($from.ToString('yyyy-MM-dd'))' AND Timesheet.APP_EndTime <= '$($to.ToString('yyyy-MM-dd HH:mm'))'
    group by Customer.APP_Code, Project.APP_Code,
        CASE
            WHEN (CHARINDEX('-', Task.APP_Code) > 0)
                THEN 'Functionality'
            WHEN ISNUMERIC(Task.APP_Code) = 1
                THEN 'Functionality'
                ELSE Task.APP_Code
            END
"@

$items = (Invoke-TCDBSqlCmd -Query $query)
$totalHours = $items | Measure-Object -property Effort -Sum;

Write-Output "## By Type of Work ##" 
foreach($item in $items) 
{
    Write-Output "$($item.TaskCode) $($item.Effort.ToString('0.##'))h ($([Math]::Round($item.Effort / $totalHours.Sum * 100, 1))%)";
}
Write-Output "Total $($totalHours.Sum.ToString('0.##'))h";
Write-Output "" 



###################################################
#### DevOps Stories and Bugs Statistics ###########
###################################################

if($iteration)
{
    $fromWithTolerance = $from.AddDays($tolerance)
    $toWithTolerance = $to.AddDays($tolerance)
    # get number of new and open userstories and storypoints at begin of sprint
    $startStories = Get-Items "Select [System.Id], [System.State] From WorkItems Where [System.WorkItemType] = 'User Story' and [System.State] IN ('New', 'Open') and [System.TeamProject] = '${devOpsProject}' AND [System.IterationPath] under '${devOpsProject}\${iteration}' ASOF '$($fromWithTolerance.ToString('yyyy-MM-dd HH:mm'))' " $fromWithTolerance
    $startStoryPoints = Get-StoryPoints $startStories

    # get number of new and open bugs and storypoints at begin of sprint
    $startBugs = Get-Items "Select [System.Id], [System.State] From WorkItems Where [System.WorkItemType] = 'Bug' and [System.State] IN ('New', 'Open') and [System.TeamProject] = '${devOpsProject}' AND [System.IterationPath] under '${devOpsProject}\${iteration}' ASOF '$($fromWithTolerance.ToString('yyyy-MM-dd HH:mm'))' " $fromWithTolerance
    $startBugStoryPoints = Get-StoryPoints $startBugs

    # get number of resolved and closed userstories and storypoints at end of sprint
    $endStories = Get-Items "Select [System.Id], [System.State] From WorkItems Where [System.WorkItemType] = 'User Story' and [System.State] IN ('Resolved', 'Closed') and [System.TeamProject] = '${devOpsProject}' AND [System.IterationPath] under '${devOpsProject}\${iteration}' ASOF '$($toWithTolerance.ToString('yyyy-MM-dd HH:mm'))' " $toWithTolerance
    $endStoryPoints = Get-StoryPoints $endStories

    # get number of resolved and closed bugs at end of sprint
    $endBugs = Get-Items "Select [System.Id], [System.State] From WorkItems Where [System.WorkItemType] = 'Bug' and [System.State] IN ('Resolved', 'Closed') and [System.TeamProject] = '${devOpsProject}' AND [System.IterationPath] under '${devOpsProject}\${iteration}' ASOF '$($toWithTolerance.ToString('yyyy-MM-dd HH:mm'))' " $toWithTolerance


    $startStoryIds = $startStories.value | Select-Object -ExpandProperty Id
    if($startStoryIds -eq $Null) { $startStoryIds = @() }
    $endStoryIds = $endStories.value | Select-Object -ExpandProperty Id
    if($endStoryIds -eq $Null) { $endStoryIds = @() }
    $startBugIds = $startBugs.value | Select-Object -ExpandProperty Id
    if($startBugIds -eq $Null) { $startBugIds = @() }
    $endBugIds = $endBugs.value | Select-Object -ExpandProperty Id
    if($endBugIds -eq $Null) { $endBugIds = @() }

    # whole iteration stable stories
    $wholeiterationStoryIds = $startStoryIds | ?{$endStoryIds -contains $_}
    $wholeiterationStories = Get-ItemsByIds $wholeiterationStoryIds $from
    $wholeiterationStoryPoints = Get-StoryPoints $wholeiterationStories

    # added stories and storypoints
    $addedStoryIds = Substract-List $endStoryIds $startStoryIds
    $addedItems = Get-ItemsByIds $addedStoryIds $to
    $addedStoryPoints = Get-StoryPoints $addedItems

    # added bugs
    $addedBugIds = Substract-List $endBugIds $startBugIds

    # removed stories and storypoints
    $removedStoryIds = Substract-List $startStoryIds $endStoryIds
    $removedItems = Get-ItemsByIds $removedStoryIds $from
    $removedStoryPoints = Get-StoryPoints $removedItems

    # removed bugs
    $removedBugIds = Substract-List $startBugIds $endBugIds

    # whole iteration stable bugs
    $wholeiterationBugIds = $startBugIds | ?{$endBugIds -contains $_}
    $wholeiterationBugs = Get-ItemsByIds $wholeiterationBugIds $from
    $wholeiterationBugStoryPoints = Get-StoryPoints $wholeiterationBugs

    Write-Output "## Summary ##" 
    Write-Output "In Sprint **${iteration}** ($($from.ToUniversalTime().ToString('dd.MM.yyyy')) - $($to.ToUniversalTime().ToString('dd.MM.yyyy'))) we **spent $([Math]::Round($totalHours.Sum / 8, 1))** PT to **implement $($endStories.Count) Stories** and **fix $($endBugs.Count) issues**."
    Write-Output "" 

    Write-Output "## Details ##" 
    Write-Output "Stories: $($startStories.Count) +$($addedStoryIds.Count) -$($removedStoryIds.Count) =$($endStories.Count)";
    Write-Output " of those where $($wholeiterationStories.Count) UserStories having ${wholeiterationStoryPoints} StoryPoints stable in whole iteration.";
    Write-Output "Bugs: $($startBugs.Count) +$($addedBugIds.Count) -$($removedBugIds.Count) =$($endBugIds.Count)";
    Write-Output " of those where $($wholeiterationBugs.Count) Bugs having ${wholeiterationBugStoryPoints} StoryPoints stable in whole iteration.";
    Write-Output "Storypoints: $($startStoryPoints + $startBugStoryPoints) +$($addedStoryPoints) -$($removedStoryPoints) =$($endStoryPoints)";
    Write-Output "" 

    Write-Output "## Velocity ##" 
    $velocityPlanned = $totalHours.Sum / ($wholeiterationStoryPoints + $wholeiterationBugStoryPoints)
    $velocity = $totalHours.Sum / $endStoryPoints
    Write-Output "A planed StoryPoint that got resolved or closed translates to an effort of $([Math]::Round($velocityPlanned, 1)) hours ($($totalHours.Sum) / $($wholeiterationStoryPoints + $wholeiterationBugStoryPoints))."
    Write-Output "A StoryPoint that got resolved or closed translates to an effort of $([Math]::Round($velocity, 1)) hours ($($totalHours.Sum) / ${endStoryPoints})."
}
#Write-Output "Started with $($startIds -join ",")"
#Write-Output "Added $($addedIds -join ",")"
#Write-Output "Removed $($removedIds -join ",")"
#Write-Output "Ended with $($endIds -join ",")"