Automating Azure DevOps Task Tracking: A Complete PowerShell Solution

Tags: Automation Azure Azure-CLI Azure-Devops DevOps PowerShell Scripting Teams WIQL

---

Overview

Tracking how many hours your team burns on tasks and bugs in Azure DevOps — day by day — is critical for sprint health monitoring and project delivery. Manually pulling this data is tedious and error-prone. This post walks through a complete PowerShell solution that:

• Queries Azure DevOps work items (Tasks and Bugs) via WIQL
• Computes burned hours delta for a given date range by comparing work item revisions
• Exports results as a CSV report
• Optionally posts a formatted Microsoft Teams notification via Incoming Webhook

---

How It Works

Authentication

The script supports two authentication modes:

1. PAT Token (Personal Access Token) — passed as a parameter, encoded as a Base64 Basic auth header.
2. Azure CLI Token — uses az account get-access-token to fetch a Bearer token for the https://app.vssps.visualstudio.com resource. This is ideal for automated or CI scenarios.

Work Item Query (WIQL)

A WIQL query fetches all Task and Bug work items from a configured iteration path. The Azure DevOps REST API (/wit/wiql) accepts a JSON body with the query string and returns a list of matching work item IDs.

Batch Fetching

The ADO API limits work item detail requests to 200 items per call. The script batches IDs accordingly, fetching only the fields needed (System.Id, System.AssignedTo).

Burned Hours Calculation (Revision Delta)

For each work item, the script fetches all revisions and:

1. Finds the last revision before the window start (baseline).
2. Finds the last revision at or before the window end.
3. Computes the delta of Microsoft.VSTS.Scheduling.CompletedWork between the two revisions.
4. Only includes items where burned hours are greater than 0.

This approach correctly captures hours logged on tasks that were closed during the window.

CSV Export

Results are sorted by AssignedTo and exported to a timestamped CSV file:

today-burned-tasks-<yyyyMMddHHmmss>.csv

Columns: TodayDate, TaskId, BurnedHrs, AssignedTo, LastChangedBy

Teams Notification

If a Teams Incoming Webhook URL is provided, the script posts a monospace-formatted table to the channel showing Task ID, burned hours, and assignee — capped at 50 rows to avoid message bloat.

---

Prerequisites

Requirement	Details
PowerShell	5.1+ or PowerShell 7+
Azure CLI	Required if not using PAT token (az login must be done)
ADO Access	Read access to Work Items and Revisions in the target project
Teams Webhook	Optional — Incoming Webhook configured on a Teams channel

---

Parameters

Parameter	Alias	Required	Default	Description
FromDate	From	No	Today	Start date for burned hours window
ToDate	To	No	Today	End date for burned hours window
OutputPath	—	No	. (current)	Directory for CSV output
TeamsWebhookUrl	—	No	''	Teams Incoming Webhook URL
PatToken	—	No	''	Azure DevOps PAT (if not using Azure CLI)

---

Configuration

Before running, update these variables inside the script to match your environment:

$organization    = "your-ado-organization"
$project         = "your-ado-project"
$iterationPath   = "your-iteration-path"          # e.g. "MyProject\Sprint 5"
$TeamsWebhookUrl = "https://outlook.office.com/webhook/..."   # optional

---

Full Script

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        = ''

# ── Date Defaults ─────────────────────────────────────────────────────────────
if (-not $ToDate)   { $ToDate   = Get-Date }
if (-not $FromDate) { $FromDate = $ToDate }   # Change to $ToDate.AddDays(-39) for a 40-day window

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

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

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

# ── Authentication ────────────────────────────────────────────────────────────
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 Query ────────────────────────────────────────────────────────────────
$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 found in the specified iteration." -ForegroundColor Yellow
    return
}

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

# ── Batch Fetch Work Item Details ─────────────────────────────────────────────
$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))" -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
}

# ── Compute Burned Hours per Work Item ────────────────────────────────────────
$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' }

    $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
    }

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

    $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 }

    # Baseline: last revision strictly before the window start (or first revision)
    $baselineRev = $allRevs | Where-Object { (Get-Date $_.fields.'System.ChangedDate') -lt $fromDay } | Select-Object -Last 1
    if (-not $baselineRev) { $baselineRev = $allRevs[0] }

    # End revision: last revision at or before 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 { '' }
    }
}

# ── Export CSV ────────────────────────────────────────────────────────────────
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

# ── Teams Notification ────────────────────────────────────────────────────────
if ($TeamsWebhookUrl -and $TeamsWebhookUrl.Trim() -ne '') {
    try {
        $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
        }

        $maxTaskIdWidth = 10
        $maxBurnWidth   = 8
        $maxAssignWidth = 30

        function Format-Row($tid, $burn, $assignee) {
            $t = $tid.ToString()
            $b = $burn.ToString()
            $a = $assignee.ToString()
            if ($t.Length -gt $maxTaskIdWidth) { $t = $t.Substring(0, $maxTaskIdWidth) }
            if ($b.Length -gt $maxBurnWidth)   { $b = $b.Substring(0, $maxBurnWidth) }
            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) {
            if ($line -match '^Work Item\s+(\d+):\s+([0-9.]+)\s+hrs\s+-\s+(.*)$') {
                $tableLines += Format-Row $matches[1] $matches[2] $matches[3]
            } else {
                $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"
        }

        $tableText = $tableLines -join "`n"
        $bodyText  = "```````n$tableText`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
}

---

Usage Examples

Run for today (default):

.\Get-BurnedHours.ps1

Run with a PAT token:

.\Get-BurnedHours.ps1 -PatToken "your-pat-token-here"

Run for a specific date:

.\Get-BurnedHours.ps1 -FromDate "2025-09-10" -ToDate "2025-09-10"

Run for a date range and post to Teams:

.\Get-BurnedHours.ps1 `
    -FromDate        "2025-09-01" `
    -ToDate          "2025-09-15" `
    -TeamsWebhookUrl "https://outlook.office.com/webhook/..." `
    -OutputPath      "C:\Reports"

Run for the last 40 days:

.\Get-BurnedHours.ps1 -FromDate (Get-Date).AddDays(-39)

---

Key Design Decisions

Why revision-based delta instead of ChangedDate filtering?

Filtering work items by ChangedDate in WIQL misses tasks that were closed with hours logged outside the window, but whose CompletedWork was updated incrementally. The revision delta approach is more reliable — it captures all hour entries within the window regardless of task state.

Why batch by 200?

The Azure DevOps REST API enforces a hard limit of 200 work items per workitems batch request. The script handles this transparently with a simple loop.

Why sort by AssignedTo in the CSV?

Sorting by assignee makes it easy to copy the CSV directly into sprint review spreadsheets or pivot tables grouped by team member.

---

Output Sample

TodayDate,TaskId,BurnedHrs,AssignedTo,LastChangedBy
2025-09-15,284719,2.5,Alice Smith,Alice Smith
2025-09-15,284723,1.0,Bob Jones,Bob Jones
2025-09-15,284730,3.0,Carol White,Carol White

---

Teams Message Sample

Burned hours report for 2025-09-15
Total tasks: 3

TaskId     | Burned   | AssignedTo
--------------------------------------------------
284719     | 2.5      | Alice Smith
284723     | 1.0      | Bob Jones
284730     | 3.0      | Carol White

---

Potential Enhancements

• Schedule via GitHub Actions — run the script nightly on a self-hosted runner and commit the CSV to a repo for historical tracking.
• Azure Automation Runbook — deploy as a runbook with a Managed Identity to avoid PAT management.
• Power BI Integration — point Power BI directly at the CSV output folder for live sprint burn-down visuals.
• Filter by Active Sprint — extend the WIQL to use @CurrentIteration to avoid hardcoding the iteration path.
• Multi-project support — parameterize $organization and $project to loop across multiple ADO projects.

---

References

• Azure DevOps REST API — Work Items
• WIQL Reference
• Work Item Revisions API
• Teams Incoming Webhooks
• az account get-access-token