Skip to content

Instantly share code, notes, and snippets.

@SBajonczak
Created May 5, 2022 10:59
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save SBajonczak/7941a3e12a9b4e3046a6ebf98ea32154 to your computer and use it in GitHub Desktop.
Save SBajonczak/7941a3e12a9b4e3046a6ebf98ea32154 to your computer and use it in GitHub Desktop.
Get a report for TIcket work duration from azure devops
# Set the Selection Parameters here!!!!
$yearToSelect=2022
# PAT Generation documented here: https://docs.microsoft.com/en-us/azure/devops/organizations/accounts/use-personal-access-tokens-to-authenticate?view=azure-devops&tabs=Windows#create-a-pat
# Required Permissions: Analytics (READ):; Work Items (READ)
$AzureDevOpsPAT = "PLEASE INSERT PAT"
$OrganizationName = "realcoretfs"
$AzureDevOpsAuthenicationHeader = @{Authorization = 'Basic ' + [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes(":$($AzureDevOpsPAT)")) }
$AzureDevOpsAuthenicationHeader
$uriAccount = 'https://analytics.dev.azure.com/realcoretfs/RealCore%20Support/_odata/WorkItemRevisions?$filter=WorkItemType eq ''Ticket'' and year(CreatedDate) eq ' + $yearToSelect + ' &$select=WorkItemId,Title,State,ChangedDate&%orderby=CreatedDate desc'
$uriAccount
$body = Invoke-RestMethod -Uri $uriAccount -Method get -Headers $AzureDevOpsAuthenicationHeader
$result= New-Object -TypeName 'System.Collections.ArrayList';
$elementsToIgnore= New-Object -TypeName 'System.Collections.ArrayList';
$i=1
foreach($item in $body.value)
{
write-host $i "von" $body.value.count
$toIgnore = $elementsToIgnore | Where-Object {$_ -eq $item.WorkItemId }
if ($toIgnore -eq $null){
# Element not exists in result list
# Get created date
$createdDate= $body.value| Where-Object {$_.WorkItemId -eq $item.WorkItemId -and $_.State -eq "New" } | select -Property ChangedDate| Select-Object -First 1
# Active date
$activeDate= $body.value| Where-Object {$_.WorkItemId -eq $item.WorkItemId -and $_.State -eq "Active" } | select -Property ChangedDate| Select-Object -First 1
# Get Closed Date
$closedDate= $body.value | Where-Object {$_.WorkItemId -eq $item.WorkItemId -and $_.State -eq "Closed" } | select -Property ChangedDate | Select-Object -First 1
# Generate object
$newObject = New-Object -TypeName PSObject -Property @{
ID = $item.WorkItemId
Title = $item.Title
CreationDate= $createdDate.ChangedDate
ActiveDate= $activeDate.ChangedDate
ClosedDate= $closedDate.ChangedDate
DurationFromNewtoClose = New-TimeSpan -Start $createdDate.ChangedDate -End $closedDate.ChangedDate
}
# add to result
$x= $result.Add($newObject);
$y=$elementsToIgnore.Add($item.WorkItemId);
}else
{
Write-host "Element Proceeded"
}
$i++
}
$result | Export-Csv "Ticket.csv"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment