AZURE: Querying Azure DevOps for PR Details

AZURE: Querying Azure DevOps for PR Details.ps1

# ============================
# CONFIG
# ============================
$Org      = "your-org"
$Project  = "your-project"
$RepoId   = "your-repo-id"
$PAT      = "your-personal-access-token"

# Filters (optional)
$FilterCreatedBy = ""          # e.g. "John Doe"
$FilterStatus    = "active"    # active | completed | abandoned | all
$FilterFromDate  = ""          # e.g. "2024-01-01"
$FilterToDate    = ""          # e.g. "2024-12-31"

# Output CSV
$CsvPath = "PR_Report.csv"

# ============================
# AUTH
# ============================
$Base64PAT = [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes(":$PAT"))
$Headers = @{ Authorization = "Basic $Base64PAT" }

# ============================
# FUNCTION: Invoke REST
# ============================
function Invoke-AdoGet {
    param([string]$Url)
    return Invoke-RestMethod -Uri $Url -Headers $Headers -Method Get
}

# ============================
# 1. Get PRs (with status filter)
# ============================
$prUrl = "https://dev.azure.com/$Org/$Project/_apis/git/repositories/$RepoId/pullrequests?searchCriteria.status=$FilterStatus&api-version=7.1-preview.1"
$prs = Invoke-AdoGet $prUrl

# Apply user/date filters
$filteredPrs = $prs.value | Where-Object {
    ($FilterCreatedBy -eq "" -or $_.createdBy.displayName -eq $FilterCreatedBy) -and
    ($FilterFromDate -eq "" -or ([DateTime]$_.creationDate -ge [DateTime]$FilterFromDate)) -and
    ($FilterToDate   -eq "" -or ([DateTime]$_.creationDate -le [DateTime]$FilterToDate))
}

# ============================
# RESULTS COLLECTION
# ============================
$results = @()

foreach ($pr in $filteredPrs) {

    $prId = $pr.pullRequestId
    $creator = $pr.createdBy.displayName

    Write-Host "`n=== PR #$prId by $creator ===" -ForegroundColor Cyan

    # ============================
    # 2. Get iterations
    # ============================
    $iterUrl = "https://dev.azure.com/$Org/$Project/_apis/git/repositories/$RepoId/pullRequests/$prId/iterations?api-version=7.1-preview.1"
    $iterations = Invoke-AdoGet $iterUrl
    $latestIteration = $iterations.value[-1].id

    # ============================
    # 3. Get file changes
    # ============================
    $changesUrl = "https://dev.azure.com/$Org/$Project/_apis/git/repositories/$RepoId/pullRequests/$prId/iterations/$latestIteration/changes?api-version=7.1-preview.1"
    $changes = Invoke-AdoGet $changesUrl

    $files = $changes.changes |
        Where-Object { $_.item -and $_.item.path } |
        Select-Object -ExpandProperty item |
        Select-Object -ExpandProperty path -Unique

    # ============================
    # 4. Get associated work items
    # ============================
    $wiUrl = "https://dev.azure.com/$Org/$Project/_apis/git/repositories/$RepoId/pullRequests/$prId/workitems?api-version=7.1-preview.1"
    $workItems = Invoke-AdoGet $wiUrl

    $storyTitles = @()
    $storyPointsList = @()
    $iterationPaths = @()
    $wiIds = $workItems.value.id -join "; "

    foreach ($wi in $workItems.value) {

        # Fetch full work item details
        $wiDetailUrl = "https://dev.azure.com/$Org/_apis/wit/workitems/$($wi.id)?api-version=7.1-preview.3"
        $wiDetail = Invoke-AdoGet $wiDetailUrl

        $wiType = $wiDetail.fields.'System.WorkItemType'

        # ----------------------------
        # CASE 1: Direct Story / PBI
        # ----------------------------
        if ($wiType -eq "User Story" -or $wiType -eq "Product Backlog Item") {

            $storyTitles += $wiDetail.fields.'System.Title'
            $storyPointsList += $wiDetail.fields.'Microsoft.VSTS.Scheduling.StoryPoints'
            $iterationPaths += $wiDetail.fields.'System.IterationPath'
        }

        # ----------------------------
        # CASE 2: Task → Parent Story
        # ----------------------------
        elseif ($wiType -eq "Task") {

            if ($wiDetail.relations) {
                $parent = $wiDetail.relations |
                    Where-Object { $_.rel -eq "System.LinkTypes.Hierarchy-Reverse" }

                if ($parent) {
                    $parentId = ($parent.url -split "/")[-1]
                    $parentUrl = "https://dev.azure.com/$Org/_apis/wit/workitems/$parentId?api-version=7.1-preview.3"
                    $parentDetail = Invoke-AdoGet $parentUrl

                    $storyTitles += $parentDetail.fields.'System.Title'
                    $storyPointsList += $parentDetail.fields.'Microsoft.VSTS.Scheduling.StoryPoints'
                    $iterationPaths += $parentDetail.fields.'System.IterationPath'
                }
            }
        }
    }

    $storyTitles   = $storyTitles -join "; "
    $storyPoints   = $storyPointsList -join "; "
    $iterationPath = $iterationPaths -join "; "

    # ============================
    # 5. Get approvers
    # ============================
    $reviewersUrl = "https://dev.azure.com/$Org/$Project/_apis/git/repositories/$RepoId/pullRequests/$prId/reviewers?api-version=7.1-preview.1"
    $reviewers = Invoke-AdoGet $reviewersUrl

    $approvers = $reviewers.value |
        Where-Object { $_.vote -eq 10 -or $_.vote -eq 5 } |
        Select-Object -ExpandProperty displayName -Unique

    $approverList = $approvers -join "; "

    # ============================
    # 6. Add to results
    # ============================
    $results += [PSCustomObject]@{
        PR_ID        = $prId
        Title        = $pr.title
        Creator      = $creator
        Status       = $pr.status
        CreatedDate  = $pr.creationDate
        FilesChanged = ($files -join "; ")
        WorkItems    = $wiIds
        StoryTitles  = $storyTitles
        StoryPoints  = $storyPoints
        IterationPath = $iterationPath
        Approvers    = $approverList
    }
}

# ============================
# 7. Export to CSV
# ============================
$results | Export-Csv -Path $CsvPath -NoTypeInformation -Encoding UTF8

Write-Host "`nCSV exported to: $CsvPath" -ForegroundColor Green