Automating Azure DevOps Task Tracking: A Complete PowerShell Solution

Automating Azure DevOps Task Tracking: A Complete PowerShell Solution

param(

    [Parameter(Mandatory = $false)]

    [Alias('From')]

    [AllowNull()][object]$FromDate,

    [Parameter(Mandatory = $false)]

    [Alias('To')]

    [AllowNull()][object]$ToDate,

    [string]$OutputPath = ".",

    [string]$TeamsWebhookUrl = '',

    [string]$PatToken = ''

)

# Configuration

$organization = "organization"

$project = "project"

$iterationPath = "iterationPath"

$TeamsWebhookUrl = "TeamsWebhookUrl"

$PatToken=''

# Default to today if no dates provided, and last 40 days for FromDate

if (-not $ToDate) { $ToDate = Get-Date }

#if (-not $FromDate) { $FromDate = $ToDate.AddDays(-39) }  # 40 days including today

if (-not $FromDate) { $FromDate = $ToDate }  # 40 days including today

# Normalize to Date objects

if ($ToDate -is [string]) { $ToDate = Get-Date $ToDate }

if ($FromDate -is [string]) { $FromDate = Get-Date $FromDate }

# Use date-only bounds for today

$fromDay = $FromDate.Date

$toDay = $ToDate.Date.AddDays(1).AddSeconds(-1)

Write-Host "Collecting burned hours for tasks and bugs between $($fromDay.ToString('yyyy-MM-dd')) and $($toDay.ToString('yyyy-MM-dd'))" -ForegroundColor Cyan

# Acquire ADO access token via Azure CLI or use provided PAT token

if ($PatToken) {

    $basicToken = [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes(":$PatToken"))

    $authHeader = @{ Authorization = "Basic $basicToken" }

} else {

    try {

        $accessToken = az account get-access-token --resource https://app.vssps.visualstudio.com --query accessToken -o tsv

    } catch {

        Write-Error "Failed to get Azure access token. Ensure 'az' is installed and you are logged in (az login)."

        exit 1

    }

    $authHeader = @{ Authorization = "Bearer $accessToken" }

}

# WIQL to find tasks and bugs in the iteration

$wiql = @"

SELECT [System.Id]

FROM WorkItems

WHERE [System.WorkItemType] IN ('Task', 'Bug')

  AND [System.IterationPath] = '$iterationPath'

"@

$wiqlBody = @{ query = $wiql } | ConvertTo-Json

$wiqlUrl = "https://dev.azure.com/$organization/$project/_apis/wit/wiql?api-version=6.0"

$wiqlResult = Invoke-RestMethod -Method Post -Uri $wiqlUrl -Headers $authHeader -Body $wiqlBody -ContentType "application/json"

if (-not $wiqlResult.workItems -or $wiqlResult.workItems.Count -eq 0) {

    Write-Host "No tasks or bugs changed in the specified date range." -ForegroundColor Yellow

    return

}

$ids = $wiqlResult.workItems | Select-Object -ExpandProperty id

# Azure DevOps API has a limit of 200 work items per request, so batch the requests

$batchSize = 200

$allWorkItems = @()

for ($i = 0; $i -lt $ids.Count; $i += $batchSize) {

    $batchEnd = [Math]::Min($i + $batchSize - 1, $ids.Count - 1)

    $batchIds = $ids[$i..$batchEnd] -join ','

   

    Write-Host "Fetching batch $([Math]::Floor($i / $batchSize) + 1) of $([Math]::Ceiling($ids.Count / $batchSize)): items $($i + 1) to $($batchEnd + 1)" -ForegroundColor Gray

   

    $fieldsList = "System.Id,System.AssignedTo"

    $detailsUrl = "https://dev.azure.com/$organization/$project/_apis/wit/workitems?ids=$batchIds&fields=$fieldsList&api-version=6.0"

   

    try {

        $detailsResult = Invoke-RestMethod -Method Get -Uri $detailsUrl -Headers $authHeader

        if ($detailsResult -and $detailsResult.value) {

            $allWorkItems += $detailsResult.value

        }

    } catch {

        $errMsg = if ($_.Exception) { $_.Exception.Message } else { $_.ToString() }

        Write-Warning "Failed to fetch batch starting at item $($i + 1): $errMsg"

        continue

    }

}

if (-not $allWorkItems -or $allWorkItems.Count -eq 0) {

    Write-Host "No work item details returned from ADO." -ForegroundColor Yellow

    return

}

# Prepare output

$out = @()

$todayString = $fromDay.ToString('yyyy-MM-dd')

foreach ($item in $allWorkItems) {

    $workItemId = $item.id

    $assigned = if ($item.fields.'System.AssignedTo') { $item.fields.'System.AssignedTo'.displayName } else { 'Unassigned' }

    # Get revisions for this work item and compute CompletedWork delta within the day

    $revisionsUrl = "https://dev.azure.com/$organization/$project/_apis/wit/workItems/$workItemId/revisions?api-version=6.0"

    try {

    $revs = Invoke-RestMethod -Method Get -Uri $revisionsUrl -Headers $authHeader

    } catch {

        $errMsg = if ($_.Exception) { $_.Exception.Message } else { $_.ToString() }

        Write-Warning ("Failed to fetch revisions for {0}: {1}" -f $workItemId, $errMsg)

        continue

    }

    # Ensure revisions were returned

    if (-not $revs -or -not $revs.value) { continue }

    # To include closed tasks where CompletedWork was updated at close, compute delta between

    # the revision just before the window start (or earliest known) and the last revision up to window end.

    $allRevs = $revs.value | Where-Object { $_.fields -and $_.fields.'System.ChangedDate' } | Sort-Object { Get-Date $_.fields.'System.ChangedDate' }

    if (-not $allRevs -or $allRevs.Count -eq 0) { continue }

    # Find the last revision strictly before the window start (baseline)

    $baselineRev = $allRevs | Where-Object { (Get-Date $_.fields.'System.ChangedDate') -lt $fromDay } | Select-Object -Last 1

    # If none, use the first revision available as baseline

    if (-not $baselineRev) { $baselineRev = $allRevs[0] }

    # Find the last revision at or before the window end

    $endRev = $allRevs | Where-Object { (Get-Date $_.fields.'System.ChangedDate') -le $toDay } | Select-Object -Last 1

    if (-not $endRev) { continue }

    $firstCompleted = 0.0

    $lastCompleted = 0.0

    if ($baselineRev.fields.'Microsoft.VSTS.Scheduling.CompletedWork') { $firstCompleted = [double]$baselineRev.fields.'Microsoft.VSTS.Scheduling.CompletedWork' }

    if ($endRev.fields.'Microsoft.VSTS.Scheduling.CompletedWork') { $lastCompleted = [double]$endRev.fields.'Microsoft.VSTS.Scheduling.CompletedWork' }

    $burned = [math]::Round(($lastCompleted - $firstCompleted), 2)

    if ($burned -le 0) { continue }

    $out += [PSCustomObject]@{

        TodayDate = $todayString

        TaskId    = $workItemId

        BurnedHrs = $burned

        AssignedTo = $assigned

        LastChangedBy = if ($endRev.fields.'System.ChangedBy') { $endRev.fields.'System.ChangedBy'.displayName } else { '' }

    }

}

if ($out.Count -eq 0) {

    Write-Host "No burned hours recorded for tasks or bugs today." -ForegroundColor Yellow

    return

}

$csvFile = Join-Path $OutputPath ("today-burned-tasks-{0}.csv" -f (Get-Date -Format "yyyyMMddHHmmss"))

$outSorted = $out | Sort-Object -Property AssignedTo

$outSorted | Export-Csv -Path $csvFile -NoTypeInformation

Write-Host "Report generated: $csvFile" -ForegroundColor Green

 

# If a Teams incoming webhook URL was provided, post the report content (not the file)

if ($TeamsWebhookUrl -and $TeamsWebhookUrl.Trim() -ne '') {

    try {

        # Build a concise text message. Limit displayed rows to avoid extremely long messages.

        $maxLines = 50

        $lines = $outSorted | ForEach-Object { "Work Item $($_.TaskId): $($_.BurnedHrs) hrs - $($_.AssignedTo)" }

        if ($lines.Count -gt $maxLines) {

            $displayLines = $lines[0..($maxLines - 1)] + ("...and $($lines.Count - $maxLines) more tasks")

        } else {

            $displayLines = $lines

        }

    # Build a monospaced table for Teams: use a code block so formatting is preserved

        # Determine column widths based on data (with some caps)

        $maxTaskIdWidth = 10

        $maxBurnWidth = 8

        $maxAssignWidth = 30

        function Format-Row($tid, $burn, $assignee) {

            $t = $tid.ToString()

            if ($t.Length -gt $maxTaskIdWidth) { $t = $t.Substring(0,$maxTaskIdWidth) }

            $b = $burn.ToString()

            if ($b.Length -gt $maxBurnWidth) { $b = $b.Substring(0,$maxBurnWidth) }

            $a = $assignee.ToString()

            if ($a.Length -gt $maxAssignWidth) { $a = $a.Substring(0,$maxAssignWidth) }

            return ("{0,-$maxTaskIdWidth} | {1,-$maxBurnWidth} | {2,-$maxAssignWidth}" -f $t, $b, $a)

        }

        $tableLines = @()

        $tableLines += ("Burned hours report for $todayString")

        $tableLines += ("Total tasks: $($outSorted.Count)")

        $tableLines += ''

        $tableLines += ("{0,-$maxTaskIdWidth} | {1,-$maxBurnWidth} | {2,-$maxAssignWidth}" -f 'TaskId','Burned','AssignedTo')

        $tableLines += ('-' * ($maxTaskIdWidth + 3 + $maxBurnWidth + 3 + $maxAssignWidth))

        foreach ($line in $displayLines) {

            # displayLines formatted like: "Work Item 2404990: 3.5 hrs - Alice Smith"

            if ($line -match '^Work Item\s+(\d+):\s+([0-9.]+)\s+hrs\s+-\s+(.*)$') {

                $tid = $matches[1]

                $burn = $matches[2]

                $assignee = $matches[3]

                $tableLines += Format-Row $tid $burn $assignee

            } else {

                # fallback: show the raw line truncated to fit

                $raw = $line

                if ($raw.Length -gt ($maxTaskIdWidth + $maxBurnWidth + $maxAssignWidth + 10)) { $raw = $raw.Substring(0, ($maxTaskIdWidth + $maxBurnWidth + $maxAssignWidth + 7)) + '...' }

                $tableLines += $raw

            }

        }

        if ($lines.Count -gt $maxLines) { $tableLines += "...and $($lines.Count - $maxLines) more tasks" }

    # Wrap the table in a code block so Teams preserves monospace formatting

    $bodyText = @"

```

$($tableLines -join "`n")

```

"@

    $payload = @{ text = $bodyText } | ConvertTo-Json -Depth 4

        Invoke-RestMethod -Method Post -Uri $TeamsWebhookUrl -ContentType 'application/json' -Body $payload -ErrorAction Stop

        Write-Host "Posted report content to Teams webhook." -ForegroundColor Green

    } catch {

        $err = if ($_.Exception) { $_.Exception.Message } else { $_.ToString() }

        Write-Warning "Failed to post report to Teams webhook: $err"

    }

} else {

    Write-Host "No Teams webhook URL provided; skipping Teams post." -ForegroundColor Gray

}