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 we are going to look into a workaround that can help to achieve this objective of finding work items associated with PRs
Solution Approach:
We are going to use the Azure DevOps API to gather the required information
Fortunately, All the APIs we need are GET APIs so we do not require any tools other than a browser
Find the repository id:
First, we need to find the repository id using the following api -"Repositories - List"
GET https://dev.azure.com/{organization}/{project}/_apis/git/repositories?api-version=7.1-preview.1
In my case, it was the following url
just calling this url from the browser gave me the following result
{
"value": [
{
"id": "614a2760-1617-4e63-a15c-a9d809edaa1e",
"name": "Maddy_Test_Project",
"url": "https://dev.azure.com/thillaimadhavan/4e8d1831-0ff3-4269-8600-857bc0f39cb7/_apis/git/repositories/614a2760-1617-4e63-a15c-a9d809edaa1e",
"project": {
"id": "4e8d1831-0ff3-4269-8600-857bc0f39cb7",
"name": "Maddy_Test_Project",
"url": "https://dev.azure.com/thillaimadhavan/_apis/projects/4e8d1831-0ff3-4269-8600-857bc0f39cb7",
"state": "wellFormed",
"revision": 38,
"visibility": "private",
"lastUpdateTime": "2023-06-27T06:46:58.373Z"
},
"defaultBranch": "refs/heads/main",
"size": 2061,
"remoteUrl": "https://thillaimadhavan@dev.azure.com/thillaimadhavan/Maddy_Test_Project/_git/Maddy_Test_Project",
"sshUrl": "git@ssh.dev.azure.com:v3/thillaimadhavan/Maddy_Test_Project/Maddy_Test_Project",
"webUrl": "https://dev.azure.com/thillaimadhavan/Maddy_Test_Project/_git/Maddy_Test_Project",
"isDisabled": false,
"isInMaintenance": false
},
{
"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",
"url": "https://dev.azure.com/thillaimadhavan/_apis/projects/4e8d1831-0ff3-4269-8600-857bc0f39cb7",
"state": "wellFormed",
"revision": 38,
"visibility": "private",
"lastUpdateTime": "2023-06-27T06:46:58.373Z"
},
"defaultBranch": "refs/heads/main",
"size": 108157,
"remoteUrl": "https://thillaimadhavan@dev.azure.com/thillaimadhavan/Maddy_Test_Project/_git/pipelines-java",
"sshUrl": "git@ssh.dev.azure.com:v3/thillaimadhavan/Maddy_Test_Project/pipelines-java",
"webUrl": "https://dev.azure.com/thillaimadhavan/Maddy_Test_Project/_git/pipelines-java",
"isDisabled": false,
"isInMaintenance": false
},
{
"id": "64667810-0306-4123-b91b-e55ea7dd4c0a",
"name": "release-management-dashboard",
"url": "https://dev.azure.com/thillaimadhavan/4e8d1831-0ff3-4269-8600-857bc0f39cb7/_apis/git/repositories/64667810-0306-4123-b91b-e55ea7dd4c0a",
"project": {
"id": "4e8d1831-0ff3-4269-8600-857bc0f39cb7",
"name": "Maddy_Test_Project",
"url": "https://dev.azure.com/thillaimadhavan/_apis/projects/4e8d1831-0ff3-4269-8600-857bc0f39cb7",
"state": "wellFormed",
"revision": 38,
"visibility": "private",
"lastUpdateTime": "2023-06-27T06:46:58.373Z"
},
"defaultBranch": "refs/heads/main",
"size": 97528,
"remoteUrl": "https://thillaimadhavan@dev.azure.com/thillaimadhavan/Maddy_Test_Project/_git/release-management-dashboard",
"sshUrl": "git@ssh.dev.azure.com:v3/thillaimadhavan/Maddy_Test_Project/release-management-dashboard",
"webUrl": "https://dev.azure.com/thillaimadhavan/Maddy_Test_Project/_git/release-management-dashboard",
"isDisabled": false,
"isInMaintenance": false
},
{
"id": "3bbf2bf1-ffef-4e8e-acee-e5fcd1099167",
"name": "input-samples.git",
"url": "https://dev.azure.com/thillaimadhavan/4e8d1831-0ff3-4269-8600-857bc0f39cb7/_apis/git/repositories/3bbf2bf1-ffef-4e8e-acee-e5fcd1099167",
"project": {
"id": "4e8d1831-0ff3-4269-8600-857bc0f39cb7",
"name": "Maddy_Test_Project",
"url": "https://dev.azure.com/thillaimadhavan/_apis/projects/4e8d1831-0ff3-4269-8600-857bc0f39cb7",
"state": "wellFormed",
"revision": 38,
"visibility": "private",
"lastUpdateTime": "2023-06-27T06:46:58.373Z"
},
"defaultBranch": "refs/heads/master",
"size": 2325866,
"remoteUrl": "https://thillaimadhavan@dev.azure.com/thillaimadhavan/Maddy_Test_Project/_git/input-samples.git",
"sshUrl": "git@ssh.dev.azure.com:v3/thillaimadhavan/Maddy_Test_Project/input-samples.git",
"webUrl": "https://dev.azure.com/thillaimadhavan/Maddy_Test_Project/_git/input-samples.git",
"isDisabled": false,
"isInMaintenance": false
}
],
"count": 4
}
I am interested in the repo named "pipelines-java" , from this json we can find the id of the mentioned repo
"id": "5731c60c-4dd0-4eaa-809a-b4a9c8c73435"
Find the PRs:
We are going to find the PRs for a specified repository, we will apply filters to get only the PRs we are interested in. For example, we can filter for PRs which are completed on a particular branch
Let's use the api "Pull Requests - Get Pull Requests"
in my case, the URL was like below, we are filtering only "completed" PRs on target branch "main"
{
"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
}
use any JSON to Excel converter of your choice to convert the above JSON to a tabular format, once we have the data in tabular format, delete unwanted columns. we are mainly interested in pullRequestId
for the two PRs that I created for demo purposes, the pullRequestId are 14 and 16
Find the associated work items:
We are going to use the following APIs to find the work items associated with the PR
"Pull Request Work Items - List"
So for the pull request with Id 14 , I had to use the following url in browser
I got the following result that the associated work item id is 44
{
"count": 1,
"value": [
{
"id": "44",
"url": "https://dev.azure.com/thillaimadhavan/_apis/wit/workItems/44"
}
]
}
similarly for pull request id 16
I got the following result that the associated work item id is 45
{
"count": 1,
"value": [
{
"id": "45",
"url": "https://dev.azure.com/thillaimadhavan/_apis/wit/workItems/45"
}
]
}
Summary:
We have created an workaround for gathering information about PRs and their associated work items, next week we will see more in detail on how to organize the data we collected so far to perform a release management audit
Thanks for reading, Goodbye until next week!