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!

Does it only limit 101 results per "page"? As in, is there no link to get the "next" page of 101 results if they exist in the return object? — nraduka, Jul 25 '19 at 03:20
a workaround could be a powershell script which checkout the ranges from 1 - 100 by `$top = 100`, `$skip=0` then 101 - 200 by `$top = 100`, `$skip=100` and so on.. if you do that until you get a response smaller than 100 you know you reached the end, could work or? — Mar Tin, Jul 25 '19 at 08:25
@MarTin could you possibly provide a powershell script to do this? I implemented this concept by just sending requests one after another while using $top=1000 instead and it works very well, thank you for that. I have very little powershell experience so I don't know how to automate it instead of just manually sending requests until I see a response <1000, and know that it would be great to use in the future! — Shep Sims, Jul 25 '19 at 13:16
@Shepsims I think starian chen-MSFT solve your issue with his answer, correct? :-) — Mar Tin, Jul 26 '19 at 04:28

score 6 · Accepted Answer · answered Jul 25 '19 at 14:54

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"

Needed to throw in a little &searchCriteria.status=all to get it to return, but other than that this is perfect, thanks a ton! — Shep Sims, Jul 25 '19 at 17:25

Shep Sims · Answer 2 · 2019-08-19T19:13:02.243

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.

score 2 · Answer 3 · edited Jun 20 '20 at 09:12

2

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.

edited Jun 20 '20 at 09:12

Community

1
1

answered Jul 25 '19 at 10:13

Mengdi Liang

17,577
2
28
35

I probably should've included this in the original question, and will add it now, but I am using a script almost identical to that already and it counts the instances returned but NOT the total count. Using the $top set to 10,000 should've done the trick since I know there are ~5,000 instances, but it seems that it still caps the returned items at a maximum of 1,000 (which IS better than 101, but still not helpful) no matter what. Any trick to get around this? – Shep Sims Jul 25 '19 at 12:50
Also, in the Postman console it also does not even show the count now, but instead simply says, "Responses larger than 1MB are not shown" – Shep Sims Jul 25 '19 at 12:50
1

@Shepsims workaound for it copy the url which returns more than 1 mb data from postman console and run it in browser address bar – Dev Jul 25 '19 at 13:09

score 0 · Answer 4 · answered Jan 07 '20 at 05:29

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

How to count all Pull Requests with Azure DevOps REST API

4 Answers4