azure-devopsazure-devops-rest-apiazure-boards

Pulling full work item information from a single DevOps API query


We have a simple query in Azure DevOps that looks for all work items in a certain iteration path and, when run in the web GUI, returns System.Id, System.WorkItemType, System.Title, System.AssignedTo, System.Tags, and System.Parent. By using API call of this form:

https://dev.azure.com///_apis/wit/wiql/<query_id>

I can get a list of the columns returned by the query and links to each work item. The links do not contain the information from the columns. Presumably, I am supposed to take each work item link and form a WIQL query that will return the information that I want. Normally not a problem (excepting my unfamiliarity with WIQL), but we are pulling this information into Azure Data Factory to be committed to an SQL database and be associated with work information from other sources. ADF can pull from a Rest API to a database (I've seen, but am still learning about...), but it seems like it would be best (or perhaps only possible) to do this with a single query, not a set of queries based on the results of yet an initial query.

So, my question: Is there a single API call for Azure DevOps that will, for each work item, return the work item information associated with the query? That is, I'd like a query that will actually rather than tell me the Id, WorkItemType, etc for each work item, not a loopable listing that has to be fed to another query to get the information we want.


Solution

  • I am afraid that only one rest api can't return the work item information in the query.

    Through a rest api you can get query settings(work item fields) and work item ids, you need to combine with another API to get work item information.

    Here is my PowerShell example:

    $token = "PAT"
    
    $url=" https://dev.azure.com/{OrganizationName}/{ProjectName}/{Team}/_apis/wit/wiql/{QueryId}?api-version=5.1"
    
    $token = [System.Convert]::ToBase64String([System.Text.Encoding]::ASCII.GetBytes(":$($token)"))
    
    
    
    $response = Invoke-RestMethod -Uri $url -Headers @{Authorization = "Basic $token"} -Method Get -ContentType application/json
    
    
    
    $IDS = $response.workItems.id  -join ',' 
    $Fieldnames = $response.columns.referenceName -join ',' 
    
    
    
    echo $IDs
    echo $Fieldnames
    
    
    $url1="https://dev.azure.com/{OrganizationName}/_apis/wit/workitems?ids=$IDs&fields=$Fieldnames&api-version=6.0"
    
    $response2 = Invoke-RestMethod -Uri $url1 -Headers @{Authorization = "Basic $token"} -Method Get 
    
    
    echo $($response2 | ConvertTo-Json -Depth 100)
    

    Explanation:

    The First Rest API: Wiql - Get is used to get the Query Settings and IDS. We can get the ids and fields from the response and form a string.

    The Second Rest API: Work Items - List is used to get the work item information, you could add fields and ids in the URL, then you could get the work item with the target fields.

    Result:

    enter image description here