Audit Pull Requests ... contd

January 08, 2024

In Azure DevOps, Pull requests provide a way to review the code before merging the changes to a branch. While submitting a PR for review it is possible to attach a work item to it. The attached work item can provide the complete background and context required for the reviewer. Also, for release management - from the work items, we can track back to the PRs

All these features are very handy,however,there is a caveat - Azure DevOps doesn't provide a way to query pull requests to find associated work items

So, in a previous article, we looked into a workaround to capture the information

today ,we will be auditing the information to perform a release management audit

Convert the PR information into Tabular format:

We used the api "Pull Requests - Get Pull Requests" to gather the PR-related information

GET https://dev.azure.com/{organization}/{project}/_apis/git/repositories/{repositoryId}/pullrequests?searchCriteria.creatorId={searchCriteria.creatorId}&searchCriteria.includeLinks={searchCriteria.includeLinks}&searchCriteria.maxTime={searchCriteria.maxTime}&searchCriteria.minTime={searchCriteria.minTime}&searchCriteria.queryTimeRangeType={searchCriteria.queryTimeRangeType}&searchCriteria.repositoryId={searchCriteria.repositoryId}&searchCriteria.reviewerId={searchCriteria.reviewerId}&searchCriteria.sourceRefName={searchCriteria.sourceRefName}&searchCriteria.sourceRepositoryId={searchCriteria.sourceRepositoryId}&searchCriteria.status={searchCriteria.status}&searchCriteria.targetRefName={searchCriteria.targetRefName}&maxCommentLength={maxCommentLength}&$skip={$skip}&$top={$top}&api-version=7.1-preview.1

in my case, the URL was like below, we are filtering only "completed" PRs on the target branch "main"

https://dev.azure.com/thillaimadhavan/Maddy_Test_Project/_apis/git/repositories/5731c60c-4dd0-4eaa-809a-b4a9c8c73435/pullrequests?searchCriteria.targetRefName=refs/heads/main&searchCriteria.status=completed&$top=100&api-version=7.1-preview.1

{
  "value": [
    {
      "repository": {
        "id": "5731c60c-4dd0-4eaa-809a-b4a9c8c73435",
        "name": "pipelines-java",
        "url": "https://dev.azure.com/thillaimadhavan/4e8d1831-0ff3-4269-8600-857bc0f39cb7/_apis/git/repositories/5731c60c-4dd0-4eaa-809a-b4a9c8c73435",
        "project": {
          "id": "4e8d1831-0ff3-4269-8600-857bc0f39cb7",
          "name": "Maddy_Test_Project",
          "state": "unchanged",
          "visibility": "unchanged",
          "lastUpdateTime": "0001-01-01T00:00:00"
        }
      },
      "pullRequestId": 16,
      "codeReviewId": 16,
      "status": "completed",
      "createdBy": {
        "displayName": "Thillai Madhavan Chelliah",
        "url": "https://spsprodcin1.vssps.visualstudio.com/Acea6439c-a370-4b66-99a5-037fe0f7735b/_apis/Identities/c755777f-833b-6701-b584-681f2bed96a2",
        "_links": {
          "avatar": {
            "href": "https://dev.azure.com/thillaimadhavan/_apis/GraphProfile/MemberAvatars/msa.Yzc1NTc3N2YtODMzYi03NzAxLWI1ODQtNjgxZjJiZWQ5NmEy"
          }
        },
        "id": "c755777f-833b-6701-b584-681f2bed96a2",
        "uniqueName": "dummy@gmail.com",
        "imageUrl": "https://dev.azure.com/thillaimadhavan/_api/_common/identityImage?id=c755777f-833b-6701-b584-681f2bed96a2",
        "descriptor": "msa.Yzc1NTc3N2YtODMzYi03NzAxLWI1ODQtNjgxZjJiZWQ5NmEy"
      },
      "creationDate": "2023-12-29T13:33:33.2166888Z",
      "closedDate": "2023-12-29T13:33:40.6854188Z",
      "title": "demo pr from another branch",
      "description": "demo pr to another branch",
      "sourceRefName": "refs/heads/demo_pr_branch_2",
      "targetRefName": "refs/heads/main",
      "mergeStatus": "succeeded",
      "isDraft": false,
      "mergeId": "21d0fcbb-f2e5-4c44-9ceb-ee391c467456",
      "lastMergeSourceCommit": {
        "commitId": "f52c10b3c4a42683f42ff2e08866718249814aa0",
        "url": "https://dev.azure.com/thillaimadhavan/4e8d1831-0ff3-4269-8600-857bc0f39cb7/_apis/git/repositories/5731c60c-4dd0-4eaa-809a-b4a9c8c73435/commits/f52c10b3c4a42683f42ff2e08866718249814aa0"
      },
      "lastMergeTargetCommit": {
        "commitId": "a1165e1b683c6aae95483c3e7a217c0c108f7f16",
        "url": "https://dev.azure.com/thillaimadhavan/4e8d1831-0ff3-4269-8600-857bc0f39cb7/_apis/git/repositories/5731c60c-4dd0-4eaa-809a-b4a9c8c73435/commits/a1165e1b683c6aae95483c3e7a217c0c108f7f16"
      },
      "lastMergeCommit": {
        "commitId": "a01c9e7d160c1276685ee84d54a0cc6faf1a5ccd",
        "url": "https://dev.azure.com/thillaimadhavan/4e8d1831-0ff3-4269-8600-857bc0f39cb7/_apis/git/repositories/5731c60c-4dd0-4eaa-809a-b4a9c8c73435/commits/a01c9e7d160c1276685ee84d54a0cc6faf1a5ccd"
      },
      "reviewers": [
        {
          "reviewerUrl": "https://dev.azure.com/thillaimadhavan/4e8d1831-0ff3-4269-8600-857bc0f39cb7/_apis/git/repositories/5731c60c-4dd0-4eaa-809a-b4a9c8c73435/pullRequests/16/reviewers/c755777f-833b-6701-b584-681f2bed96a2",
          "vote": 10,
          "hasDeclined": false,
          "isFlagged": false,
          "displayName": "Thillai Madhavan Chelliah",
          "url": "https://spsprodcin1.vssps.visualstudio.com/Acea6439c-a370-4b66-99a5-037fe0f7735b/_apis/Identities/c755777f-833b-6701-b584-681f2bed96a2",
          "_links": {
            "avatar": {
              "href": "https://dev.azure.com/thillaimadhavan/_apis/GraphProfile/MemberAvatars/msa.Yzc1NTc3N2YtODMzYi03NzAxLWI1ODQtNjgxZjJiZWQ5NmEy"
            }
          },
          "id": "c755777f-833b-6701-b584-681f2bed96a2",
          "uniqueName": "dummy@gmail.com",
          "imageUrl": "https://dev.azure.com/thillaimadhavan/_api/_common/identityImage?id=c755777f-833b-6701-b584-681f2bed96a2"
        }
      ],
      "url": "https://dev.azure.com/thillaimadhavan/4e8d1831-0ff3-4269-8600-857bc0f39cb7/_apis/git/repositories/5731c60c-4dd0-4eaa-809a-b4a9c8c73435/pullRequests/16",
      "completionOptions": {
        "mergeCommitMessage": "Merged PR 16: demo pr from another branch\n\ndemo pr to another branch\n\nRelated work items: #45",
        "squashMerge": true,
        "mergeStrategy": "squash",
        "autoCompleteIgnoreConfigIds": [
          
        ]
      },
      "supportsIterations": true,
      "completionQueueTime": "2023-12-29T13:33:40.1203155Z"
    },
    {
      "repository": {
        "id": "5731c60c-4dd0-4eaa-809a-b4a9c8c73435",
        "name": "pipelines-java",
        "url": "https://dev.azure.com/thillaimadhavan/4e8d1831-0ff3-4269-8600-857bc0f39cb7/_apis/git/repositories/5731c60c-4dd0-4eaa-809a-b4a9c8c73435",
        "project": {
          "id": "4e8d1831-0ff3-4269-8600-857bc0f39cb7",
          "name": "Maddy_Test_Project",
          "state": "unchanged",
          "visibility": "unchanged",
          "lastUpdateTime": "0001-01-01T00:00:00"
        }
      },
      "pullRequestId": 14,
      "codeReviewId": 14,
      "status": "completed",
      "createdBy": {
        "displayName": "Thillai Madhavan Chelliah",
        "url": "https://spsprodcin1.vssps.visualstudio.com/Acea6439c-a370-4b66-99a5-037fe0f7735b/_apis/Identities/c755777f-833b-6701-b584-681f2bed96a2",
        "_links": {
          "avatar": {
            "href": "https://dev.azure.com/thillaimadhavan/_apis/GraphProfile/MemberAvatars/msa.Yzc1NTc3N2YtODMzYi03NzAxLWI1ODQtNjgxZjJiZWQ5NmEy"
          }
        },
        "id": "c755777f-833b-6701-b584-681f2bed96a2",
        "uniqueName": "dummy@gmail.com",
        "imageUrl": "https://dev.azure.com/thillaimadhavan/_api/_common/identityImage?id=c755777f-833b-6701-b584-681f2bed96a2",
        "descriptor": "msa.Yzc1NTc3N2YtODMzYi03NzAxLWI1ODQtNjgxZjJiZWQ5NmEy"
      },
      "creationDate": "2023-12-29T13:28:20.4834833Z",
      "closedDate": "2023-12-29T13:28:28.9209163Z",
      "title": "demo pr commit 1",
      "description": "demo pr commit 1",
      "sourceRefName": "refs/heads/demo_query_pr_workitem",
      "targetRefName": "refs/heads/main",
      "mergeStatus": "succeeded",
      "isDraft": false,
      "mergeId": "9d4acc3a-99b8-4501-a2ae-b8687d23e812",
      "lastMergeSourceCommit": {
        "commitId": "e7fbc46758b7f19dc62dec314e1b2ff8568fdcce",
        "url": "https://dev.azure.com/thillaimadhavan/4e8d1831-0ff3-4269-8600-857bc0f39cb7/_apis/git/repositories/5731c60c-4dd0-4eaa-809a-b4a9c8c73435/commits/e7fbc46758b7f19dc62dec314e1b2ff8568fdcce"
      },
      "lastMergeTargetCommit": {
        "commitId": "b30700ed6277d536f520360d7791c8ef1938c1ea",
        "url": "https://dev.azure.com/thillaimadhavan/4e8d1831-0ff3-4269-8600-857bc0f39cb7/_apis/git/repositories/5731c60c-4dd0-4eaa-809a-b4a9c8c73435/commits/b30700ed6277d536f520360d7791c8ef1938c1ea"
      },
      "lastMergeCommit": {
        "commitId": "a1165e1b683c6aae95483c3e7a217c0c108f7f16",
        "url": "https://dev.azure.com/thillaimadhavan/4e8d1831-0ff3-4269-8600-857bc0f39cb7/_apis/git/repositories/5731c60c-4dd0-4eaa-809a-b4a9c8c73435/commits/a1165e1b683c6aae95483c3e7a217c0c108f7f16"
      },
      "reviewers": [
        {
          "reviewerUrl": "https://dev.azure.com/thillaimadhavan/4e8d1831-0ff3-4269-8600-857bc0f39cb7/_apis/git/repositories/5731c60c-4dd0-4eaa-809a-b4a9c8c73435/pullRequests/14/reviewers/c755777f-833b-6701-b584-681f2bed96a2",
          "vote": 10,
          "hasDeclined": false,
          "isFlagged": false,
          "displayName": "Thillai Madhavan Chelliah",
          "url": "https://spsprodcin1.vssps.visualstudio.com/Acea6439c-a370-4b66-99a5-037fe0f7735b/_apis/Identities/c755777f-833b-6701-b584-681f2bed96a2",
          "_links": {
            "avatar": {
              "href": "https://dev.azure.com/thillaimadhavan/_apis/GraphProfile/MemberAvatars/msa.Yzc1NTc3N2YtODMzYi03NzAxLWI1ODQtNjgxZjJiZWQ5NmEy"
            }
          },
          "id": "c755777f-833b-6701-b584-681f2bed96a2",
          "uniqueName": "dummy@gmail.com",
          "imageUrl": "https://dev.azure.com/thillaimadhavan/_api/_common/identityImage?id=c755777f-833b-6701-b584-681f2bed96a2"
        }
      ],
      "url": "https://dev.azure.com/thillaimadhavan/4e8d1831-0ff3-4269-8600-857bc0f39cb7/_apis/git/repositories/5731c60c-4dd0-4eaa-809a-b4a9c8c73435/pullRequests/14",
      "completionOptions": {
        "mergeCommitMessage": "Merged PR 14: demo pr commit 1\n\ndemo pr commit 1\n\nRelated work items: #44",
        "squashMerge": true,
        "mergeStrategy": "squash",
        "autoCompleteIgnoreConfigIds": [
          
        ]
      },
      "supportsIterations": true,
      "completionQueueTime": "2023-12-29T13:28:28.229145Z"
    }
  ],
  "count": 2
}

Let's use a JSON to Excel converter and the result will be like the below

Plugin the work item ids:

Now that we have the pull request ids in a table we can add another column and capture the associated work item ids which we retrieved using the API response

The table will look like the one below

Query the work items:

Now we can copy the values in the column workitemid to query the work items

to do so, click on "New Query" button under Boards → Queries

Then use the ID column and IN clause to query all the pul request associated work items

Summary:

Now that we have tabulated the data it's easy to perform release management related audits on the following lines

Which release item requires attention based on the work item state?
Whether all the PRs been properly associated with the work item?
Whether the work item state reflect the appropriate state based on PR status?

Thanks for reading, Goodbye until next week!