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:
- PAT Token (Personal Access Token) — passed as a parameter, encoded as a Base64 Basic auth header.
- Azure CLI Token — uses
az account get-access-tokento fetch a Bearer token for thehttps://app.vssps.visualstudio.comresource. 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:
- Finds the last revision before the window start (baseline).
- Finds the last revision at or before the window end.
- Computes the delta of
Microsoft.VSTS.Scheduling.CompletedWorkbetween the two revisions. - 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
@CurrentIterationto avoid hardcoding the iteration path. - Multi-project support — parameterize
$organizationand$projectto loop across multiple ADO projects.
