How to count all Pull Requests with Azure DevOps REST API

Question

I am trying to use the Azure DevOps REST API to count the total number of Pull Requests in our repository, and eventually use it to hopefully get some more useful info out of the git data.

I have tried using a GET request to the repository to return a list of Pull Requests, but the Azure API limits the responses to 101 per request. You can use the $top and $skip to change how many and which responses are returned, and $count to count the responses returned. This, however, still limits the results to 1,000 at the absolute maximum and returns the entire set of data contained within a PR, when I really just need to know the count of the instances in it, I don't need its data to be returned at all since this produces HUGE results on large repos.

Here is the GET request I am using:

https://dev.azure.com/{organization}/{project}/_apis/git/repositories/{repository}/pullrequests?$top=999&$count=true&searchCriteria.status=all&api-version=5.0

And here is the test script I am using to return the count of items, with Postman

var body = JSON.parse(responseBody);
tests[body.value.length + " Pull Requests in this Repository" ] = true;

This returns with a response count, as expected but not desired, of 101. Any tips and tricks are much appreciated!

Answer 1

Below code might help. Here i am extracting all the pull request against a project. Output will be pushed in an excel. Please follow comments inline

#+++Declaring all the parameters+++
Param(
   [string]$collectionurl = "https://dev.azure.com/<organizationname>",
   [string]$project = "<Projectname>",
   [string]$token = "<PAT>",
   [string]$Filename = '<local path>'
)

#++PassingToken as Base 64++
$auth = [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes(("{0}:{1}" -f "",$token)))

#++Generating Pull Request Base URL++
$pullRequestURL = "$collectionurl/$project/_apis/git/pullrequests?searchCriteria.status=all&&api-version=5.1"

#++Passing Respone in variable++           
$pullRequest = (Invoke-RestMethod -Uri $pullRequestURL -Method Get -Headers @{Authorization=("Basic {0}" -f $auth)}).value

#++Displaying result on screen( this step can be avoided )++
Write-Output $pullRequest
#++Counting Active vs Completed Request++
Write-host "Count of active pull request:" ($pullRequest | where({$_.status -eq 'active'})).count
Write-host "Count of completed pull request:" ($pullRequest | where({$_.status -eq 'completed'})).count

#++Temporary Object to store varaible values"
$output = @()
Write-Output $output

#++Extracting Values and storing in Object++
foreach ($request in $pullRequest) {
    $customObject = new-object PSObject -property @{
          "RepositoryName" = $request.repository.name
          "Status" = $request.status
          "PullRequestId" = $request.pullRequestId
          "SourceBranch" = $request.sourceRefName
          "TargetBranch" = $request.targetRefName
          "CreatedBy" = $request.createdBy.displayName
          "Reviewers" = $request.reviewers.displayName
          "CreatedDate" = $request.creationDate
          "ClosedDate" = $request.closedDate
          "MergeStrategy" = $request.completionOptions.mergeStrategy
        }
        $output += $customObject
        }
#++Generating excel out of Object++     
$output | Select `
            RepositoryName,
            Status,
            PullRequestId,
            SourceBranch,
            TargetBranch,
            CreatedBy,
            Reviewers,
            CreatedDate,
            ClosedDate,
            MergeStrategy | export-csv -Path $Filename -NoTypeInformation

Answer 2

I'm providing an answer in python as I believe this might be more useful to some than a powershell or postman script, as this is what I ended up using in my final implementation.

Hopefully it helps some others!

First here is the code...

def count_PRs():
   skip = 0
   count = 0
   while True:
        # Retrieve next Pull Requests based on pager value, or all remaining if less than value
        req_url=("https://%s/%s//%s/_apis/git/repositories/%s/pullrequests?$top=%s&$skip=%s&searchCriteria.status=all&api-version=%s" % (instance, organization, project, repository, pager, skip, api_version))
        response = requests.get(req_url, auth=(username, password)).json()

        # If no data returned, break out of loop, otherwise count items returned
        if len(response["value"]) == 0:
            break
        else:
            count += len(response["value"])
            skip += pager

   return count

Now an explanation of what's happening here so that you can UNDERSTAND it, not just use it blindly...

First, the request URL is created with variables that you should previously define.

req_url=("https://%s/%s//%s/_apis/git/repositories/%s/pullrequests?$top=%s&$skip=%s&searchCriteria.status=all&api-version=%s" % (instance, organization, project, repository, pager, skip, api_version))

These are: instance, organization, project, repository, pager, skip, api_version

Instance, organization, project, and repository are based on your use case so I can't help you there.

The "pager" value is how many items are returned per call, from my use I've noticed that the Azure API currently caps this at 1,000 but that may change in the future, I will try to update that if I notice it.

The "skip" value represents what pull requests have already been counted, so it starts at 0 and is then incremented by the pager value for each iteration through the loop so that it does not count the same PR multiple times.

The next line sends the request and saves the reply to the response variable:

response = requests.get(req_url, auth=(username, password)).json()

There is an authentication header which contains the username and password. I have previously set up a Personal Access Token through the Azure DevOps page, and that PAT is what you should use for your password here, this is significantly easier than trying to authenticate with OAuth2.0 and I would recommend doing this before anything else.

This section checks to see if you are still getting new pull requests in the response, and if you aren't, it breaks out of the while loop to return the count.

if len(response["value"]) == 0:
    break

If you do receive a response, this section counts the pull requests and adds it to the count, then increments the skip variable before moving onto the next iteration

else:
    count += len(response["value"])
    skip += pager

This took me forever to figure out and I really hope that it can help you in the future, if so, consider upvoting! Any questions you've got drop them in the comments and I'll try to help you out as soon as I can.

Answer 3

Simple sample code of powershell:

function GetPullRequest{
    param(
[string]$org,
[string]$project,
[string]$repo,
[string]$token
)
    $base64AuthInfo = [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes(("{0}:{1}" -f "test",$token)))
    $count=0
    $i=0
    do{
        $uri="https://dev.azure.com/$org/$project/_apis/git/repositories/$repo/pullRequests?api-version=5.0&`$top=100&`$skip=$i"
        $i+=100
        Write-Output $uri
        $result= Invoke-RestMethod -Method Get -Uri $Uri -ContentType "application/json" -Headers @{Authorization=("Basic {0}" -f $base64AuthInfo)} -Body $bodyJson
        Write-Output $result.Count
        $count+=$result.Count
        if($result.Count-lt 100){
            break;
        }
    }while($true)
    write-output "Finish. Total Pull Request count: $count";
}

GetPullRequest -org "your organization" -project "your teamproject" -repo "your repository" -token "your personal access token"

Answer 4

The Azure API limits the responses to 101 per request

This is the default limit which as designed. Since you may retrieving thousands of records in a single API request, and it paginated to only provide a certain number of results per call. So, you need to use top and skip to paginate through the rest.

And also, if you don't want to view its data which be returned at all since this produces HUGE results on repos, here has an script can help you achieve the count directly:

  var body = JSON.parse(responseBody);
  tests["Count: "  + body.value.length] = true;

Add this script into test, the result will displayed in the Test result after you execute the api, refer the pic below:

At this time, I will not be bothered by the large data result.

Hope this can give you some help.