1

I am working on a analytics query in Azure DevOps Server 2019 that will be set up as a data feed into PowerBI Desktop. I need to know who updated a new custom field on a work item, which may or may not be the last change made, and what that update is. I've tried using the following:

  • ChangedBy Works only if the last change is the one needed
  • History Contains [field name] doesn't work at all

Is there a way to query for a specific history entry that captures the update of a field and who made it in DevOps Server, or will it have to be written using the API?

Jenny
  • 11
  • 2
  • Hi Did you get a chance to try out below rest api. How did it go? – Levi Lu-MSFT Jul 23 '20 at 09:20
  • Hi, thanks for asking. I'm struggling a bit as I'm a BA, not a dev, so I'm trying to learn enough about REST API's and how to correctly write an Advanced Editor query to figure this out. – Jenny Jul 24 '20 at 18:02

1 Answers1

1

You can use the rest api to get the historical updates of a workitem. Below rest api will get the detailed information of all the updates of a workitem:

GET https://{instance}/{collection}/{project}/_apis/wit/workItems/{id}/updates?api-version=5.0

You can call above rest api in PowerBI query to get the updates historical data. For example example:

In the PowerBI Desktop go to Get Data->Blank Query->Advanced Editor :

let
    Source = Json.Document(Web.Contents("https://{instance}/{collection}/{project}/_apis/wit/workItems/369/updates?api-version=5.0")),
    value = Source[value]
in
    value

To get the changed data of a specific historical update, you can use get update rest api:

You will to call the first rest api to list all the updateNumbers and use below:

GET https://{instance}/{collection}/{project}/_apis/wit/workItems/{id}/updates/{updateNumber}?api-version=5.0

See this blog for more information.

You can also get data source from the sql server of your azure devops server and query the historical update using sql. Check this document for more information.

Levi Lu-MSFT
  • 27,483
  • 2
  • 31
  • 43