Export latest discussion post for each and every work item in Azure DevOps

Question

I'm looking to bring back the latest discussion post in DevOps for each work item, along with the name of the person leaving the comment (or their email address) and the date that they left the comment. The project that I'm working on has had all sorts of people come and go over the past few weeks, and there's not really been handovers in most cases.

Step 1 I think I have covered based on a similar question on here...

$token = "PAT"

$url="https://dev.azure.com/{Org}/_apis/wit/wiql?api-version=5.1"

$token = [System.Convert]::ToBase64String([System.Text.Encoding]::ASCII.GetBytes(":$($token)"))

$JSON = @'
{
   "query": "Select [System.Id], [System.Title], [System.State] From WorkItems Where [System.WorkItemType]  <> ''"
}
'@

$response = Invoke-RestMethod -Uri $url -Headers @{Authorization = "Basic $token"} -Method Post -Body $JSON -ContentType application/json


ForEach( $workitemid in $response.workItems.id ) 
{

echo $workitemid

???

If I'm right, this snippet is essentially bringing into play work item ids which could then be used to find the discussion info - as mentioned before I'm looking for the discussion comment, date posted, name of individual posting, and for this just to be the latest discussion post per work item, but don't know if this is possible or how to finish this off. Could anyone provide some guidance around Step 2?

It doesn't look to be something that can be queried in DevOps itself, but assume it's information that can be surfaced somehow?

Answer 1

You can use Rest Api Comments:

$token = "<pat>"

$urlQueryItems="https://dev.azure.com/<org>/_apis/wit/wiql?api-version=5.1"
$urlWorkItemTemplate="https://dev.azure.com/<org>/_apis/wit/workItems/{workItemId}?api-version=5.1"
$urlGetCommentsTemplate = "https://dev.azure.com/<org>/{projectName}/_apis/wit/workItems/{workItemId}/comments?api-version=5.1-preview.3 "

$token = [System.Convert]::ToBase64String([System.Text.Encoding]::ASCII.GetBytes(":$($token)"))

$JSON = @'
{
   "query": "Select [System.Id], [System.Title], [System.State] From WorkItems Where [System.WorkItemType]  <> ''"
}
'@

$queryResponse = Invoke-RestMethod -Uri $urlQueryItems -Headers @{Authorization = "Basic $token"} -Method Post -Body $JSON -ContentType application/json


ForEach( $workitemid in $response.workItems.id ) 
{
    $urlWorkItem = $urlWorkItemTemplate -replace "{workItemId}", $workitemid

    $workItemResponse = Invoke-RestMethod -Uri $urlWorkItem -Headers @{Authorization = "Basic $token"} -Method Get -ContentType application/json

    $urlGetComments = $urlGetCommentsTemplate -replace "{workItemId}", $workitemid

    $urlGetComments = $urlGetComments -replace "{projectName}", $workItemResponse.fields.'System.TeamProject'
    
    $commentsResponse = Invoke-RestMethod -Uri $urlGetComments -Headers @{Authorization = "Basic $token"} -Method Get -ContentType application/json

    foreach($comment in $commentsResponse.comments)
    {
        echo $comment.createdDate $comment.createdBy.displayName $comment.text
    }
}